Monday, September 3, 2012

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

Thursday, August 30, 2012

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

Tuesday, August 21, 2012

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)