Re: Newbie question: Is there a way to let "select ...from" statement accept a variable as a table name?

Karsten Farrell wrote:

> phmyhn wrote:
>
>> I created a procedure which has two IN type parameters. Then I
>> declared a local variable and assign it to the concatenation of the
>> two passed in variables. Then I wanted to use this variable as a table
>> name for "select...from" statement. But I got error said that table
>> name didn't exist. Is there a way to let the select statement get the
>> value of the variable but not the literal name of the variable ? My
>> code was like this:
>> create or replace procedure addpara(p_para1 varchar2, p_para2
>> varchar2)
>> is
>> v_name varchar2(20) := p_para1 || p_para2;
>> begin
>> select * from v_name where id = 10000; --if commented this
>> line,there's no error
>> dbms_output.put_line(v_name);
>> end addpara; Any helps would be appreciated.
>
> Change the select to:
>
> execute immediate 'select * from ' || v_name || ' where id = 10000';

Karsten,

Is it possible to retrieve the results of a query executed in this manner?

 From my knowledge of dynamic SQL in Pro*languages, it's going to be more complex than this.

Martin Doherty Received on Mon Nov 25 2002 - 13:38:21 CST