1 Reply Latest reply: Jan 7, 2004 3:14 AM by 87083 RSS

    executing DDL

    66433
      hi. sounds strange, but i need to develop a Windows Form that to some extent replicates SQLPlus.

      that is, i need to allow a user to enter one or more ddl commands and then click an 'execute' button, and return results in a similar fashion to SQLPlus (ie. execute one command at a time, and displaying meaningful success/error messages, perhaps even indicating the problem statement in the same way as SQLPlus).

      can anyone offer me pointers to this? do i need to create a sophisticated command interpreter (eg. look for the "/" character and execute all ddl up to that point) or is their built-in support for this kind of situation?

      OracleCommand.ExecuteNonQuery() seems a bit limited for this purpose.

      thanks, any help appreciated

      kevin hector

        • 1. Re: executing DDL
          87083
          Hi,

          You can use OracleCommand.ExecuteNonQuery() to execute DDL

          Heres an example

          Sub foo()
          Dim oParam As New Oracle.DataAccess.Client.OracleParameter()
          Dim sStr As String = "create table mytab (id number, name varchar2(10))"
          oCommand.CommandText = "begin execute immediate :sStr; end;"
          oParam.Direction = ParameterDirection.Input
          oParam.DbType = DbType.String
          oParam.Value = sStr
          oCommand.Parameters.Add(oParam)
          oCommand.ExecuteNonQuery()
          End Sub;

          Make sure that Database USER has explicitly granted CREATE TABLE priviledge otherwise statement result into errors. EXECUTE IMMEDIATE needs explicit priviledges and not granted through ROLE.

          HTH

          Sameer