DB Oracle_Query

  •   Merge OR Insert Query (If Record already there then it will update the existing record else Insert new Entry) 

MERGE INTO TEST_TABLE temp
USING (
        SELECT * FROM DUAL
        ) P
ON (temp.FIELD_1='PACK12' AND temp.FIELD_2='ITEM12')
WHEN MATCHED THEN
    UPDATE SET
        temp.FIELD_11 = 1234,
        temp.FIELD_12 = 'VANSH_TEST',
        temp.FIELD_13 = 'TEST_123'
WHEN NOT MATCHED THEN
        INSERT (ID, FIELD_1, FIELD_2, FIELD_3, FIELD_4) VALUES(PRH_ID_SEQ.NEXTVAL, 'TEST1', 1234321, 'TEST3', 'TEST4')


Note: In above query, it will check FIELD_1 & FIELD_2 (Unique Constraints), If these 2 values are matching then it will update records (Except these 2 column) else it will insert new entry (2 Column must not be NULL) 

  • Insert Single Apostrophe
            SUPPOSE WANT TO INSERT TIME LIKE 12 O'CLOCK (APOSTROPHE BETWEEN O AND C):
UPDATE <TABLE_NAME> SET <COLUMN_NAME_1>='12 O''CLOCK' WHERE <COLUMN_NAME_2>='TEST';
  • Insert Ampersand sign (&)
            LIKE TOMM & JERRY USING PIPE OPERATOR:
UPDATE <TABLE_NAME> SET <COLUMN_NAME_1>='TOMM' || '&' || 'JERRY' WHERE <COLUMN_NAME_2>='TEST';
  • Find duplicate rows
            SELECT <COLUMN_NAME_1> , <COLUMN_NAME_2>, <COLUMN_NAME_3>, COUNT(*)
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME_1> , <COLUMN_NAME_2>, <COLUMN_NAME_3>
HAVING COUNT(*) > 1;
  • Search keywords in all package
            SELECT * FROM ALL_SOURCE 
WHERE UPPER(TEXT) LIKE UPPER('%<SEARCH_KEYWORD>%') ESCAPE '\'
  • Declare local var and group operation
            WITH
TEMP_TABLE1 AS (SELECT DISTINCT TAB1.COL1, TAB2.COL2, SUM(TAB1.COL2) TOTAL_COL2
FROM TABLE1 TAB1, TABLE2 TAB2
                WHERE 
                         TAB1.COL4 = 'TEST'
                     AND TAB1.COL5 = TAB2.COL2
                GROUP BY COL1, COL2)
            
            -- FOR MORE TEMP TABLE (NO NEED TO WRITE 'WITH')
            , TEMP_TABLE2 AS (SELECT ID, NAME FROM TABLE4)
        SELECT DISTINCT TAB1.COL1, TAB1.TOTAL_COL2, TAB2.ID, TAB3.COL4, TAB4.COL1,
            (TAB4.COL11 - NVL(TAB1.TOTAL_COL2, 0)) USD_CIV
            FROM TABLE3 TAB3, TABLE4 TAB4, TEMP_TABLE1 TAB1, TEMP_TABLE2 TAB2
            WHERE 
                TAB3.COL2 = TAB4.COL2
                AND TAB3.COL1 = TAB1.COL1
                AND TAB3.COL4 = TAB2.NAME;

  • Know DB Name
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '%outbound_messages%’;

  • Copy data from one table to another
insert into from_table
select * from to_table;