Page 1 of 2
Need to call oracle functions in datastage.
Posted: Wed Mar 06, 2013 8:04 am
by prasson_ibm
Hi All,
I need to call below oracle functions into datastage once load completes or fails:-
Code: Select all
CALL DWH.TEST.INSERT(<<Name>>,?<<Seq>>)
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.
Posted: Wed Mar 06, 2013 8:49 am
by eph
Hi,
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
Eric
Posted: Wed Mar 06, 2013 8:51 am
by prasannakumarkk
What is the value you are passing to the function. You can make a call in after sql
Posted: Wed Mar 06, 2013 9:18 am
by prasson_ibm
Hi All.
thanks for your reply.
Eph:- But i need to call above function when load is started.I need to call another function like below once load is completed.
Code: Select all
CALL DWH.UPDATE(<<Tname>>,?<<seq>>,<<TotalCont>>,<<Errcnt>>)
I can not call above function in after sql of the load job..??
Prasanna:- Values passed to the functions are:-
SEquence No
Transtype
RecordCunt
ErrRecordCount
Posted: Wed Mar 06, 2013 9:25 am
by eph
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.
Eric
Posted: Wed Mar 06, 2013 9:46 am
by prasson_ibm
Hi,
Thanks Eph for your input.
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.
Posted: Wed Mar 06, 2013 9:52 am
by prasson_ibm
Hi,
I am trying to call like below :-
Code: Select all
CALL DWH.INSERT('#P_TYPE#',ORCHESTARTE.BATCHID)
But job is failing whith below fatal error:-
Code: Select all
ORA_LOAD_TABLE: The following SQL statement failed: CALL CALL DWH.INSERT('ABCD',ORCHESTARTE.BATCHID)code]
Can anyone please assist me.
Posted: Wed Mar 06, 2013 10:04 am
by prasannakumarkk
Didnt you get additional warning or info along with this warning in director. If there are any share it
Posted: Wed Mar 06, 2013 10:12 am
by prasson_ibm
Hi,
When i ran with hardcoded value on preSeq statement of oracle connector,it worked fine and row got inserted.
CALL DWH.INSERT('ABCD',1234)
when i tried to pass the parameter and column name i got below fatal Errors:-
Code: Select all
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)
Posted: Wed Mar 06, 2013 10:27 am
by prasannakumarkk
I don't know what the system source batch Will return . If it is a DS macro it has to be within hash .
Posted: Wed Mar 06, 2013 2:35 pm
by prasson_ibm
Hi,
Can someone focus on my issue?
Re: Need to call oracle functions in datastage.
Posted: Wed Mar 06, 2013 3:42 pm
by ray.wurlod
prasson_ibm wrote:I need to call below oracle functions into datastage once load completes or fails....
Why?
Why can't you replicate the logic using regular DataStage components?
Worst case, envelop the function in a stored procedure and invoke that.
Posted: Thu Mar 07, 2013 1:48 am
by prasson_ibm
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.
Posted: Thu Mar 07, 2013 2:11 am
by prasson_ibm
Hi Ray,
Thanks for your reply.I can not change these functions in datastage because it belong to different infrastructure team.
As you suggested i tried to call Insert function in Stored Procedure(Target) and passing values as columns but i am getting below error message:-
Code: Select all
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().
Posted: Thu Mar 07, 2013 2:33 am
by prasannakumarkk
Can you please uncheck generate SQL and post the SQL that was already generated.
What all the parameters you configured in parameter tab and what is the column name mapped and paramter marker passed?