DYNAMIC STORED PROCEDURE! (HELP NEEDED!)

[naarora]

Hello All,

I want to write a program which takes procedure name and input, output parameters at run time and executes the procedure.

In other words the stored procedure name, input parameters and output parameters are being specified only at the run-time and are not known at the compile time.

I am using Pro"C" and oracle 8i.

Can oracle dynamic sql 4 help me?

or can oracle native sql can be of some help?


Thanks for any help.

Naveen Arora (naarora@hss.hns.com)

 

[Kenrae]

I think you can write it using dynamic sql. You could make the string ("execute" + procedure + "(" + ... ) and then try to send it to Oracle.

 

[Opus4]

Try the following

PROCEDURE execImmed (sql_string IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE sql_string;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

PROCEDURE dynPLSQL (blk IN VARCHAR2)
IS
BEGIN
execImmed (
'BEGIN ' || RTRIM (blk, ';') || '; END;');
END;

This was taken from Steven Feuerstein's book "Guide to Oracle8i Features" Chapter 4