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

mjfigurAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your procedure only builds up the SQL string, but does not execute them, actually....

you need:

TASK_SQL_DROP := 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';
EXECUTE IMMEDIATE TASK_SQL_DROP;
COMMIT;
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Or

You can bypass variable as:-
EXECUTE IMMEDIATE 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';
0
 
awking00Commented:
How are you trying to run this procedure?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.