How to assign multiple values to a variable using into clause in oracle query?

Question

I have an oracle stored procedure like this

CREATE OR REPLACE PROCEDURE DEMO (V_IN CHAR, V_OUT VARCHAR2) 
IS
  BEGIN
    FOR ITEM IN LOOP (SELECT DISTINCT (NAME) 
                        FROM TABLE1 INTO V_OUT 
                       WHERE ID = V_IN
    LOOP 
      --CODE TO PRINT V_OUT
    END LOOP;
  END;

Now how should I create that V_OUT variable so that it can hold all the values coming from query? I'm doing this in oracle12C.

Answer 1

You don't put the INTO clause in the cursor query. And even if you did, you have it in the wrong place in the SQL statement.

You deal with that when you fetch a row from the query:

CREATE OR REPLACE PROCEDURE 
 DEMO (V_IN CHAR, V_OUT 
   VARCHAR2)IS
  BEGIN
    FOR ITEM IN LOOP
        (SELECT DISTINCT (NAME) 
         FROM TABLE1 
         WHERE ID= V_IN
        )
    LOOP 
      dbms_output.put_line(item.name);
      v_out := item.name;
     END LOOP;
   END;

But then the problem is that we just keep overlaying the previous value, so that when your procedure actually exits, the only value of v_out is that last assigned. If you truely need a collection of values, you need to declare your output variable to be a ref cursor, and adjust code accordingly. I've never actually worked with them, so perhaps someone else will chime in.

Answer 2

You can work with collections, like this:

--declare the pakage type
CREATE OR REPLACE PACKAGE PKG_TYPES AS        
  TYPE LIST_VARCHAR        IS TABLE OF VARCHAR2(2000);                        
END;

--create the proc that will assemble the value list
CREATE OR REPLACE PROCEDURE DEMO ( V_IN IN  varchar2, V_OUT IN OUT PKG_TYPES.LIST_VARCHAR) IS
  BEGIN
    FOR ITEM IN ( 
      SELECT DISTINCT (NAME) name 
        FROM (SELECT 'X' ID, 'A' name FROM dual
              UNION 
              SELECT 'X' ID, 'b' name FROM dual
              UNION
              SELECT 'y' ID, 'c' name FROM dual
              ) TABLE1 
        WHERE ID= V_IN 
        )
    LOOP
      V_OUT.EXTEND;
      V_OUT(V_OUT.LAST) := item.name;
      --CODE TO PRINT V_OUT
    END LOOP;
  END;

--use the list. I separated this step but it can be in the demo proc as well
DECLARE 
  names PKG_TYPES.LIST_VARCHAR := PKG_TYPES.LIST_VARCHAR();
BEGIN
  demo('X',names) ;
  FOR i IN names.first..names.last LOOP
    Dbms_Output.put_line(i); 
  END LOOP;
END;

You will have to handle exceptions for when no value is returned from the cursor (when no ID is found).

Answer 3

If you need a collection variable - you can use a nested table and bulk collect like below. To be able to return the value from the procedure you will need to declare the nested table type in some package or on DB schema level.

declare
    type test_type is table of varchar2(2000);

    test_collection test_type;
begin
    select distinct(name) bulk collect into test_collection
    from (
        select 1 id, 'AAA' name from dual
        union all
        select 1 id, 'BBB' name from dual
        union all
        select 1 id, 'AAA' name from dual
        union all
        select 2 id, 'CCC' name from dual
    )
    where id = 1;

    for i in test_collection.first..test_collection.last loop
        dbms_output.put_line(test_collection(i));
    end loop;
end;
/

If you just need a string with concatenated values - you can use listagg to create it like below

declare
    test_str varchar2(4000);
begin
    select listagg(name, ', ') within group(order by 1)
    into test_str
    from (
        select distinct name
        from (
            select 1 id, 'AAA' name from dual
            union all
            select 1 id, 'BBB' name from dual
            union all
            select 1 id, 'AAA' name from dual
            union all
            select 2 id, 'CCC' name from dual
        )
        where id = 1
    );

    dbms_output.put_line(test_str);
end;
/