Oracle Package

[sonia jacob]

Hi,


I need to run an oracle procedure from DS parallel job. I am using an oracle enterprise stage and I say

exec schema.package.procedure('val1', 'val2','val3','val4','val5')

It throws a ORA - 900 error. I did check the same procedure from PLSQL, from the UNIX box and it did work. Any idea as to why its failing in DS Oracle Enterprise Stage?

Thanks[/i]

[sud]

If you do a search on ORA-900 error at google university, you will probably find that - and do check this - that this means that the Procedural Option is not installed on the Oracle instance. Before anything, try executing the same procedure from any third party application like Toad, PL/SQL developer and see if you get the same error. Chances are that you will, and then contact your DBA about it.

[sonia jacob]

If you do a search on ORA-900 error at google university, you will probably find that - and do check this - that this means that the Procedural Option is not installed on the Oracle instance. Before anything, try executing the same procedure from any third party application like Toad, PL/SQL developer and see if you get the same error. Chances are that you will, and then contact your DBA about it.

If you refer to the post, I did mention that I have already tested the procedure from teh ETL Unix server using PL SQL. This was done after I did a Google search and alsso after checking the forum out.

Thanks

[sud]

No, please try a third party application not Oracle's sqlplus. That is the whole point. Please use some third party application like Toad etc and test it.

[sonia jacob]

No, please try a third party application not Oracle's sqlplus. That is the whole point. Please use some third party application like Toad etc and test it.

oops! ok , I will check this out.

Thanks

[chulett]

Also ensure whatever user the job will be leveraging has execute grants to the package in question.

[sonia jacob]

No, please try a third party application not Oracle's sqlplus. That is the whole point. Please use some third party application like Toad etc and test it.

oops! ok , I will check this out.

Thanks

Correct me if I am worng

Test 1 : Using PL SQL developer on my client I ran the package sucessfully

Test 2 : DBA tested the same using DB ARtisan

Teat 3 : As mentioned earlier I ran Proc from UNIX box (ETL server) using sqlplus, sucessfully

Is there anything that I am missing?

[sud]

No, you are good. Hmmm, from what I am able to find on the net, this might also be because you are using some copy command in the procedure ... is it so? Is it possible to rewrite the procedure as a function and return some dummy value? then you could also invoke it as : select func() from dual. And another thing, are you using the exec statement in place of the usual SQL in the enterprise stage? If yes, then you cannot do that, instead put some dummy SQL in the SQL in the enterprise stage like select 1 from dual and use the exec command in the open connection command.

Alongside, can you please ask your DBA as to the exact reason for ORA-900?

[DSguru2B]

For debuggin purposes, create a simple job using an OCI stage and try to execute the package in the before after sql tab.
See if that works.
Then try with the "Call" command instead of "exec".

Also, you did not specify where in the enterprise stage you are running this command. Open, close commands ???

[Teej]

FYI: For Version 8.0.1, there is a Shared Container stage available using Oracle and DB2.

Another solution -- instead of using EXEC, wrap the function in a SQL statement.

[lstsaur]

Sonia,
Actually the error from your Oracel Enterprise Stage is saying:
esql complaint: ORA-00900: invalid SQL statement.

Just use the STP stage that will save you a lot of pain.

[sonia jacob]

Thanks all. All the replies and few earlier posts from the forum were helpful to get the issue resolved.

I used call instead of exec.

after which there were a bunch of other ora errors

ORA-01861 Error - parameter of date type was not passed appropriately to the package

ORA-01008: not all variables bound - the same column was mapped twice to two different parameters of the package. I had to put that as separate columns in a transform stage

Thanks again

[sonia jacob]

Sonia,
Actually the error from your Oracel Enterprise Stage is saying:
esql complaint: ORA-00900: invalid SQL statement.

Just use the STP stage that will save you a lot of pain.

Sorry for being ignorant, but what is the STP stage?

[chulett]

Stored Procedure stage.

[sonia jacob]

Stored Procedure stage. ...

thanks.

Will try it out!