Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett , rschirm , roy
Mun
Charter Member
Posts: 33 Joined: Tue May 02, 2006 10:02 am
Post
by Mun » Fri May 12, 2006 7:42 am
I am trying to call and execute a stored procedure in After SQL.
Oracle_OCI_9i_6: Executing AfterSQL
Oracle_OCI_9i_6: CALL BI_APP.MUNS_PROC
Oracle_OCI_9i_6: ORA-06576: not a valid function or procedure name
BI_APP is the database schema and MUNS_PROC is the name of my stored procedure.
I've tried using EXEC instead of CALL too but it gave me the same message.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Fri May 12, 2006 7:55 am
To me, that would imply that the user you are connected as doesn't have the grants they need to execute the procedure.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Mun
Charter Member
Posts: 33 Joined: Tue May 02, 2006 10:02 am
Post
by Mun » Fri May 12, 2006 8:04 am
Thanks! I've thought about that initially. When I copy and paste that command in TOAD and run it, it ran successfully......
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Fri May 12, 2006 3:22 pm
Does your DataStage job connect to Oracle using the same login/password that you used in TOAD?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Mun
Charter Member
Posts: 33 Joined: Tue May 02, 2006 10:02 am
Post
by Mun » Fri May 12, 2006 3:42 pm
Yes, it is the same user id and password.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Fri May 12, 2006 5:03 pm
Ok then... what exactly does your stored procedure do?
-craig
"You can never have too many knives" -- Logan Nine Fingers
Mun
Charter Member
Posts: 33 Joined: Tue May 02, 2006 10:02 am
Post
by Mun » Fri May 12, 2006 5:09 pm
The stored procedure inserts some rows into the table. It does not return any values. Just plain insert statements.
I also forgot to mention that the stored procedure is in BI_APP schema and the stored procedure insert the rows into another schema (let's say BI_APP_TEST). I couldn't create the stored procedure in the BI_APP_TEST schema due to insufficient privilege with the userid.
Could that be the issue?
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Sat May 13, 2006 10:01 pm
Oh yea definetly I beleive, its the reason. YOu dont have privelages to insert into, or execute a procedure in the other schema.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.