Tags: below, database, display, format, mysql, oracle, output, parameters, procedure, sql, stored, storedprocedures
How to display output parameters in storedprocedures in front end?
4,334 words with 4 Comments; publish: Thu, 14 Feb 2008 19:15:00 GMT; (25077.15, « »)
hi all ,thank you for all the support.
Now i want your help .Here below i have given the general format of stored procedure we are using in our application . In procedure we are catching the exception and assigning to an output variable P_STATUS .
If any exception occurs ,How we display this caught exception from front end that means how we display the exception in front end .We are using VB.NET and VS 2005 and Oracle 9i for development.
CREATE OR REPLACE PROCEDURE SELECTINTERVALS
(
P_MINVAL INTEGER,
P_MAXVAL INTEGER,
P_INTERVAL INTEGER,
P_USERID VARCHAR2,
P_STATUS OUT VARCHAR2
)
AS
BEGIN
SELECT STATEMENT or INSERT STATEMENT
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_STATUS:=SQLERRM;
END;
If anybody knows, please help me
thanks & regards
http://oracle.itags.org/q_oracle_103050.html
All Comments
Leave a comment...
- 4 Comments
- You have the error in the var P_STATUS, you can view if is null there is no problem else the problem is in the var
CREATE OR REPLACE PROCEDURE SELECTINTERVALS
(
P_MINVAL INTEGER,
P_MAXVAL INTEGER,
P_INTERVAL INTEGER,
P_USERID VARCHAR2,
P_STATUS OUT VARCHAR2
)
AS
BEGIN
SELECT STATEMENT or INSERT STATEMENT
P_STATUS:= null;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_STATUS:=SQLERRM;
END;
You also can propagate the error
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_STATUS:=SQLERRM;
RAISE_APPLICATION_ERROR(-20011, SQLERRM);
END;
#1; Sun, 24 Feb 2008 00:20:00 GMT
- The approach illustrated with your sample code is not sound.
There are only two valid reasons for writing an exception handler in code.. any code. Not just PL/SQL.
1. You can fix the exception. Which means fixing it, and the suppressing it that the caller will never know that there were a problem.
2. You need to re-act to the exception, but you cannot fix it. Which means that after you have responded to the exception (by logging it, cleaning up, rolling back or whatever), you re-raise that exception.. as the caller MUST know that there was an error.
You are doing neither of this in your approach. Which is why it is questionable. Worse, you are attempting to return the exception as an output error. This does not make sense.
Why do you think that modern programming languages of today use exceptions and not return codes? Why would you want to use an old and clunky and error-prone method of error handling and management?
If an exception occurs, and you cannot fix it, then it must be raised all the way up to the call stack.. as each caller in turn needs to determine WHAT to do with the exception and HOW to respond to it.
Even up to the client app level - where the client can catch that exception and display an appropriate error message/response to the app user.
#2; Sun, 24 Feb 2008 00:22:00 GMT
- Both methods are wrong. Returning error/exit codes for the reasons I've stated above.
As for handling the exception... If you want to propagate the exception, then DO NOT CHANGE IT INTO A MEANINGLESS USER EXCEPTION!
This is useless:
WHEN OTHERS THEN
ROLLBACK;
P_STATUS:=SQLERRM;
RAISE_APPLICATION_ERROR(-20011, SQLERRM);
Why it is meaningless? Because the caller now has to parse the error message to determine just what the actual root exception was... as the root exception number has been replaced with a meaningless number.
This is meaningful and (almost) correct:
when OTHERS then
rollback;
raise;
-- re-raise the exception
PS. It makes better sense to trap the actual exception like no data found, duplicate PK violations, etc... it seldom makes sense to trap all other exceptions and then do something like a rollback.
#3; Sun, 24 Feb 2008 00:23:00 GMT
- I would tend to think that just let the exception be thrown back to the VB.Net code without writing the WHEN OTHERS (of course assuming that rollback is all that OP wants to do when error happens).
That will achieve the job of doing a rollback as well as fall comfortably into the catch() block of the try statement to handle the error at client side with complete error stack available (assuming that the statements are enclosed within a try ... catch() block).
Message was edited by:
Kamal Kishore
#4; Sun, 24 Feb 2008 00:24:00 GMT