Thanks for the question, Edwin.

Asked: March 16, 2003 - 10:18 pm UTC

Last updated: April 28, 2004 - 5:35 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

My question is that how can i select a bulk of record from pl/sql table with a where clause into another pl/sql table.

I have a simple plsql program as such:

declare
TYPE Line_Rec_Type IS RECORD
( STATUS VARCHAR2 (50),
SET_OF_BOOKS_ID NUMBER (15)
);

TYPE Line_Tbl_Type IS TABLE OF Line_Rec_Type
INDEX BY BINARY_INTEGER;

a line_tbl_type;
b line_tbl_type;

begin

a(1).status := 'A';
a(1).set_of_books_id :=1;
a(2).status := 'B';
a(2).set_of_books_id :=2;
a(3).status := 'A';
a(3).set_of_books_id :=3;
a(4).status := 'B';
a(4).set_of_books_id :=4;

select cast( multiset( select *
from table (cast(a as line_tbl_type))
where status.column_value = 'A'
)as line_tbl_type )
into b
from dual;

for i in b.first..b.last loop
dbms_output.put_line(b(i).status);
dbms_output.put_line(b(i).set_of_books_id);
end loop;
end;
/

After run the program, the following error msg displayed:
declare
*
ERROR at line 1:
ORA-06550: line 26, column 28:
PLS-00382: expression is of wrong type
ORA-06550: line 25, column 1:
PL/SQL: SQL Statement ignored

Thanks in advance.

Edwin

and Tom said...

You must use SQL types in SQL -- no plsql records, no plsql index by tables -- you'll use an object type and a collection of that type.

The functionality is virtually the same, the semantics a tad different. Your example would be:

ops$tkyte@ORA817DEV> create or replace TYPE Line_Rec_Type as object
2 ( STATUS VARCHAR2 (50),
3 SET_OF_BOOKS_ID NUMBER (15)
4 )
5 /

Type created.

ops$tkyte@ORA817DEV> create or replace TYPE Line_Tbl_Type as TABLE OF Line_Rec_Type
2 /

Type created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
2
3 a line_tbl_type := line_Tbl_Type();
4 b line_tbl_type;
5
6 begin
7
8 a.extend(4);
9 a(1) := line_rec_type( 'A', 1 );
10 a(2) := line_rec_type( 'B', 2 );
11 a(3) := line_rec_type( 'A', 3 );
12 a(4) := line_rec_type( 'B', 4 );
13
14 select cast( multiset( select *
15 from table (cast(a as line_tbl_type))
16 where status = 'A'
17 )as line_tbl_type )
18 into b
19 from dual;
20
21 for i in b.first..b.last loop
22 dbms_output.put_line(b(i).status);
23 dbms_output.put_line(b(i).set_of_books_id);
24 end loop;
25 end;
26 /
A
1
A
3

PL/SQL procedure successfully completed.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Tom

Edwin, March 17, 2003 - 8:05 pm UTC

Hi Tom,

Thanks for your excellent approach!
I still got someting mix-up in using object type.
What is the advantage of using object type rather than the record type? Since I read many store proc. in oracle application are using record type, so i wonder using record type can enhance the performance of the program.

March 17, 2003 - 8:38 pm UTC

they are programming constructs, not performance tools necessarily.

In this case -- unless you use a SQL object type -- you won't be doing what you are attempting to do, so here the discussion is "moot".

Me, I have never typed in the lines:

type X is record ....

in my entire life. Do I use records in PLSQL? yes --

declare
x table%rowtype;
y cursor%rowtype;
....

but I've never defined my own record. Object types and collections -- all of the time, so I can use SQL on them. User defined record types -- I don't really use them.

A Reader, March 18, 2003 - 5:53 am UTC

Can u please explain CAST and MULTISET.

Thanks

March 18, 2003 - 8:47 am UTC

cast is just a function to tell oracle the datatype expected back. You can cast a number to number(7,2) for example -- or as in this case we are casting a result set as a collection type.

multiset just tells Oracle "hey, this here subquery, it is not a scalar subquery, it'll return many rows possibly".

You use them predominantly as I have above -- to cast a multi-row result set as a collection type.

Using where clause in collection

Mohan K, July 19, 2003 - 2:35 am UTC

In PL/SQL tables it is possible tu user index by integer. Using collection objects in SQL is it possiblt to do the same or is there any other way to create an array with index.

Mohan

July 19, 2003 - 11:39 am UTC

Not in SQL, no.

SQL = sets
PLSQL = procedural.

Q regarding bulk fetching records and then inserting.-

Mariano, April 28, 2004 - 11:28 am UTC

Tom, hi.
I open a cursor, bulk collect its result into two collections with limit 100 and loop this last collection to retrieve info I put into a record (as explained at Expert 1 on 1, chapter 20 pages 895-896 -Apress edition-) and then insert into a nested table column in a table with the info of this collection record.
As example,

open c$1;
<<outer_loop>>
loop
fetch c$1 bulk collect
into r$clu1, r$dt1 limit 100;
exit when c$1%notfound;
<<inner_loop>>
for a in r$clu1.first .. r$clu1.last
loop
select cast(multiset
(<<query here using r$clu1 & r$dt1>>)
into r$resultc1 -- this's like myTableType
from dual;
end loop inner_loop;
end loop outer_loop;
close c$1;

My question: must I insert the record info (r$resultc1) in the destination table after every outer loop (I don't know if the collection record is override in every loop, as the bulk collect limit is 100) or it'll keep the info till the end of the process.

I hope my question makes any sense.
Best regards.-

April 28, 2004 - 5:35 pm UTC

each select into will overwrite whatever was in there previously

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer