Page 1 of 1

OracleScript cannot execute Procedures?

Posted: Fri 18 Jan 2013 09:53
by cyborg0001
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

Re: OracleScript cannot execute Procedures?

Posted: Tue 22 Jan 2013 14:32
by Pinturiccio
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.

Re: OracleScript cannot execute Procedures?

Posted: Fri 25 Jan 2013 10:51
by cyborg0001
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