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

How to Calculate Running Total in Oracle SQL

Purpose:Get running total Using Analytical Function

select empno,
       ename,
       sal,
       deptno,
       sum(sal) over(partition by null ORDER BY EMPNO RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Running_Total
  from scott.emp
 order by empno

How to APPEND Hint Affects Redo Generation

If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING. The examples below step through this process to show it in action.
The following example is run against a database running in NOARCHIVELOG mode. The redo generation is displayed in bold.

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        634  recursive calls
       9946  db block gets
      50116  consistent gets
          2  physical reads
    8464520  redo size
        830  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1508  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        369  recursive calls
       1689  db block gets
      48194  consistent gets
          2  physical reads
      46048  redo size
        822  bytes sent via SQL*Net to client
        810  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1500  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> COMMIT;

Commit complete.

SQL>

As suggested, with the database running on NOARCHIVELOG mode, the addition of the APPEND hint did reduce the amount of redo generated.
The next example performs the same test, but this time on a database running in ARCHIVELOG mode.

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        613  recursive calls
      11792  db block gets
     116808  consistent gets
          2  physical reads
   10222352  redo size
        370  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3142  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
   10222864  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL>

Notice how the addition of the APPEND hint no longer has an impact on the amount of redo generated.
To allow the APPEND hint to have an impact on redo generation again, we must set the table to NOLOGGING.

SQL> ALTER TABLE t1 NOLOGGING;

Table altered.

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        506  recursive calls
      11790  db block gets
     116652  consistent gets
          0  physical reads
   10222328  redo size
        373  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3139  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
      25968  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>