How to call a pl/SQL functuion having array as an argument using SQL

Question

I have a SQL function named as IP_ELEARN_PERSON.F_GET_PERSON(int, string, array).

Now I want to run this function directly from sql developer and I am trying to execute this function like this --

select IP_ELEARN_PERSON.F_GET_PERSON(32433,'SOURCED',('ALL')) from dual;

Now the problem is when I am trying to execute this funtion I am getting the following errror --

ORA-06553: PLS-306: wrong number or types of arguments in call to 'F_GET_PERSON'
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 3 Column: 8

We were calling this function from java by using setArray method of the CallableStatment like this--

cstmt.setArray(4, new ObtainSqlArrayFromJava().returnSqlArray(
                    underlyingConn, roles));

So my doubt is, is the way I used to mention the array in the query right?

I went through many Stack Overflow posts but no where any thing was written for arrays as an argument.

score 0 · Answer 1 · edited May 23 '17 at 12:24

CREATE TYPE string_list IS TABLE OF VARCHAR2(100);
/

CREATE FUNCTION F_GET_PERSON (
  id    INT,
  type  VARCHAR2,
  array string_list
) RETURN INT
AS
BEGIN
  RETURN 0;
END;
/

SELECT F_GET_PERSON( 1, 'SOURCED', string_list( 'ALL' ) )
FROM   DUAL;

If you want some java code for passing an array as a bind variable to a PreparedStatement then you can look at my answer here. You should be able to easily adapt it to a CallableStatement.

score 0 · Answer 2 · answered Apr 13 '16 at 13:37

As stated here:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS00501

Oracle supports the following collections types: associative array, VARRAY (variable-size array), and nested table.

You'll need to properly initialize the collection before passing it to the function.

How to call a pl/SQL functuion having array as an argument using SQL

2 Answers2