?
Solved

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

Posted on 2003-03-25
8
Medium Priority
?
4,185 Views
Last Modified: 2007-12-19
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
Comment
Question by:Daniel Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Accepted Solution

by:
FBIAGENT earned 1000 total points
ID: 8205237
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
 
LVL 6

Expert Comment

by:venkotch
ID: 8205257
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
 
LVL 6

Expert Comment

by:venkotch
ID: 8205270
ok, FBIagent, you got me in 2 minutes :)
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 5

Expert Comment

by:FBIAGENT
ID: 8205341
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
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 8205382
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
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 8205393
thx,
daniels@asix.com
0
 
LVL 6

Expert Comment

by:venkotch
ID: 8205947
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
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 8206535
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question