Help with the code 1

[dbalearner]

I have been given a sample code to test. The code is as follows:

create table t as select * from (select * from all_objects order by dbms_random.random);
alter table t add constraint t_pk primary key (object_id;


1 declare
2 type array is table of t%rowtype index by binary_integer;
3 l_data array;
4 l_rec t%rowtype;
5 begin
6 select object_id bulk collect into l_data from all_objects;
7 for i in 1 .. l_data.count
8 loop
9 select * into l_rec from t where object_id = l_data(i);
10 end loop;
11* end;
mich@MYDB.MICH.LOCAL> /
select object_id bulk collect into l_data from all_objects;
*
ERROR at line 6:
ORA-06550: line 6, column 45:
PL/SQL: ORA-00913: too many values
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 51:
PLS-00382: expression is of wrong type

When I replace line 6 as follows:

6 select * bulk collect into l_data from all_objects;

I get the following:

1 declare
2 type array is table of t%rowtype index by binary_integer;
3 l_data array;
4 l_rec t%rowtype;
5 begin
6 select * bulk collect into l_data from all_objects;
7 for i in 1 .. l_data.count
8 loop
9 select * into l_rec from t where object_id = l_data(i);
10 end loop;
11* end;
mich@MYDB.MICH.LOCAL> /
select * into l_rec from t where object_id = l_data(i);
*
ERROR at line 9:
ORA-06550: line 9, column 51:
PLS-00382: expression is of wrong type

Any help is appreciated.

Thanks

 

[SantaMufasa]

dbalearner,

The "...wrong type..." error occurs because you are trying to compare a number (object_id) to an entire row of data (l_data(i)). To fix the problem, you need to specify l_data(i)'s object_id in this way:

declare
    type array is table of t%rowtype index by binary_integer;
    l_data array;
    l_rec t%rowtype;
begin
    select * bulk collect into l_data from all_objects;
    for i in 1 .. l_data.count loop
       select * into l_rec from t where object_id = l_data(i)[B][I].object_id[/I][/B];
    end loop;
end;
/

Let us know how things progress once you have made this adjustment.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[dbalearner]

Thank you Santa all fixed.

In the statement below:

FOR i IN 1 .. l_data.count
LOOP
BEGIN
SELECT * INTO l_rec FROM t WHERE object_id = l_data(i).object_id;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
END LOOP;

Am I correct in stateing that l_rec is variable of table t record type. All we are doing here is to go through table t and inserting into this variable (which is in memory) where object_id of record in table t matches that of l_data array. My point being is we are doing physical read (from t to buffer) one record at a time.

dbaslearner

 

[SantaMufasa]

Yes, that's a reasonable analysis.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.