Re: Can you pass table/view name to a procedure?
Date: 1997/12/14
Message-ID: <kirtt-1412970731520001_at_kirtt.sky.net>#1/1
In article <01bd0878$76149500$LocalHost_at_lcdc>, "Luis Cabral" <cabral_at_merconet.com.br> wrote:
> Robert Augustyn <augustyn_at_unn.unisys.com> escreveu no artigo
> <66rutj$g25$1_at_mail.pl.unisys.com>...
> > Hi,
> > I would like to pass table name to the stored procedure and use it in one
of
> > the sqls inside the procedure but had not much lack doing it, is it
> > possible?
> > Any solutions will be greatly appreciated.
> > Robert
>
>
> Hi Robert,
>
> Here is a piece of code that can help you:
>
> declare
> c integer;
> t varchar2(40);
> begin
> t := 'tablea';
> c := dbms_sql.open_cursor;
> dbms_sql.parse(c,'update '||t||' set column = 10',1);
> dbms_sql.execute(c);
> dbms_sql.close_cursor(c);
> end;
>
> As you may imagine, the code above will execute dinamically the statement
> 'update tablea set column = 10'.
> See the package spec SYS.DBMS_SQL for more details, and good luck!
A caveat here, in my experience, you must use stored procedures if you are going to use dbms_sql -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
kirtt_at_macsrule.com http://www.nowonder.com http://www.sky.net/~kirtt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"Eagles may soar, but weasels don't get sucked into jet engines" .-.-.-.-.-.-.- Public Key Available at: pgpkeys.mit.edu-.-.-.-.-.-.-. Received on Sun Dec 14 1997 - 00:00:00 CET