Can commit be applied to every INSERT INTO in a script?

Question

I have just moved from SQL Server to ORACLE, and I am working on a script (SQL Server style). In SQL Server, we use BEING TRAN - END TRAN after applying some logic in a script. And this can be done inside a BEGIN-END block. In Oracle, I am finding this a bit difficult. After a lot of Googling and searching on this site, I am still not clear on how can I met this requirement.

When I ran the script (pasted below), this ran as one transaction. And DBMS_OUTPUT.Put_line also displays once the whole script is executed. Is there any way to print the DBMS_OUTPUT.Put_line after each and every commit?

Also, I am open to ideas, if there's any other way to work on this script, so that every sub-query commits before the script moves to the next sub-query... Please let me know.

Here's my script:

SET SERVEROUTPUT ON;
--spool Consolidated.log;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET DEFINE OFF;

ALTER SESSION SET GLOBAL_NAMES=FALSE;

DECLARE
ExtractType         NUMBER(9);
RecordsExtracted    NUMBER(9);
CurStatus           NUMBER(9);
StartDate           date;
ErrorMessage        NVARCHAR2(1000);
LastExtrctTimestamp DATE;


BEGIN    
    -- AgreementTradeTypes
    StartDate := sysdate;
    ExtractType := 44;

    DELETE FROM AgreementTradeTypes;

    INSERT INTO AgreementTradeTypes (AgreementId,AgreementName,PrincipalId,Principal,CounterpartyId,Counterparty, TradeTypeId,TradeTypeName,BusinessLine,AdditionalCriteria)
    -- From CORE DB 
    SELECT  
            IATT.AgreementId, AG.AgreementName, IATT.PRINCIPALID, Principal.ENTITYNAME Principal, 
            IATT.COUNTERPARTYID, Cpty.ENTITYNAME Counterparty, 
            IATT.TradeTypeID, TT.TradeTypeName, BusLine.ENUMTEXT BusinessLine, IATT.ADDITIONALCRITERIA
    FROM    IncludedAgreementTradeTypes@RPTCORE IATT 
            INNER JOIN Entities@RPTCORE Principal ON IATT.PRINCIPALID = Principal.ENTITYID
            INNER JOIN Entities@RPTCORE Cpty ON IATT.CounterpartyId = Cpty.ENTITYID
            INNER JOIN EnumValues@RPTCORE BusLine ON IATT.BusinessLine = BusLine.ENUMVALUE AND BusLine.ENUMTYPE = 'BusinessLine'
            INNER JOIN Agreements@RPTCORE AG ON IATT.AGREEMENTID = AG.AgreementID
            INNER JOIN TradeTypes@RPTCORE TT ON IATT.TRADETYPEID = TT.TradeTypeID
    ORDER BY IATT.AgreementId;
    RecordsExtracted := SQL%RowCount;

    DBMS_OUTPUT.put_line('AgreementTradeTypes Records Extracted:' || RecordsExtracted);       

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'AgreementTradeTypes Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);

    COMMIT; /* Committing first Block */

    -- INTEREST PAYMENT PERIODS 
    StartDate := sysdate;
    ExtractType := 57;

    DELETE FROM InterestPaymentPeriods;

    INSERT INTO InterestPaymentPeriods (InterestPaymentPeriodId,AgreementId,AgreementName,CurrencyId,CurrencyName,InstrumentId,InstrumentName,PositionType,CollateralMarginType,PeriodStart,PeriodEnd,NextPeriodEnd,AccruedInterest,OpeningBalance,EndingBalance,MarketIndexId,MarketIndexName,Spread,DayCountConvention,CalculationType,ManagingLocation,BusinessLine)
    -- From CORE DB 
    SELECT
            IPP.INTERESTPAYMENTPERIODID, IPP.AGREEMENTID, AG.AGREEMENTNAME, IPP.CURRENCYID, CUR.CODE CurrencyName, IPP.INSTRUMENTID,
            Instruments.DESCRIPTION InstrumentName, PosType.ENUMTEXT PositionType, CollMargType.ENUMTEXT CollateralMarginType,
            IPP.PERIODSTART, IPP.PERIODEND, IPP.NEXTPERIODEND, IPP.ACCRUEDINTEREST, IPP.OPENINGBALANCE, IPP.ENDINGBALANCE,
            IPP.MARKETINDEXID, MI.MARKETINDEXNAME, IPP.SPREAD, DCC.ENUMTEXT DayCountConvention, CalcType.ENUMTEXT CalculationType,
            Cty.CITYNAME ManagingLocation, BusLine.ENUMTEXT BusinessLine
    FROM
            INTERESTPAYMENTPERIODS@RPTCORE IPP
            INNER JOIN Agreements@RPTCORE AG ON IPP.AGREEMENTID = AG.AGREEMENTID
            LEFT OUTER JOIN Currencies@RPTCORE CUR ON IPP.CURRENCYID = CUR.CURRENCYID
            LEFT OUTER JOIN Cities@RPTCORE Cty ON IPP.MANAGINGLOCATIONID = Cty.CITYID
            LEFT OUTER JOIN MarketIndexes@RPTCORE MI ON IPP.MARKETINDEXID = MI.MARKETINDEXID
            LEFT OUTER JOIN Instruments@RPTCORE ON IPP.INSTRUMENTID = Instruments.INSTRUMENTID
            LEFT OUTER JOIN EnumValues@RPTCORE PosType ON IPP.POSITIONTYPE = PosType.ENUMVALUE AND PosType.ENUMTYPE = 'PositionType'
            LEFT OUTER JOIN EnumValues@RPTCORE CollMargType ON IPP.COLLATERALMARGINTYPE = CollMargType.ENUMVALUE AND CollMargType.ENUMTYPE = 'CollateralMarginType'
            LEFT OUTER JOIN EnumValues@RPTCORE DCC ON MI.DAYCOUNTCONVENTION = DCC.ENUMVALUE AND DCC.ENUMTYPE = 'DayCountConvention'
            LEFT OUTER JOIN EnumValues@RPTCORE CalcType ON IPP.CALCULATIONTYPE = CalcType.ENUMVALUE AND CalcType.ENUMTYPE = 'CalculationType'
            LEFT OUTER JOIN EnumValues@RPTCORE BusLine ON IPP.BUSINESSLINE = BusLine.ENUMVALUE AND BusLine.ENUMTYPE = 'BusinessLine';
    RecordsExtracted := SQL%RowCount;       

    DBMS_OUTPUT.put_line('InterestPaymentPeriods Records Extracted:' || RecordsExtracted);

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'Interest_Payment_Periods Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);

    COMMIT; /* Committing Second Block */
END;

--spool off;
/

Answer 1

The usual practice in Oracle is to commit only when the business transaction is complete, so that a transaction is not part-processed. This differs from some others systems because Oracle's multiversioning and locking systems ensure that writers do not block readers and readers do not block writers.

For the DBMS_Output issue, no you cannot get a response from the server through DBMS_Output partway through the block's execution. You might like to look at writing data to a serverside file using Utl_File for that.

Other thoughts:

• Consider using TRUNCATE instead of DELETE if you're deleting every row AND you do not need to use foreign keys against that table. TRUNCATE has an implicit commit associated with it, so apply all your truncates at the beginning of the procedure.

• Consider using the APPEND hint on the inserts to invoke direct path insert if you are loading bulk data, have indexes to maintain, and do not need to allow multiple simultaneous inserts into the table.

• I'm guessing that the ORDER BY on the inserts is there for a reason -- usually in Oracle it would be to ensure that data rows are physically clustered on the order-by column(s), which leads to greater efficiency on index-based access via those columns. Using a direct path insert would help guarantee physical row ordering, but if you don't need that clustering then remove the ORDER BY