Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Thursday, March 9, 2017

Erase everything - Database schema

If you wish to format your schema, then follow these steps->

1) Run the following query->

SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;

2) Copy and paste the output from above query and execute it.

3) Execute this -> PURGE RECYCLEBIN;

Wednesday, October 26, 2016

Why DDL is faster than DML?

DDL is always and always faster than DML.

After firing DDL statement we cannot fire rollback command, but in DML we can.

The reason being for DML; Oracle stores records in Redo Log Files; and copies them back during rollback.

For DDL statements; Log Files are not maintained.



One common question always asked is this -> What is the difference between Delete and Truncate?

Difference 1: Truncate (DDL) is faster than Delete (DML)

Difference 2: Delete can be rollbacked whereas Truncate can't.

Reason being Redo Log Files not maintained for Truncate.

Indexing in Oracle

Ever wondered what Indexing actually does :)

An index is a performance tuning mechanism that allows faster retreival of records.

Searching an indexed table is always faster than a normal table since indexing keeps the records sorted (B-Tree).

In normal search oracle has to scan the entire table, but in an indexed table there is no need to scan the entire table since the records are already sorted.


Index on Single column ->

CREATE INDEX PIndex ON TableName (ColumnName)


Index on multiple columns->

CREATE INDEX PIndex ON TableName (ColumnOne, ColumnTwo)


Following query fetches only the user defined Indexes->

SELECT
i.INDEX_NAME, i.TABLE_NAME, ic.COLUMN_NAME, ic.COLUMN_POSITION, UNIQUENESS
FROM
USER_INDEXES i JOIN USER_IND_COLUMNS ic ON i.INDEX_NAME = ic.INDEX_NAME
WHERE
i.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='CITI_AMWS_CLUSTER_162');


Above you need to enter OWNER = schemaName.


To fetch all available Indexes use this ->

SELECT * FROM ALL_INDEXES;


Following DDL statement can be used to rebuild in Indexes.

ALTER INDEX index_name REBUILD;


Drop Index ->

DROP INDEX index_name;

Saturday, September 24, 2016

Remove Special Characters, Tabs, New Lines, Spaces and HTML Tags in SQL

We come across situations where we need to remove HTML tags , New Lines, Tabs and Spaces from a database column. Also we need to allow/disallow some special characters.

This can be achieved through the following SQL queries->


Remove HTML tags->

SELECT REGEXP_REPLACE(memotext,'<[^>]*>','') FROM TB_CITIALERTS_MEMO;



Remove new line(\n) and tabs(\t) ->

SELECT REPLACE(REPLACE(memotext,CHR(10),''),CHR(13),'') FROM TB_CITIALERTS_MEMO;

CHR(10) = New Line
CHR(13) = TAB



Replace Multiple spaces by Single space ->

SELECT REGEXP_REPLACE(memotext,'( ){2,}', ' ') FROM TB_CITIALERTS_MEMO;

The above query replaces more than 1 space by a single space.



Allow some special characters ->

SELECT REGEXP_REPLACE(memotext,'[^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []') FROM TB_CITIALERTS_MEMO;

The above query allows those special characers that are included in this list [^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []'), here you can omit the ones that you dont want to show.



We can also combine everything together as shown below ->

SELECT REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(memotext,'<[^>]*>',''),'( ){2,}', ' '),CHR(10),''),CHR(13),''),'[^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []') memotext
FROM TB_CITIALERTS_MEMO;

Wednesday, April 27, 2016

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

Tuesday, April 26, 2016

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.

Wednesday, April 20, 2016

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

Tuesday, March 15, 2016

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.

Tuesday, March 8, 2016

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



Home