How do you insert multiple copies of a record in PL/SQL based on conditions

alfrecc 0
Viewed 15

I'm making a task tracking application in Oracle Apex. I'm having trouble figuring out how to insert multiple copies of the same record based on the RESOURCES value. When assigning a task, it may have more than one resource attached to it. I believe the delimiter in Apex is a colon ':' character. How do I insert a task record the same amount of times as there are resources attached to the task (and have each resource attached to one)? RESOURCES is of type VARCHAR2.

INSERT INTO TASKS_PM (TASK_ID,PROJECTID,START_DATE,END_DATE,DESCRIPTION,TASK_NAME,RESOURCES,PRIORITY,STATUS,PROGRESS,HAS_PARENT,PARENT_TASK)
VALUES ( 
    null, 
    :P3_PROJECTID,
    TO_DATE(:P3_START_DATE,'DD-MON-YYYY'),
    TO_DATE(:P3_END_DATE,'DD-MON-YYYY'),
    :P3_DESCRIPTION,
    :P3_TASK_NAME,
    :P3_RESOURCES,
    :P3_PRIORITY,
    :P3_STATUS, 
    :P3_PROGRESS,
    :P3_HAS_PARENT, 
    :P3_PARENT_TASK);

For example, if the following resources were attached to an insert: "Bobby Joe" and "Tyrion Lannister", there would be two copies of this record made, one with Bobby Joe as the resource, and the other with Tyrion Lannister as the resource.

Thanks!

oracle-apex sql
1 Answers

You'd split P3_RESOURCES into rows; here's one way to do it (I'm using a CTE which "simulates" some of the values you have in page items):

SQL> with temp (p3_projectid, p3_description, p3_resources) as
  2    (select 1, 'Some description', 'Bobby Joe:Tyrion Lannister' from dual)
  3  select p3_projectid,
  4         p3_description,
  5         regexp_substr(p3_resources, '[^:]+', 1, level) p3_resource
  6  from temp
  7  connect by level <= regexp_count(p3_resources, ':') + 1;

P3_PROJECTID P3_DESCRIPTION   P3_RESOURCE
------------ ---------------- --------------------
           1 Some description Bobby Joe
           1 Some description Tyrion Lannister

SQL>

In your case, you'd

insert into tasks_pm (projectid, description, resources)
select :p3_projectid, 
       :p3_description,
       regexp_substr(:p3_resources, '[^:]+', 1, level)
from dual
connect by level <= regexp_count(:p3_resources, ':') + 1;

(add other columns yourself)

Littlefoot0
Related