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 message The other is an exit code eg bteq <<_END_ .logon $IDW_LOGONCODE .sidetitles off .foldline .titledashes off .width 256 .os rm $FILE .export file $FILE call $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.
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.
Ive tried that but it still doesnt work: My exception block is as follows, you can see I set p_return_msg & p_status which 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 of whether 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;\ . . . ..
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);
BTEQ -- Enter your DBC/SQL request or BTEQ command: call spexit(level, errorcode);
*** Procedure has been executed. *** Total elapsed time was 1 second.
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.
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!