CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
roymustang86
roymustang86

Reputation: 8553

Is there any way to do a selective commit in oracle?

I have a PL/SQL script where it runs a bunch of commands, and calls commit at the very end.

 select count(*) into countCol from USER_TAB_COLUMNS where TABLE_NAME = 'EVAPP_CARLETON_RESULT' and COLUMN_NAME = 'AMTFIN' and DATA_SCALE is null; 
 IF (countCol <> 0) then   

 execute immediate 'alter table EVAPP_CARLETON_RESULT add AMTFIN_TMP NUMBER(10,2)' ; 

 execute immediate 'update EVAPP_CARLETON_RESULT set AMTFIN_TMP = AMTFIN' ; 

 execute immediate 'alter table EVAPP_CARLETON_RESULT drop column AMTFIN' ; 

 execute immediate 'alter table EVAPP_CARLETON_RESULT rename column AMTFIN_TMP to AMTFIN' ; 


 DBMS_OUTPUT.put_line('This column EVAPP_CARLETON_RESULT.AMTFIN has been modified to the required precision'); 
 END IF; 
 logger('68');

 evaluate.commitScript;

There are 68 such blocks prior to this but evaluate.commitScript is called at the very end.

But, logger is called after every such block, and it has a commit statement inside of it.

 create or replace
 PROCEDURE LOGGER 
(MESSAGE1 IN VARCHAR2  ) AS 
 pragma autonomous_transaction;
   BEGIN
     insert into message_log (datetime, message) values (sysdate, message1);
     commit;
   END LOGGER;

The commit commits all changes simultaneously. Not just the changes made by the procedure. Is there anyway, we can call commit selectively?

Upvotes: 0

Views: 2002

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

The commit in the LOGGER procedure will only commit the changes made by that call of the LOGGER procedure.

If the snippet you posted is representative of the 68 blocks, the problem is that DDL statements (like ALTER TABLE) implicitly issue two commits-- one before the statement is executed and one after the execution if the execution was successful. That means that DDL cannot be done transactionally and the evaluate.commitScript call is neither committing nor rolling back any changes.

Depending on the Oracle version, edition, and configuration, you may be able to use something like Oracle flashback database to create a restore point before running your script and the flashing back to that restore point if your script fails (though that would also roll back the changes made by your LOGGER procedure).

Upvotes: 4

BD.
BD.

Reputation: 890

It looks like you already have the solution with this line in the LOGGER proc:

pragma autonomous_transaction

That statement sets a separate transaction within the procedure. The COMMIT issued there will not commit any transactions outside of that procedure.

Also, the DDL in the EXECUTE IMMEDIATE statements are implicitly committed.

Upvotes: 7

Related Questions