oracle-databaseplsql

Save output of loop in single table in oracl SQL


I want to go through a for loop in sql and save the output of every loop in a single table.

declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t(4, 6, 7);

begin
   for i in 1..array.count 
   loop
       dbms_output.put_line(array(i)+1);
       dbms_output.put_line(array(i)+10);
      
   end loop;
   
end;

This code gives me 2 lines of output every iteration. i would like to save them in a single table, but can't seem to find the function to 'append'? My wanted output is the following: Table:

5
14
7
16
8
17

Solution

  • To display the output on the same line, you can use DBMS_OUTPUT.PUT (which doesn't flush the output to a new line) instead of DBMS_OUTPUT.PUT_LINE (which does):

    declare
       type array_t is varray(3) of varchar2(10);
       array array_t := array_t(4, 6, 7);
    begin
       for i in 1..array.count 
       loop
           dbms_output.put(array(i)+1);
           dbms_output.put(' ');
           dbms_output.put_line(array(i)+10);
       end loop;
    end;
    /
    

    or you can use a single DBMS_OUTPUT.PUT_LINE and string concatenation:

    declare
       type array_t is varray(3) of varchar2(10);
       array array_t := array_t(4, 6, 7);
    begin
       for i in 1..array.count 
       loop
           dbms_output.put_line((array(i)+1) || ' ' || (array(i)+10));
       end loop;
    end;
    /
    

    Which both output:

    5 14
    7 16
    8 17
    

    If you want a result set then you can use SQL (rather than PL/SQL):

    SELECT COLUMN_VALUE + 1 AS value1,
           COLUMN_VALUE + 10 AS value2
    FROM   TABLE(SYS.ODCINUMBERLIST(4, 6, 7))
    

    Which outputs:

    VALUE1 VALUE2
    5 14
    7 16
    8 17

    fiddle