Skip to Main Content
  • Questions
  • DBMS_output.get_lines: Why I am getting 11th line as "value: "

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Santosh.

Asked: June 06, 2016 - 7:37 am UTC

Last updated: June 06, 2016 - 9:38 am UTC

Version: Oracle Database 11g Release 11.2.0.1.0 - Production

Viewed 1000+ times

You Asked

declare
lines dbmsoutput_linesarray;
status integer;
--type array is table of varchar2(2000);
--result array;
begin
select level bulk collect into lines from dual connect by level<=10;
status:=20;
for i in 1..lines.count
loop
dbms_output.put_line(lines(i));
end loop;
dbms_output.get_lines(lines,status);
for i in lines.first..lines.last loop
dbms_output.put_line('Value: '||lines(i));
end loop;
dbms_output.put_line('Total lines fatched: '||status);
end;
/
--------
output
-----------
Value: 1
Value: 2
Value: 3
Value: 4
Value: 5
Value: 6
Value: 7
Value: 8
Value: 9
Value: 10
Value:
Total lines fatched: 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

and Chris said...

Because there are 11 lines in your output!

DBMS_output.put_line ends calls with a new line character. So you have an extra line at the end:

SQL> declare
  2    lines dbmsoutput_linesarray;
  3    status integer;
  4
  5  begin
  6    select level bulk collect into lines from dual connect by level<=10;
  7    status:=15;
  8    for i in 1..lines.count
  9    loop
 10      dbms_output.put_line ( lines ( i ) ) ;
 11    end loop;
 12
 13    dbms_output.get_lines ( lines,status ) ;
 14
 15    dbms_output.put_line ('#Lines: ' || lines.count ||
 16      ' first ' || lines.first || ' last ' || lines.last ) ;
 17    for i in lines.first..lines.last
 18    loop
 19      dbms_output.put_line ( 'Value: '|| lines ( i ) || ' i ' || i ) ;
 20    end loop;
 21
 22  end;
 23  /
#Lines: 11 first 1 last 11
Value: 1 i 1
Value: 2 i 2
Value: 3 i 3
Value: 4 i 4
Value: 5 i 5
Value: 6 i 6
Value: 7 i 7
Value: 8 i 8
Value: 9 i 9
Value: 10 i 10
Value:  i 11


Note you don't get this with dbms_output.get_line:

SQL> declare
  2    lines dbmsoutput_linesarray;
  3    line  varchar2(1000);
  4    status integer;
  5
  6    inx pls_integer := 0;
  7  begin
  8    select level bulk collect into lines from dual connect by level<=10;
  9    status:=15;
 10    for i in 1..lines.count
 11    loop
 12      dbms_output.put_line ( lines ( i ) ) ;
 13    end loop;
 14
 15    dbms_output.get_line( line, status);
 16    while ( line is not null ) loop
 17      inx := inx + 1;
 18      lines (inx) := line;
 19      dbms_output.get_line( line, status);
 20    end loop;
 21    dbms_output.put_line ('#Lines: ' || lines.count ||
 22      ' first ' || lines.first || ' last ' || lines.last ) ;
 23    for i in lines.first..lines.last
 24    loop
 25      dbms_output.put_line ( 'Value: '|| lines ( i ) || ' i ' || i ) ;
 26    end loop;
 27
 28  end;
 29  /
#Lines: 10 first 1 last 10
Value: 1 i 1
Value: 2 i 2
Value: 3 i 3
Value: 4 i 4
Value: 5 i 5
Value: 6 i 6
Value: 7 i 7
Value: 8 i 8
Value: 9 i 9
Value: 10 i 10

This is less efficient than get_lines (plural) though.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Santosh Panigrahi, June 06, 2016 - 10:14 am UTC

Thank you for answering Chris.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library