2

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.

MatthewMartin
  • 32,326
  • 33
  • 105
  • 164
Kevin Appleyard
  • 149
  • 2
  • 8

1 Answers1

1

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Unfortunately, I have to use the sql provided by the ASG as is, so I have no opportunity to uee a better method. Given that, if I retrieve using get_line, and it contains the error message show above, if I then make a 2nd call to get_line, it returns an empty string, and the status param is 1. But I'm still not getting the actual message being sent, only the oracle hex/raw conversion error. – Kevin Appleyard Apr 25 '13 at 16:58
  • @KevinAppleyard - Sorry, what does `ASG` stand for here? And which SQL statement cannot be changed? – Justin Cave Apr 25 '13 at 17:01
  • Sorry, ASG is application support group, the guys providing me the SQL function to run against their DB. Scalability isnt really an issue here, as this will only be run a few hundred times a month. They have provided me with a chunk of SQL that performs a specific function. For a number of reasons (mostly political) this is just what I have to work with. – Kevin Appleyard Apr 25 '13 at 17:11
  • Turns out the problem was on the DB side, the Oracle guys have identified and put in a fix for it, now getting the correct response on the 1st attempt. Marking this answer as accepted as its all good advice, thankyou @Justin Cave. – Kevin Appleyard Apr 26 '13 at 10:41