How to write a function which returns associative array and call the function in Oracle PLSQL?

Question

How to declare and define function which returns associative array in Oracle PL/SQL? And how to call the same function from procedure ?

Answer 1

Declaration of package

CREATE or replace PACKAGE myinv AS 
                
           TYPE namevalue_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
           Procedure Test ( params IN varchar2 );
           Function ParseParams ( params IN varchar2) RETURN namevalue_type;
            
    END myinv ; 
    /
    

Definition of the package

    CREATE OR REPLACE PACKAGE BODY myinv AS  
    --   TYPE namevalue_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
    

       -- Function which does the parsing and returns associative array
       Function ParseParams ( params IN varchar2) 
       RETURN namevalue_type 
       IS
          nv_pairs SYS.ODCIVARCHAR2LIST;
          nv_pair  SYS.ODCIVARCHAR2LIST;
          i number;
          j number;
          name varchar2(100);
          value varchar2(100);
          nv_t namevalue_type;
    
       BEGIN 
       
             SELECT regexp_substr(params,'(.*?)(;|$)', 1, level, NULL, 1)
              BULK COLLECT INTO nv_pairs
              FROM   dual
              CONNECT BY level <= regexp_count(params, ';') + 1;
            
              i := nv_pairs.FIRST;  -- Get first element of array
              while i is not null LOOP
                      
                  SELECT regexp_substr(nv_pairs(i),'(.*?)(=|$)', 1, level, NULL, 1)
                  BULK COLLECT INTO nv_pair
                  FROM   dual
                  CONNECT BY level <= regexp_count(nv_pairs(i), '=') + 1;
                  
                  j := nv_pair.FIRST;
                  while j is not null LOOP
                    name := nv_pair(j);
                    j := nv_pairs.NEXT(j); 
                    value := nv_pair(j);
                    nv_t(name) := value;
                    j := nv_pairs.NEXT(j); 
                  END LOOP;
                  
                 i := nv_pairs.NEXT(i);  -- Get next element of array      
              END LOOP;
              
              name := nv_t.FIRST;
              WHILE name IS NOT NULL LOOP
                name := nv_t.NEXT(name);
              END LOOP;
        
            RETURN nv_t;
    
       END ParseParams; 
       
       --Procedure to test the function
        Procedure Test ( params IN varchar2 )
        AS
        nvt namevalue_type;
        name varchar2(100);
        i number;
        BEGIN
        dbms_output.put_line('  Inside Procedure Test');
            i := 1;
            nvt := ParseParams('param1=10;param2=20');
            name := nvt.FIRST;
            WHILE name IS NOT NULL LOOP
              --dbms_output.put_line('            ' || i ||'. Name: ' || name || '  Value: ' || nvt(name) );
              dbms_output.put_line('            ' || i ||'. Name: ' || name || '  Value: ' || (to_number(nvt(name)) + 10) );
              name := nvt.NEXT(name);
              i := i+1;
            END LOOP;
              
              
        END Test;
    END myinv; 
    /
    

Run the test

    BEGIN
      DBMS_OUTPUT.PUT_LINE('Test Parse.........................');
      myinv.Test('Check');
    END;
    /