Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> problem in calling a user-defined package function i an SQL statment

problem in calling a user-defined package function i an SQL statment

From: Don Woods <Donwoods6_at_attbi.com>
Date: Thu, 25 Apr 2002 06:25:35 GMT
Message-ID: <zjNx8.119228$G72.78489@sccrnsc01>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US