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.
Not that I can find:
Using a simple assignment doesn't work as the arrays have different types:
Outputs:
Trying to use
CAST
doesn't work in a PL/SQL scope as it only works in an SQL scope:Outputs:
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:Outputs:
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:Outputs:
However, using loops and assigning values individually does work:
db<>fiddle here