Re: Dynamic SQL

From: <oratune_at_aol.com>
Date: Thu, 12 Oct 2000 22:30:49 GMT
Message-ID: <8s5e2q$9mg$1@nnrp1.deja.com>

In article <8s5cft$87s$1_at_nnrp1.deja.com>,   clcope_at_my-deja.com wrote:
> I am trying to loop through all tables that have a specific field in
> them. As I process each record, I want to issue a Select statement on
> the table that matches that record. Here is the code:
>
> SET SERVEROUTPUT ON;
> DECLARE
> TableName varchar2(30);
> FieldName varchar2(30);
> DynCursor int;
> RowCount int;
> FetchStatus int;
> Total int;
> TransNbr VARCHAR2(9);
> CustId VARCHAR2(15);
> CURSOR TransNbrTables IS
> SELECT
> Table_Name,
> Column_Name
> FROM all_tab_columns
> WHERE Column_Name = 'KCM_TRANS_NBR';
>
> BEGIN
> DBMS_OUTPUT.ENABLE(1000000);
> TransNbr := '461431';
> CustId := '100648236';
> OPEN TransNbrTables;
>
> LOOP
> TableName := '';
> FieldName := '';
> DynCursor := DBMS_SQL.OPEN_CURSOR;
> FETCH TransNbrTables INTO TableName, FieldName;
> EXIT WHEN TransNbrTables%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE('Updating table: ' || TableName);
> DBMS_OUTPUT.NEW_LINE();
>
> DBMS_SQL.PARSE(DynCursor, 'SELECT COUNT(*) FROM :TableName',
> DBMS_SQL.NATIVE);
> ---- WHERE KCM_TRANS_NBR = :TransNbr
> -- AND CUST_ID = :CustId,
> DBMS_SQL.NATIVE);
> DBMS_SQL.BIND_VARIABLE(DynCursor, ':TableName', TableName);
> DBMS_SQL.DEFINE_COLUMN(DynCursor, 1, Total);
> RowCount := DBMS_SQL.EXECUTE (DynCursor);
> FetchStatus := DBMS_SQL.FETCH_ROWS(DynCursor);
> DBMS_SQL.COLUMN_VALUE(DynCursor, 1, Total);
> -- DBMS_SQL.CLOSE_CURSOR(DynCursor);
> --
> -- DBMS_OUTPUT.PUT_LINE('Records To Update: ' || Total);
> -- DBMS_OUTPUT.NEW_LINE();
> --
> DBMS_SQL.CLOSE_CURSOR(DynCursor);
>
> DBMS_OUTPUT.PUT_LINE('Update complete');
> DBMS_OUTPUT.NEW_LINE();
> DBMS_OUTPUT.NEW_LINE();
> END LOOP;
> CLOSE TransNbrTables;
> END;
> /
>
> Can anyone help me please.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Use this version:

 SET SERVEROUTPUT ON;
 DECLARE
    TableName varchar2(30);
    FieldName varchar2(30);
    DynCursor int;
    RowCount int;
    FetchStatus int;
    Total int;
    TransNbr VARCHAR2(9);
    CustId VARCHAR2(15);
    CURSOR TransNbrTables IS

       SELECT
          Table_Name,
          Column_Name
       FROM all_tab_columns
       WHERE Column_Name = 'KCM_TRANS_NBR';

 BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    TransNbr := '461431';
    CustId := '100648236';
    OPEN TransNbrTables;

    LOOP

       TableName := '';
       FieldName := '';
 	   DynCursor := DBMS_SQL.OPEN_CURSOR;
       FETCH TransNbrTables INTO TableName, FieldName;
       EXIT WHEN TransNbrTables%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Updating table: ' || TableName);
       DBMS_OUTPUT.NEW_LINE();

       DBMS_SQL.PARSE(DynCursor, 'SELECT COUNT(*) FROM '||TableName,
 DBMS_SQL.NATIVE);
       DBMS_SQL.DEFINE_COLUMN(DynCursor, 1, Total);
       RowCount := DBMS_SQL.EXECUTE (DynCursor);
       FetchStatus := DBMS_SQL.FETCH_ROWS(DynCursor);
       DBMS_SQL.COLUMN_VALUE(DynCursor, 1, Total);
       DBMS_SQL.CLOSE_CURSOR(DynCursor);

       DBMS_OUTPUT.PUT_LINE('Update complete');
       DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.NEW_LINE();

    END LOOP;
    CLOSE TransNbrTables;
 END;
 /

Your thought on using a bind variable is admirable, but you cannot use a bind variable in a FROM clause.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Oct 12 2000 - 17:30:49 CDT