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 -> Embedding a function call in a SELECT statement

Embedding a function call in a SELECT statement

From: Jeremy <jeremy0505_at_gmail.com>
Date: Tue, 2 Jan 2007 13:51:56 -0000
Message-ID: <MPG.20047365b58667d298a3b5@news.individual.net>

Hello experts, the following is valid in a procedure within a package where the myfunc function is also defined:

  select myfunc(1,ID) from my_id_tab;

The function myfunc is defined as

  function myfunc

(p_id_array in number,

         p_id in number)
  return varchar2;

NOW the problem.

Suppose myfunc is defined like this:

  function myfunc

(p_id_array in utils.numarray,

         p_id in number)
  return varchar2;

And the datatype numarray is defined in a UTILS package header as

    type numarray is table of number index by binary_integer;

Then within my procedure, I have a local variable defined:

  l_id_array ic_util.numarray;

Then I change the original select statement to be

  select myfunc(l_id_array,ID) from my_id_tab;

Now if I try to compile my procedure, I get error: 630/7 PL/SQL: SQL Statement ignored
782/12 PLS-00306: wrong number or types of arguments in call to 'MYFUNC' 782/12 PL/SQL: ORA-00904: "MYPACKAGE"."MYFUNC": invalid identifier 782/24 PLS-00382: expression is of wrong type

I think all the data types match correctly, is there a reaon why I should be able to embed the function in the select clause only if the input parameters are NOT of a user-defined type?

A simple call to the function outside of the select clause works fine:

    l_str := id_in_array(l_id_array,1);

-- 
jeremy

 ============================================================
   ENVIRONMENT:                                             
   Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8 
 ============================================================
Received on Tue Jan 02 2007 - 07:51:56 CST

Original text of this message

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