| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.server -> problem in calling a user-defined package function i an SQL statment
I have created a package function that performs a simple function, similar
to Oracle's analytic function row_number() over (partition by <key> order by
<key>).
The function uses two persistent variables in a package. If the control
variable is the same then a counter is incremented else it resets it to 1.
I am currently using version 8.1.7.3 on Solaris
Package foo
function rn( istring in varchar2) return number;
end;
/
Package body foo
MyString varchar2;
MyCounter number;
--
function rn( istring in varchar2) return number as
begin
if MyString <> istring then
MyCounter := 1;
MyString := istring;
else
MyCounter := MyCounter + 1;
end if;
return MyCounter;
end;
end;
/
When I call this function in the follwoing SQL statement it work fine:
select x.*, foo.rn( x.table_type) RN from (
select table_name, table_type from cat order by 2,1
) x
The result sets are as follows:
Table_name, type_type, RN
TBL_A, TABLE, 1
TBL_B, TABLE, 2
VW_A, VIEW, 1
When I call this function in the following SQL statement it fails:
select z.* from (
select x.*, foo.rn( x.table_type) RN from (
select table_name, table_type from cat order by 2,1
) x
) z
where z.RN <= z.RN;
The result sets are as follows:
Table_name, type_type, RN
TBL_A, TABLE, 3
TBL_B, TABLE, 6
VW_A, VIEW, 3
Conclusion, the reference to variable z.rn actually invokes the function
call instead of evaluating the value from the inner view.
I have tried PRAGMA statements, but that did not help.
Does anybody know why/how this happens?
Thanks for your help, Don
Received on Thu Apr 25 2002 - 01:25:35 CDT
![]() |
![]() |