Re: Beginners question : How to use varchar2 within a sql statement ?

Jim Kennedy schrieb:

> "Daniel Wetzler" <Daniel.Wetzler_at_sig.biz> wrote in message
> news:1149170186.017146.288660_at_y43g2000cwc.googlegroups.com...
> > Dear Oracle experts,
> >
> > I have a beginners question concerning development of Stored Procedures
> > in a Oracle 10g database.
> >
> > I tried to use a procedure like below, where I gave a varchar2 called
> > tablename.
> > If I try to use the variable tablename in a select statement I get the
> > the error
> > "invalid table name" during compilation of the SP.
> > But how can I use a variable to type a tablename into the SP externally
> > ?
> >
> > Best regards,
> >
> > Daniel
> >
> > CREATE OR REPLACE PROCEDURE SA.SELECTFROMTABLE
> > (modus BOOLEAN, tablename varchar2 ) AS
> > cursor tabledata is
> > select * from tablename ;
> > BEGIN
> > if modus = true then
> > for i in tabledata
> > LOOP
> > dbms_output.put_line(i.ID);
> > END LOOP;
> > else
> > dbms_output.put_line(tablename);
> > end if;
> > END;
> >
>
> You can't have a table name as a variable. It is generally a bad idea to do
> this, but you could use dbms_sql or native dynamic sql.
> Jim

Thank you very much. These were good hints.

Best regards,

Daniel Received on Thu Jun 01 2006 - 09:37:09 CDT