Re: Dynamic SQL
Date: Fri, 13 Oct 2000 16:13:34 GMT
Message-ID: <8s7cb5$qig$1_at_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 - 18:13:34 CEST