ORA-00932: inconsistent datatypes: expected - got -

Question

I have been using Oracle(10g.2) as a PHP programmer for almost 3 years, but when I gave an assignment, I have tried to use the ref cursors and collection types for the first time. And I 've searched the web, when I faced with problems, and this ora-00932 error really overwhelmed me. I need help from an old hand.

Here is what I've been tackling with, I want to select rows from a table and put them in a ref cursor, and then with using record type, gather them within an associative array. And again from this associative array, make a ref cursor. Don't ask me why, I am writing such a complicated code, because I need it for more complex assignment. I might be sound confusing to you, thus let me show you my codes.

I have 2 types defined under the types tab in Toad. One of them is an object type:

CREATE OR REPLACE
TYPE R_TYPE AS OBJECT(sqn number,firstname VARCHAR2(30), lastname VARCHAR2(30));

Other one is collection type which is using the object type created above:

CREATE OR REPLACE
TYPE tr_type AS TABLE OF r_type;

Then I create a package:

CREATE OR REPLACE PACKAGE MYPACK_PKG IS
TYPE MY_REF_CURSOR IS REF CURSOR;
PROCEDURE MY_PROC(r_cursor OUT MY_REF_CURSOR);
END MYPACK_PKG;

Package Body:

CREATE OR REPLACE PACKAGE BODY MYPACK_PKG AS
 PROCEDURE MY_PROC(r_cursor OUT MY_REF_CURSOR) AS
  rcur MYPACK_PKG.MY_REF_CURSOR;
  sql_stmt VARCHAR2(1000);
  l_rarray   tr_type := tr_type();
                l_rec r_type;

 BEGIN
     sql_stmt :=  'SELECT 1,e.first_name,e.last_name  FROM hr.employees e ';
     OPEN rcur FOR sql_stmt;
     LOOP
       fetch rcur into l_rec;
                  exit when rcur%notfound;
     l_rarray := tr_type( l_rec );
     END LOOP;
   CLOSE rcur;
    --OPEN r_cursor FOR SELECT * FROM TABLE(cast(l_rarray as tr_type) );


END MY_PROC;
END MYPACK_PKG;

I commented out the last line where I open ref cursor. Because it's causing another error when I run the procedure in Toad's SQL Editor, and it is the second question that I will ask. And lastly I run the code in Toad:

variable r refcursor
declare
r_out MYPACK_PKG.MY_REF_CURSOR;
begin
MYPACK_PKG.MY_PROC(r_out);
:r := r_out;
end;
print :r

There I get the ora-00932 error.

Minny

Answer 1

The way you are using the REF CURSOR is uncommon. This would be the standard way of using them:

SQL> CREATE OR REPLACE PACKAGE BODY MYPACK_PKG AS
  2     PROCEDURE MY_PROC(r_cursor OUT MY_REF_CURSOR) AS
  3     BEGIN
  4        OPEN r_cursor FOR SELECT e.empno,e.ENAME,null  FROM scott.emp e;
  5     END MY_PROC;
  6  END MYPACK_PKG;
  7  /

Corps de package crÚÚ.

SQL> VARIABLE r REFCURSOR
SQL> BEGIN
  2     MYPACK_PKG.MY_PROC(:r);
  3  END;
  4  /

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> PRINT :r

     EMPNO ENAME      N
---------- ---------- -
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      [...]

14 ligne(s) sÚlectionnÚe(s).

I'm not sure what you are trying to accomplish here, you're fetching the ref cursor inside the procedure and then returning another ref cursor that will have the same data. I don't think it's necessary to fetch the cursor at all in the procedure. Let the calling app do the fetching (here the fetching is done by the print).

Update: why are you getting the unhelpful error message?

You're using a cursor opened dynamically and I think that's part of the reason you are getting the unhelpful error message. If we use fixed SQL the error message is different:

SQL> CREATE OR REPLACE PACKAGE BODY MYPACK_PKG AS
  2     PROCEDURE MY_PROC(r_cursor OUT MY_REF_CURSOR) AS
  3        TYPE type_rec IS RECORD (qn number,
  4                                 firstname VARCHAR2(30),
  5                                 lastname VARCHAR2(30));
  6        lt_record type_rec; /* Record type */
  7        lt_object r_type; /* SQL Object type */
  8     BEGIN
  9        OPEN r_cursor FOR SELECT e.empno,e.ENAME,null  FROM scott.emp e;
 10        FETCH r_cursor INTO lt_record; /* This will work */
 11        FETCH r_cursor INTO lt_object; /* This won't work in 10.2 */
 12     END MY_PROC;
 13  END MYPACK_PKG;
 14  /

Package body created

SQL> VARIABLE r REFCURSOR
SQL> BEGIN
  2     MYPACK_PKG.MY_PROC(:r);
  3  END;
  4  /
BEGIN
*
ERREUR Ó la ligne 1 :
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "APPS.MYPACK_PKG", line 11
ORA-06512: at line 2

I outlined that currently in 10.2 you can fetch a cursor into a PLSQL record but not in a SQL Object.

Update: regarding the PLS-00306: wrong number or types of arguments

l_rarray is a NESTED TABLE, it needs to be initialized and then extended to be able to store elements. For example:

SQL> CREATE OR REPLACE PACKAGE BODY MYPACK_PKG AS
  2     PROCEDURE MY_PROC(r_cursor OUT MY_REF_CURSOR) AS
  3        lr_array tr_type := tr_type(); /* SQL Array */
  4     BEGIN
  5        FOR cc IN (SELECT e.empno, e.ENAME, NULL lastname
  6                     FROM scott.emp e) LOOP
  7           lr_array.extend;
  8           lr_array(lr_array.count) := r_type(cc.empno,
  9                                              cc.ename,
 10                                              cc.lastname);
 11           /* Here you can do additional procedural work on lr_array */
 12        END LOOP;
 13        /* then return the result set */
 14        OPEN r_cursor FOR SELECT * FROM TABLE (lr_array);
 15     END MY_PROC;
 16  END MYPACK_PKG;
 17  /

Corps de package crÚÚ.

SQL> print r

       SQN FIRSTNAME                      LASTNAME
---------- ------------------------------ -----------
      7369 SMITH                          
      7499 ALLEN                          
      7521 WARD                           
      [...]

14 ligne(s) sÚlectionnÚe(s).

For further reading you can browse the documentation for PL/SQL collections and records.

Breviary

Thank you for your response, but as I mentioned before, this is just a simple example, the real problem is more complex and refcursor that is identified in the procedure's declaration part and the out parameter that is returning the results which is another ref cursor won't be containing the same data in the actual case. – Minny

Thank you so much for the answer. With the help of your code, I've declared my record type inside the procedure, and it is the only change that I did in the code, and the error went away. Although it helped me to rectify the error, I 've started to recieve another error. It is on the line where I identify my associative array with the type record (l_rarray := tr_type( l_rec );), and the error is "PLS-00306: wrong number or types of arguments in call to 'TR_TYPE'". Do you have any idea, why the error occured? – Minny

Thank you so much. I now realized that there is no need to fetch the ref cursor into a record type, and I solved the error by doing the way you depicted in your last post. Thank you again, and again. – Minny

I want to ask you one more question, I hope this will be the last.In your last post, if you use dynamic sql, like 'sql_stmt := SELECT e.empno, e.ENAME, NULL lastname FROM scott.emp e', and then FOR cc IN (select sql_stmt FROM dual), and then try to reach the cursor's elements by writing cc.empno for example, you will get a PLS-00302 error.How can you handle it? Is there a way because, I have to use a dynamic sql. – Minny

@duygu: If you use dynamic SQL, you will have to use an explicit cursor, for example: DECLARE lc SYS_REFCURSOR; ldummy VARCHAR2(1); BEGIN OPEN lc FOR 'SELECT * FROM DUAL'; FETCH lc INTO ldummy; CLOSE lc;END; – Breviary