STP stage -input but not write rows?

vpauls · Post by **vpauls** » Thu Mar 29, 2007 5:09 am

Hi,

I am trying to use the stored procedure stage in DS on an already existing job but I'm having problem with the input. Before I used dual and a dummy row and wrote to a /dev/null file and the executed the procedure in the before sql clause.

Now I want to use the store procedure stage instead. Tha STP itself does inserts into two different tables so I really only want to run the SPT from DS, however I need to have an input so what should I send there? Before the SPT stage I have an oracle OCl that put rows into a table that the SPT uses.

I'm then sending the rows from this table into the SPT stage, but it feels really unnecessary since they are not needed for the procedure, should i change the procedure itself and make it more adopted to the DS call or what is the best way? Also tried to use dual and a dummy column and send that as input but it didn't work. I only want to run the procedure once for the job.

Hoping for some good ideas!!

BR
/Elin

chulett · Post by **chulett** » Thu Mar 29, 2007 7:10 am

Dual and a dummy row *is* a perfectly valid answer. That should only select one record and thus only run the procedure once. Or use a constraint to ensure that:

@OUTROWNUM=1

What "didn't work" about it for you?

vpauls · Post by **vpauls** » Thu Mar 29, 2007 7:20 am

When I ran it the reply was "number not correct" and it aborted. Now I'm using a table with many rows but I have said that the procedure should only be run once for the job, but it is taking a very long time. Last time I ran it using a oracOCl stage writing one line to /dev/null and before sql called the procedure it took about 20 mins. With the stored procedure stage it has so far taken about 40 mins.

shouldn't take any longer, should it?

/Elin

chulett · Post by **chulett** » Thu Mar 29, 2007 7:32 am

What did you select from dual? You either need to select data appropriate to use in the stage or something you ignore. We select '1' from dual and then ignore that in the Transformer that sits between the two stages, instead creating and populating columns for consumption by the procedure being called. That, in my opinion, is the 'best' solution.

I'm assuming it is taking 'a long time' because you are running it more than once or you are waiting for it to select 'many rows' from the source table or... it's just taking longer than you expect. Do you have a constraint like the one I mentioned? How busy is your Oracle database? Not sure how you would expect anyone other than you to know how long your procedure should run...