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

Daniel Wetzler wrote:
> 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;
PL/SQL has static and dynamic sql.
The default is static. This means object names need to be known at compile time.
The sql is also parsed at compile time.
The opposite of static sql is dynamic sql. Now everything can be variable, but also the statement will be parsed every time you run the procedure.
Using dynamic sql is one of the foremost methods to obtain a fully unscalable application.
The nature of your question (which has been asked many time here by people with a similar background) betrays you think Oracle is sqlserver sold by a different vendor.
Fortunately for you, it can do much more than sqlserver will be ever capable of.
Unfortunately for you, Oracle requires you to read those manuals, and this forum is not very tolerant towards people who are too lazy to do so, asking the same boring FAQ oer and over again. You are just the next instance of one of those people.
You need to read up on 'Native Dynamic SQL' in your PL/SQL reference manual.
You need also to forget everything you learned about sqlserver.

-- 
Sybrand Bakker
Senior Oracle DBA