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?
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.
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
