Synthesizing rows in oracle

Purpose:  Convert a comma delimited string into rows. In following example i am using the string '123,456,789',

SELECT SUBSTR(('123,456,789'),
              loc + 1,
              NVL(LEAD(loc) OVER(ORDER BY loc) - loc - 1,
                  length('123,456,789') - loc)) output
  FROM (SELECT DISTINCT INSTR(('123,456,789'), ',', 1, LEVEL) loc
          FROM dual
        CONNECT BY LEVEL < LENGTH('123,456,789')) ;

OUTPUT
-----------
123
456
789

Quoting a String Literals in Oracle PL/SQL

Purpose: Oracle 10g Introduces smart way to Quote String Literals. It allows us to define our own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter.

BEGIN
  -- Before 10g.
  DBMS_OUTPUT.put_line('This is Fahd''s Data!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Fahd's Data!#');
  DBMS_OUTPUT.put_line(q'[This is Fahd's Data!]');
END;
/

How to Delete duplicate rows in Oracle Database

Purpose: To delete duplicate rows from Table. There are many ways to do this, but i can remember this right now..

delete from table_name A where A.rowid >
   (select min(B.rowid) from table_name B where A.col = B.col);

How to Generate Gape free sequence oracle

Purpose: To get next sequence number(max+1) from table, but if there is gap, fill the gap first.
Useful for small tables   

Drop Table num_seq_test;
create table num_seq_test
(num_seq number(3) );
insert into num_seq_test values(1);
insert into num_seq_test values(2);
insert into num_seq_test values(3);
insert into num_seq_test values(4);
insert into num_seq_test values(5);
insert into num_seq_test values(7);
insert into num_seq_test values(8);
insert into num_seq_test values(9);
commit;

declare
 l_my_aray dbms_sql.Number_Table;
 l_next_sr# number(3);
begin
  select num_seq
  bulk collect into l_my_aray
  from num_seq_test
  where num_seq is not null
 -- and rownum < 1
  order by 1;
 /* When no data in table just return 1 */
 if sql%rowcount != 0
   then 
      for i in 1..l_my_aray.count
        loop
          if l_my_aray(i) != i
            then
              l_next_sr# := i ;
              exit;
          else
              l_next_sr# := i + 1  ;
          end if;
      end loop;
  else
   l_next_sr# := 1;    
  end if;     
 dbms_output.put_line(l_next_sr#);
end;

Drop Table num_seq_test;

How to make Matrix Report in Oracle SQL (PIVIOT)

Purpose: Matrix Report in Oracle SQL using PIVIOT

SELECT * FROM scott.emp
PIVOT
(
SUM(sal)
FOR deptno
IN (10 AS "Ten", 20 AS "Twenty", 30)
)

How to Enable PLSQL WARNINGS in Oracle

Purpose: Oracle has improved its PL/SQL warning system in 11g. If we write code like
WHEN OTHERS THEN NULL, it will give warning.

ALTER SESSION SET plsql_warnings = 'enable:all'
/
CREATE OR REPLACE PROCEDURE others_test AS
BEGIN
   RAISE_APPLICATION_ERROR(-20000, 'Force and exception');
EXCEPTION
WHEN OTHERS THEN
       NULL;
END;

This procedure will compile with following warning
PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

CONNECT BY LEVEL

Purpose: Get Serialno

SELECT LEVEL just_a_column FROM dual CONNECT BY LEVEL <= 365