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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Sep 1999 07:08:06 -0400
Message-ID: <oh=iN34txtJlFeRdEnzOzDIC1NlN@4ax.com>

A copy of this was sent to corky711_at_my-deja.com (if that email address didn't require changing) On Fri, 17 Sep 1999 02:36:59 GMT, you wrote:

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

because in Oracle8.0 and before -- the one thing, the only thing that a funcion must promise NOT to do is insert/update/delete/create etc in a function that is to be called from SQL.

Your procedure cannot make this promise since it does dynamic sql and dbms_sql can just as easily do an INSERT as it does a SELECT.

In Oracle8.0 and before, what you are attempting to do is not possible.

In Oracle8i, given that dynamic sql is implemented in a totally different fashion (dbms_sql is still supported as before), we can write a function that does dynamic sql AND can be called from sql. For example:

tkyte_at_8i> create or replace function count_rows( p_tname in varchar2 ) return number
  2 as
  3 l_cnt number;
  4 begin

  5          execute immediate 'select count(*) from ' || p_tname INTO l_cnt;
  6          return l_cnt;

  7 end;
  8 /

Function created.

tkyte_at_8i>
tkyte_at_8i> exec dbms_output.put_line( count_rows( 'emp' ) ); 14

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> select count_rows( 'emp' ) from dual;

COUNT_ROWS('EMP')

---

               14

That is callable from SQL since the plsql compiler sees the INTO clause on the execute immediate statement and *knows* we are doing a select. This function, count_rows, promises to not update the database.

Additionally, in Oracle8i release 8.1, it is possible to call a function from SQL that does write to the database. It uses a feature called autonomous transactions to do that. You can read about that with an example of calling a function from sql that writes to the database at: http://govt.us.oracle.com/~tkyte/autonomous/index.html

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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 06:08:06 CDT