OracleCommand get Error

OracleCommand get Error

Postby lewis » Fri 08 Aug 2014 19:03

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
}
lewis
 
Posts: 13
Joined: Thu 13 Sep 2012 15:59

Re: OracleCommand get Error

Postby Pinturiccio » Mon 11 Aug 2014 12:43

lewis wrote:- 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.

lewis wrote:- 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_success_with_compilation_error.htm

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

Re: OracleCommand get Error

Postby lewis » Mon 11 Aug 2014 14:56

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.
lewis
 
Posts: 13
Joined: Thu 13 Sep 2012 15:59

Re: OracleCommand get Error

Postby Pinturiccio » Tue 12 Aug 2014 12:43

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

lewis wrote: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);
}
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle