How to create view using dynamic action in APEX oracle?

1
votes

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.

2
oracleoracle-apexoracle-apex-5oracle-apex-5.1
I can't think of any reason why you would need to perform such a task. What is it you're trying to solve? – Scott
I want to map one customer with a set of employees. Let's say i have 3 customers and 10 team members. e.g. Customer1 -> Employee2, 3, 4 ; Customer2 -> Employee1, 8, 9 and so on. This mapping keeps on changing every month, It's not fixed. And i want to retain this mapping till the end of the month. Task related to this view creation will be handled only by one user. Later i intend to pass the customer_id as a parameter so as to create separate view for each customer. So the view name for every customer would be like temp_batch_id_<customer_id> . – Simar Singh
It sounds like you should modify your table design to accommodate this sort of information, rather than dynamically moulding your DDL around the problem, eg: cust_emp_link table containing cust_id, emp_id, month – Scott
Then i will just take another table to store that information and delete the data at the end of month. Although i still would like to know as to why it doesn't work. Thanks @Scott – Simar Singh
Try remove the final semi-colon within the string. And you should see these errors somewhere. – Scott

2 Answers

0
votes

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.

0
votes

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.