Oracle Package
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Oracle Package
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]
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]
Regards
Sonia Jacob
Sonia Jacob
Re: Oracle Package
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.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Re: Oracle Package
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.sud wrote: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.
Thanks
Regards
Sonia Jacob
Sonia Jacob
Re: Oracle Package
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.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Re: Oracle Package
oops! ok , I will check this out.sud wrote: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.
Thanks
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Re: Oracle Package
Correct me if I am worngsonia jacob wrote:oops! ok , I will check this out.sud wrote: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.
Thanks
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?
Regards
Sonia Jacob
Sonia Jacob
Re: Oracle Package
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?
Alongside, can you please ask your DBA as to the exact reason for ORA-900?
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
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 ???
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 ???
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
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
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
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA