Need to call oracle functions in datastage.

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 8:04 am

Hi All,
I need to call below oracle functions into datastage once load completes or fails:-

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.

eph · Post by **eph** » Wed Mar 06, 2013 8:49 am

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

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 06, 2013 8:51 am

What is the value you are passing to the function. You can make a call in after sql

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 9:18 am

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

eph · Post by **eph** » Wed Mar 06, 2013 9:25 am

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

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 9:46 am

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.

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 9:52 am

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.

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 06, 2013 10:04 am

Didnt you get additional warning or info along with this warning in director. If there are any share it

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 10:12 am

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)

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 06, 2013 10:27 am

I don't know what the system source batch Will return . If it is a DS macro it has to be within hash .

prasson_ibm · Post by **prasson_ibm** » Wed Mar 06, 2013 2:35 pm

Hi,
Can someone focus on my issue?

ray.wurlod · Post by **ray.wurlod** » Wed Mar 06, 2013 3:42 pm

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.

prasson_ibm · Post by **prasson_ibm** » Thu Mar 07, 2013 1:48 am

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.

prasson_ibm · Post by **prasson_ibm** » Thu Mar 07, 2013 2:11 am

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().

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 07, 2013 2:33 am

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?

DSXchange

Need to call oracle functions in datastage.

Need to call oracle functions in datastage.

Re: Need to call oracle functions in datastage.