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 -> Re: problem in calling a user-defined package function i an SQL statment

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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 25 Apr 2002 05:08:27 -0700
Message-ID: <aa8rjr0fn2@drn.newsguy.com>


In article <zjNx8.119228$G72.78489_at_sccrnsc01>, "Don says...
>
>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
>
>

Your package has side effects, what you are trying to do it not possible or practical. It works in special cases only and a simple change in the access plan will REALLY mess you up.

In short, anything you try to build this way will be very very unreliable -- it is a "trick" and not a good idea. Suggest you abandon this approach now.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Thu Apr 25 2002 - 07:08:27 CDT

Original text of this message

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