ORA-00932: inconsistent data types: expected - received -

Question

I have been using Oracle (10g.2) as a PHP programmer for almost 3 years, but when I gave the assignment, I first tried using cursors and link types. And I I searched the Internet when I ran into problems, and this error ora-00932 really overloaded me. I need help from an old hand.

Here is what I was doing with. I want to select rows from a table and put them in the cursor, and then use the record type to collect them in an associative array. And again from this associative array, make the cursor. Do not ask me why, I write such complex code, because I need it for a more complex purpose. Maybe I'm embarrassing you, so let me show you my codes.

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

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

Another is the collection type, which uses the object type created above:

 CREATE OR REPLACE TYPE tr_type AS TABLE OF r_type; 

Then I create the 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; 

Packing case:

 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 on the last line where I open the cursor. Since this causes another error when starting the procedure in the Toad SQL Editor, and this is the second question I will ask. And finally, 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 error ora-00932.

Answer 1

The use of REF CURSOR is unusual. This will be the standard way to use 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 execute here, you retrieve the cursor cursor inside the procedure and then return another cursor that will have the same data. I do not think that you need to resort to the cursor at all in the procedure. Let the calling application select (here, the selection is done with print ).

Update: why do you get a useless error message?

You are using a speaker that opens dynamically, and I think this is part of the reason you get a useless 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 stated that currently in 10.2 you can get the cursor in the PLSQL record, but not in the SQL object.

Update: regarding PLS-00306 : invalid number or argument types

l_rarray - UNSTANDED TABLES, it needs to be initialized and then expanded 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 view documentation for PL / SQL collections and records .