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

"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 Received on Thu Jun 01 2006 - 09:13:56 CDT