| PLS-00382: expression is of wrong type on OPEN of cursor [message #4240] |
Wed, 20 November 2002 22:57  |
Barb Switzer
Messages: 2
Registered: November 2002
|
Junior Member |
|
|
I am new to stored procedures and need to call an existing stored proc that returns a REF CURSOR as a
parm (IN OUT).
When I compile the calling procedure via TOAD I get a PLS-00382: expression is of wrong type error when trying to open the CURSOR returned to get the data.
The stored procedure I am calling is quite large so I will only include portions of it in this message. Here is the definition of the procedure/package I need to call:
PACKAGE Ter_Meantimetorepair_Pkg AS
TYPE TER_MeanTimeToRepair_RefCursor IS REF CURSOR;
PROCEDURE TER_MeanTimeToRepair( ptBegDate IN VARCHAR2, ref_cursor IN OUT TER_MeanTimeToRepair_RefCursor);
END Ter_Meantimetorepair_Pkg;
Here is how I am trying to call this stored procedure:
TYPE l_cursor_rec IS RECORD
(
f_region VARCHAR2(255),
f_site_id VARCHAR2(255)
);
TYPE My_RefCursor IS REF CURSOR RETURN l_cursor_rec;
l_cursor My_RefCursor;
BEGIN
ARS357.TER_MEANTIMETOREPAIR_PKG.TER_MEANTIMETOREPAIR ( '15/NOV/2002 12:00:00',l_cursor);
OPEN l_cursor;
LOOP
FETCH l_cursor INTO l_cursor_rec;
EXIT WHEN l_cursor%NOTFOUND;
I get the error on compile on the OPEN statement
above.
I think it has something to do with the definition of the cursor and I spent about 2 hours looking around on the internet for possible solutions, but I found nothing that works.
Can anyone help?
|
|
|
|
|
|
| Re: PLS-00382: expression is of wrong type on OPEN of cursor [message #4248 is a reply to message #4240] |
Thu, 21 November 2002 12:48  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Also, you need to declare a variable for the record type (you can't fetch into the type directly, only a variable based on that type):
declare
type l_cursor_rec is record
(
f_region varchar2(255),
f_site_id varchar2(255)
);
l_rec l_cursor_rec;
l_cursor ter_meantimetorepair_pkg.ter_meantimetorepair_refcursor;
begin
ter_meantimetorepair_pkg.ter_meantimetorepair ('15/NOV/2002 12:00:00', l_cursor);
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
-- do something with values
end loop;
close l_cursor;
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
