Re: select record type

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 10 Aug 2006 16:40:58 -0700
Message-ID: <1155253258.485566@bubbleator.drizzle.com>

Anil G wrote:
> I have package specification having TYPE as
> TYPE some_rec IS RECORD(
> col1 varchar2(100),
> col2 varchar2(100)
> );
>
> TYPE some_ntbl IS TABLE OF some_rec;
>
> ..........
>
> ..........
>
>
> And i have Package Body some function trying to do following:
>
> v_some_ntbl some_ntbl := som_ntbl();
> ....
>
> SELECT some_rec(
> tbl.col1,
> tbl.col2
> )
> BUILK COLLECT INTO v_some_ntbl
> FROM tableCol tbl
>
>
> Some how oracle does not recognize record types, I am aware that this
> can be done with Pure Object types, but those requires additional
> maintainance.
>
> Let me know,
>
> Thanksm
> Anil G

Try this syntax:

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray;

CURSOR r IS
SELECT part_num * 10, part_name
FROM parent;

BEGIN
   OPEN r;
   LOOP
     FETCH r BULK COLLECT INTO l_data LIMIT 1000;

     FORALL i IN 1..l_data.COUNT
     INSERT INTO child VALUES l_data(i);

     EXIT WHEN r%NOTFOUND;

   END LOOP;
   COMMIT;
   CLOSE r;
END fast_way;
/

It is very efficient and gives you control over the fetch size.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 10 2006 - 18:40:58 CDT