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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
