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);

Buffer too small for CLOB to CHAR or BLOB to RAW conversion in Oracle.

Sometimes when we try to fetch some column from database, which has large number of character data, we get the following error->

SQL->

SELECT
            WORKITEMID , EXCEPTION_DESC
FROM
       (SELECT
                 TPO.WORKITEMID,
                 TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                         ED.WORKITEMID).EXTRACT('//text()')) AS EXCEPTION_DESC
       FROM
                 FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
      WHERE
                 FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                AND TPO.WORKITEMID=ED.WORKITEMID
       GROUP BY
                TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE,
                TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD,
                TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);

Error ->

The workaround for this is to use getClobVal() function in inner query and fetch it in outer query by using TO_CHAR

SELECT 
            WORKITEMID , TO_CHAR(EXCEPTION_DESC)
FROM
           (SELECT
                   TPO.WORKITEMID, 
                   TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                          ED.WORKITEMID).EXTRACT('//text()').getClobVal()) AS EXCEPTION_DESC   
           FROM
                   FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
           WHERE
                    FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                    AND TPO.WORKITEMID=ED.WORKITEMID
           GROUP BY
                   TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE, 
                   TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD, 
                   TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);


Even after this you may still get the following error ->

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5940, maximum: 4000)
22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"

*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where the LOB size was bigger than the buffer limit for CHAR and RAW types.

Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are
reported in bytes.

*Action: Do one of the following

1. Make the LOB smaller before performing the conversion, for example, by using SUBSTR on CLOB

2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.



In this case you have to use DBMS_LOB.SUBSTR, as shown in below SQL.

SELECT 
            WORKITEMID , DBMS_LOB.SUBSTR(EXCEPTION_DESC,4000)
FROM
           (SELECT
                   TPO.WORKITEMID, 
                   TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                          ED.WORKITEMID).EXTRACT('//text()').getClobVal()) AS EXCEPTION_DESC   
           FROM
                   FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
           WHERE
                    FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                    AND TPO.WORKITEMID=ED.WORKITEMID
           GROUP BY
                   TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE, 
                   TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD, 
                   TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);


Here DBMS_LOB.SUBSTR(EXCEPTION_DESC,4000) means we are selection only the first 4000 characters.

Show multiple rows of a SQL query in a single row (comma separated)

Suppose If you have a SQL query which returns multiple rows when you join 2 tables, as the second table has multiple records for the same joining column of the first table.

In this case you can use the inbuilt XMLAGG function available in SQL.

Table 1 -> TXN_PROC_OPN

Table 2 -> TXN_PROC_REMARK_M

SQL Query ->

SELECT 
           TPO.WORKITEMID, 
           TRIM(',' FROM XMLAGG(XMLELEMENT(E,PRM.REMARKS || ',' ) ORDER BY                                                              PRM.WORKITEMID).EXTRACT('//text()')) AS MAKER_REMARKS
FROM
           TXN_PROC_OPN TPO, TXN_PROC_REMARK_M PRM
WHERE
           TPO.WORKITEMID = PRM.WORKITEMID
GROUP BY
           TPO.WORKITEMID


Output->