CODEWITHSUNDEEP
sqloracle-databaseplsql
jayanth
jayanth

Reputation: 27

How to pass keys and values as a parameter to stored procedure in pl/sql

for a stored procedure I need to pass keys and values as a parameter, but the problem is instead of value I need to pass an array with respective of key-value.`

SET SERVEROUTPUT ON;

DECLARE
    TYPE filter_map_type IS
        TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
    TYPE valuearray IS
        VARRAY(5) OF VARCHAR2(10);
    filter_map   filter_map_type;
    key_map      VARCHAR2(30);
    x            valuearray;
BEGIN
    filter_map('Key 1') := 'Value 1';
    filter_map('Key 2') := 'value 2';
    filter_map('Key 3') := 'Value 3';
    x := valuearray('a1', 'a2', 'a3');
    key_map := filter_map.first;
    WHILE key_map IS NOT NULL LOOP
        dbms_output.put_line('element('
                             || key_map
                             || '): '
                             || filter_map(key_map));

        key_map := filter_map.next(key_map);
    END LOOP;

END;

instead of value1 in filter_map I need to pass an array(x) . Like :-
filter_map('country') := ('india','us','japan'); filter_map('city') := ('mumbai','hyderabad','delhi');

Upvotes: 0

Views: 1119

Answers (1)

Ergi Nushi
Ergi Nushi

Reputation: 863

You can try this:

DECLARE

    TYPE valuearray IS
        VARRAY(5) OF VARCHAR2(10);
    TYPE filter_map_type IS
        TABLE OF valuearray INDEX BY VARCHAR2(30);

    filter_map   filter_map_type;
    key_map      VARCHAR2(30);
    x            valuearray;
    y            valuearray;
    z            valuearray;

BEGIN
    x := valuearray('a1', 'a2', 'a3');
    y := valuearray('b1', 'b2', 'b3');
    z := valuearray('c1', 'c2', 'c3');

    filter_map('Key 1') := x;
    filter_map('Key 2') := y;
    filter_map('Key 3') := z;

    key_map := filter_map.first;
    WHILE key_map IS NOT NULL LOOP
        dbms_output.put_line('element('|| key_map|| '): (' || filter_map(key_map)(1) || ', '  || filter_map(key_map)(2) || ', ' || filter_map(key_map)(3) || ')' );
        key_map := filter_map.next(key_map);
    END LOOP;

END;

You can do another loop if your valuearray is dynamic in size in order to print them.

Upvotes: 1

Related Questions