Re: table name substitution in pl/sql

Suresh wrote:

> how can we use a variable in the place of a tablename?
>
> towner=scott
> ttable_name=emp
> select count(*) into totrows from towner.ttable_name;
>
> 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

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 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*