While you can use dynamic sql of this (execute immedite or dbms_sql) I think
you might have a design problem. Why are the tables not in existance, or
may not, are you dynamically creating temporary tables? Not a good idea.
Jim
"Graham Turner" <turnergw_at_logica.nojunk.com> wrote in message
news:1011701204.680953_at_ernani.logica.co.uk...
>
> 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 - 08:15:17 CST
