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

[Ana C. Dent]

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

Use Dynamic SQL, such as EXECUTE IMMEDIATE

[Michael Willer]

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 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.]

su******@yahoo.com (Suresh) wrote in message news:<2a**************************@posting.google. com>...

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

Suresh,

Use a REF_CURSOR.

Frans H.