|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2008 7:43:26 PM
Posts: 4,
Visits: 31
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 8:00:39 AM
Posts: 154,
Visits: 437
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2008 7:43:26 PM
Posts: 4,
Visits: 31
|
|
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;\
.
.
.
..
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 9/30/2008 4:58:28 PM
Posts: 69,
Visits: 83
|
|
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
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 9/30/2008 4:58:28 PM
Posts: 69,
Visits: 83
|
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2008 7:43:26 PM
Posts: 4,
Visits: 31
|
|
|
OK Ill take a closer look there. Thanks for your help and advice!
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2008 7:43:26 PM
Posts: 4,
Visits: 31
|
|
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!
|
|
|
|