Oracle 9i compile error PLS-00382: expression is of wrong type
The following procedure worked fine all along. When Oracle 9i was installed,
I suddently get compile error: PLS-00382: expression is of wrong type
See line with arrow. What's the solution to this problem?
Please help.
Thanks.
Alad
PLS-00382: expression is of wrong type
FUNCTION f_cov_count(covcode IN VARCHAR2,SEGMENT IN VARCHAR2)RETURN cursorType
IS
ref_cur cursorType;
res BOOLEAN:=FALSE;
ncount NUMBER;
BEGIN
IF ref_cur%isopen THEN
CLOSE ref_cur;
END IF;
SELECT COUNT(*) INTO ncount FROM simdata.L_SEGMENT
WHERE coverage_code = covcode AND segment_number=SEGMENT;
IF ncount > 0 THEN
res:=TRUE;
ELSE
res:=FALSE;
END IF;
OPEN ref_cur FOR SELECT res AS res FROM dual; //<-- I get error here. What's the solution to this problem?
RETURN ref_cur;
END;
Oracle Database
Last Comment
Anil Lad
8/22/2022 - Mon
slightwv (䄆 Netminder)
Is this function is part of a package or is it stand alone?
Also, why are you trying to return a cursor that just holds true or false? Why not just return true or false?
paquicuba
This is what you're doing:
SQL> DECLARE
2 RES BOOLEAN := FALSE;
3 type CURSORTYPE is ref cursor return dual%rowtype;
4 ref_cur cursorType;
5 BEGIN
6 RES := TRUE;
7 OPEN REF_CUR FOR SELECT RES FROM DUAL;
8 END;
9 /
OPEN REF_CUR FOR SELECT RES FROM DUAL; <--- You cannot return res from dual
*
ERROR at line 7:
ORA-06550: line 7, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
You have to do something like this:
Elapsed: 00:00:00.00
SQL> DECLARE
2 RES BOOLEAN := FALSE;
3 cursor res_cur is select res from dual;
4 type CURSORTYPE is ref cursor return res_cur%rowtype;
5 ref_cur cursorType;
6 BEGIN
7 RES := TRUE;
8 OPEN REF_CUR FOR SELECT RES FROM DUAL;
9 END;
10 /
PL/SQL procedure successfully completed.
paquicuba
You don't have to use the return clause, just decalre your cursor for dual.
1 DECLARE
2 RES BOOLEAN := FALSE;
3 cursor res_cur is select res from dual;
4 type CURSORTYPE is ref cursor;
5 ref_cur cursorType;
6 BEGIN
7 RES := TRUE;
8 OPEN REF_CUR FOR SELECT RES FROM DUAL;
9* END;
10 /
PL/SQL procedure successfully completed.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Anil Lad
ASKER
Hi. Thanks for your reply. But I guess I must be missing something.
I tried what you suggested, but it still gives me error now on the third line.
Thanks.
Al
DECLARE
RES BOOLEAN:=FALSE;
cursor res_cur is select res from dual; //<-- here
type CURSORTYPE is ref cursor return dual%rowtype;
ref_cur cursorType;
BEGIN
RES:=TRUE;
OPEN REF_CUR FOR SELECT RES FROM DUAL;
END;
/
ORA-06550: line 3, column 27:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 25:
PLS-00382: expression is of wrong type
slightwv (䄆 Netminder)
What I think you're hitting is the ability to use a boolean in a select.
------------------------------
BOOLEAN
You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Only logic operations are allowed on BOOLEAN variables.
The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable. You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.
paquicuba
You have to return the cursor:
DECLARE
RES BOOLEAN := FALSE;
cursor res_cur is select res from dual;
type CURSORTYPE is ref cursor return res_cur%rowtype; -- <--- res_cur Not dual%rowtype;
ref_cur cursorType;
BEGIN
RES := TRUE;
OPEN REF_CUR FOR SELECT RES FROM DUAL;
END;
/
Or simply, don't return anything, just open the cursor:
DECLARE
RES BOOLEAN := FALSE;
cursor res_cur is select res from dual;
type CURSORTYPE is ref cursor;
ref_cur cursorType;
BEGIN
RES := TRUE;
OPEN REF_CUR FOR SELECT RES FROM DUAL;
END;
/
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)
paq:
what version are you testing on?
on 10g I reproduce the askers error:
cursor res_cur is select res from dual;
*
ERROR at line 3:
ORA-06550: line 3, column 26:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 25:
PLS-00382: expression is of wrong type
paquicuba
You're right, I don't know what's wrong in 10g. I was using version 8.
paquicuba
Try this:
1 DECLARE
2 TYPE RES_REC_TYPE IS RECORD ( RES BOOLEAN );
3 RES_REC RES_REC_TYPE;
4 TYPE cursorType IS REF CURSOR;
5 ref_cur cursorType;
6 V_RES BOOLEAN;
7 BEGIN
8 RES_REC.RES := TRUE;
9 OPEN REF_CUR FOR SELECT RES_REC.RES FROM DUAL;
10 FETCH REF_CUR INTO V_RES;
11 IF V_RES = TRUE THEN
12 DBMS_OUTPUT.PUT_LINE( 'TRUE' );
13 ELSE
14 DBMS_OUTPUT.PUT_LINE( 'FALSE' );
15 END IF;
16 CLOSE REF_CUR;
17* END;
SQL> /
TRUE
PL/SQL procedure successfully completed.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)
ALad2005:
Can you provide more info? Why are you returning a cursor from this function?
Also, please answer the question I asked previously:
Is this function is part of a package or is it stand alone?
Anil Lad
ASKER
Sorry.
Q1: Is this function is part of a package or is it stand alone
A1: It's a function in package.
Q: Why are you returning a cursor from this function?
Answer: The front end is Java. It's expecting a cursor as in:
public static boolean countCoverageCode(String covcode,String segment) {
boolean result=false;
String sql = "";
sql = "begin ?:=simdata.PCK_CLPSS_VALIDATE.f_cov_count('" + covcode + "','";
sql+= segment+"'); end;";
DBResultSet db = new DBResultSet();
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Also, why are you trying to return a cursor that just holds true or false? Why not just return true or false?