Collections [message #659498] |
Thu, 19 January 2017 07:30 |
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 |
|
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 |
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 #659506 is a reply to message #659505] |
Thu, 19 January 2017 08:00 |
cookiemonster
Messages: 13925 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 |
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
|
|
|
|
|
|