title: Can commit be applied to every INSERT INTO in a script?
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>Here's my script:</p>
  
  <pre><code>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;
  /
  </code></pre>