Re: Dynamic SQL

Thanks that worked great, but it brought me into another snag. The table name is in the all_tables and all_tab_columns tables. However, the table does not actually exist in the database. I tried using the user_tables table, but that table is empty. Is there a way to determine if the table exists before trying to select from it? Or if I could just skip over that error, that would work.

Thanks in advance,
Crystal L. Cope

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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 10:50:59 CDT