我试图从oracle表中获取一个名为“sys.all_objects”的查询
进入一个字符串变量,所以我可以把它作为输入“dbms_obfuscation_toolkit.DESEncrypt”,加密后的字符串将进入“utl_file”,所以我可以把它写入一个txt文件.
进入一个字符串变量,所以我可以把它作为输入“dbms_obfuscation_toolkit.DESEncrypt”,加密后的字符串将进入“utl_file”,所以我可以把它写入一个txt文件.
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;
错误是;
ORA-06550: line 7,column 12: PLS-00642: local collection types not allowed in sql statements
关于这个问题的任何想法？
对于那些想看完整代码的人;
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;
解决方法
尝试使用光标和BULK COLLECT代替：
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
应该看起来像这样：
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;
没有检查代码
