oracle ORA-00932: 不一致的数据类型：预期 - 得到 -
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4592319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected - got -
提问by Duygu Akyol
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.
我作为 PHP 程序员使用 Oracle(10g.2) 已经快 3 年了，但是当我给一个作业时，我第一次尝试使用引用游标和集合类型。当我遇到问题时，我已经在网上搜索过，这个 ora-00932 错误真的让我不知所措。我需要一位老手的帮助。
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:
我在 Toad 的类型选项卡下定义了 2 种类型。其中之一是对象类型：
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:
我注释掉了打开引用光标的最后一行。因为当我在 Toad 的 SQL 编辑器中运行该过程时，它会导致另一个错误，这是我要问的第二个问题。最后我在 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.
在那里我收到 ora-00932 错误。
采纳答案by Vincent Malgrat
The way you are using the REF CURSOR is uncommon. This would be the standard way of using them:
您使用 REF CURSOR 的方式并不常见。这将是使用它们的标准方式：
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 succTs.
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).
我不确定您要在这里完成什么，您在过程中获取 ref 游标，然后返回另一个具有相同数据的 ref 游标。我认为完全没有必要在过程中获取游标。让调用应用程序进行提取（这里的提取由 完成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，错误信息是不同的：
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 notin a SQL Object.
我概述了目前在 10.2 中，您可以将游标提取到 PLSQL 记录中，但不能提取到SQL 对象中。
Update: regarding the PLS-00306: wrong number or types of arguments
更新：关于PLS-00306：错误数量或类型的参数
l_rarray is a NESTED TABLE, it needs to be initialized and then extended to be able to store elements. For example:
l_rarray 是一个 NESTED TABLE，它需要被初始化然后扩展才能存储元素。例如：
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.
如需进一步阅读，您可以浏览PL/SQL 集合和记录的文档。
