Converting SQL type to PLSQL collection / converting one collection type to another

Question

Trying to convert SQL type collection to PLSQL type.

-- create an SQL type
create or replace type arrayforvarchar as table of varchar2(30);
/

Check below anonymous block:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar := arrayforvarchar();
begin
    select cola
    bulk collect into var_plsql_array
    FROM (
        select 'X' as cola from dual
        union all
        select 'Y' as cola from dual
        );
end;
/

How is it possible to assign values of var_plsql_array to var_sql_array apart from using loops?

Anyway simple assignment isn't working.

Answer 1

Not that I can find:

Using a simple assignment doesn't work as the arrays have different types:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := var_plsql_array;
end;
/

Outputs:

ORA-06550: line 8, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored

Trying to use CAST doesn't work in a PL/SQL scope as it only works in an SQL scope:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := CAST( var_plsql_array AS arrayforvarchar );
end;
/

Outputs:

ORA-06550: line 8, column 22:
PLS-00204: function or pseudo-column '' may be used inside a SQL statement only
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored

Trying to pass the PL/SQL collection into an SQL scope to be able to use CAST doesn't work as PL/SQL collections can't be used in the SQL scope:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    SELECT CAST( var_plsql_array AS arrayforvarchar )
    INTO   var_sql_array
    FROM   DUAL;
end;
/

Outputs:

ORA-06550: line 8, column 18:
PLS-00642: local collection types not allowed in SQL statements

Trying to pass the PL/SQL collection into a table collection expression in the SQL scope and using BULK COLLECT doesn't work for the same reason as the previous:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    SELECT COLUMN_VALUE
    BULK COLLECT INTO var_sql_array
    FROM   TABLE( var_plsql_array );
end;
/

Outputs:

ORA-06550: line 10, column 19:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 10, column 12:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

However, using loops and assigning values individually does work:

declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := arrayforvarchar();
    var_sql_array.EXTEND( var_plsql_array.COUNT );
    FOR i IN 1 .. var_plsql_array.COUNT LOOP
      var_sql_array(i) := var_plsql_array(i);
    END LOOP;
end;
/

db<>fiddle here