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;Received on Thu Apr 25 2002 - 01:25:35 CDT
/
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