How we can call this function in Datastage.I have search the fourm and someone said we can call this in Oracle connector stage but can someone explain more how to call in Oracle Connector stage.
Last edited by prasson_ibm on Thu Mar 14, 2013 9:34 am, edited 1 time in total.
You can write PL/SQL code into after sql statement in oracle connector. Thus you can put your sql call directly there. Normally if your load fails, the after sql statement will be triggered anyway as stated in this technote : http://www-01.ibm.com/support/docview.w ... wg21577490
For the insert part you should be able to do it in before sql statement.
For the update part, you need first to gather info on total record count and error record count before calling your procedure.
It cannot be done in one job, you need to design your job to reject records on failure, then get the record count on input link and on reject link to pass them as parameters to you procedure.
For Insert Sql i am able to call function in before SQL successfully but how can i pass values to this function. One is value is parameter name P_TYPE and another one column value of BATCH_NO column i want to call these values into function.Can you please help me with this.
Last edited by prasson_ibm on Thu Mar 14, 2013 9:35 am, edited 2 times in total.
ORA_LOAD_TABLE: The following SQL statement failed: CALL DWH.INSERT('ABCD',:ORCHESTRATE.BATCH_ID)
ORA_LOAD_TABLE: The OCI function executeDirect returned status -1. Error code: 1,008, Error message: ORA-01008: not all variables bound. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 4,794)
Last edited by prasson_ibm on Thu Mar 14, 2013 9:37 am, edited 1 time in total.
Hi,
I am facing strange issue.I have added insert function into before seq statement of target oracle connector stage,but when i am running a job,this function got invoked but when i am calling this job through sequence job its is not invoked.
Do i need some setting in sequence job to call functions in before sql statement.
Last edited by prasson_ibm on Thu Mar 07, 2013 2:44 am, edited 1 time in total.
Stored_Procedure_0,0: Info: Ora_Function_using_Sp.Stored_Procedure_0: OCIAttrGet - Must be an anonymous pl/sql block being prepared.
Stored_Procedure_0,0: Error:
Stored_Procedure_0,0: Error occurred in call to ORPHCallActivePluginInitialize().
Last edited by prasson_ibm on Thu Mar 14, 2013 9:38 am, edited 1 time in total.