CODEWITHSUNDEEP
.netoracle-databasedbms-output
Kevin Appleyard
Kevin Appleyard

Reputation: 149

Oracle dotnet DBMS output query

I am running some code in .net, using the oracleclient in ,net. The SQL code can output a number of responses using the DBMS_PUT_LINE function, which I then retrieve with a call to DBMS_GET_LINE. If the output is just a specific line of text, ie 'DBMS_OUTPUT.PUT_LINE('USER IS NOT AVAILABLE : PLEASE CONTACT SYSTEM ADMINISTRATOR');' Then the call to get_line works fine, and I get that text. However, if the call is to output the SQL error message, 'DBMS_OUTPUT.PUT_LINE(SQLERRM);' then I get the following error returned, 'ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error'

The really wierd thing, is if I then run exactly the same code a 2nd time (including a close and reconnect to the DB) then the call to get_line returns the actual error message being output.

My code essentially does the following:

Opens the DB connection Runs the SQL query with executeNonQuery Creates the output parameters like:

Dim anonymous_block = "begin dbms_output.get_line(:1, :2); end;"

aCmd.CommandText = anonymous_block
aCmd.Parameters.Add("1", OracleType.VarChar, 32000)
aCmd.Parameters("1").Direction = ParameterDirection.Output
aCmd.Parameters.Add("2", OracleType.Int32)
aCmd.Parameters("2").Direction = ParameterDirection.Output

Then runs another executeNonQuery to get the output.

then closes the db with .close()

But on the 2nd run, it gets the correct output. I there perhaps something I am not setting up correctly the 1st time?

Any thoughts? I can always just run the code twice, but that seems horribly inefficient.

Upvotes: 2

Views: 421

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

First off, designing an application to do meaningful interactions between the client and the server using dbms_output is a terrible approach. If your stored procedure needs to send information to the caller, that should be accomplished via OUT parameters or exceptions. Having a stored procedure catch the exception, attempt to write it to the dbms_output buffer, and then having the application attempt to read from the dbms_output buffer to determine if there was an exception is not a scalable approach to writing an application.

That said, if you are going to fetch data from the dbms_output buffer, you'll need to do so in a loop. In your specific case, the error stack contains multiple lines of data (there may be only one call to dbms_output.put_line but the text contains internal newline characters. You'll need to loop until the status (the second parameter) returns 1.

Upvotes: 1

Related Questions