Re: fuctions and DBMS_SQL package

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Sun, 31 Jan 1999 19:13:09 GMT
Message-ID: <36b4a9c2.11555455@inet16.us.oracle.com>

On Sun, 31 Jan 1999 13:41:05 GMT, "gocham" <gocham_at_mci2000.com> wrote:

>Hi,
>
>Whenever I try to use DBMS_SQL package within a function I'm getting error
>on functon execution
>ORA-06571:
>Finction doesn't guarantee not to update database
>
>Am I doing something wrong?
>
>Thanks
>
>Gocha
>

<snip function definition>

Well, you cannot select a function that uses dbms_sql because that package is not pure.

SQL> select RECCNT( 'emp' ) from dual;
select RECCNT( 'emp' ) from dual

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

If you want to run your function from sql*plus you can do the following...

SQL> variable n number;
SQL> exec :n := RECCNT( 'emp' );

PL/SQL procedure successfully completed.

SQL> print n

         N

---

        14

or how about this...

SQL> set serverout on
SQL> exec dbms_output.put_line( 'The record count is ' || RECCNT( 'emp' ) ); The record count is 14

PL/SQL procedure successfully completed.

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jan 31 1999 - 13:13:09 CST