• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4305
  • Last Modified:

PLS-00382: expression is of wrong type when loading cursor row into table type record

working with version EE 9.2.0.1.0

trying to scroll through a cursor and load it's values into a table type object; then return it with the select table(cast()) function.

i'm having some trouble and i have not work all that much with table and record types.

i've listed two very simple examples, example #1 works fine.  example #2 does not and returns a pls-382 err at function creation.


example #1    -works fine

create or replace type rec1 as object(
a int, b date, c varchar2(25));
/

create or replace type tab1 as table of rec1;
/

create or replace function objTest return tab1 as
var tab1 := tab1();
begin
for i in 1 .. 10 loop
var.extend;
var(i) := rec1(i, sysdate+i, 'rec ' || i);
end loop;
return var;
end;
/

select * from table(cast(objTest() as tab1));

                   A B         C
-------------------- --------- -------------------------
                   1 07-MAR-03 rec 1
                   2 08-MAR-03 rec 2
                   3 09-MAR-03 rec 3
                   4 10-MAR-03 rec 4
                   5 11-MAR-03 rec 5
                   6 12-MAR-03 rec 6
                   7 13-MAR-03 rec 7
                   8 14-MAR-03 rec 8
                   9 15-MAR-03 rec 9
                  10 16-MAR-03 rec 10

10 rows selected.



example #2    -returns a pls-382 on the function

create or replace type rec1 as object(
a varchar2(30), b number, c date);
/

create or replace type tab1 as table of rec1;
/


create or replace function objTest
 2  return tab1 as
 3     dat tab1 := tab1();
 4     cursor c1 is
 5     select * from all_users;
 6  begin
 7     for i in c1 loop
 8     dat.extend;
 9     dat(i) := rec1(i.username, i.user_id, i.created);
10     end loop;
11  return dat;
12  end;
13  /

Warning: Function created with compilation errors.

$master_wa$ORCL@scatcat>show err
Errors for FUNCTION OBJTEST:

LINE/COL ERROR
-------- -----------------------------------------
9/2      PL/SQL: Statement ignored
9/6      PLS-00382: expression is of wrong type

please help.

thx in advance.
daniels@asix.com
0
Daniel Stanley
Asked:
Daniel Stanley
  • 3
  • 3
  • 2
1 Solution
 
FBIAGENTCommented:
DRS66,

Your problem is

for i in c1 loop

you should use another variable, e.g., vc1

your code should be as follows

  create or replace function objTest
  return tab1 as
     dat tab1 := tab1();
     cursor c1 is
     select * from all_users;
     i number := 0;
  begin
     for vc1 in c1 loop
     dat.extend;
     i := i + 1;
     dat(i) := rec1(vc1.username, vc1.user_id, vc1.created);
     end loop;
  return dat;
  end;
  /

good luck
0
 
venkotchCommented:
I think the problem is in your for cursor loop - you cannot use "i" both as cursor type and index.

look at the uppercase edition:

create or replace function objTest
2  return tab1 as
3     dat tab1 := tab1();
4     cursor c1 is
5     select * from all_users;
    J INTEGER := 0;
6  begin
7     for i in c1 loop
8     dat.extend;
      J := J + 1;
9     dat(J) := rec1(i.username, i.user_id, i.created);
10     end loop;
11  return dat;
12  end;
13  /

0
 
venkotchCommented:
ok, FBIagent, you got me in 2 minutes :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
FBIAGENTCommented:
VENKOTCH,
it is a simple problem.

DRS66,
please give VENKOTCH the points.
i got satisfaction in solving problems

peace.  may god bless the service men and women in the iraq war.
0
 
Daniel StanleyDatabase engineerAuthor Commented:
you guys are studs, thanks a lot.

fbiagent, i'll accept your comment as the answer.

venkotch,
pick your points up here.
http://www.experts-exchange.com/Databases/Oracle/Q_20562816.html


thx again,
daniels@asix.com
0
 
Daniel StanleyDatabase engineerAuthor Commented:
thx,
daniels@asix.com
0
 
venkotchCommented:
Thanks guys, but I am not here because of the points either! I just notice that obvously we were typing the answer at the same time!

Venko.
0
 
Daniel StanleyDatabase engineerAuthor Commented:
venko, please collect those points. it will be easier than trying to get the forum monitor to delete and refund them. :D


thx again,
daniels@asix.com


0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now