Code Snippet: PL/SQL Cursor inside Procedure or Anonymous Block

CREATE OR REPLACE PROCEDURE LOAD_DATA AS
/* replace the above line with DECLARE to have anonymous block */

  A_VAR VARCHAR2(30) := 'A Variable';

  CURSOR CUR_ST IS
  SELECT ST_ID, DESCRIPTION, ST_TYPE from SAMPLE_TABLE;
  /* SQL Query for defining cursor */

BEGIN

FOR CUR IN CUR_ST LOOP

  /* Looping... add main logic here */
  IF CUR.ST_TYPE = '1' THEN
    INSERT INTO OTHER_TABLE (OT_ID, DESCRIPTION)
    VALUES(CUR.ST_ID, CUR.DESCRIPTION);
  ELSE
    A_VAR := 'SKIPPING';
  END;

END LOOP;

COMMIT;

EXCEPTION
    /* Do error handling here */
    /* Remove the EXCEPTION block for exceptions
       to show up outside of procedure */
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred!');
    ROLLBACK;

END LOAD_DATA;

DBMS_OUTPUT.PUT_LINE can be used to write to console. It works if server output is on:
set serveroutput on size 30000;

RepMonitor: Oracle Replication Error Monitoring Tool

I had an assignment to rectify the Oracle data replication implementation between multiple Oracle databases connected over a wide area network. The replication was throwing a large number of errors and I needed an efficient tool to monitor the replication process. The standard tool provided by Oracle had certain limitations so I decided to quickly write something which could fill in the gaps. That’s how I got into writing RepMonitor which I am sharing here.

RepMonitor is capable of doing the following:
1- list replication errors
2- analyze them by comparing original and current data values at different sites
3- Re-execute replication transaction
4- Remove replication transaction from pipeline
5- Generate reports and serialize replication transaction data

Following are the tasks which can be done more efficiently in RepMonitor than Replication Management Tool that comes with Oracle Enterprise Manager:
1- Search and filter transaction errors by date and other criterias.
2- Load data on-demand only to provide better performance.
2- Generate transaction error reports.
3- Save/Serialize selected transactions and all related info in xml format.

Just to briefly elaborate on point # 2 i.e. loading data on-demand. Oracle Enterprise Manager loads all the transaction error data at start up so if you have a large number of errors, it would take ages to load. Similarly any refresh reloads everything. RepMonitor overcomes these problems by loading data on-demand.

If you want to know more about Oracle Advanced Replication, go to Oracle.com. Here is an introductory whitepaper from Oracle.

RepMonitor is developed using Visual Studio 2005 and programming language is C#.Net. The source code can be downloaded from here.