Re: Can I call another function inside the package?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 23 Jun 1999 11:54:31 GMT
Message-ID: <3773c68e.174760041@newshost.us.oracle.com>

A copy of this was sent to Johnson Chao <zjx110_at_my-deja.com> (if that email address didn't require changing) On Wed, 23 Jun 1999 10:48:57 GMT, you wrote:

>What's Wrong with my package?
>
>Hello, :
> I have been racked by this strange problems for 2 days.
> Any one can tell me what's wrong with my package script file?
>
>The below is the package creating script, in which one procedure will
>call another function aaa. While I put aaa inside the package, it can
>not work, while I put aaa outof the package , and made it a seperate
>function, it work. Anyone can tell me why?
>
>The zt table is quite a easy sample table with two columns
>c1 number , c2 number in it.
>
>Sample Data in zt:
>1 10
>8 10
>100 444
>200 555
>
>when I put the aaa outside the package body,
>In SQLPlus, "exec try.rrr(10)" will make the table zt to:
>1 10
>8 10
>2 10 -- new record
>9 10 -- new created record
>100 444
>200 555
>
>when I put the aaa function inside thepackage body,
>I do the same, but no new record is created.
>
>
>
>CREATE OR REPLACE PACKAGE try AS
> FUNCTION aaa (x IN NUMBER) RETURN number;
>
> PROCEDURE rrr (Mm number) ;
>END try;
>/
>
>CREATE OR REPLACE PACKAGE BODY try AS
> FUNCTION aaa (x IN NUMBER) RETURN number IS
> BEGIN
> return (x+1);
> END aaa;
>
> PROCEDURE rrr(mm IN number) IS
> CURSOR ccur1 IS SELECT * FROM zt where c2=mm;
> rec1 ccur1%ROWTYPE;
>
> BEGIN
> FOR rec1 IN ccur1 LOOP
> insert into zt values(aaa(rec1.c1),rec1.c2);
> END LOOP;
>
> close ccur1;
>
> commit;
> return;
> EXCEPTION
> WHEN OTHERS THEN

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Why would you do this? You are basically saying "if there is some sort of error, please ignore it, undo all work you've done in the procedure and return silently, telling no one that there was in fact an error".

If you remove the exception block, you immeiately discover:

SQL> exec try.rrr(10)
begin try.rrr(10); end;

*
ERROR at line 1:

ORA-06571: Function AAA does not guarantee not to update database
ORA-06512: at "TKYTE.TRY", line 13
ORA-06512: at line 1

The reason it works when AAA is outside the package is because the 'purity' of standalone functions is computed at compile time. when a function is in a package -- the purity is computed ONLY when you direct the compiler to figure it out.

2 solutions --

1. code rrr as:

  7 PROCEDURE rrr(mm IN number)
  8 IS

  9     CURSOR ccur1 IS   SELECT * FROM zt where c2=mm;
 10     rec1 ccur1%ROWTYPE;
 11     l_x  number;
 12  BEGIN
 13          FOR rec1 IN ccur1 LOOP
 14                  l_x := aaa(rec1.c1);
 15                  insert into zt values(l_x,rec1.c2);
 16           END LOOP;
 17  
 17          close ccur1;
 18  
 18          commit;

 19 END rrr;

but then you'll find that your WHEN OTHERS hid this other error:

SQL> exec try.rrr(10)
begin try.rrr(10); end;

*
ERROR at line 1:

ORA-01001: invalid cursor
ORA-06512: at "TKYTE.TRY", line 17
ORA-06512: at line 1

You don't close cursors used in cursor for loops -- plsql does that for you. so you should code:

  7 PROCEDURE rrr(mm IN number)
  8 IS

  9     CURSOR ccur1 IS   SELECT * FROM zt where c2=mm;
 10     rec1 ccur1%ROWTYPE;
 11     l_x  number;
 12  BEGIN
 13          FOR rec1 IN ccur1 LOOP
 14                  l_x := aaa(rec1.c1);
 15                  insert into zt values(l_x,rec1.c2);
 16           END LOOP;
 17          commit;

 18 END rrr;

Now, your commit statement is actually getting executed as well.

2) specify the purity of the function AAA. For example:

SQL> CREATE OR REPLACE PACKAGE try AS

  2          FUNCTION aaa (x IN NUMBER)  RETURN number;
  3          pragma restrict_references(aaa, WNDS, RNDS, WNPS, RNPS );
  4  
  4          PROCEDURE rrr (Mm number)  ;

  5
  5
  5 pragma restrict_references(try, WNDS, RNDS, WNPS, RNPS );   6 END try;
  7 /
Package created.

You have to tell us that

• at a minimum this function does not Write to the Database state (WNDS)
• since it doesn't read the database, or read/write the package state we can also assert RNDS, RNPS, WNPS

Now, rrr can be coded as:

  7 PROCEDURE rrr(mm IN number)
  8 IS

  9     CURSOR ccur1 IS   SELECT * FROM zt where c2=mm;
 10     rec1 ccur1%ROWTYPE;
 11     l_x  number;
 12  BEGIN
 13          FOR rec1 IN ccur1 LOOP
 14                  insert into zt values(aaa(rec1.c1),rec1.c2);
 15           END LOOP;
 16          commit;

 17 END rrr;

successfully.

Please -- forget that WHEN OTHERS even exists. Never use it (unless you follow it with RAISE; to reraise the exception).

> return;
> END rrr;
>END try;
>/
>
>
>In article <377091bb.0_at_145.227.194.253>,
> "Mark G" <mgumbs_at_nospam.hotmail.com> wrote:
>> Could you post the package in question? It may be easier to find.
>>
>> M
>>
>> Johnson Chao wrote in message <7kpvve$usa$1_at_nnrp1.deja.com>...
>> >
>> >
>> >Hello:
>> > I am making a package to include some functions and procedures.
>Inside
>> >one procedure, I call another function inside the same package, but I
>> >can not do that. Something seems wrong wiht the program. When I move
>> >the called function out of my procedure, it works.
>> >(even , I add to package name ahead of the function name did not
>work)
>> >
>> >What's wrong with it?
>> >
>> >--
>> >Johnson Chao
>> >ctc Japan
>> >
>> >
>> >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 Wed Jun 23 1999 - 06:54:31 CDT