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

Question

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');

Ergi Nushi · Accepted Answer · 2019-11-14 13:16:51Z

1

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.

answered Nov 14, 2019 at 13:16

Ergi Nushi

8731 gold badge6 silver badges18 bronze badges

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

2 Comments

jayanth Over a year ago

how to create filter_map_type type in schema level..? (I need to pass filter_map_type as input to a procedure so there in declaration part we need to define a variable with this same type ).

Ergi Nushi Over a year ago

You can create "valuearray" as vararray type, but unfortunately I can't say the same thing for filter_map_type. Maybe this might give you a hint on how to create filter_map_type: stackoverflow.com/questions/1066476/…

Collectives™ on Stack Overflow

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

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