"local collection types not allowed" error in PL/SQL ORA-06550

Question

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;

Estrella

Answer 1

Try to use cursors and BULK COLLECT instead: 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

Chantel

Hello A.B.Cade, when i executed your code, it gives this error; ORA-22165: given index [51] must be in the range of [1] to [50] ORA-06512: at line 12 and when i changed "array(50)" to "array(100) now it says given index [101] must be in the range of [1] to [100] still trying to fix that, but do u have any idea ? – Estrella

then try a different type of collection i.e. TYPE name_array is table of varchar2(100); – Chantel

that actually worked, thank you very much. the reason array wasn't working is, query returned 18000 results, array was too short for that, i tried up to 5000 only, wasn't expected that much return. – Estrella

Answer 2

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:

• SELECT INTO Statement

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;
/

Downwards

Answer 3

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;

Materse

Answer 4

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;

Vaquero

Please add further details to expand on your answer, such as working code or documentation citations. – Khamsin