我在执行下面的PL/SQL代码时遇到了这个错误,但我找不出原因。
PLS-00382:表达式的类型错误PL/ SQL : ORA-00913:值太多PL/SQL:忽略SQL语句
我知道ORA-00913的原因是SELECT语句中引用的两组数据中的值不匹配。
在我将checkVar变量添加到记录中之前,代码运行良好。我只想知道有没有办法不从testRec记录中取出checkVar就能解决这个问题?
DECLARE
X XMLType:=XMLType( '<Testmain>
<TeamSel>
<code>abc123</code>
<name>awrqer</name>
</TeamSel>
<TeamSel>
<code>abc678</code>
<name>gokhg</name>
</TeamSel>
</Testmain>
');
TYPE testRec IS RECORD(
name VARCHAR2(512),
code VARCHAR2(512),
checkVar BOOLEAN:=FALSE
);
TYPE testRecTab IS TABLE OF testRec INDEX BY BINARY_INTEGER;
testList testRecTab;
BEGIN
SELECT EXTRACTVALUE(VALUE(xml_list),'//Name') AS lname,
EXTRACTVALUE(VALUE(xml_list),'//Code') AS lcode
BULK COLLECT
INTO testList
FROM TABLE(XMLSEQUENCE(EXTRACT(X,'Testmain/TeamSel'))) xml_list;
--rest of the code
END;
发布于 2016-07-14 12:15:36
你不能选择一个布尔型变量,所以我认为有两个选择:
a)从记录类型中删除布尔值
b)在如下所示的循环中填充表
DECLARE
X XMLType:=XMLType( '<Testmain>
<TeamSel>
<code>abc123</code>
<name>awrqer</name>
</TeamSel>
<TeamSel>
<code>abc678</code>
<name>gokhg</name>
</TeamSel>
</Testmain>
');
TYPE testRec IS RECORD(
name VARCHAR2(512),
code VARCHAR2(512),
checkVar BOOLEAN:=FALSE
);
TYPE testRecTab IS TABLE OF testRec INDEX BY BINARY_INTEGER;
lTestRec testRec;
testList testRecTab;
BEGIN
FOR cRecord IN (
SELECT EXTRACTVALUE(VALUE(xml_list),'//name') lname,
EXTRACTVALUE(VALUE(xml_list),'//code') lcode
FROM TABLE(XMLSEQUENCE(EXTRACT(X,'Testmain/TeamSel'))) xml_list
)
LOOP
lTestRec.name := cRecord.lname;
lTestRec.code := cRecord.lcode;
testList(testList.count + 1) := lTestRec;
END LOOP;
--rest of the code
END;
https://stackoverflow.com/questions/38373154
复制相似问题