CODEWITHSUNDEEP
oracle-databaseoracle-apex
jase sykes
jase sykes

Reputation: 206

Oracle Apex Run 2 sql scripts on via button press

I have users who enter staff at the end of the day i have to run 2 scripts

which sets all the base master and detail data for each member over a 2 years.

Script 1. Generates the master data

MERGE INTO MD_TS_MAST d
USING (SELECT e.ENG_ID,
            a.DT 
       FROM MAN_ENGINEERS e
       CROSS JOIN ALL_DATES a) s 
ON (d.ENG_ID = s.ENG_ID AND
    d.MS_DATE = s.DT) 
WHEN NOT MATCHED THEN 
INSERT (ENG_ID, 
        MS_DATE) 
VALUES (s.ENG_ID, 
        s.DT);

Script 2. Generates the detail data

INSERT INTO MD_TS_DETAIL 
        (MD_ID) 
SELECT MAST_ID 
FROM   MD_TS_MAST 
WHERE  MAST_ID NOT IN (SELECT MD_ID 
                   FROM   MD_TS_DETAIL 
                   WHERE  MD_ID IS NOT NULL);

question is how can I create a button that when pressed will run the 2 scripts.

Upvotes: 0

Views: 1046

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

Create a PL/SQL page process that runs on submit with the source:

MERGE INTO MD_TS_MAST d
USING (SELECT e.ENG_ID,
            a.DT 
       FROM MAN_ENGINEERS e
       CROSS JOIN ALL_DATES a) s 
ON (d.ENG_ID = s.ENG_ID AND
    d.MS_DATE = s.DT) 
WHEN NOT MATCHED THEN 
INSERT (ENG_ID, 
        MS_DATE) 
VALUES (s.ENG_ID, 
        s.DT); 

INSERT INTO MD_TS_DETAIL 
        (MD_ID) 
SELECT MAST_ID 
FROM   MD_TS_MAST 
WHERE  MAST_ID NOT IN (SELECT MD_ID 
                   FROM   MD_TS_DETAIL 
                   WHERE  MD_ID IS NOT NULL);

Then put a button on the page that submits the page.

Upvotes: 3

Related Questions