Dynamic PL/SQL ?

I need to produce a stored procedure that is capable of reading data from one of ten tables created dynamically by a system, but which may or may not exist at the time of installation of the procedure.

One of the arguments passed in is an ID which indicates the table : I have a simple procedure which will just do the relevant select from the relevant table which is fine, as long as all the tables exist: However, if one or more tables don't exist, obviously the procedure will not compile as it cannot parse the select.

Hence I need a method of using dynamic SQL within this stored procedure. I can do this easily in a Pro*C executable, but cannot get the same functionality to work within PL/SQL.

All I want to do is build a string containing a select statement, and pass selected values back to host variables. I can build the string OK, do an execute on it, and it all sems to work, except I cannot get a value passed back to a host variable.

The PREPARE statement doesn't seem usable in PL/SQL, and an EXECUTE IMMEDIATE doesn't let you pass values back to host variables ... I think. Can anyone shed some light on this for me?

Many Thanks

Graham Turner Received on Tue Jan 22 2002 - 06:06:47 CST