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.
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you trying to run this procedure?
You can bypass variable as:-
EXECUTE IMMEDIATE 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';