Re: Question on DBMS_SQL package

John,
  Just tried it, didn't work (plus, if things work the way I think they do, this would make the function search for a table named TABLE_NAME) also have tried:

SELECT 'EMP', tcount('EMP') FROM dual;

with the same resulting error message. Even tried creating a PL/SQL script, SELECTING the table name and the function with a cursor, and writing the result with DBMS_OUTPUT.PUT_LINE(). That failed too! There is something going on here I don't quite understand (then again I admit I have not thoroughly read the documentation on DBMS_SQL).

Thanks anyway,
Paul

In article <387E3B53.A4117274_at_dee.wellesley.edu>,   jpearson_at_dee.wellesley.edu wrote:
> hi
>
> try this
>
> SELECT table_name, tcount('table_name') FROM user_tables
>
> pberetta_at_my-deja.com wrote:
>
> > Tried to create and use what I thought was a rather simple function
> > using the DBMS_SQL package. All I'm attempting to do is create a
> > dynamic SQL statement that will return the count of records in any
> > table. Wrote the following:
> > CREATE OR REPLACE FUNCTION TCOUNT(TNAME VARCHAR2)
> > RETURN NUMBER IS OUTCOUNT NUMBER(10);
> > v_select varchar2(100);
> > v_cursor number;
> > v_count number(10);
> > BEGIN
> > v_cursor := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
> > v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
> > DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
> > DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
> > DBMS_SQL.CLOSE_CURSOR(v_cursor);
> > outcount := v_count;
> > RETURN outcount;
> > END;
> > /
> > The function compiles without errors, but when I attempt to invoke
it
> > in a SELECT statement, the result is:
> > SQL>
> > SQL> CREATE OR REPLACE FUNCTION TCOUNT(TNAME VARCHAR2)
> > RETURN NUMBER IS OUTCOUNT NUMBER(10);
> > v_select varchar2(100);
> > v_cursor number;
> > v_count number(10);
> > BEGIN
> > v_cursor := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
> > v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
> > DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
> > DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
> > DBMS_SQL.CLOSE_CURSOR(v_cursor);
> > outcount := v_count;
> > RETURN outcount;
> > END;
> > /
> > SELECT table_name, tcount(table_name) FROM user_tables
> > *
> > ERROR at line 1:
> > ORA-06571: Function TCOUNT does not guarantee not to update database
> >
> > I'm at a loss as to how to correct this problem (or if it can be
> > corrected). Any help would be appreciated.
> > Thanks,
> > Paul
> >
> > 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 Jan 14 2000 - 00:30:52 CET