Bulk Collect for Record type [message #421908] |
Fri, 11 September 2009 10:38  |
bond007
Messages: 64 Registered: March 2009
|
Member |
|
|
Hi Friend,
I am facing problem in Bulk Collect for %ROWTYPE .Where there is no issue in %TYPE .
create table bc (
a number,
b varchar2(10)
);
insert into bc values (11,'A11');
insert into bc values (12,'A12');
insert into bc values (13,'A13');
insert into bc values (14,'A14');
commit;
Follwing code works fine
set serveroutput on size 10000
declare
type t_bc_a is table of bc.a%type;
type t_bc_b is table of bc.b%type;
l_bc_a t_bc_a;
l_bc_b t_bc_b;
begin
select a, b bulk collect into l_bc_a, l_bc_b from bc;
for i in l_bc_a.first .. l_bc_a.last loop
dbms_output.put_line('Value OF i is --> ' || i);
dbms_output.put_line('Value OF l_bc_a(i) is --> ' || l_bc_a(i));
dbms_output.put_line('Value OF l_bc_b(i) is --> ' || l_bc_b(i));
end loop;
end;
/
Follwing code does not work .
create table bc_new as select * from bc where 1=2;
declare
type bc_rcrd_type is table of bc%rowtype;
bc_rcrd bc_rcrd_type;
begin
select * bulk collect into bc_rcrd from bc;
--dbms_output.put_line( 'No Of Records Are : ' || bc_rcrd.count);
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd.first);
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd(1));
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd.a);
FOR indx IN 1 .. bc_rcrd.COUNT
LOOP
dbms_output.put_line( 'Chk The Rcrd : ' || bc_rcrd(indx));
END LOOP;
end;
/
|
|
|
|
|
Re: Bulk Collect for Record type [message #421930 is a reply to message #421908] |
Fri, 11 September 2009 20:59   |
bond007
Messages: 64 Registered: March 2009
|
Member |
|
|
Got the answer .. Thanks michel
set serveroutput on
declare
type bc_rcrd_type is table of bc%rowtype;
bc_rcrd bc_rcrd_type;
begin
select * bulk collect into bc_rcrd from bc;
--dbms_output.put_line( 'No Of Records Are : ' || bc_rcrd.count);
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd.first);
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd(1));
--dbms_output.put_line( 'bc_rcrd.first : ' || bc_rcrd.a);
FOR indx IN 1 .. bc_rcrd.COUNT
LOOP
dbms_output.put_line( 'Chk The Rcrd : ' || bc_rcrd(indx).a);
END LOOP;
end;
/
|
|
|
Re: Bulk Collect for Record type [message #422071 is a reply to message #421909] |
Mon, 14 September 2009 03:50   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Please follow Posting Guideline, for the first time ever here!
I'm sorry, @BlackSwan - I'm going to have to call you on this one - what is wrong with that post?
It's formatted using code tags
It provides a test case that contains everything required to duplicate the problem
What, exactly and in detail please, are you complaining about?
|
|
|
|
Re: Bulk Collect for Record type [message #422133 is a reply to message #422126] |
Mon, 14 September 2009 09:51   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Truely, your reluctance to provide any actual help leaves me flabbergasted.
It took me 12 seconds to cut and paste the OPs test case into SQL*Developer and reproduce their problem - I cannot easily believe that it will have taken you much less time to post your pointless reply.
Why do you bother?
|
|
|
|
Re: Bulk Collect for Record type [message #422407 is a reply to message #422134] |
Wed, 16 September 2009 03:40   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You don't reward any behaviour - the majority of your posts are simply carping about the quality of other peoples posts, which would be amusingly ironic if it weren't for the copycat behaviour it inspires, and the aggressive and unhelpful tone it gives to the whole board.
This thread is a perfect example - if you exclude your posts from it (and my criticism) then we have a thread in which the Op asks a question, Michel points out what they've missed, and the Op fixes the problem and presents the corrected code - virtually a perfect thread.
Your post adds nothing positive at all.
Worse - by applying the same criticism to a well presented and formatted post that others do to much more egregious examples, you just cause people to ignore the criticism and reduce the liklihood of improvements.
I would ask that until such time as you are made a moderator of the board, you leave moderation of other users postings to those who are Moderators.
|
|
|
Re: Bulk Collect for Record type [message #422408 is a reply to message #421908] |
Wed, 16 September 2009 03:54  |
bond007
Messages: 64 Registered: March 2009
|
Member |
|
|
Dear Friends ,
Frankly speaking after seeing the comments of Black Swan I have made up my mind not to post any more topic inthis forum.
I felt very bad and decided to quit from this forum. I really respect the attitude of Michel Cadot,JRowbottom and Thomas .
because of these friends I am here only.
So My simple request to Black Swan, If you cant help us,it is Ok But kindly dont disturb us.
|
|
|