REPOST -- maybe the previous title was ugly = D-- this is a question on DBMS_SQL

Please Sirs, I need help on this.

Here is the situation:
I created the following function utilizing the DBMS_SQL package. The function returns the number of rows in the specified table (which is the parameter).

  SQL> create or replace function n_rows (tab IN varchar2)     2 return number
    3 is
    4 i number;
    5 cnt number;
    6 cursor_number number;
    7 BEGIN
    8 cursor_number:=dbms_sql.open_cursor;     9 dbms_sql.parse(cursor_number, 'select count(*) from '||tab, 1); 10 dbms_sql.define_column(cursor_number, 1, cnt);   11 i:=dbms_sql.execute(cursor_number);   12 IF DBMS_SQL.FETCH_ROWS(cursor_number)>0 THEN   13 DBMS_SQL.COLUMN_VALUE(cursor_number,1,cnt);   14 return (cnt);
  15 END IF;
  16 dbms_sql.close_cursor(cursor_number);   17 END;
  18 /

  Function created.

It compiled!
Then I tried the following:

  SQL> ed
  Wrote file afiedt.buf

    1 declare
    2 x number;
    3 Begin
    4 x:=n_rows('employee');
    5 sys.dbms_output.put_line(x);
    6* end;
  SQL> /
  6

  PL/SQL procedure successfully completed.

The function worked fine!
But when I do a select I get the following:

  SQL> select n_rows('employee') from dual;   select n_rows('employee') from dual

        *
  ERROR at line 1:
  ORA-06571: Function N_ROWS does not guarantee not to update database

why is this?
i checked the error message and it told me to recreate the package with the required pragma...something about not being able to update the database.

Any help or hints is greatly appreciated, Thank you,
Corky

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 16 1999 - 21:36:59 CDT