Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or replace type arrayforvarchar as table of varchar2(30);
/
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;
/
ORA-06550: line 8, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored

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;
/
ORA-06550: line 8, column 18:
PLS-00642: local collection types not allowed in SQL statements

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

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

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;
/
1 rows affected