Link to home
Start Free TrialLog in
Avatar of mjfigur
mjfigur

asked on

Oracle Procedure and will not commit delete and insert statements

Just been having a problem with this procedure that I wrote for Oracle 10g. I have copied the procedure code below and it is a pretty simple statment in which it does a refresh of some materialized views then drops and creates a table and then runs two insert and two delete statments.

The insert and delete statments perform perfectly when ran by themselves in TOAD however in this procedure the procedure will complete but the insert and delete does not function at all.
CREATE OR REPLACE PROCEDURE BPMGR.REFRESH_BREACH AS
TASK_SQL_CREATE    VARCHAR2(500);
   TASK_SQL_DROP      VARCHAR2(500);
ON_DEMAND_HIST_UPDATE      VARCHAR2(500);
ON_DEMAND_HIST_UPDATE_MANUAL VARCHAR2(500);
ON_DEMAND_LPS_DELETE    VARCHAR2(500);  
ON_DEMAND_LPS_DELETE_MANUAL   VARCHAR2(500); 

BEGIN
dbms_refresh.refresh('BPMGR.EMC_BREACH');
   
   
   TASK_SQL_DROP := 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';
   COMMIT;
   
   
   TASK_SQL_CREATE := 'CREATE TABLE BPMGR.EMC_COLFCD_TASKS 
                        AS SELECT * FROM BPMGR.COLFCD_TASKS';
    COMMIT;
    
   ON_DEMAND_HIST_UPDATE := 'INSERT INTO BPMGR.EMC_ON_DEMAND_HIST
                                SELECT L.LETTER_ID, L.LOAN_NUMBER, TRUNC(CURRENT_DATE), REQUEST_DATE
                                FROM BPMGR.ON_DEMAND O,
                                BPMGR.LPS_REQUEST L
                                WHERE  O.LETTER_ID = L.LETTER_ID
                                and O.LOAN_NUMBER = L.LOAN_NUMBER';
                             
     COMMIT; 
   ON_DEMAND_HIST_UPDATE_MANUAL := 'INSERT INTO BPMGR.EMC_ON_DEMAND_HIST
                                    SELECT L.LETTER_ID, L.LOAN_NUMBER, TRUNC(CURRENT_DATE), REQUEST_DATE
                                    FROM BPMGR.ON_DEMAND_MANUAL O,
                                         BPMGR.LPS_REQUEST L
                                    WHERE  O.LETTER_ID = L.LETTER_ID
                                    and O.LOAN_NUMBER = L.LOAN_NUMBER ';
     
   COMMIT;                           
  
 ON_DEMAND_LPS_DELETE := 'Delete From BPMGR.LPS_REQUEST WHERE EXISTS
                                    (SELECT *
                                        FROM BPMGR.LPS_REQUEST L,
                                             BPMGR.EMC_BREACH B
                                        WHERE L.LOAN_NUMBER = B.LOAN_NUMBER
                                        AND L.LETTER_ID = B.LETTER_ID)';
   
   COMMIT;     
   
   ON_DEMAND_LPS_DELETE_MANUAL:=  'Delete From BPMGR.LPS_REQUEST WHERE EXISTS
                                    (SELECT *
                                        FROM BPMGR.LPS_REQUEST L,
                                             BPMGR.EMC_MANUAL_BREACH B
                                        WHERE L.LOAN_NUMBER = B.LOAN_NUMBER
                                        AND L.LETTER_ID = B.LETTER_ID)';
   
   COMMIT;                                       
                                    
END;
/

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or

You can bypass variable as:-
EXECUTE IMMEDIATE 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';
How are you trying to run this procedure?