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

On 23 Nov 2002 18:23:26 -0800, phmyhn_at_yahoo.com (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.

You'll need Native Dynamic SQL (Open <cursor variable > for), which is defined in the PL/SQL reference manual.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Nov 24 2002 - 00:04:26 CST