Home » SQL & PL/SQL » SQL & PL/SQL » Collections (Oracle 11.2.0.0)
Collections [message #659498] Thu, 19 January 2017 07:30 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

Why is the following behaviour.

declare
   type t_rec is record(a number, b number);
   type t_tt is table of t_rec;
   l_tt t_tt;
begin
   select c,d 
     bulk collect into  l_tt 
     from (select 1 c , null d from dual 
            union all
           select 2, null from dual 
            union all 
           select 3, 10 from dual);
   
   dbms_output.put_line(l_tt(1).b);
   dbms_output.put_line(l_tt(2).b);
   dbms_output.put_line(l_tt(3).b);
end;
/

o/p: 
10
10
10


I am guessing the output should be null, null and 10 but I am getting as 10, 10, 10.

Thank you in advance.

Regards,
Poinnters
Re: Collections [message #659500 is a reply to message #659498] Thu, 19 January 2017 07:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I don't believe your oracle version is 11.2.0.0

SQL> set serveroutput on
SQL> ed
Wrote file afiedt.buf

  1  declare
  2	type t_rec is record(a number, b number);
  3	type t_tt is table of t_rec;
  4	l_tt t_tt;
  5  begin
  6	select c,d
  7	  bulk collect into  l_tt
  8	  from (select 1 c , null d from dual
  9		 union all
 10		select 2, null from dual
 11		 union all
 12		select 3, 10 from dual);
 13	dbms_output.put_line(l_tt(1).b);
 14	dbms_output.put_line(l_tt(2).b);
 15	dbms_output.put_line(l_tt(3).b);
 16* end;
 17  /
10

PL/SQL procedure successfully completed.

[Updated on: Thu, 19 January 2017 07:36]

Report message to a moderator

Re: Collections [message #659503 is a reply to message #659500] Thu, 19 January 2017 07:46 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Sorry, It is Release 11.2.0.4.0

First time it shows expected values, but if you run second time in the same session it shows no null values. Previous not null values are repeated.

Another example-
declare
   type t_tt is table of varchar2(20);
   l_tt t_tt := t_tt();
begin
   l_tt.extend;
   l_tt(1) := 's';
   l_tt.extend;
   l_tt(2) := 'r';
   l_tt.extend;
   l_tt(3) := '';
   l_tt.extend;
   l_tt(4) := 'i';
   l_tt.extend;
   l_tt(5) := '';
   for i in 1..l_tt.last
   loop
      dbms_output.put_line(l_tt(i));
   end loop;   
end;
/

after second run, the o/p of above statement is as follows which is wrong -
s
r
r
i
i

Regards,
Pointers
Re: Collections [message #659504 is a reply to message #659498] Thu, 19 January 2017 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> set serveroutput on
SQL> declare
  2     type t_rec is record(a number, b number);
  3     type t_tt is table of t_rec;
  4     l_tt t_tt;
  5  begin
  6     select c,d
  7       bulk collect into  l_tt
  8       from (select 1 c , null d from dual
  9              union all
 10             select 2, null from dual
 11              union all
 12             select 3, 10 from dual);
 13
 14     dbms_output.put_line(l_tt(1).b);
 15     dbms_output.put_line(l_tt(2).b);
 16     dbms_output.put_line(l_tt(3).b);
 17  end;
 18  /
10

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrap
SQL> /


10

PL/SQL procedure successfully completed.

SQL> @v

Oracle version: 11.2.0.4.0
Post the result of
select * from v$version;
And use SQL*Plus, I bet the problem comes from your client tool.

Re: Collections [message #659505 is a reply to message #659503] Thu, 19 January 2017 07:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
So file a bug report
SQL> declare
   type t_tt is table of varchar2(20);
   l_tt t_tt := t_tt();
begin
   l_tt.extend;
   l_tt(1) := 's';
   l_tt.extend;
   l_tt(2) := 'r';
   l_tt.extend;
   l_tt(3) := '';
   l_tt.extend;
   l_tt(4) := 'i';
   l_tt.extend;
   l_tt(5) := '';
   for i in 1..l_tt.last
   loop
      dbms_output.put_line(l_tt(i));
   end loop;   
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20  
s
r
i

PL/SQL procedure successfully completed.

SQL> /
s
r
i

PL/SQL procedure successfully completed.

SQL> 

Re: Collections [message #659506 is a reply to message #659505] Thu, 19 January 2017 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQLplus definitely should never give that output. GUIs with output windows that aren't automatically cleared at the start of execution can give that if you run it multiple times.
Re: Collections [message #659507 is a reply to message #659506] Thu, 19 January 2017 08:16 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
I am using toad. As Micheal and Cookiemonster guessed the problem is with the Client Tool.

It works well in sqlplus.

Thank you.

Regards,
Pointers
Re: Collections [message #659508 is a reply to message #659507] Thu, 19 January 2017 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you use unreliable Toad?
Re: Collections [message #659510 is a reply to message #659508] Thu, 19 January 2017 08:27 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Laughing
Re: Collections [message #659511 is a reply to message #659510] Thu, 19 January 2017 08:30 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is NOT funny to depend upon tool that produces unreliable results.
Previous Topic: unable to get data from V$SESSION table
Next Topic: Remove null records from collection
Goto Forum:
  


Current Time: Thu Mar 28 16:07:56 CDT 2024