Error in calling user defind PL/SQL

[kcbland]

You CAN collect statistics, brew a cup of coffee, whatever, between two processes.

What you can't do is expect the SP stage to "pass" rows thru like it's not even there. The SP stage will "do something" for you and give you back an answer, it will not pass rows thru it. Do you understand why I originally posted about a "square peg in a round hole"? You're trying to do something that doesn't work the way you want it to.

Please understand that your job design is the issue, not the functionality of the stages. If you understand what/how the stages operate, you would understand that the mechanism you want to use requires you to design your job differently. I've given you the design that works according to the stages you have stated you must use.

The SP stage is used to "run something" and tell you how it worked. If you want to use an SP to get data, then you use the OCI stage and select stored procedure as the SQL method. You're trying to get rows being input into the SP stage to come out the output link, WHICH DOESN'T WORK.

[nkln@you]

The SP stage will "do something" for you and give you back an answer, it will not pass rows thru it... If you want to use an SP to get data, then you use the OCI stage and select stored procedure as the SQL method.

But SP does pass the rows. If i have a procedure which operates on the input row(by checking the execute procedure for each row option), then the procedure is executed on the input rows and the data is forwarded to the output link. But i achieved this using a procedure and defining parameters.

So i was thinking whether i can just use to transfer the rows w/o doing any operation on the rows passed to it.

[kcbland]

It passes rows if you check the box to operate the SP against input rows, otherwise, it just needs one row to "do its thing" - thus your problem. You're trying to make the SP operate differently than how it is intended. I'm glad your situation is now resolved.

[nkln@you]

I'm glad your situation is now resolved. ...

Well. The problem is not solved but i guess i have got enough proof to say that SP will not work this way and we need to call the procedure from the before/after sql.

[chulett]

Probably going to regret this, but you *could* build your job like this:

Code: Select all

XFM --> SP --> OCI --> OCI

A single dummy record triggers the SP, passes through and does some sort of harmless no-op style operation in Oracle. Then the main process kicks in and the update leverages your newly polished, shiny indexes.

[nkln@you]

Code: Select all

XFM --> SP --> OCI --> OCI

...

What does XFM mean here?

[chulett]

Transformer.