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.
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;
4 Answers
This is just a clarification to A.B.Cade's answer. The cursor has nothing to do with the problem.
The root cause of
PLS-00642: local collection types not allowed in SQL statements
is sql into can be used only with a PL/SQL variable or record but not with a PL/SQL collection.
With PL/SQL collections one have to use select bulk collect into instead.
(Yes - I agree the error message could be more descriptive.)
See also:
Examples
The following anonymous block compiles with PLS-00642 because select into can't be used with collections:
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;
/
Try to use cursors and BULK COLLECT instead:
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
Simply use below given block for successful execution :
DECLARE
TYPE name_array is array(50) OF varchar2(100);
var_input name_array;
BEGIN
SELECT owner Bulk Collect
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;
Try this:
DECLARE
TYPE name_array IS ARRAY(50) OF VARCHAR2(100);
var_input name_array := name_array();
idx NUMBER := 1;
BEGIN
var_input.extend;
SELECT owner INTO var_input (idx) FROM sys.all_objects WHERE object_id = 540;
FOR i IN var_input.first..var_input.last LOOP
dbms_output.put_line(var_input(i));
END LOOP;
END;
Or this:
DECLARE
TYPE name_array IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
var_input name_array;
BEGIN
FOR x IN 535..546 LOOP
SELECT owner INTO var_input (x) FROM sys.all_objects WHERE object_id = x;
END LOOP;
FOR i IN var_input.first..var_input.last LOOP
dbms_output.put_line(var_input(i));
END LOOP;
END;
