Oracle 9i compile error PLS-00382: expression is of wrong type

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?

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.

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.

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

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.

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;
/

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

You're right, I don't know what's wrong in 10g. I was using version 8.

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.

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?

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

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.

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.