HOW TO CALL A ORACLE STORED PROCEDURE

mchaves · Post by **mchaves** » Mon Aug 15, 2005 1:36 am

Hi there,

I know that this topic was covered before using odbc plug-in to call a stored procedure but it seems to me so sloppy. So I would like to know if there is any other way to call a oracle procedure in oracle oci plug-in.

I have tried calling the procedure in the after tab in oci plug-in but no success. The datastage gives me the error:

ORA-06576: not a valid function or procedure name

and my code is :

CALL PRC_REFRESH_ALL_MVIEWS;

Thanks mates

elavenil · Post by **elavenil** » Mon Aug 15, 2005 1:50 am

There is one way(it may not be nice way) to execute the Oracle stored procudure by calling SQLPLUS utility in Jobcontrol using DSExecute.

HTWH.

Regards
Saravanan

roy · Post by **roy** » Mon Aug 15, 2005 2:50 am

Hi,
You simply need the full name shema name and all _ you must have the privilages to read/run it.
It has been a while so if call doesn't work try execute (or similar)
I did it a year ago with before and after sql with OCI stage it works fine.

IHTH,

chulett · Post by **chulett** » Mon Aug 15, 2005 7:20 am

Or upgrade to 7.5 so you have access to the Stored Procedure stage.

roy · Post by **roy** » Mon Aug 15, 2005 8:04 am

Just clearing that you need Oracle 9+ for Craig's idea.
IHTH,

mchaves · Post by **mchaves** » Mon Aug 15, 2005 6:27 pm

Hi Mates,

I went for the sqlplus option. Thanks Saravanan. It's not that elegant but it's easier to implement.

Thanks all for the assistance. This forum is very useful.

Cheers,
Melquior