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