oracle 将 RefCursor 插入表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13482554/
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
Inserting a RefCursor into a table
提问by Victor
I have a simple function where I run a stored procedure that returns a RefCursor and I try to use that RefCursor to insert data to a temporary table. I get the following error when trying to do so:
我有一个简单的函数，我运行一个返回 RefCursor 的存储过程，我尝试使用该 RefCursor 将数据插入到临时表中。尝试这样做时出现以下错误：
SQL Error: ORA-00947: not enough values
SQL Error: ORA-00947: not enough values
I know for a fact that the refcursor returns exactly the same number of values as the temporary table has, correct column names, their order and their type. I ran print RefCursorand I can see all of the data. Here's the code:
我知道一个事实，即 refcursor 返回的值数量与临时表具有的值、正确的列名、它们的顺序和类型完全相同。我跑了print RefCursor，我可以看到所有的数据。这是代码：
var r refcursor;
EXEC SCHEMA.PACKAGE.SPROC(:r);
insert into SCHEMA.TEMP_TABLE
values
(r);
I have to add that the stored procedure has a refcursor defined as a OUT parameter so it returns a correct type. Using print r;prints the correct data.
我必须补充一点，存储过程有一个 refcursor 定义为 OUT 参数，因此它返回正确的类型。使用print r;打印正确的数据。
What am I doing wrong?
我究竟做错了什么？
EDIT: Based on a suggestion I tried to use a fetch to a rowtype variable, but getting Invalid Number exception whenever I attempt to fetch a row:
编辑：根据建议，我尝试使用对 rowtype 变量的提取，但是每当我尝试提取一行时都会出现 Invalid Number 异常：
DECLARE
cur SYS_refcursor;
rec SCHEMA.TEMP_TABLE%rowtype;
begin
SCHEMA.PACKAGE.SPROC( cur );
LOOP
FETCH cur INTO rec;
EXIT WHEN cur%NOTFOUND;
INSERT INTO SCHEMA.TEMP_TABLE
VALUES rec;
END LOOP;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_output.put_line(rec.move_id);
end;
I added the exception block to see which row is failing and needless to say it is the first one. The stored procedure I run returns a refcursor of a select query from multiple tables. The temporary table defined as the exact copy of the refcursor columns and their types. Not sure what could be causing the exception.
我添加了异常块来查看哪一行失败，不用说它是第一行。我运行的存储过程从多个表中返回一个选择查询的引用。临时表定义为 refcursor 列及其类型的精确副本。不确定是什么导致了异常。
回答by Justin Cave
You can't insert into a table from a refcursor. You could write a procedure that fetches from the cursor and inserts into the table. If schema.package.sprocreturns a ref cursor of temp_table%rowtype, you could do something like
您不能从refcursor. 您可以编写一个从游标中获取数据并插入到表中的过程。如果schema.package.sproc返回 的引用游标temp_table%rowtype，您可以执行类似的操作
DECLARE
cur sys_refcursor;
rec schema.temp_table%rowtype;
BEGIN
schema.package.sproc( cur );
LOOP
FETCH cur INTO rec;
EXIT WHEN cur%NOTFOUND;
INSERT INTO schema.temp_table
VALUES rec;
END LOOP;
END;
