problem with Stored Procedure in datastage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chvenkat.v
Participant
Posts: 94
Joined: Fri Dec 14, 2007 3:22 am

problem with Stored Procedure in datastage

Post by chvenkat.v »

pls tell me problem with server or my code.

in datastage, oracle entr. stage. we can call stored procedure or not.

if yes pls tell me . if no then in stored procedure stage how can i get output result to a dataset.

pls tell me


thanks
venkat

Code: Select all

SQL> CREATE OR REPLACE PROCEDURE TEST1( INDATE IN DATE)
  2  IS
  3  BEGIN
  4   SELECT COUNT(*) FROM IP WHERE EFF_DT=TO_DATE(INDATE, 'YYYY-MM-DD');
  5  END;
  6  /

Warning: Procedure created with compilation errors.

SQL> EXECUTE TEST1
BEGIN TEST1; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object BDWUSER.TEST1 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
niharranjan
Participant
Posts: 18
Joined: Mon Jul 23, 2007 1:32 am
Location: Singapore

Post by niharranjan »

Hi,

i could not find the answer... Can anyone tell me the answer..


Many thanks in advance.
nihar
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

The procedure didn't compile. What else are you expecting to be told? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Should IS (line #2) be AS ?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Nope. For a procedure it would be 'IS', a table or view - 'AS'.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I defer to your Oracle knowledge. It's not my forte.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chvenkat.v
Participant
Posts: 94
Joined: Fri Dec 14, 2007 3:22 am

Re: problem with Stored Procedure in datastage

Post by chvenkat.v »

Sorry ray, I forget to post my executed procedure any way it is working fine.

My problem is here any possible to call procedure in oracle ent. stage.
Post Reply