Re: Dynamic SQL

From: <oratune_at_aol.com>
Date: Fri, 13 Oct 2000 16:13:34 GMT
Message-ID: <8s7cb5$qig$1@nnrp1.deja.com>

In article <8s7b0v$p5r$1_at_nnrp1.deja.com>,   clcope_at_my-deja.com wrote:
> 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.
>

If the table is in ALL_TABLES and ALL_TAB_COLUMNS but not in USER_TABLES or USER_TAB_COLUMNS you are not connected as the owner of the table in question. USER_ views are based upon the connected user; the table IS in the database, but was not created by the user account you are using when you run this script. Connect as the owner of the table (you can get that information from ALL_TABLES or ALL_TAB_COLUMNS) then you can use USER_TABLES or USER_TAB_COLUMNS. Conversely you could modify the script to return the owner as well as the table_name (since you are selecting based upon a given column name returning the COLUMN_NAME field is a bit redundant) and then select from OWNER.TABLE_NAME:  SET SERVEROUTPUT ON;
 DECLARE
    TableName varchar2(30);
    TabOwner varchar2(30);
    DynCursor int;
    RowCount int;
    FetchStatus int;
    Total int;
    TransNbr VARCHAR2(9);
    CustId VARCHAR2(15);
    CURSOR TransNbrTables IS

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

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

    LOOP

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

       DBMS_SQL.PARSE(DynCursor, 'SELECT COUNT(*)
FROM '||TabOwner||'.'||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;
 /

Presuming you are using a DBA account this should work quite nicely.

--
David Fitzjarrell
Oracle Certified DBA


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

Received on Fri Oct 13 2000 - 11:13:34 CDT