Re: Substituting table_name in pl/sql

yasin_mohammed_at_yahoo.com (Yasin) wrote in message news:<b7157cbc.0304021223.30500a0_at_posting.google.com>...
> Hi Guys, I am totally new to pl/sql. I am trying to substitute the
> table name in a select query with a variable but I don't know how to
> do that. Please help.
>
> Here's the script:
> >>>>>>>>
> DECLARE
> x NUMBER;
> y NUMBER;
> TABNAME CHAR(150);
> BEGIN
> x := 1;
> FOR channel IN (
> SELECT PLGRPID FROM PLGROUP)
> LOOP
> TABNAME := 'PLGRPMSG'||x;
> SELECT COUNT(*) INTO y FROM TABNAME;
> DBMS_OUTPUT.PUT_LINE(TABNAME);
> x := x+1;
> END LOOP;
> END;
> /
>
> <<<<<<<<
>
> Thanks,
>
> Yasin

You'll need dynamic sql, either Native Dynamic Sql available in 8i and higher or dbms_sql available in all versions. Looking at your code however, you are likely to end up with a non-scalable application due to incorrect design. You would to better to go for 1 PLGRPMSG table, with an appropiate foreign key to the PLGRP table.
Due to previous experiences with similar designs I strongly *urge* you to convert from this errant design.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Apr 03 2003 - 13:06:05 CEST