Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect for Record type (oracle 10g)
Bulk Collect for Record type [message #421908] Fri, 11 September 2009 10:38 Go to next message
bond007
Messages: 58
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 #421909 is a reply to message #421908] Fri, 11 September 2009 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Follwing code does not work .

My car does not work.
Tell me how to make my car go.

Please follow Posting Guideline, for the first time ever here!
Re: Bulk Collect for Record type [message #421911 is a reply to message #421908] Fri, 11 September 2009 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does Oracle know how to display the concatenation of a string and a record?
What is displaying a record?

Regards
Michel
Re: Bulk Collect for Record type [message #421930 is a reply to message #421908] Fri, 11 September 2009 20:59 Go to previous messageGo to next message
bond007
Messages: 58
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 Go to previous messageGo to next message
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 #422126 is a reply to message #422071] Mon, 14 September 2009 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What, exactly and in detail please, are you complaining about?

From Posting Guidelines below:

Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.

Nobody reading this thread knows what the "good" output is or what the "bad" results looked like even now!
Re: Bulk Collect for Record type [message #422133 is a reply to message #422126] Mon, 14 September 2009 09:51 Go to previous messageGo to next message
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 #422134 is a reply to message #421908] Mon, 14 September 2009 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>It took me 12 seconds to cut and paste the OPs test case into SQL*Developer and reproduce their problem
& how many fewer seconds would it had taken you or OP to actually follow Posting Guidelines by posting the actual results?

I won't reward lazy behavior.
Re: Bulk Collect for Record type [message #422407 is a reply to message #422134] Wed, 16 September 2009 03:40 Go to previous messageGo to next message
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 Go to previous message
bond007
Messages: 58
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.
Previous Topic: equivalent of oracle
Next Topic: Fetching the last DML statements exectuted.
Goto Forum:
  


Current Time: Fri Dec 09 15:10:26 CST 2016

Total time taken to generate the page: 0.38884 seconds