OracleCommand get Error

[lewis]

hi, first it all, i will show the following code :


private void btnExecute_Click(object sender, EventArgs e)
{
try
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = this.oracleConnection;
oracleCommand.CommandType = CommandType.Text;
//oracleCommand.CommandText = this.textBox.Text;

oracleCommand.CommandText = "CREATE OR REPLACE FUNCTION SOME_FUNC_RETURNING_A_CURSOR RETURN SYS_REFCURSOR IS\n" +
" CSRLOCAL SYS_REFCURSOR;\n" +
" BEGIN\n" +
" OPEN CSRLOCAL FOR SELECT WHATEVER FROM WHEREVER;\n" +
" RETURN CSRLOCAL;\n" +
" END SOME_FUNC_RETURNING_A_CURSOR;\n";

oracleCommand.ExecuteNonQuery();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

So when execute this command the error dotconnect show me is :

---------------------------

---------------------------
Code 24344 Mesage ORA-24344: correcto con error de compilación
---------------------------
OK
---------------------------

However this error code was captured in the oracleConnection_Error event,

private void oracleConnection_Error(object sender, Devart.Data.Oracle.OracleConnectionErrorEventArgs e)
{
MessageBox.Show("Error : Code " + e.Code + " Mesage " + e.Message);
}

So my questions are

- This is the normal scenario in order to catch the executions?
- Why OracleConnectionErrorEventArgs object does not provide me the information about line, Offset as OracleScript and OracleException provide?
-It should be oracleCommand.ExecuteNonQuery(); generated a OracleException in order to get the error information in the
Catch (OracleException)
{
// here
}

[Pinturiccio]

- This is the normal scenario in order to catch the executions?

Yes, this is normal scenario. This is an Oracle approach to working with invalid objects.

- Why OracleConnectionErrorEventArgs object does not provide me the information about line, Offset as OracleScript and OracleException provide?

ORA-24344 error does not provide information about line and offset of the error. For more information, please refer to http://www.dba-oracle.com/t_ora_24344_s ... _error.htm

-It should be oracleCommand.ExecuteNonQuery(); generated a OracleException in order to get the error information in the
Catch (OracleException)
{
// here
}

The command text is "CREATE OR REPLACE FUNCTION...". As the result, the SOME_FUNC_RETURNING_A_CURSOR is successfully created. Though the created function is invalid, however, the command itself is considered successfully executed, and OracleException is not generated when executing this command. The 'ORA-24344' message is additionally generated. You can get this message using the Error event of the connection.

[lewis]

thanks for your answer, so is there any way knowing with this error code, and next doing something in order to get de object that failed its compilation?

the idea is having this object that failed go and get the errors.

[Pinturiccio]

the idea is having this object that failed go and get the errors.

However this error code was captured in the oracleConnection_Error event,

private void oracleConnection_Error(object sender, Devart.Data.Oracle.OracleConnectionErrorEventArgs e)
{
MessageBox.Show("Error : Code " + e.Code + " Mesage " + e.Message);
}

The first parameter "sender" of the event handler is the command that that created an invalid object. Thus, you could use the following code to display the command text that created an invalid object:

Code: Select all

private void oracleConnection_Error(object sender, Devart.Data.Oracle.OracleConnectionErrorEventArgs e)
{
    Console.WriteLine("Conn error: "+ e.Code + " Mesage " + e.Message);
    Console.WriteLine("An invalid object is created by the following SQL query:\n" + ((OracleCommand)sender).CommandText);
}