PL/SQL ORA-06550 中的“不允许本地集合类型”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8458806/
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
"local collection types not allowed" error in PL/SQL ORA-06550
提问by Burak Atar
i am trying trying to get a query from oracle table called "sys.all_objects" into a string variable, so then i can give it to "dbms_obfuscation_toolkit.DESEncrypt" as input, after than encrypted string will go in to "utl_file" so i can write it into a txt file.
我正在尝试从名为“sys.all_objects”的 oracle 表中获取一个查询到一个字符串变量中，然后我可以将它作为输入提供给“dbms_obfuscation_toolkit.DESEncrypt”，之后加密的字符串将进入“utl_file”，所以我可以写成txt文件。
Here's the problem, when i try to query with this code;
这是问题，当我尝试使用此代码进行查询时；
DECLARE
TYPE name_array is array(50) OF varchar2(100);
var_input name_array;
BEGIN
SELECT owner
INTO var_input
FROM sys.all_objects;
FOR i IN var_input.FIRST .. var_input.LAST
LOOP
dbms_output.put_line(var_input(i));
END LOOP;
END;
and the error is;
错误是;
ORA-06550: line 7, column 12:
PLS-00642: local collection types not allowed in SQL statements
any idea about geting through this issue ?
关于解决这个问题的任何想法？
for the ones who want to see the full code ;
对于那些想要查看完整代码的人；
CREATE OR REPLACE DIRECTORY data AS 'd:\folder';
GRANT read, write ON DIRECTORY data TO PUBLIC;
DECLARE
var_input varchar2(64) := 'Rndminpt';
var_key varchar2(16) := 'Anahtar1';
var_enc varchar2(1024);
var_dec varchar2(1024);
var_file utl_file.file_type;
BEGIN
-- (query part)
dbms_obfuscation_toolkit.DESEncrypt(
input_string => var_input,
key_string => var_key,
encrypted_string => var_enc);
dbms_output.put_line('Encrypted...');
var_file := utl_file.fopen('DATA','textfile.txt','W');
utl_file.put_line(var_file,var_enc);
utl_file.fclose(var_file);
dbms_output.put_line('Writen in to text... ');
END;
采纳答案by A.B.Cade
Try to use cursors and BULK COLLECT instead: http://www.dba-oracle.com/t_oracle_bulk_collect.htm
尝试使用游标和 BULK COLLECT 代替：http://www.dba-oracle.com/t_oracle_bulk_collect.htm
should look something like this:
应该是这样的：
DECLARE
TYPE name_array is array(50) OF varchar2(100);
var_input name_array;
cursor c1 is
SELECT owner
FROM sys.all_objects;
BEGIN
open c1;
fetch c1 bulk collect into var_input;
close c1;
FOR i IN var_input.FIRST .. var_input.LAST
LOOP
dbms_output.put_line(var_input(i));
END LOOP;
END;
Didn't check the code
没有检查代码
回答by user272735
This is just a clarification to A.B.Cade's answer. The cursor has nothing to do with the problem.
这只是对 ABCade 答案的澄清。光标与问题无关。
The root cause of
根本原因
PLS-00642: local collection types not allowed in SQL statements
is sql intocan be used only with a PL/SQL variable or record but not with a PL/SQL collection.
issql into只能用于 PL/SQL 变量或记录，但不能用于 PL/SQL 集合。
With PL/SQL collections one have to use select bulk collect intoinstead.
对于 PL/SQL 集合，必须select bulk collect into改用。
(Yes - I agree the error message could be more descriptive.)
（是的 - 我同意错误消息可能更具描述性。）
See also:
也可以看看：
Examples
例子
The following anonymous block compiles with PLS-00642 because select intocan't be used with collections:
以下匿名块使用 PLS-00642 编译，因为select into不能与集合一起使用：
declare
type dual_list_t is table of dual%rowtype;
v_duals dual_list_t;
begin
select *
into v_duals
from dual
connect by level <= 2
;
end;
/
The following anonymous block compiles fine:
以下匿名块编译良好：
declare
type dual_list_t is table of dual%rowtype;
v_duals dual_list_t;
begin
select *
bulk collect into v_duals
from dual
connect by level <= 2
;
end;
/
