Dynamic SQL

From: <clcope_at_my-deja.com>
Date: Thu, 12 Oct 2000 22:03:46 GMT
Message-ID: <8s5cft$87s$1@nnrp1.deja.com>

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. Received on Thu Oct 12 2000 - 17:03:46 CDT