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
Oracle Stored Procedure Call with parameters
Moderators: chulett, rschirm, roy
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518