Re: Can I call another function inside the package?

A copy of this was sent to Johnson Chao <zhao_at_ctc-g.co.jp> (if that email address didn't require changing) On Fri, 25 Jun 1999 04:33:42 GMT, you wrote:

>Hi,:
> Thanks very much for your useful help.
> Now I can do it.
>
> But another question comes. If Fuction AAA wil do a dabase operation,
>how can it be done? I have tried to add a insert statement inside AAA,
>but the compiling does not pass. While I remove the pragma statement
>away, though it can pass the compile, when executing, I was again told
>" Function AAA does not guarantee not to update database". How can I
>do that.
>
>Thanks
>
>

Don't call AAA from sql if it modifies the database state (you cannot call that function from sql if it writes to the database -- this is changed in Oracle8i, release 8.1 but for your purposes, you cannot call it).

If you don't want to have to specify the purity, you can code AAA as I described in another posting:

>> PROCEDURE rrr(mm IN number) IS
>> CURSOR ccur1 IS SELECT * FROM zt where c2=mm;

      x number;
>>
>> BEGIN
>> FOR rec1 IN ccur1 LOOP

        x := aaa(rec1.c1);
        insert into zt values(x,rec1.c2);


>>    END LOOP;


>>

that way, you are not calling AAA from SQL but from plsql and you don't need the pragmas at all.

>
>
>
>In article <3770d372.0_at_145.227.194.253>,
> "Mark G" <mgumbs_at_nospam.hotmail.com> wrote:
>> 1. Your exception when others was not letting the real error message
>filter
>> up back to you so you never saw the real error problem which was.....
>>
>> ORA-06571: Function AAA does not guarantee not to update
>database
>>
>> The reason why it worked when aaa was a stand-alone function is that
>the
>> compiler can determine the 'purity' level of stand alone functions
>but not
>> when it is part of a package.
>>
>> 2. You do not need returns in procedures.
>>
>> 3. Because of the nature of your for loop, you do not need to
>explicitly
>> declare rec1 as a rowtype of the zt table, the loop handles that for
>you.
>>
>> 3. Because you are using a function in a sql statement, you have to
>declare
>> a pragma
>> in the package body header for the. Look up pragma restrict
>references (and
>> purity) in any good book.
>>
>> This code should work...
>>
>> CREATE OR REPLACE PACKAGE try AS
>> FUNCTION aaa (x IN NUMBER) RETURN number;
>> pragma restrict_references(aaa, WNDS);
>>
>> PROCEDURE rrr (Mm number) ;
>> END try;
>> /
>>
>> CREATE OR REPLACE PACKAGE BODY try AS
>> FUNCTION aaa (x IN NUMBER) RETURN number IS
>> BEGIN
>> return (x+10);
>> END aaa;
>>
>> PROCEDURE rrr(mm IN number) IS
>> CURSOR ccur1 IS SELECT * FROM zt where c2=mm;
>>
>> BEGIN
>> FOR rec1 IN ccur1 LOOP
>> insert into zt values(aaa(rec1.c1),rec1.c2);
>> END LOOP;
>>
>> commit;
>> END rrr;
>>
>> END try;
>>
>> -------------------------------------
>> Mark
>>
>> <snip>
>>
>>

--
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 Jun 25 1999 - 06:39:23 CDT