Dynamic SQL in Pro*C calling stored package procedures

I am looking to develop a Pro*C routine that will be supplied with the name of a stored package procedure and values for the arguments for that package procedure. I see these as being passed in a single character string.

This string i.e.procedure and arguments, may be different each time.

I would like the Pro*C routine to be able to dynamically execute this string causing it to execute the package procedure using the supplied arguments. I suspect I can do this using Dynamic Method 2 as documented by Oracle as this appears to be a simple execute of the character string. This depends on how the arguments are supplied, but lets assume they are built into the string as follows:

   package.procedure('argval1','argval2')

rather than passed as a reference as in:

   package.procedure(:argvar1, :argvar2)

If the package procedure fails I want it to return a relevant error message which will then be passed back to the C routine. If the package retrieves data, I would also like to be able to pass this back to the C routine. I see the error code/message and return value being passed back from the package procedures as character strings.

Has anyone developed any dynamic SQL to handle the calling of different package procedures. Can you give me some guidance?

The online documentation implies that not much can be done with PL/SQL other than executing it!

Regards.

Paul.

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!