PLSQL: Assign array returned by one function into an array defined in another function

Question

I have two functions – func1 and func2. func1 selects some values from a table and assigns it to an array and returns this array. func2 calls func1. func2 uses the array returned by func1 to perform some operations. My question is: How to assign the array returned by func1 to an array in func2. Please find below the code snippets of func1 and func2.

function func1 (table1 varchar2, table2 varchar2) return j_list
  is
   type j_list is varray (10) of VARCHAR2(50);
   attr_list j_list := j_list(); 
   counter integer :=0;  
  begin   
for i in 
    (select  a.column_name from  all_tab_columns a) 

    LOOP 
      counter := counter + 1; 
      attr_list.extend; 
      attr_list(counter)  := i.column_name;
    END LOOP;
   return attr_list;
end func1;


function func2 (table1 varchar2, table2 varchar2) return varchar2
  is
   type new_j_list is varray (10) of VARCHAR2(50);
   new_attr_list j_list := j_list();
   new_attr_list.extend;
   new_attr_list() := func1 (table1, table2) /*does this assign the array                  
that is returned by func1 into the array new_attr_list ??? */
   jt varchar2(4000);
  begin  
  jt := /*some operations using the new_attr_list*/
  return jt;
end func2;

score 1 · Answer 1 · answered Oct 12 '18 at 11:32

Your code have some errors. You cannot use j_list without declaring it at first place. The scope of j_list has to be declared prior to use. See below how you can do it. Also method extend should be used in begin block not in declaration block.

CREATE OR REPLACE TYPE j_list IS VARRAY (10) OF VARCHAR2(50);
/

CREATE OR REPLACE FUNCTION func1 (    table1 VARCHAR2,
                                      table2 VARCHAR2
                                  ) 
RETURN j_list 
IS    
    attr_list   j_list := j_list ();
    counter     INTEGER := 0;
BEGIN
    FOR i IN ( SELECT a.column_name FROM all_tab_columns a) 
    LOOP
        counter := counter + 1;
        attr_list.extend;
        attr_list(counter) := i.column_name;
    END LOOP;
    RETURN attr_list;
END func1;
/

CREATE OR REPLACE FUNCTION func2 ( table1 VARCHAR2,
                                  table2 VARCHAR2
) RETURN VARCHAR2 
IS
    new_attr_list   j_list := j_list ();
    jt              VARCHAR2(4000);
BEGIN
    new_attr_list.extend;
    new_attr_list:= func1(table1, table2 );

    jt := 'some operations using the new_attr_list';
    RETURN jt;
END func2;

PLSQL: Assign array returned by one function into an array defined in another function

1 Answers1