ORA-00932:数据类型不一致:预期-得到- [英] ORA-00932: inconsistent datatypes: expected - got -
问题描述
我已经将Oracle(10g.2)作为PHP程序员使用了将近3年，但是当我进行分配时，我第一次尝试使用ref游标和集合类型.和我 当我遇到问题时，我已经在网上搜索了，这个ora-00932错误确实使我不知所措.我需要老手帮忙.
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.
这就是我一直在努力解决的问题， 我想从表中选择行并将其放入ref游标，然后使用记录类型将它们收集在关联数组中.然后再次从该关联数组中创建一个ref游标.不要问我为什么，我正在编写这么复杂的代码，因为我需要它来进行更复杂的分配.我可能会让您感到困惑，因此让我向您展示我的代码.
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.
我在Toad的类型"选项卡下定义了2种类型.其中之一是对象类型:
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;
然后我创建一个包:
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;
包装体:
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;
我注释掉了我打开参考光标的最后一行.因为在Toad的SQL编辑器中运行该过程时会导致另一个错误，所以这是我要问的第二个问题. 最后，我在Toad中运行代码:
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
我收到ora-00932错误.
There I get the ora-00932 error.
推荐答案
使用REF CURSOR的方式并不常见.这将是使用它们的标准方法:
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).
我不确定您要在这里完成什么，您要在过程中获取ref游标，然后返回另一个具有相同数据的ref游标.我认为在该过程中根本不需要获取游标.让调用的应用执行提取操作(这里的提取操作由print完成).
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).
您正在使用动态打开的游标，我认为这是收到无用的错误消息的部分原因.如果我们使用固定的SQL，则错误消息会有所不同:
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
我概述了当前在10.2中可以将游标提取到PLSQL记录中，但在SQL对象中不能.
I outlined that currently in 10.2 you can fetch a cursor into a PLSQL record but not in a SQL Object.
l_rarray是一个嵌套表，它需要初始化然后扩展才能存储元素.例如:
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).
为进一步阅读，您可以浏览 PL/SQL集合的文档和记录.
For further reading you can browse the documentation for PL/SQL collections and records.
这篇关于ORA-00932:数据类型不一致:预期-得到-的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
