OracleScript cannot execute Procedures?

OracleScript cannot execute Procedures?

Postby 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
cyborg0001
 
Posts: 2
Joined: Fri 18 Jan 2013 09:39

Re: OracleScript cannot execute Procedures?

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleScript cannot execute Procedures?

Postby 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
cyborg0001
 
Posts: 2
Joined: Fri 18 Jan 2013 09:39


Return to dotConnect for Oracle