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
Oracle Stored Procedure User defined errors
Moderators: chulett, rschirm, roy
Re: Oracle Stored Procedure User defined errors
USER-DEFINED ERRORS :The lists should contain both database-specific errors expressed as the integer portion only as well as any user-defined errors that can be returned by the procedure example in your case ORA-20001, so just specify 20001.
Now you can use this value for Fatal errors or Warnings.
Fatal errors. Specify errors that should be treated as fatal. The stage checks the list of fatal errors first. If it finds one of the codes identified as fatal, the job aborts.
Warnings. Specify errors that should be treated as warnings. The stage checks the list of warnings only after it checks the list of fatal errors. If the stage finds any of the codes identified as a warning, the stage writes a log entry and processing continues.
in your case if you want to check for error code returned from procedure, which is user defined error message and then abort a job, you can use in-built utility by sdk called UtilityAbortToLog, for this case don't put anything in FATAL ERRORS, and put 20001 in WARNINGS.
Now you can use this value for Fatal errors or Warnings.
Fatal errors. Specify errors that should be treated as fatal. The stage checks the list of fatal errors first. If it finds one of the codes identified as fatal, the job aborts.
Warnings. Specify errors that should be treated as warnings. The stage checks the list of warnings only after it checks the list of fatal errors. If the stage finds any of the codes identified as a warning, the stage writes a log entry and processing continues.
in your case if you want to check for error code returned from procedure, which is user defined error message and then abort a job, you can use in-built utility by sdk called UtilityAbortToLog, for this case don't put anything in FATAL ERRORS, and put 20001 in WARNINGS.