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
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 |