OracleScript cannot execute Procedures?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cyborg0001
Posts: 2
Joined: Fri 18 Jan 2013 09:39

OracleScript cannot execute Procedures?

Post by cyborg0001 » Fri 18 Jan 2013 09:53

Hi,

I made my first try with the OracleScript Class. The scripts I want to execute are a mixture of Inserts, Packages, procedures, types, ... . At the moment I read the scripts from simple txt-files into C#.NET and run them with script.Execute(). This works quite fine for nearly every case. But some errors occur, which do not occur if I run the scripts with sql Plus:

1.) "set define off" throws an error. If I remove it in C#, it runs without errors and the result looks okay in my database. Is this always the case???

2.) if i have a skript looking like the following "execute myprocedure(param1,param2);" and try to run it with script.Execute(), I get an Ora00900. What am I doing wrong here??

thanks in advance for answers!

Best regrads
Cyb

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: OracleScript cannot execute Procedures?

Post by Pinturiccio » Tue 22 Jan 2013 14:32

cyborg0001 wrote:But some errors occur, which do not occur if I run the scripts with sql Plus
We don't support SQL*Plus commands.

"set define off" - must either be excluded manually, or processed in code.
"execute myprocedure(param1,param2);" - the execute command must be executed inside PL/SQL block. These situations can be processed using the SqlStatementExecute event.

The following example shows how this situation can be processed using the SqlStatementExecute event of the OracleScript class object. If we come across the statement

Code: Select all

static void Main(string[] args)
{
        OracleConnection conn = new OracleConnection("connection string");
        conn.Open();
        OracleScript script = new OracleScript("script text", conn);
            
        script.SqlStatementExecute += new SqlStatementExecuteEventHandler(script_SqlStatementExecute);
        script.Execute();
        conn.Close();
}

static void script_SqlStatementExecute(object sender, SqlStatementExecuteEventArgs e)
{
	//we skip executing Set commands
	if (e.SqlPlusStatementType == SqlPlusStatementType.Set)
        {
                e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
                return;
        }

	//we process the execute command, for it to be executed inside the PL/SQL block
        if (e.SqlPlusStatementType == SqlPlusStatementType.Execute)
        {
                var script=(OracleScript)sender;
                e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
                OracleCommand comm = new OracleCommand();
                comm.Connection = script.Connection;
                comm.CommandText = "begin " + e.Text.Remove(0, "execute".Length).Trim() + "; end;";
                comm.ExecuteNonQuery();
                comm.Dispose();
        }            
}
Please also note that extended PL/SQL commands must end with the new line character "\n". Then OracleScript understands when an extended PL/SQL command ends.

cyborg0001
Posts: 2
Joined: Fri 18 Jan 2013 09:39

Re: OracleScript cannot execute Procedures?

Post by cyborg0001 » Fri 25 Jan 2013 10:51

Hi Pinturiccio,

thank you very much for your reply. I was not aware of the SqlStatementExecuteEventHandler. After some minor modifications of your code it works exactly as I liked. Perfect!

Cyborg0001

Post Reply