Calling User Defined Oracle Function

deepalis · Post by **deepalis** » Sun Dec 04, 2005 6:58 pm

Hi,

I am new to Datastage and have been struggling to get the Call to Oracle Function to work.

Here's a sample flow in my DS Job
=====================
Read from Source OCI Stage (5 columns)-> Send to Transformer Stage-> Manipulate Data (5 columns)-> Call Oracle Function that accepts 3 fields from the 5 input columns, which returns 1 column -> Combine Result from Function Call w/ 5 fields from Transfomer in previous step -> Load to Target (6 columns)

Here's one of the few things I tried
============
Use ODBC Stage with output link going into transformer; In Outputs tab Colums of ODBC create 4 fields (3 keys , 1 non key for returned value from fn); Select "User defined SQL query" option; In both Reference & Primary Input SQL area entered SELECT :1,:2,:3, FunctionName(:1,:2:3) FROM DUAL - each position to match the columns specified in 4 columns of the Output Columns.

The job run does not call the function (does not appear to be going to the ODBC stage).

Please Suggest.
Thanks in Advance,
Deepali

chulett · Post by **chulett** » Sun Dec 04, 2005 8:50 pm

First off, I'd suggest rethinking the Function approach. What exactly is it doing and is there any reason you can't recreate the logic in the DataStage job itself?

Also curious if you have a proper ODBC entry setup for this instance? That's an 'extra' step you'd need to take if you want to use an ODBC stage in your jobs. If you are using OCI stages for source and target, why not use it for the lookup as well?

When you say the job "does not appear to be going to the ODBC stage" do you mean the job monitor shows zero rows for that link? If so, that's a sign of a lookup failure and in a normal lookup would indicate a miss on the passed in lookup key values. Do you get any warnings in the job's log when you run it?

deepalis · Post by **deepalis** » Mon Dec 05, 2005 1:02 am

1) I will not be able to replicate the function logic within DS. The function is owned by another group, and we do not have visibilty to the underlying code. The purpose of the function is to get sales commission for a given Contract-Rep-TimePeriod.
SELECT GetCommission(Contract,Rep,Period) pct FROM DUAL;

2) Yes - ODBC is setup correctly. I've used this stage for multi-resultset return on lookups in other server jobs.

3) I've tried implementing the same thing using OCI stage - with no luck. Either way I get this error in the log - "ORA-01008: not all variables bound". I was wrong earlier when I said "call not going to the ODBC Stage", when infact it was always erroring out with ORA-01008.

Thanks!
Deepali

ray.wurlod · Post by **ray.wurlod** » Mon Dec 05, 2005 2:05 am

Can you feed a reference input link with the user-defined SQL? Of course, you will need to figure a way to represent the three arguments as key values using this approach.

Otherwise, can you create a stored procedure in Oracle, and use "stored procedure" as the access method from the ODBC stage?

deepalis · Post by **deepalis** » Mon Dec 05, 2005 10:56 am

I've used Stored Procedure Stage and had the job working. But, for some reason having an SP in the job, brings the DS server to a crawl. When I do an ipcs I see a whole bunch of "0xade" memory segments still open, which I need to kill/bounce server to get the server to it's normal performance!

Thought implementing function call using an OCI stage would help (if there is a way);

Thanks,
Deepali

ray.wurlod · Post by **ray.wurlod** » Mon Dec 05, 2005 1:32 pm

Do you have any idea what these shared memory segments do? If not, how do you know that they're not doing something vital? For example, there is one which, if removed, will cause DataStage to halt.
Every DataStage process has - needs - a shared memory segment (key 0xadebnnnn), plus there are a few others. Never, ever, remove the one with key 0xadecnnnn.

deepalis · Post by **deepalis** » Mon Dec 05, 2005 2:08 pm

Well, Yeah I remove ade's that point to my username on the DS server.
Thanks for pointing this out. Will be more careful.

In any case, is there a way I can solve my problem of being able to implement a function call in the job ?

Thanks,
Deepali

ameyvaidya · Post by **ameyvaidya** » Mon Dec 05, 2005 2:20 pm

Just thinking out loud:

Cant the function be called in the target stage in the insert( and update?) statement(using user-defined sql) rather than doing a lookup in DataStage?.

Are there any manipulations being done in DataStage on the value returned by the UDF?

deepalis · Post by **deepalis** » Mon Dec 05, 2005 2:39 pm

Yes- There are tranformations done based on the return value of the function. I'll not be able to call the Fn. in source or target.
Deepali