Re: User defined functions in PL/SQL loop

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 13 Feb 1999 00:34:06 GMT
Message-ID: <36cec7ce.20658074@192.86.155.100>

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Fri, 12 Feb 1999 22:01:26 -0000, you wrote:

>Oracle 7.3.4
>Pure user defined function that takes 3 in parameters
>Usable from SQL*Plus in simple select.
>
>However in PL/SQL :-
>
>create or replace procedure fix_something(
> i_datestamp date, i_numeric number
>) is
>
>m_thingy varchar2(10);
>
>cursor c1 (i_date1 date, i_number1 number, i_thingy varchar2) is
> select my_function(i_date1, i_number, i_thingy) from my_table where
>......;
>
>begin
>
> m_thingy := 'xyz'
> for r1 in c1 (i_datestamp, i_numeric, m_thingy) loop
> ....
> end loop;
>end;
>.

Jonathan --

I just ran:

SQL> select * from v$version;

BANNER

Oracle7 Server Release 7.3.4.0.1 - Production PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production NLSRTL Version 3.2.4.0.0 - Production

SQL> create function my_function( x in date, y in number, z in varchar2 )   2 return number
  3 as
  4 begin
  5 return 0;
  6 end;
  7 /

Function created.

SQL>
SQL> select my_function( sysdate, 2, 'xyz' ) from dual;

MY_FUNCTION(SYSDATE,2,'XYZ')

SQL>
SQL> create procedure my_procedure( i_datestamp in date, i_num in number ) 2 as

  3          cursor c1 (l_x date, l_y int, l_z varchar2 )
  4          is select my_function( l_x, l_y, l_z ) col from dual;
  5  
  5          m_thingy  varchar2(10);
  6  begin
  7          m_thingy := 'xyz';
  8  
  8          for x in c1( i_datestamp, i_num, m_thingy ) loop
  9                  dbms_output.put_line( x.col );
 10          end loop;

11 end;
12 /

Procedure created.

SQL>
SQL> exec my_procedure( sysdate, 2 )
0

PL/SQL procedure successfully completed.

which seems to be your scenario but I cannot reproduce... does the above reproduce on your system?

btw: i also ran it in

SQL> select * from v$version;

BANNER

Oracle7 Server Release 7.1.6.2.0 - Production Release PL/SQL Release 2.1.6.2.0 - Production
CORE Version 2.3.7.1.0 - Production (LSF Alpha) TNS for SVR4: Version 2.1.6.0.0 - Production NLSRTL Version 2.3.6.0.0 - Production

and it works there as well.

>
>Error ORA-904 (number may be wrong, I'm not on-site) Value Error.
>
>
>With SQL trace on, I can see that the cursor is
>actually sent to the database engine for parsing as
>
> select my_function(,,) from my_table where .... ;
>
>In other words the input parameters simply cease to exist.
>
>
>Have I missed something obvious about restriction in
>PL/SQL and user-defined functions in the manuals or
>is this an error ?
>
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 12 1999 - 18:34:06 CST