Capturing Procedure output parameter in BTEQ

Database

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 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.

Can anyone explain/help me on this one please?

7 REPLIES
Teradata Employee

Re: Capturing Procedure output parameter in BTEQ

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

Re: Capturing Procedure output parameter in BTEQ

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_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;\
.
.
.
..
rgs
Enthusiast

Re: Capturing Procedure output parameter in BTEQ

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
Enthusiast

Re: Capturing Procedure output parameter in BTEQ

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.

Re: Capturing Procedure output parameter in BTEQ

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

Re: Capturing Procedure output parameter in BTEQ

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!
Enthusiast

Re: Capturing Procedure output parameter in BTEQ

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.