Re: Assigning a variable to a table name in FROM statement ???

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: Tue, 10 Apr 2001 23:57:11 +0800
Message-ID: <9avbls$292$6@news.seed.net.tw>

"Bernhard Acke" <ba_at_metropolis.de> ????? news:3ad1a9f4$1_at_netnews.web.de...
> I want to get the number of datasets from a different tables,
> where table names are stored in a separate table.
>
>
> Is it possible to assign the table name dynamically in the
> FROM-Part of the SELECT-Statement (as a variable), so can do
> this with a loop.
>
>
> I thought of something like this, but it doesnt work:
>
>
> DECLARE
> actual_scheme_name VARCHAR(30) := 'students';
> actual_table_name VARCHAR(30) := 'adresses';
> number_of_datasets_entered NUMBER(10) := 0;
> BEGIN
> actual_table := actual_scheme_name || '.' || actual_table_name;
> SELECT count (rowid)
> INTO number_of_datasets_entered
> FROM actual_table;
> ============
> DBMS_OUTPUT.PUT_LINE(number_of_datasets_entered);
> EXCEPTION
> END;
On Oracle8i, you can use the following:

set serveroutput on;
DECLARE

    actual_scheme_name          VARCHAR(30) := 'students';
    actual_table_name           VARCHAR(30) := 'adresses';
    actual_table                varchar(61);
    number_of_datasets_entered  NUMBER(10) := 0;

    sql_statement varchar2(2000);
    type ctype is ref cursor;
    c ctype;
BEGIN
    actual_table := actual_scheme_name || '.' || actual_table_name;     sql_statement := 'SELECT count(*) FROM '||actual_table;     open c for sql_statement;
    fetch c into number_of_datasets_entered;     close c;
    DBMS_OUTPUT.PUT_LINE(number_of_datasets_entered); EXCEPTION
   ......
END; By the way, use COUNT(*) inetead of COUNT(ROWID). Prior to Oracle8i, you can use the DBMS_SQL package. Received on Tue Apr 10 2001 - 10:57:11 CDT