Avatar of Anil Lad
Anil Lad
Flag for Canada asked on

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

Avatar of undefined
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.

Excerpt from:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/03_types.htm#10889

------------------------------
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;
/
Get an unlimited membership to EE for less than $4 a week.
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();

      try {
            java.sql.ResultSet rs = db.openRefCursor(sql);
            while (rs.next()) {
                  result=(rs.getBoolean("RES"));
            }
      }       
     
I guess, whatever I do,  I have to ensure the front end java and backend Oracle match.  But I am not sure how.

Thank you.
Al
Anil Lad

ASKER
I am using 9i.   8 is fine, everything compiled perfectly.  As soon as I put on 9i, I get all the compile errors such as the
one discussed here.

Thanks.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Anil Lad

ASKER
You're right.  I don't think there is any other way.  I will be changing all my function that return true and false to return numeric 1 or 0.

Thanks.