?
Solved

Oracle Procedure and will not commit delete and insert statements

Posted on 2009-12-28
3
Medium Priority
?
659 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:mjfigur
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26131761
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26131836
Or

You can bypass variable as:-
EXECUTE IMMEDIATE 'DROP TABLE BPMGR.EMC_COLFCD_TASKS';
0
 
LVL 32

Expert Comment

by:awking00
ID: 26131960
How are you trying to run this procedure?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question