Dynamic SQL

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';

      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();

Can anyone help me please.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 00:03:46 CEST