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.
You can use OracleCommand.ExecuteNonQuery() to execute DDL
Heres an example
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
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.