Thanks a ton, George!
Here is the solution
------------------------
--Define a PL/SQL record type representing a book:
TYPE title_table IS TABLE OF BOOK.TITLE%TYPE;
TYPE author_table IS TABLE OF
BOOK.author_last_name%TYPE;
TYPE published_table IS TABLE OF
BOOK.published_date%TYPE;
TYPE book_rec IS record
(title TITLE_table,
author author_table,
published_date published_table);
my_book_rec_tab book_rec;
cursor book_cur is
select title, author_last_name, published_date from book;
begin
...
end;
-----Original Message-----
From: Gregor Malensek [mailto:gregor.[Email address protected]
Sent: Thursday, November 02, 2006 9:27 PM
To: Abhishek Toraskar (WT01 - BANKING & INSURANCE)
Subject: Re: use of record with bulk collect
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text
Hi.
If your database version is pre 10g then you have to declare three
pl/sql tables (one for each feched column).
abhishek wrote:
> Hi,
> I have a a problem with the following procedure used as a test for
> larger procedure (which if gets any larger will start answering all
the
> questions of the universe :-)
>
> I am looking primarily to fetch a cursor into a collection of more
than
> one column. However, there is some problem with declaration part.
Oracle
> refuses to recognise my_book_rec_tab.title etc.
>
> Can you please help me out?
>
> Thanks in advance
> Abhi
> create table book
> (title varchar2(20),
> author_last_name varchar2(20),
> published_date date);
>
>
> ------------------------------------------------
>
> create or replace procedure book_procedure is
> --Define a PL/SQL record type representing a book:
> TYPE book_rec IS record
> (title book.title%TYPE,
> author book.author_last_name%TYPE,
> published_date book.published_date%TYPE);
>
> --define a PL/SQL table containing entries of type book_rec:
> Type book_rec_tab IS TABLE OF book_rec ---- TABLE OF book_rec%TYPE --
> also not working
> INDEX BY BINARY_INTEGER;
>
> my_book_rec_tab book_rec_tab;
>
> cursor book_cur is
> select * from book;
>
> begin
> open book_cur;
> loop
> fetch book_cur
> bulk collect into my_book_rec_tab.title , my_book_rec_tab.author,
> my_book_rec_tab.published_date;
> end loop;
> close book_cur;
> end;
>
>
> ------------------------------
> insert into book values( 'title1', 'last_name1', '02-Nov-2006' );
> insert into book values( 'title2', 'last_name2', '01-Nov-2006' );
> insert into book values( 'title3', 'last_name3', '31-Oct-2006' );
> insert into book values( 'title4', 'last_name4', '30-Oct-2006' );
> insert into book values( 'title5', 'last_name5', '29-Oct-2006' );
> insert into book values( 'title6', 'last_name6', '28-Oct-2006' );
> insert into book values( 'title7', 'last_name7', '27-Oct-2006' );
> insert into book values( 'title8', 'last_name8', '26-Oct-2006' );
> insert into book values( 'title9', 'last_name9', '25-Oct-2006' );
> insert into book values( 'title10', 'last_name10', '24-Oct-2006' );
> insert into book values( 'title11', 'last_name11', '23-Oct-2006' );
> insert into book values( 'title12', 'last_name12', '22-Oct-2006' );
> insert into book values( 'title13', 'last_name13', '21-Oct-2006' );
> insert into book values( 'title14', 'last_name14', '20-Oct-2006' );
> insert into book values( 'title15', 'last_name15', '19-Oct-2006' );
> insert into book values( 'title16', 'last_name16', '18-Oct-2006' );
> insert into book values( 'title17', 'last_name17', '17-Oct-2006' );
> insert into book values( 'title18', 'last_name18', '16-Oct-2006' );
> insert into book values( 'title19', 'last_name19', '15-Oct-2006' );
> insert into book values( 'title20', 'last_name20', '14-Oct-2006' );
> commit;
>
>
>
> The information contained in this electronic message and any
attachments to this message are intended for the exclusive use of the
addressee(s) and may contain proprietary, confidential or privileged
information. If you are not the intended recipient, you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately and destroy all copies of this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.
>
> www.wipro.com
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
Oracle LazyDBA home page