How to create view using dynamic action in APEX or

Question

I created a procedure to create a view dynamically using a shuttle item in APEX that returns the colon separated values. Here's the code to create it.

create or replace procedure create_temporary_view (input_employees in varchar2) is
begin
execute immediate 'create or replace view temp_batch_id as with emps(shuttle_item) as 
(select '''||input_employees||''' from dual)
select regexp_substr(shuttle_item, ''[^:]+'', 1, level) batch_id from emps connect by level <= regexp_count(shuttle_item, '':'') + 1;';
commit;
end;

And inside the Execute PL/SQL code dynamic action i added the code something like

begin
create_temporary_view(:P12_EMPLOYEES);
end;

where :P12_EMPLOYEES is the shuttle item giving the colon separated values. Apparently this piece of code works when i do it from the SQL Commands tab in Oracle APEX but the procedure isn't getting called from the EXECUTE PL/SQL code dynamic action. Is there a specific reason for it? If yes, what is it? If not, what could be the possible errors? Thanks in Advance.

Answer 1

It looks like you're trying to tokenize your P12_EMPLOYEES variable. This is not the right way to do this. Apex has a built-in PL/SQL function for doing this:

apex_string.split( :P12_EMPLOYEES, ':' )

this will give you a apex_t_varchar2 collection, split by colons.

Why your approach is a bad idea, consider what happens if two users are using your application at the same time. You'll create a view visible to both users with data from only one.

Answer 2

I'm not sure what's your final goal, but I wouldn't create any table or view from APEX like that.

I think you actually want to populate a collection.

Have a look at how to create/populate collection in APEX and I think you'll find whatever you're trying to do.