Number to Words dynamic

SELECT CAST

(

TO_CHAR

( TO_TIMESTAMP( LPAD(ABS(TO_CHAR(num,'99')),9,'0'), 'FF9' ), 'FFSP' )

AS VARCHAR2(100) 

) WORD, NUM FROM (

SELECT LEVEL NUM

FROM   DUAL

CONNECT BY LEVEL <= 20)

Quoting String literals with 10g

This is new feature of 10g that enables us to single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals. The mechanism is invoked with simple 'q' in PL/SQL only. Older oracle version fails with ORA-01756.

BEGIN
  -- Orginal syntax.
  DBMS_OUTPUT.put_line('This is Tim''s string!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Tim's string!#');
  DBMS_OUTPUT.put_line(q'[This is Tim's string!]');
  DBMS_OUTPUT.put_line(q'!This is Tim's string!!');
  DBMS_OUTPUT.put_line(q'{This is Tim's string!}');
  DBMS_OUTPUT.put_line(q'<This is Tim's st'ring!>');
  DBMS_OUTPUT.put_line(q'(This is Tim's string!)');
END;
/

DECLARE
  v_sql VARCHAR2(1024);
  v_cnt PLS_INTEGER;
BEGIN
  v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE']';
  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.');
END;
/

Convert Comma delimited String into Rows

SELECT    REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) WORDS
FROM       (SELECT 'AA,BB,CC,A,B,C'STR FROM DUAL)
CONNECT BY REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) IS NOT NULL

Duplicate Rows

Select Duplicate rows only

SELECT * FROM  SB_TEST1 A
WHERE  ROWID > (SELECT MIN(ROWID) FROM SB_TEST1 B
                WHERE A.A=B.A)
SELECT *
FROM   SB_TEST1
WHERE  ROWID NOT IN (SELECT MAX(ROWID) FROM SB_TEST1 GROUP BY A)  

Select all rows which considered having duplicate
 
SELECT *
FROM   SB_TEST1
WHERE  A IN (SELECT A FROM SB_TEST1
GROUP BY A HAVING COUNT (*) >1)

Find Decimal Part

SELECT REGEXP_SUBSTR(99999.0123456789, '[[:digit:]]+$')
FROM DUAL

--------------------------------------------------------------------------------
SELECT REGEXP_SUBSTR(TO_CHAR(99999.0123456789,'999999999.9999999999'), '[[:digit:]]+$')
FROM DUAL

Amount to Words by Rupees

Order of Trigger Firing

• Before Statement trigger (If present)
• Each row affected by the statement
  (a) Execute row level trigger (If present)
  (b) Execute the statement itself
  (c) Execute the after row level trigger (If Present)
• After statement trigger (If Present)