Page 1 of 1
Oracle Stored Procedure Call with parameters
Posted: Thu Sep 26, 2002 7:22 am
by Su
Hi,
I have a job that takes records from a sequential file , does some basic transformations and then links to an OCI8 stage.
In the OCI stage I want to call an Oracle STored procedure that will insert into multiple tables.
I need to pass the the sequential file fields as parameters to this stored proc.
How can it be done? we are forced to specify a tablename in the OCI8 stage. What tablename do i give here? What do i set the updateaction to? user-defined sql?
thanks in advance
Posted: Thu Sep 26, 2002 7:36 am
by uneumann
Use ODBC-Stage with Update Action "Call stored procedure"
Regards,
Udo
Posted: Thu Sep 26, 2002 2:04 pm
by Su
Thanks
Posted: Wed Feb 05, 2003 1:18 pm
by fjgirante
yes.
But how can i abort my job if my procedure fails.
I explain.
I have a procedure that raise an application error if something goes wrong.
But the best i could do in my job, in that case, was to put a warning in the job log (JDAEDWSITLOADAS4..TR_EXEC.my_job: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call my_job(?,?,?,?)}
SQLSTATE=HY000, DBMS.CODE=20006
[DataStage][SQL Client][ODBC][MERANT][ODBC Oracle driver][Oracle]ORA-20006: my_message
ORA-01839: date not valid for month specified
ORA-06512: at "DW.my_job", line 181
ORA-06512: at line 1
p_cod_plataforma = "AS4"
p_month = "200211"
p_window = "36"
p_ctrl_load = 43755
)
I tried with the tab "Error Codes" of the ODBC stage. I did put the ORA-20006 (my code) in the Fatal Error box, but i only got an warning and the job succeds.
regards
Posted: Fri Feb 07, 2003 8:57 am
by Triton46
Only a DBA can kill a procedure.
Posted: Fri Feb 07, 2003 9:19 am
by fjgirante
I only want my job to abort if a called procedure raise an error...
Can i do that?
Posted: Fri Feb 07, 2003 3:43 pm
by ray.wurlod
You can detect it by obtaining the values of link variables on the link that supplies rows via the stored procedure. Do this on another output link, marked as handling rejects, with its counter set to 1 to abort on the first occurrence of a reject.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518