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)

Predefined Exceptions

An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.You can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate. To handle unexpected Oracle errors, you can use the OTHERS handler. Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error.PL/SQL declares predefined exceptions globally in package STANDARD. You need not declare them yourself. Can wirte handlers for predefined exceptions by using the SQLCODE.

ZERO_DIVIDE -A program attempts to divide a number by zero.
VALUE_ERROR- An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)