All Forums Database
yamahaha 4 posts Joined 06/08
16 Jun 2008
Capturing Procedure output parameter in BTEQ

Hi,Im am capturing procedure output in BTEQ and writing a file with the two output values.On is a simple message that reports activity count or an SQL error messageThe other is an exit codeegbteq <<_END_.logon $IDW_LOGONCODE.sidetitles off.foldline.titledashes off.width 256.os rm $FILE.export file $FILEcall $TGTDB.$PROCNAME('','','','','',p_return_msg,p_status) ;.export reset_END_Howerver if i force the procedure to fail the output parameter values are no longer get writen to the output file by BTEQ even though I set the paramaters to a value in the exception block of the stored procedure.Can anyone explain/help me on this one please?

Adeel Chaudhry 773 posts Joined 04/08
17 Jun 2008

Hello,What is the definition of your stored-procedure? You have to use EXCEPTION HANDLERS and set the out variables parameters in it. Then it should show the values. With-out handlers if there is some error in the procedure it will fail and display the error message not the out variable.HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

yamahaha 4 posts Joined 06/08
17 Jun 2008

Hi thanks for the reply...Ive tried that but it still doesnt work:My exception block is as follows, you can see I set p_return_msg & p_statuswhich are both the output parameters. They only seem to appear in BTEQ when the procedure runs sucessfully. Im trying to achieve a standard output regardless ofwhether or not an exception is raised but BTEQ seems to behave differently!CREATE PROCEDURE do_stuff ( IN p_sequence integer, IN p_job_name varchar(256), IN p_task_name varchar(256), IN p_job_id integer, IN p_task_id integer, OUT p_return_msg varchar(256), OUT p_status integer )BEGIN.... --===================================================== -- Exceptions --===================================================== DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET v_sql_code = SQLCODE; SELECT ErrorText INTO :v_sql_error FROM dbc.ErrorMsgs WHERE ErrorCode = :v_sql_code; SET v_msgtext = 'Unhandled Exception in insert_all_zero_key_rows. '|| ' Step ' || CAST(v_step AS VARCHAR(64)) || ' SQL Error Code: ' || CAST(v_sql_code AS VARCHAR(10)) || ' - ' || v_sql_error; SET p_return_msg = v_msgtext; CALL [METABASE].WsWrkAudit('F', :p_job_name, :p_task_name, :p_sequence , :v_msgtext, :v_sql_code, :v_sql_error, :p_task_id, :p_job_id); SET p_status = -3; END;\.....

rgs 106 posts Joined 02/07
17 Jun 2008

That should work. What is probably happening is that you are getting an exception in your EXIT handler. You are doing a lot in that handler. So you need to figure out what that is about. You can put another handler in that handler and have that handler do something simple. Study this example:replace procedure spexit(out level integer, out errorcode integer )begin declare errlevel integer default 0; declare exit handler for sqlexception begin declare exit handler for sqlexception begin set errlevel = errlevel+1; set level = errlevel; set errorcode = SQLCODE; end; set errlevel = errlevel+1; set level = errlevel; set errorcode = SQLCODE; -- another table does not exist error insert into tddummy2 (6); end; -- table does not exist error insert into tddummy (5);end; BTEQ -- Enter your DBC/SQL request or BTEQ command: call spexit(level, errorcode); *** Procedure has been executed. *** Total elapsed time was 1 second. level errorcode----------- -----------2 3807

rgs 106 posts Joined 02/07
17 Jun 2008

I should have mentioned, when you have a handler to handle an exception and that handler has an error it’s called an “unhandled exception”, because the handler that was invoked could not handle the error (it caused its own problems). If that is the case then it looks for another handler at the next higher level or a handler inside the handler to handle the error. In your case, since you had no higher level handler (an outer compound block for example) the procedure had to exit with the error which reported this to BTEQ since your procedure had no way to handle the problem.

yamahaha 4 posts Joined 06/08
17 Jun 2008

OK Ill take a closer look there. Thanks for your help and advice!

yamahaha 4 posts Joined 06/08
17 Jun 2008

OK I did what you suggested and you were right, its actually a problem with the exception block itself! Stange because this is generated code from a third party tool which is slightly worrying!What is it they say about not being able to see the woods for the trees??? :)Thanks for your help!

26 Aug 2014

Hi Yamahaha, Can share your working solution for above problem. I'm facing the same problem. I want to write the out parameter in stored procedure EXCEPTION handler.

You must sign in to leave a comment.