Function in a view problem

Hi all,

One of our developer is testing a function as shown below and this function is used in a view. Just to see how many times the function is called, we have a dbms_output statement in the function body. I was surprised to find that the function gets called 3 times for each row.

Maybe the glaring truth is right in front of me, but I don't see it. For all that matters, "episode_airings" is a large table.

Please help .. why the function is being called 3 times? I also traced it with dbms_pipe, all the input parameters are same for all the threee calls.

<package>
CREATE OR REPLACE PACKAGE DBPK_raj_TEST1 IS function getnumber(nemp in number, nNumber IN number) return number; PRAGMA RESTRICT_REFERENCES (GetNumber,WNDS); END DBPK_raj_TEST1;
/

CREATE OR REPLACE PACKAGE BODY DBPK_raj_TEST1 IS function getnumber(nemp in number, nNumber In Number) return number is x number;
BEGIN
  dbms_output.put_line ('In Procedure' || to_char(nNumber));   sendtracemessage('VIEW: ' || to_char(nemp) || ' - ' || to_char(nnumber) || ' - ' || to_char(nnumber+1));
  Return (nNumber + 1);
END getnumber;

--
END DBPK_raj_test1;
/
</package>

<view>
create or replace view raj_test (dept_no, rn, pkg_rtn) as
select ep_number Dept_no, rownum rn, dbpk_raj_test1.getnumber(ep_number,
rownum) pkg_rtn
from episode_airings where rownum < 10
</view>

<script>
Declare
cursor c is select dept_no, rn, pkg_rtn from raj_test;
n1 number := 0;
n2 number := 0;
n3 number := 0;
begin
dbms_output.enable(100000);
dbms_output.put_line ('$$$$$$$$$$$$$$$$$$$');
dbms_output.put_line ('$ For Loop Cursor $');
dbms_output.put_line ('$$$$$$$$$$$$$$$$$$$');
for r in c loop
n1 := c%rowcount;
dbms_output.put_line ('$ Loop Counter ' || to_char(n1));
dbms_output.put_line ('$     value ' || to_char(r.dept_no) || ' - ' ||
                      to_char(r.rn) || ' - ' ||  to_char(r.pkg_rtn));
end loop;
/
</script>

<explain plan>
SELECT STATEMENT Optimizer=RULE
  VIEW OF RAJ_TEST
    COUNT (STOPKEY)
      TABLE ACCESS (FULL) OF EPISODE_AIRINGS
</explain plan>

<output>
SQL> @raj
$$$$$$$$$$$$$$$$$$$


$ For Loop Cursor $

$$$$$$$$$$$$$$$$$$$
In Procedure1
In Procedure1
In Procedure1


$ Loop Counter 1

$     value 32635 - 1 - 2

In Procedure2
In Procedure2
In Procedure2


$ Loop Counter 2

$     value 32636 - 2 - 3

In Procedure3
In Procedure3
In Procedure3


$ Loop Counter 3

$     value 32637 - 3 - 4

In Procedure4
In Procedure4
In Procedure4


$ Loop Counter 4

$     value 32638 - 4 - 5

In Procedure5
In Procedure5
In Procedure5


$ Loop Counter 5

$     value 32639 - 5 - 6

In Procedure6
In Procedure6
In Procedure6


$ Loop Counter 6

$     value 32640 - 6 - 7

In Procedure7
In Procedure7
In Procedure7


$ Loop Counter 7

$     value 32641 - 7 - 8

In Procedure8
In Procedure8
In Procedure8


$ Loop Counter 8

$     value 32642 - 8 - 9

In Procedure9
In Procedure9
In Procedure9


$ Loop Counter 9

$     value 32643 - 9 - 10

</output>

Connected to: 
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

TIA
Raj
______________________________________________________
Rajendra Jamadagni              MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !



*********************************************************************

This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

*********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: rajendra.jamadagni_at_espn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).