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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
@KevinAppleyard - Sorry, what does ASG stand for here? And which SQL statement cannot be changed?
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.