Procedure and Cursor - Simple example in PLSQL

Following is an simple example of Procedure and Cursors.

Here we fetch all the data in a cursor, loop through it and insert the data into the database.

Note: the following line is very important else the cursor will move to an infinite loop -> EXIT WHEN CURSOR_TB_DATA%NOTFOUND;

SchemaName is an argument to the procedure and is passed when calling the procedure.

Example ->

CREATE OR REPLACE
PROCEDURE FETCH_TABLES_ROWS(SchemaName IN VARCHAR2) AS
 
    CURSOR CURSOR_TB_DATA IS
    SELECT TABLE_NAME,
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM '
    ||OWNER ||'.' ||TABLE_NAME)),'/ROWSET/ROW/C')) TABLE_ROWS
    FROM ALL_TABLES
    WHERE OWNER=SchemaName;  
   
    TABLE_DATA CURSOR_TB_DATA%ROWTYPE;
BEGIN    
    OPEN CURSOR_TB_DATA;
    LOOP
      FETCH CURSOR_TB_DATA INTO TABLE_DATA;
      EXIT WHEN CURSOR_TB_DATA%NOTFOUND;
      INSERT INTO GTB_FETCH_TABLE_ROWS VALUES (TABLE_DATA.TABLE_NAME,TABLE_DATA.TABLE_ROWS,SchemaName,SYSDATE);
    END LOOP;
    CLOSE CURSOR_TB_DATA;
    DBMS_OUTPUT.PUT_LINE('End of Procedure');
   
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error!');  
END;

Calling : The above procedure is called by executing the following statement->

EXECUTE FETCH_TABLES_ROWS('CIBC_SCRIPT_TEST');

SQL query to fetch all user defined tables and their Row count.

If you want to find how many user defined tables exists in the database and also how many Rows each table contains, fire the below query.

SELECT TABLE_NAME,
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT                     COUNT(*) C FROM '
    ||OWNER ||'.' ||TABLE_NAME)),'/ROWSET/ROW/C')) TABLE_ROWS
FROM ALL_TABLES
WHERE OWNER=SchemaName;

Note: Here SchemaName should be your schema name in single quotes.

Remove duplicate records from Table in Oracle

Suppose by mistake, many duplicate records have been entered in the database and you need to keep only distinct records.

You can use the following query to achieve this.

First we select only the distinct records by using MIN(ROWID).

Later we delete all the remaining duplicate records which are not present in the distinct records fetched earlier.

DELETE FROM GTB_CUST_ACCOUNT
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM GTB_CUST_ACCOUNT
GROUP BY ACCOUNT_NO||'~'||TRANSIT_NO);