table name substitution in pl/sql

[Suresh]

how can we use a variable in the place of a tablename?

towner=scott
ttable_name=emp
select count(*) into totrows from towner.ttable_n ame;

ERROR at line 18:
ORA-06550: line 18, column 49:
PLS-00201: identifier 'TOWNER.TTABLE_ NAME' must be declared
ORA-06550: line 18, column 15:
PL/SQL: SQL Statement ignored

I need to substitute different table names. How do I pass variable
names as table names? Towner and ttable_names are declared in the
declare section.

Thanks

Suresh

[Ana C. Dent]

Re: table name substitution in pl/sql

Suresh wrote:[color=blue]
> how can we use a variable in the place of a tablename?
>
> towner=scott
> ttable_name=emp
> select count(*) into totrows from towner.ttable_n ame;
>
> ERROR at line 18:
> ORA-06550: line 18, column 49:
> PLS-00201: identifier 'TOWNER.TTABLE_ NAME' must be declared
> ORA-06550: line 18, column 15:
> PL/SQL: SQL Statement ignored
>
> I need to substitute different table names. How do I pass variable
> names as table names? Towner and ttable_names are declared in the
> declare section.
>
> Thanks
>
> Suresh[/color]


Use Dynamic SQL, such as EXECUTE IMMEDIATE

[Michael Willer]

Re: table name substitution in pl/sql

Suresh wrote:
[color=blue]
> how can we use a variable in the place of a tablename?
>
> towner=scott
> ttable_name=emp
> select count(*) into totrows from towner.ttable_n ame;
>
> ERROR at line 18:
> ORA-06550: line 18, column 49:
> PLS-00201: identifier 'TOWNER.TTABLE_ NAME' must be declared
> ORA-06550: line 18, column 15:
> PL/SQL: SQL Statement ignored
>
> I need to substitute different table names. How do I pass variable
> names as table names? Towner and ttable_names are declared in the
> declare section.
>
> Thanks
>
> Suresh[/color]

Three ways to handle this:

1) use EXECUTE IMMEDIATE

2) use DBMS_SQL package

3) use a substitution variable
SELECT * FROM &TABLE_NAME WHERE .....

Option 1 and 2 will work anywhere

Option 3 will not work if you plan to deploy your PL/SQL as a stored
procedure/funtion/package.


hope this helps,


--
------------------------------------------------------------------------

*Michael Willer*
*Oracle & J2EE architect*

[Frans H.]

Re: table name substitution in pl/sql

sureshcb@yahoo. com (Suresh) wrote in message news:<2af7b65c. 0310011322.43ad 7aaf@posting.go ogle.com>...[color=blue]
> how can we use a variable in the place of a tablename?
>
> towner=scott
> ttable_name=emp
> select count(*) into totrows from towner.ttable_n ame;
>
> ERROR at line 18:
> ORA-06550: line 18, column 49:
> PLS-00201: identifier 'TOWNER.TTABLE_ NAME' must be declared
> ORA-06550: line 18, column 15:
> PL/SQL: SQL Statement ignored
>
> I need to substitute different table names. How do I pass variable
> names as table names? Towner and ttable_names are declared in the
> declare section.
>
> Thanks
>
> Suresh[/color]

Suresh,

Use a REF_CURSOR.

Frans H.