在尝试将collect大容量收集到表类型时,我收到以下错误:ORA-00947";没有足够的值">
我需要创建一个数据库级别的类型来使用我的应用程序访问结果集(打开光标(。
文件:FOO_STATUS.sql
CREATE OR REPLACE TYPE "FOO_STATUS" AS OBJECT
(
a NUMBER,
b NUMBER,
c NUMBER
);
/
文件:FOO_STATUS_TABLE.sql
CREATE OR REPLACE TYPE FOO_STATUS_TABLE IS TABLE OF FOO_STATUS;
/
软件包:
PROCEDURE foo
(
...
)
IS
l_foo FOO_STATUS_TABLE := FOO_STATUS_TABLE();
BEGIN
UPDATE foo_table
SET ...
WHERE ...
RETURNING foo_table_a, foo_table_b, c BULK COLLECT INTO l_foo; -- ERROR: ORA-00947
OPEN p_results FOR
SELECT
foo_results.a AS a,
foo_results.b AS b,
foo_results.c AS c,
other.d AS d
FROM
TABLE( l_foo ) foo_results INNER JOIN other_table other ON foo_results.obj = other.obj
END;
由于l_foo是对象的表,您需要将对象收集到此集合中,而不仅仅是像这样的值
returning foo_status(a, b, c) bulk collect into l_foo
小提琴示例