CODEWITHSUNDEEP
sqloracle-databaseplsqlassociative-arraysql-function
Mike T
Mike T

Reputation: 25

Associative array in Oracle as Parameter

I have something like:

TYPE MyType IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

How can I use this in an function like

function TestFunction
(
 Input    in   MyType 
) return number;

as Parameter?

Something like TestFunction(MyType('Value1','Value2'));

don't work since I need a key -> value definition like

TestFunction(MyType('key1': 'value1', 'key2': 'value2'));

How can I pass a associative array in Oracle as Parameter?

Upvotes: 1

Views: 3208

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Oracle 18c introduces Qualified Expressions:

Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed.

A qualified expression combines expression elements to create values of a RECORD type or associative array type.

Qualified expressions use an explicit type indication to provide the type of the qualified item.

DECLARE
    TYPE MyType IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
    
    FUNCTION TestFunction(Input IN MyType) RETURN NUMBER
    IS
    BEGIN
        RETURN Input.Count;
    END;
BEGIN
    DBMS_OUTPUT.put_line(TestFunction(MyType('Ind1' => 1, 'Ind2' => 2)));
END;
/
-- output:
-- 2

Creating type(varray) and direct SELECT from collection:

CREATE OR REPLACE TYPE MyType is varray(10) of integer;
/

SELECT *
FROM MyType(1,2,3);
-- Output:
COLUMN_VALUE
===========
1
2
3

Or as default parameter to function:

CREATE OR REPLACE FUNCTION MyFunc(Input IN MyType DEFAULT MyType(1,2,3)) 
RETURN NUMBER
IS
BEGIN
   RETURN Input.Count;
END;
/

SELECT MyFunc FROM dual;
-- Output:
MYFUNC
======
3

db<>fiddle demo

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

This is a simple way:

SQL> set serveroutput on
SQL> declare
  2      TYPE MyType IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
  3      myVar MyType;
  4      function TestFunction
  5      (
  6       Input    in   MyType
  7      ) return number is
  8      begin
  9          return Input.count;
 10      end;
 11
 12  begin
 13      myVar(1) := 'one';
 14      myVar(2) := 'two';
 15      myVar('three') := 'THREE';
 16      dbms_output.put_line(TestFunction(myVar));
 17  end;
 18  /
3

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions