Compilation error calling Function with Table Parameter from Procedure

Question

In a package, I have:

A Type of Table of Numbers
A Function that takes said Table of Numbers type as a parameter
A Procedure that needs to call said Function using Table of Numbers type

I know the Type works, I know the Function works. But when I try to call the Function within the Procedure, I get the following Compilation errors:

Error(262,16): PLS-00306: wrong number or types of arguments in call to '[FUNCTION]' Error(262,16): PL/SQL: ORA-00904: "[PACKAGE]"."[FUNCTION]": invalid identifier Error(264,41): PLS-00642: local collection types not allowed in SQL statements

Package Header:

CREATE OR REPLACE PACKAGE [PACKAGE] AS 

    TYPE NUMBER_LIST IS TABLE OF NUMBER;

    FUNCTION [FUNCTION] (
        [LIST_PARAM]       NUMBER_LIST
      ) RETURN VARCHAR2;

    PROCEDURE [PROCEDURE] (
        RECORDS_AFFECTED    OUT NUMBER
      );

END [PACKAGE];

Problem Code:

  PROCEDURE [PROCEDURE](
      RECORDS_AFFECTED    OUT NUMBER
  )AS
      [ID_LIST]  NUMBER_LIST;
  BEGIN
      ...

      SELECT  [COLUMN]
      BULK COLLECT INTO    [ID_LIST]
      FROM    [TABLE]

      INSERT INTO [TABLE]( [COLUMN] )
      SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )
      FROM    [OTHER TABLE];

      ...  
  END [PROCEDURE];

And the error revolves around this line:

SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )

I have tried:

[ID_LIST]  NUMBER_LIST;
[ID_LIST]  [PACKAGE].NUMBER_LIST;

SELECT   [FUNCTION]( [ID_LIST] )
SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )
SELECT   [PACKAGE].[FUNCTION]( [ID_LIST] )
SELECT   [PACKAGE].[FUNCTION]( LIST_PARAM => [ID_LIST] )

But I feel like the problem is in the way the [ID_LIST] is being passed.

Any thoughts on what I'm doing wrong passing this Type TABLE OF NUMBER parameter to the function?

What version of Oracle do you use, 11g, 12c ? In 11g you cannot use PL/SQL types (declared in the Pl/SQL package/procedure/function) in SQL statements, you have to use schema level types. In 12c this restriction was relaxed. — krokodilko
– krokodilko, Commented Dec 7, 2016 at 18:02
I am having this problem on 12c but the software needs to be backwards compatible with 11g, so thank you for pointing out this limitation as well — Tom Halladay
– Tom Halladay, Commented Dec 7, 2016 at 20:04

Tom Halladay · Accepted Answer · 2016-12-07 21:08:12Z

2

The problem isn't the parameter list to the function per se, it's that you're trying to use the locally defined type NUMBER_LIST in an SQL statement (that's the PLS-00642 error). The SQL engine knows nothing about this type. You would have to create a database type for this to succeed e.g.:

CREATE OR REPLACE  TYPE NUMBER_LIST IS TABLE OF NUMBER;

or else something like this:

  PROCEDURE [PROCEDURE](
      RECORDS_AFFECTED    OUT NUMBER
  )AS
      [ID_LIST]  NUMBER_LIST;
      fnc_ret    VARCHAR2;
  BEGIN
      ...

      SELECT  [COLUMN]
      BULK COLLECT INTO    [ID_LIST]
      FROM    [TABLE]

      fnc_ret := [FUNCTION]( LIST_PARAM => [ID_LIST] );

      INSERT INTO [TABLE]( [COLUMN] )
      VALUES (fnc_ret);

      ...  
  END [PROCEDURE];

You'll likely need to modify this to suit your real code, but this is the idea. You just can't use ID_LIST in an SQL statement without making it a database defined type, as the SQL engine can't see it.

PL/SQL is one "engine" within the RDBMS. It has a statement processor, and when it encounters a SQL statement, it hands it off to the SQL "engine" for processing. I think you can see why that causes the problem.

edited Dec 7, 2016 at 21:08

Tom Halladay

5,7917 gold badges51 silver badges67 bronze badges

answered Dec 7, 2016 at 18:02

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Halladay Over a year ago

your suggestion to use a database type was successful. Is it possible I could skip the type altogether and just create the parameter as the TABLE OF DECIMAL to begin with?

DCookie Over a year ago

I don't know of a way to do that.

Collectives™ on Stack Overflow

Compilation error calling Function with Table Parameter from Procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related