DSXchange

Hi!
I've got very stupid problem using STP stage - when I use the STP stage to call an Oracle procedure the procedure (probably) hangs or maybe it's better to say runs forever. When I call the same procedure (which takes no paramters and just makes some selects on database) from Oracle client it runs perfectly. So the question is are there any known differences between calling a storeproc from inside of Data Stage and from Oracle client?

More details:
Stored Procedure Name is: DATA_STAGE_API.DISTRIBUTE_XXX
Procedure type is defined as: Transform
Generate procedure call: NO
Stored procedure statement is: BEGIN DATA_STAGE_API.DISTRIBUTE_XXX(); END;
Transaction isolation level: None

Regards.

Pawel Janowski

Describe your job design - where exactly are you calling the proc?

Thx,

Details about the job design:
- the overall design is very simple - I want to load data from flat files to staging tables and then redistribute data to target tables using the stored procedure (without any parameters)
- and so I use STP stage (at the end of that sequence) - in a job activity I pass the name of the server job which consists of STP Stage with output passed to sequential file stage (but this is just error code and message text).

While debugging I found that the procedure stucks on simple selects (as SELECT Count(*) from XXX) - those select executed from Oracle client work fine and pretty fast.

Any ideas, pls?

Regards.

Pawel

Hi!
The problem was outside of DS - the stored procedure was sometimes inefficient.

Pawel