Oracle Stored Procedure User defined errors
Posted: Tue Jun 12, 2007 6:19 am
Hi,
can anybody tell me how to use the fields "Fatal errors:" and "Warnings:" on the tab "Error Codes" on "Inputs" of an ODBC Stage calling a stored procedure?
The procedure to call is:
create or replace procedure test_exception (i in number) as
begin
if i=0 then null;
elsif i=1 then raise_application_error (-20001,'YOU typed 1');
elsif i=2 then raise_application_error (-20002,'YOU typed 2');
else
null;
end if;
return;
end;
/
I want the job to abort if I pass 1 or 2 as a parameter to the procedure.
I tried almost any entry in the field "Fatal errors:" but the job log only shows a warning as follows:
TestReturnValue..Transformer_10.ExecuteStoredProcedure: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call XXXXXXX.TEST_EXCEPTION(?)}
SQLSTATE=S1000, DBMS.CODE=20001
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-20001: YOU typed 1
ORA-06512: at "XXXXXXX.TEST_EXCEPTION", line 4
ORA-06512: at line 1
START = 1
can anybody tell me how to use the fields "Fatal errors:" and "Warnings:" on the tab "Error Codes" on "Inputs" of an ODBC Stage calling a stored procedure?
The procedure to call is:
create or replace procedure test_exception (i in number) as
begin
if i=0 then null;
elsif i=1 then raise_application_error (-20001,'YOU typed 1');
elsif i=2 then raise_application_error (-20002,'YOU typed 2');
else
null;
end if;
return;
end;
/
I want the job to abort if I pass 1 or 2 as a parameter to the procedure.
I tried almost any entry in the field "Fatal errors:" but the job log only shows a warning as follows:
TestReturnValue..Transformer_10.ExecuteStoredProcedure: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call XXXXXXX.TEST_EXCEPTION(?)}
SQLSTATE=S1000, DBMS.CODE=20001
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-20001: YOU typed 1
ORA-06512: at "XXXXXXX.TEST_EXCEPTION", line 4
ORA-06512: at line 1
START = 1