Create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings

Create a query to display the total number of employees and, of that total, the number of
employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings


select count(DECODE (to_char(hire_date,'YYYY'), '2005',  'test'))Yr2005,
count(DECODE (to_char(hire_date,'YYYY'), '2006', 'test'))Yr2006,
count(DECODE (to_char(hire_date,'YYYY'), '2007', 'test'))Yr2007,
count(DECODE (to_char(hire_date,'YYYY'), '2008', 'test'))Yr2008
from employees
where to_char(hire_date,'YYYY') in ('2005','2006','2007','2008')

Basic Difference between CASE and DECODE Function in SQL (Oracle)

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.


Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a fucntion.



----Example of CASE----

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;

----Example of DECODE----

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;

Ref: http://theprofessionalspoint.blogspot.com/2012/05/decode-function-vs-case-statement-in.html

Find position of current day in a year (Oracle SQL)

---Find the day against any number

select to_date('365','ddd') find_date from dual;

SQL> select to_date('365','ddd') find_date from dual;

FIND_DATE
---------
31-DEC-14

SQL> select to_date('366','ddd') find_date from dual;
select to_date('366','ddd') find_date from dual
               *
ERROR at line 1:
ORA-01848: day of year must be between 1 and 365 (366 for leap year)


Note: ORA-01848: day of year must be between 1 and 365 (366 for leap year)

---Find position of current day in a year

select to_char(sysdate,'ddd') postion from dual;

POSITION
---
168

Uses of Escape Special Characters in Oracle SQL queries

Uses of Escape Special Characters in Oracle SQL queries

SELECT employee_id, last_name, job_id
FROM   employees WHERE  job_id LIKE '%SA\_%' ESCAPE '\';

or

SELECT employee_id, last_name, job_id
FROM   employees WHERE  job_id LIKE '%SA_%';

Note: Above both quires given same output in HR schema. The ESCAPE clause identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Following example we want to find all Oracle parameter that relate to I/O

LIKE “%_io_%’. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:

select ksppinm 
from sys.x$ksppi 
where ksppinm like '%_io_%';

Here we can see that we did not get the answer we expected. The SQL displayed all values that contained "io", and not just those with an underscore. To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:

select ksppinm 
from sys.x$ksppi 
where ksppinm like '%\_io\_%' ESCAPE '\';

###Here we create a table and test the scenario###

CREATE TABLE test (
test_col VARCHAR2(25));

INSERT INTO test VALUES ('23%45');
INSERT INTO test VALUES ('2345');
INSERT INTO test VALUES ('2365');
INSERT INTO test VALUES ('Dhaka Metro');
INSERT INTO test VALUES ('Rangpur');
COMMIT; 

--Now try to user to find out the value contain "%" special character--

SELECT *
FROM test
WHERE test_col LIKE '23_5';

SELECT *
FROM test
WHERE test_col LIKE '2%5';

SELECT *
FROM test
WHERE test_col LIKE '_3%5';

SELECT *
FROM test
WHERE test_col LIKE '%a%a %';

--Finally we can get the output using following query--

SELECT *
FROM test
WHERE test_col LIKE '%\%%' ESCAPE '\';


Above example is applicable for all version of oracle database in any platform

Cheers----

Find Top 100 SQL Statement based on CPU utilization

-----Find Top 100 SQL ID in Oracle----

select * from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL)) DESC) elapsed_rank
from
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where
   sn.begin_interval_time between to_date('05-may-2014 0001','dd-mon-yyyy hh24mi')
and
   to_date('12-may-2014 0600','dd-mon-yyyy hh24mi')
and
   sn.snap_id=s.snap_id
group by
   s.sql_id
   ) where cpu_rank <=100 and elapsed_rank<=100;
   





  
-----Find Top 100 SQL text in Oracle----

select sql_id,sql_text from dba_hist_sqltext where sql_id in ( select sql_id from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL)) DESC) elapsed_rank
from
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where
   sn.begin_interval_time between to_date('05-may-2014 0001','dd-mon-yyyy hh24mi')
and
   to_date('12-may-2014 0600','dd-mon-yyyy hh24mi')
and
   sn.snap_id=s.snap_id
group by
   s.sql_id
   ) where cpu_rank <=100 and elapsed_rank<=100);

Example of ROLLUP and CUBE in Oracle

DROP TABLE dimension_tab;

CREATE TABLE dimension_tab (
  fid1   NUMBER NOT NULL,
  fid2   NUMBER NOT NULL,
  fid3   NUMBER NOT NULL,
  fid4   NUMBER NOT NULL,
  sales_value NUMBER(10,2) NOT NULL
);

INSERT INTO dimension_tab
SELECT TRUNC(DBMS_RANDOM.value(1,3)) AS fid1,
       TRUNC(DBMS_RANDOM.value(1,6)) AS fid2,
       TRUNC(DBMS_RANDOM.value(1,11)) AS fid3,
       TRUNC(DBMS_RANDOM.value(1,11)) AS fd4,
       ROUND(DBMS_RANDOM.value(1,100), 2) AS sales_value
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

Note:
fid1 generate value 1 and 2
fid2 generate value 1,2,3,4 and 5;
fid3 generate value 1 to 10;
fid4 generate value 1 to 10;

sum of sales_value always shold be same.
num_rows always should be 1000

---Find total sales value---

SELECT SUM(sales_value) AS sales_value
FROM   dimension_tab;

Including the first two columns in the GROUP BY clause should give us 10 rows (2*5), 
each with its aggregated values.

SELECT fid1,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1
ORDER BY fid1;

SELECT fid1,
       fid2,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, fid2
ORDER BY fid1, fid2;

Including the first three columns in the GROUP BY clause should give us 100 rows (2*5*10).


SELECT fid1,
       fid2,
  fid3,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, fid2,fid3
ORDER BY fid1, fid2,fid3;

----ROLLUP----
In addition to the regular aggregation results we expect from the GROUP BY clause, 
the ROLLUP extension produces group subtotals from right to left and a grand total. 
If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals

SELECT fid1,
       fid2,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fid1, fid2)
ORDER BY fid1, fid2;

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fid1, fid2, fid3)
ORDER BY fid1, fid2, fid3;


It is possible to do a partial rollup to reduce the number of subtotals calculated. 
The output from the following partial rollup is shown here.

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, ROLLUP (fid2, fid3)
ORDER BY fid1, fid2, fid3;

----CUBE----

In addition to the subtotals generated by the ROLLUP extension, the CUBE extension 

will generate subtotals for all combinations of the dimensions specified. If "n" is 

the number of columns listed in the CUBE, there will be 2n subtotal combinations.

SELECT fid1,
       fid2,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fid1, fid2)
ORDER BY fid1, fid2;

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fid1, fid2, fid3)
ORDER BY fid1, fid2, fid3;

Find a list of all users with DBA privilege in Oracle

Problem: Find a list of all users with DBA privilege in Oracle

Solution:

select * from dba_role_privs where granted_role='DBA';

Find Oracle Database Growth

1. Create a view to store Database Used Size
 
create or replace view day_wise_tblspc as
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM','EXAMPLE')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


select  sum(cur_size_mb),days
from day_wise_tblspc
group by days
order by days desc

run vmstat and direct the output into the Oracle table

# run vmstat and direct the output into the Oracle table . . .
cat /tmp/msg$$|sed 1,3d | awk  '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU
   do

      $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1<      insert into perfstat.stats\$vmstat
                           values (
                             sysdate,
                             $SAMPLE_TIME,
                             '$SERVER_NAME',
                             $RUNQUE,
                             $PAGE_IN,
                             $PAGE_OUT,
                             $USER_CPU,
                             $SYSTEM_CPU,
                             $IDLE_CPU,
                             0
                                  );
      EXIT
EOF
   done
  
---Finding System CPU Utilization----  

SELECT * FROM GV$OSSTAT ;

SELECT * FROM GV$SYSMETRIC_HISTORY

Displays information on all long operations for whole RAC.

-- Description  : Displays information on all long operations for whole RAC.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.module,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   gv$session s,
       gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.inst_id = sl.inst_id
AND    s.serial# = sl.serial#;

Displays memory allocations for the current database sessions for the whole RAC.

   Description  : Displays memory allocations for the current database sessions for the whole RAC.

    SELECT a.inst_id,
           NVL(a.username,'(oracle)') AS username,
           a.module,
           a.program,
           Trunc(b.value/1024) AS memory_kb
    FROM   gv$session a,
           gv$sesstat b,
           gv$statname c
    WHERE  a.sid = b.sid
    AND    a.inst_id = b.inst_id
    AND    b.statistic# = c.statistic#
    AND    b.inst_id = c.inst_id
    AND    c.name = 'session pga memory'
    AND    a.program IS NOT NULL
    ORDER BY b.value DESC;

Replace the SQL> Prompt with the Current User Name

 In order to replace the SQL> prompt with the current user name, edit ORACLE_HOME/sqlplus/admin/glogin.sql and add:


set sqlprompt '_user> '

Test the new SQL prompt:


$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 21 00:35:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SCOTT>

Play with SQL

Overview

Structured Query Language (SQL) is an implementation of a "data sublanguage" used in almost all relational database systems. SQL was developed by IBM in the early 1970s for use in System R, and is "de facto" standard, as well as an ISO and ANSI standard. In common usage, SQL supports four types of SQL statements:

1. Queries: This type of SQL statement implements the standard relational operations such as SELECTION, PROJECTION and JOIN. The SELECT statement forms the basis for many other SQL operations. You can use SELECT statements to query tables, create views, in subqueries, or when creating a table defined as the result set of query.
   
2. Data Manipulation Language (DML): DML statements are used to allow data in the database to be added, amended or deleted. DML functionality is provided by the use of INSERT, UPDATE and DELETE statements.
   
3. Data Definition Language (DDL): This is the language that allows for objects to be created or altered. Provided below are a few example Oracle DDL statements:
   
   
   • CREATE TABLE
   • DROP TABLE
   • ALTER TABLE
   • CREATE INDEX
   • DROP INDEX
   • ALTER INDEX
   • CREATE VIEW
   • CREATE TYPE
4. Data Control Language (DCL): This is the language that allows for transaction control. Provided below are a few example Oracle DCL statements:
   
   
   • COMMIT
   • SAVEPOINT
   • ROLLBACK
   • SET TRANSACTION

The development of SQL is governed by standards. A major revision of the SQL standard was completed in 1992, called SQL2. SQL3 is a newer revision that supports object extensions and will be partially implemented starting with Oracle8.

---

How can I transform a subquery involving the IN clause to a Join?

Original Statement:

  SELECT  e.empno, e.ename
  FROM    emp e
  WHERE   e.deptno IN (
      SELECT  d.deptno
      FROM    dept d
      WHERE   d.loc = 'CHICAGO'
  );

New Statement:

  SELECT e.empno, e.ename
  FROM   emp e, dept d
  WHERE  d.loc = 'CHICAGO'
    AND  d.deptno = e.deptno;

---

How can I transform a statement involving an OR condition to a UNION ALL?

Original Statement:

  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
     OR   loc = 'NEW YORK';

New Statement:

  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
  UNION ALL
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'NEW YORK';

---

How do I eliminate duplicate values in a table?

Provided below are four methods for identifying or removing duplicate rows from a table: Method 1:

  DELETE FROM emp a
    WHERE rowid > (
      SELECT min(rowid)
      FROM   emp b
      WHERE  a.emp_id = b.emp_id
    );

Method 2:

  CREATE TABLE emp2 AS
    SELECT distinct *
    FROM   emp;

  DROP TABLE emp;

  RENAME emp2 TO emp;

Method 3:

  DELETE FROM emp
    WHERE rowid NOT IN (
      SELECT    MIN(rowid)
      FROM      emp
      GROUP BY  emp_id
    );

Method 4:

  DELETE FROM emp a
    WHERE EXISTS (
      SELECT 'true'
      FROM   emp b
      WHERE  b.emp_id = a.emp_id
        AND  b.rowid < a.rowid
    );

NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record.

---

How can I get a count of the different data values in a column?

  SELECT
      dname
    , sum(decode(job, 'CLERK',     1, 0)) Clerk
    , sum(decode(job, 'SALESMAN',  1, 0)) Salesman
    , sum(decode(job, 'MANAGER',   1, 0)) Manager
    , sum(decode(job, 'ANALYST',   1, 0)) Analyst
    , sum(decode(job, 'PRESIDENT', 1, 0)) President
  FROM
      emp e
    , dept d
  WHERE
      e.deptno (+) = d.deptno
  GROUP BY
      dname;


  DNAME               CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
  -------------- ---------- ---------- ---------- ---------- ----------
  ACCOUNTING              1          0          1          0          1
  OPERATIONS              0          0          0          0          0
  RESEARCH                2          0          1          2          0
  SALES                   1          4          1          0          0

---

How can I get count/sum RANGES of data values in a column?

A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).

  SELECT
      job
    , sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
    , sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
    , sum(decode(greatest(sal,0),    least(sal,999), 1, 0))  "Range 0-1000"
  FROM      emp
  GROUP BY  job;


  JOB       Range 3000-6000 Range 1000-3000 Range 0-1000
  --------- --------------- --------------- ------------
  ANALYST                 2               0            0
  CLERK                   0               2            2
  MANAGER                 0               3            0
  PRESIDENT               1               0            0
  SALESMAN                0               4            0

---

How can I dynamically generate a list of comma-separated items?

I often find myself needing to dynamically generate a list of comma-separated items (i.e. a list of database files separated by commas). The output format, for example, would be:

...  Datafile1, Datafile2, Datafile3 ...

Instead of thinking of the list as a linear set of items, re-write the set of items each on a separate line as follows:

...
    Datafile1
  , Datafile2
  , Datafile3
...

Notice that a comma is used on each line except for the first item. We can now take advantage of a popular Analytic Function (RANK) to distinguish the first line. Consider the following CREATE CONTROLFILE example:

SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 9000
SET LINESIZE 135
SET TRIMSPOOL ON
SET TRIMOUT ON
SET ECHO OFF
SET SQLBLANKLINES ON

SPOOL cr_control_file_example.sql

prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
prompt   MAXLOGFILES 16
prompt   MAXLOGMEMBERS 3
prompt   MAXDATAFILES 100
prompt   MAXINSTANCES 8
prompt   MAXLOGHISTORY 3182
prompt LOGFILE
prompt   GROUP 1 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
prompt   ) SIZE 50M,
prompt   GROUP 2 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
prompt   ) SIZE 50M,
prompt   GROUP 3 (
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',
prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
prompt   ) SIZE 50M
prompt DATAFILE

SELECT
    CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1
      THEN
        '    ''' || name || ''''
      ELSE
        '  , ''' || name || ''''
      END AS file_name
FROM v$datafile;

prompt CHARACTER SET WE8ISO8859P1

SPOOL OFF

------------------------------------------------------------

CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 3182
LOGFILE
GROUP 1 (
'/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
) SIZE 50M,
GROUP 2 (
'/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
) SIZE 50M,
GROUP 3 (
'/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
) SIZE 50M
DATAFILE

    '+ORCL_DATA1/orcl/datafile/example.263.623550121'
  , '+ORCL_DATA1/orcl/datafile/soe.269.623559923'
  , '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217'
  , '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085'
  , '+ORCL_DATA1/orcl/datafile/system.259.623549989'
  , '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053'
  , '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147'
  , '+ORCL_DATA1/orcl/datafile/users.265.623550181'
CHARACTER SET WE8ISO8859P1

---

Can I retrieve only the Nth row from a table?

Method 1:

  SELECT
      ename
    , job
    , hiredate
  FROM
      emp
  WHERE
      rowid = (
        SELECT rowid FROM emp
        WHERE rownum <= 3
        MINUS
        SELECT rowid FROM   emp
        WHERE rownum < 3
      );

Method 2:

  SELECT
      ename
    , job
    , hiredate
  FROM
      emp
  WHERE
        rownum = 1
    AND rowid NOT IN (
      SELECT rowid FROM emp
      WHERE  rownum < 3
    );

NOTE: Always remember that there is no explicit order in a relational database.

---

Can I retrieve only rows X to Y from a table?

  SELECT
      ename
    , job
    , hiredate
  FROM
      emp
  WHERE
      rowid in (
        SELECT rowid FROM emp
        WHERE rownum <= 7
        MINUS
        SELECT rowid FROM   emp
        WHERE rownum < 3
      );

---

Can I retrieve EVERY Nth row from a table?

Method 1:

  SELECT
      ename
    , job
    , hiredate
  FROM
      emp
  WHERE
      (rowid,0) in (
        SELECT rowid, mod(rownum,4)
        FROM emp
      );

Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)

  SELECT
      ename
    , job
    , hiredate
  FROM (  SELECT rownum rn, empno, ename, job, hiredate
          FROM   emp
       ) d_table
  WHERE mod(d_table.rn,4) = 0;

---

Retrieve the TOP N Rows from a table?

Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause

  SELECT
      ename
    , job
    , hiredate
    , sal
  FROM (  SELECT empno, ename, job, hiredate, sal
          FROM   emp
          ORDER BY sal DESC
       )
  WHERE rownum < 6;

Method 2: The following workaround will work with prior releases

  SELECT
      ename
    , job
    , hiredate
    , sal
  FROM
      emp a
  WHERE 5 >= ( SELECT count(distinct b.sal)
               FROM   emp b
               WHERE  b.sal >= a.sal
             )
  ORDER BY a.sal DESC

---

Is it possible to dump/examine the exact content of a database column?

  SELECT ename, job, dump(job)
  FROM   emp
  WHERE  ename = 'SMITH';


ENAME      JOB        DUMP(JOB)
---------- ---------  ---------------------------
SMITH      CLERK      Typ=1 Len=5: 67,76,69,82,75

Type Code	Oracle Datatype
1	VARCHAR2
2	NUMBER
12	DATE
96	CHAR

Oracle Type Codes for Column Dumps

---

Can I code a MATRIX Report using SQL?

  SELECT *
  FROM ( SELECT    job
                 , sum(decode(deptno, 10, sal)) DEPT10
                 , sum(decode(deptno, 20, sal)) DEPT20
                 , sum(decode(deptno, 30, sal)) DEPT30
                 , sum(decode(deptno, 40, sal)) DEPT40 
         FROM      emp e
         GROUP BY  job
  ) 
  ORDER BY 1;


  JOB           DEPT10     DEPT20     DEPT30     DEPT40
  --------- ---------- ---------- ---------- ----------
  ANALYST                    6000
  CLERK           1300       1900        950
  MANAGER         2450       2975       2850
  PRESIDENT       5000
  SALESMAN                              5600

---

Tree-Structured (Hierarchical) Queries

  SELECT  lpad(' ', level*4) || ename
  FROM    emp
  CONNECT BY PRIOR empno = mgr
  START WITH mgr IS NULL;


  Employee Name
  ------------------------
  KING
      JONES
          JHUNTER
              ADAMS
          FORD
              SMITH
      BLAKE
          ALLEN
          WARD
          MARTIN
          TURNER
          JAMES
      CLARK
          MILLER

NOTE: Using the "EMP" table from the SCOTT/TIGER schema makes a great test table since it has a "self-referencing" relation. (The MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree you are. Oracle can handle queries with a depth up to 255 levels.
The "START WITH" clause is used to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "CONNECT BY PRIOR" clause is that you cannot perform a JOIN to other tables.

---

Insert Large Number of Sequence Values using UNION

Here is an example SQL script that will insert 1,000,000 sequence values into a table using a series of UNION's and a cartesian product.

DROP TABLE sequence_values
/

CREATE TABLE sequence_values (id NUMBER)
/

INSERT INTO sequence_values
    SELECT
        millions.x           +
        hundred_thousands.x  +
        ten_thousands.x      +
        thousands.x          +
        hundreds.x           +
        tens.x               +
        ones.x
    FROM
        (SELECT 0 x FROM dual
         UNION SELECT 1 FROM dual
         UNION SELECT 2 FROM dual
         UNION SELECT 3 FROM dual
         UNION SELECT 4 FROM dual
         UNION SELECT 5 FROM dual
         UNION SELECT 6 FROM dual
         UNION SELECT 7 FROM dual
         UNION SELECT 8 FROM dual
         UNION SELECT 9 FROM dual) ones,
        (SELECT 0 x FROM dual
         UNION SELECT 10 FROM dual
         UNION SELECT 20 FROM dual
         UNION SELECT 30 FROM dual
         UNION SELECT 40 FROM dual
         UNION SELECT 50 FROM dual
         UNION SELECT 60 FROM dual
         UNION SELECT 70 FROM dual
         UNION SELECT 80 FROM dual
         UNION SELECT 90 FROM dual) tens,
        (SELECT 0 x FROM dual
         UNION SELECT 100 FROM dual
         UNION SELECT 200 FROM dual
         UNION SELECT 300 FROM dual
         UNION SELECT 400 FROM dual
         UNION SELECT 500 FROM dual
         UNION SELECT 600 FROM dual
         UNION SELECT 700 FROM dual
         UNION SELECT 800 FROM dual
         UNION SELECT 900 FROM dual) hundreds,
        (SELECT 0 x FROM dual
         UNION SELECT 1000 FROM dual
         UNION SELECT 2000 FROM dual
         UNION SELECT 3000 FROM dual
         UNION SELECT 4000 FROM dual
         UNION SELECT 5000 FROM dual
         UNION SELECT 6000 FROM dual
         UNION SELECT 7000 FROM dual
         UNION SELECT 8000 FROM dual
         UNION SELECT 9000 FROM dual) thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 10000 FROM dual
         UNION SELECT 20000 FROM dual
         UNION SELECT 30000 FROM dual
         UNION SELECT 40000 FROM dual
         UNION SELECT 50000 FROM dual
         UNION SELECT 60000 FROM dual
         UNION SELECT 70000 FROM dual
         UNION SELECT 80000 FROM dual
         UNION SELECT 90000 FROM dual) ten_thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 100000 FROM dual
         UNION SELECT 200000 FROM dual
         UNION SELECT 300000 FROM dual
         UNION SELECT 400000 FROM dual
         UNION SELECT 500000 FROM dual
         UNION SELECT 600000 FROM dual
         UNION SELECT 700000 FROM dual
         UNION SELECT 800000 FROM dual
         UNION SELECT 900000 FROM dual) hundred_thousands,
        (SELECT 0 x FROM dual
         UNION SELECT 1000000 FROM dual
         UNION SELECT 2000000 FROM dual
         UNION SELECT 3000000 FROM dual
         UNION SELECT 4000000 FROM dual
         UNION SELECT 5000000 FROM dual
         UNION SELECT 6000000 FROM dual
         UNION SELECT 7000000 FROM dual
         UNION SELECT 8000000 FROM dual
         UNION SELECT 9000000 FROM dual) millions
/


Table dropped.


Table created.


10000000 rows created.

Using Analytic Functions in Oracle9i

Overview

Oracle has made analytic functions available starting with Oracle 8i (8.1.6). This article documents the usage and syntax surrounding the new analytic functions found in Oracle9i. Analytic functions are designed to address problems like:

• Calculate a running total
• Find percentages within a group
• Top-N queries
• Compute moving averages
• and so many more...

Although most of these types of problems can be solved in other programming languages like PL/SQL and Java, the performance is often not very effiecient. Analytic functions add extensions to the SQL language to not only make these operations easier to code; they make them faster than could be achieved with pure SQL or PL/SQL. These extensions are currently under review by the ANSI SQL committee for inclusion in the SQL specification.
Analytic functions enable you to compute aggregate values for a specific group of rows. Groups are formed using the new WITHIN GROUP clause. Enhancements related to analytic functions new to Oracle9i are explained below. In this section of the document, all examples use the all famous EMP / DEPT tables. I included the DDL script (scott.sql) to create these tables in the SQL Scripts repository.
As mentioned above, analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by an analytic clause. For each row, a sliding window of rows is defined. This window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed BEFORE the analytic functions are processed. Therefore, analytic functions can appear only in the SELECT list or ORDER BY clause.
The Syntax

Analytic-Function(,,...)
OVER (
  
  
  
)

• Analytic-Function Specify the name of an analytic function. Oracle provides many analytic functions such as:
  
  • AVG
  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • COUNT
  • CUME_DIST
  • DENSE_RANK
  • FIRST
  • FIRST_VALUE
  • LAG
  • LAST
  • LAST_VALUE
  • LEAD
  • MAX
  • MIN
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • RATIO_TO_REPORT
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE
• Arguments Analytic functions take 0 to 3 arguments.
  
• Query-Partition-Clause The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "PARTITION" and "GROUP" are used synonymously here. The analytic functions are applied to each group independently, they are reset for each group.
  
• Order-By-Clause The ORDER BY clause specifies how the data is sorted within each group (PARTITION). This will definitely affect the outcome of any analytic function.
  
• Windowing-Clause The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group.

---

Distribution Functions

The first analytical function to look at is the cumulative distribution function (CUME_DIST). Cumulative distribution is an analytic function that computes the relative position of a specified value in a group of values. For a row R, assuming ascending ordering, the CUME_DIST of R is the number of rows with values lower than or equal to the value of R divided by the number of rows being evaluated (the entire query result set or a partition). The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value. You can use CUME_DIST as an aggregate function or as an analytic function. When you use CUME_DIST as an aggregate function, it determines the relative position of an apparent row of information within an existing group of rows. CUME_DIST as an analytic function determines the relative position of a specific value within a group of values.
The following example calculates the salary percentile for each employee within each job category excluding job categories PRESIDENT and MANAGER. In the examle below, 50% of CLERKS have salaries less than or equal to JAMES.

SQL> SELECT job, ename, sal, CUME_DIST()
     OVER (PARTITION BY job ORDER BY sal) AS cume_dist
     FROM emp
     WHERE job NOT IN ('MANAGER', 'PRESIDENT'); 


JOB       ENAME             SAL  CUME_DIST
--------- ---------- ---------- ----------
ANALYST   SCOTT            3000          1
ANALYST   FORD             3000          1
CLERK     SMITH             800        .25
CLERK     JAMES             950         .5
CLERK     ADAMS            1100        .75
CLERK     MILLER           1300          1
SALESMAN  WARD             1250         .5
SALESMAN  MARTIN           1250         .5
SALESMAN  TURNER           1500        .75
SALESMAN  ALLEN            1600          1

10 rows selected.

---

Inverse Distribution Functions

Oracle9i introduces the PERCENTILE_CONT and PERCENTILE_DIST which are both inverse distribution functions. The PERCENTILE_CONT function is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.
The PERCENTILE_DISC function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.
Both functions, given a percentile and a sequence, will determine the interpolated value at that percentile. In both functions, interpolation ignores NULL values. All interpolated values will be between 0 and 1.
Aggregate Example
The following example computes the median salary in each department:

SQL> SELECT deptno,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC)
          "Median cont"
      , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal DESC)
          "Median disc"
    FROM emp
        GROUP BY deptno;


    DEPTNO Median cont Median disc
---------- ----------- -----------
        10        2450        2450
        20        2975        2975
        30        1375        1500

Analytic Example - (PERCENTILE_CONT)
In the following example, the median for Department 30 is 1375, which has a corresponding percentile (Percent_Rank) of 0.5. None of the salaries in Department 30 have a percentile of 0.5, so the median value must be interpolated between 2400 (percentile 0.4) and 2500 (percentile 0.6), which evaluates to 2450.

SQL> SELECT
         ename
       , sal
       , deptno
       , PERCENTILE_CONT(0.5)
             WITHIN GROUP (ORDER BY sal DESC)
             OVER (PARTITION BY deptno) "Percentile_Cont"
       , PERCENT_RANK()
             OVER (PARTITION BY deptno 
                   ORDER BY sal DESC)   "Percent_Rank"
      FROM emp WHERE deptno IN (10, 30);


ENAME             SAL     DEPTNO Percentile_Cont Percent_Rank
---------- ---------- ---------- --------------- ------------
KING             5000         10            2450            0
CLARK            2450         10            2450           .5
MILLER           1300         10            2450            1
BLAKE            2850         30            1375            0
ALLEN            1600         30            1375           .2
TURNER           1500         30            1375           .4
WARD             1250         30            1375           .6
MARTIN           1250         30            1375           .6
JAMES             950         30            1375            1

9 rows selected.

Analytic Example - (PERCENTILE_DISC)
The following example calculates the median discrete percentile of the salary of each employee in the sample table emp.
The median value for Department 10 is 2450, which is the value whose corresponding percentile (Cume_Dist) is the smallest value greater than or equal to 0.5. The median value for Department 30 is 1500, which is the value whose corresponding percentile is the smallest value greater than or equal to 0.5.

SQL> SELECT
         ename
       , sal
       , deptno
       , PERCENTILE_DISC(0.5)
             WITHIN GROUP (ORDER BY sal DESC)
             OVER (PARTITION BY deptno) "Percentile_Disc"
       , CUME_DIST()
             OVER (PARTITION BY deptno 
                   ORDER BY sal DESC) "Cume_Dist"
     FROM emp where deptno in (10, 30);


ENAME             SAL     DEPTNO Percentile_Disc  Cume_Dist
---------- ---------- ---------- --------------- ----------
KING             5000         10            2450 .333333333
CLARK            2450         10            2450 .666666667
MILLER           1300         10            2450          1
BLAKE            2850         30            1500 .166666667
ALLEN            1600         30            1500 .333333333
TURNER           1500         30            1500         .5
WARD             1250         30            1500 .833333333
MARTIN           1250         30            1500 .833333333
JAMES             950         30            1500          1

9 rows selected.

---

Top-N Queries

Using Top-N Queries, you are able to get the Top-N records by some set of fields. Let's take a look at several examples.
Example 1
This example will sort the sales people by salary from greatest to least. Give the first three rows. If there are less then three people in a department, this will return less than three records.

set autotrace on explain
break on deptno skip 1

SELECT *
FROM (
  SELECT
      deptno
    , ename
    , sal
    , ROW_NUMBER()
  OVER (
    PARTITION BY deptno ORDER BY sal DESC
  ) Top3 FROM emp
)
WHERE Top3 <= 3;


    DEPTNO ENAME             SAL       TOP3
---------- ---------- ---------- ----------
        10 KING             5000          1
           CLARK            2450          2
           MILLER           1300          3

        20 SCOTT            3000          1
           FORD             3000          2
           JONES            2975          3

        30 BLAKE            2850          1
           ALLEN            1600          2
           TURNER           1500          3

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     WINDOW (SORT PUSHED RANK)
   3    2       TABLE ACCESS (FULL) OF 'EMP'

This query works by sorting each partition (or group, which is the deptno), in a descending order, based on the salary column and then assigning a sequential row number to each row in the group as it is processed. The use of a WHERE clause after doing this to get just the first three rows in each partition. Example 2
In this example, I want to see the set of sales people who make the top 3 salaries - that is, find the set of distinct salary amounts, sort them, take the largest three, and list everyone who makes one of those values.

SELECT *
FROM (
  SELECT
      deptno
    , ename
    , sal
    ,DENSE_RANK()
  OVER (
    PARTITION BY deptno ORDER BY sal desc
  ) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC;

    DEPTNO ENAME             SAL       TOPN
---------- ---------- ---------- ----------
        10 KING             5000          1
           CLARK            2450          2
           MILLER           1300          3

        20 SCOTT            3000          1  <--- --------------------------------------------="" 0="" 10="" 1100="" 1500="" 1600="" 1="" 2850="" 2975="" 2="" 3000="" 30="" 3="" access="" adams="" allen="" blake="" execution="" ford="" jones="" of="" optimizer="CHOOSE" plan="" pre="" pushed="" rank="" rows="" select="" selected.="" statement="" table="" turner="" view="" window="">


    In the example above, the DENSE_RANK function was used to get 
    the top three salaries. We assigned the dense rank to the salary 
    column and sorted it in a descending order.
    
    The DENSE_RANK() function computes the rank of a row in an ordered 
    group of rows. The ranks are consecutive integers beginning with 1. 
    The largest rank value is the number of unique values returned by the 
    query. Rank values are not skipped in the event of ties. Rows with equal 
    values for the ranking criteria receive the same rank.
    


    The DENSE_RANK function does not skip numbers and will assign the same 
    number to those rows with the same value. Hence, after the result set is 
    built in the inline view, we can simply select all of the rows with a dense 
    rank of three or less, this gives us everyone who makes the top three 
    salaries by department number.
  

Scalar Subqueries in Oracle

Scalar Subqueries

Introduced in Oracle9i, scalar subqueries allow you to treat the output of a subquery as a column or even an expression within a SELECT statement. It is a query that only selects one column or expression and returns just one row. If the scalar subquery fails to return select any rows, Oracle will use a NULL value for the output of the scalar subquery. Before Oracle9i, it was possible to use the scalar subquery within the SET clause of the UPDATE statement and within the VALUES list of the INSERT INTO statement. This ability provides greater flexibility to use the scalar subquery just about anywhere you can use an expression. Sometimes it is easier to list the places you CANNOT use a scalar subquery:

• For default column values
• As the basis for a function-based index
• As a hash expression for a cluster
• In CHECK constraints for DDL statements
• In WHEN conditions for CASE statements
• In GROUP BY and HAVING clauses for SQL queries
• In CONNECT BY and START WITH clauses for SQL queries

The following is an example of using the scalar subquery to determine how many rows in the DEPARTMENT table contain an employee corresponding to each row in the EMPLOYEE table:

SQL> SELECT
  2      d.dept_id
  3    , d.dept_name
  4    , (SELECT count(*) FROM employee e 
  5       WHERE e.dept_id = d.dept_id) as "How Many Departments"
  6  FROM department d;


   DEPT_ID DEPT_NAME         How Many Departments
---------- --------------- ----------------------
       100 Finance                              1
       101 Engineering                          2
       103 HR                                   1

Using CASE Statements in Oracle

Introduction

With the introduction of Oracle9i, and Oracle's support for SQL:99, you can now perform If ... Then ... Else logic within SQL statements without having to use programming logic. (i.e. Java, PL/SQL). SQL:99 provides four types of CASE statements, all of which are described in this document.

---

The Simple CASE expression

The simple CASE expression was first introduced and available in 8.1.7 SQL, but NOT in PL/SQL. This type of expression is very similar to the DECODE statement (value and substitution expression pairs). The simple CASE expression enables users to specify a number of WHEN ... THERE ... pairs. The following is the general format used:

CASE expression
    WHEN comparison_expression1 THEN use_expression1
    WHEN comparison_expression2 THEN use_expression2
    WHEN comparison_expression3 THEN use_expression3
    ELSE default_use_expression
END

In the above statement, for each WHEN clause, Oracle will perform the use_expression if the CASE expression if equal to the comparison_expression. Only one use_expression will be executed; it will either be the first one associated with a true comparison_expression or it will be the default_use_expression. Keep in mind that you cannot use the NULL literal value to specify the use_expression or a default_use_expression. It is also possible to specify an ELSE clause, which is a "catchall" clause if none of the WHEN ... conditions are met and would therefore run the default_use_expression specified. If none of the comparison_expression1 are met and there is no ELSE clause specified, Oracle will return a NULL value.
Each CASE statement can have up to 255 arguments, and each WHEN ... THEN ... pair counts as 2 arguments. With this limit, you can have 127 WHEN ... THEN ... pairs and one ELSE expression in one CASE expression. CASE expressions can be nested as well.

SELECT
    emp_name AS "Employee Name"
  , (CASE dept_id
       WHEN 100 THEN 'Finance'
       WHEN 101 THEN 'Engineering'
       WHEN 103 THEN 'HR'
     ELSE
       'Not Assigned'
     END) AS "Department"
FROM employee;

Employee Name      Department
------------------ ------------
John Doe           Finance
Sarah Smith        Engineering
Jane Snow          Engineering
James Williams     HR

---

The Searched CASE expression

The next type of CASE expression is the searched CASE expression. The general format of the searched CASE expression is very similar to that of the simple CASE expression. The difference though, is that in a searched CASE expression, you specify a condition or predicate instead of a comparison_expression after the WHEN keyword. A predicate is the criteria that you use to specify in the WHERE clause of an SQL statement. Only one use_expression will be executed; it will either be the first one associated with a true condition or it will be the default_use_expression. It is important to note that even though more than one condition in the searched CASE expression may be true, Oracle stops at the first TRUE condition and performs that use_expression.
Here is the general format followed by a simple example of using the searched CASE expression:

CASE
    WHEN condition1 THEN use_expression1
    WHEN condition2 THEN use_expression2
    WHEN condition3 THEN use_expression3
    ELSE default_use_expression
END

SELECT
    emp_name AS "Employee Name"
  , (CASE
       WHEN UPPER(emp_name) LIKE 'JANE%' THEN 'This is Jane'
       WHEN emp_id > 102 THEN 'Out of range'
     ELSE
       'Not Assigned'
     END) AS "Department"
FROM employee;


Employee Name             Department
------------------------- ------------
John Doe                  Out of range
Sarah Smith               Out of range
Jane Snow                 This is Jane
James Williams            Out of range

---

The NULLIF Function
The general format for the NULLIF function is:

NULLIF(expression1, expression2)

If expression1 is equal to expression2, Oracle will return a NULL value; otherwise, it returns expression1. The SQL:99 syntax does not allow you to specify the NULL literal value for expression1. Keep in mind that you can use the following searched CASE expression to perform the same task. Oracle will return a NULL value when nothing is met in the CASE statement:

CASE
    WHEN expression1 != expression2 THEN expression1
END

ANYDATA Type in Oracle

With the introduction of Oracle9i, developers can now take advantage of three new special SQL datatypes that enable applications to store generic attributes - attributes for which you do not know what the datatypes are until you actually execute the code. Here is a brief description of these new SQL Datatypes:

• SYS.ANYTYPE

  A type description type. A SYS.ANYTYPE can contain a type description of any SQL type, named or unnamed, including object types and collection types. An ANYTYPE can contain a type description of a persistent type, but an ANYTYPE itself is transient: in other words, the value in an ANYTYPE itself is not automatically stored in the database. To create a persistent type, use a CREATE TYPE statement from SQL.

• SYS.ANYDATA

  A self-describing data instance type. A SYS.ANYDATA contains an instance of a given type, with data, plus a description of the type. In this sense, a SYS.ANYDATA is self-describing. An ANYDATA can be persistently stored in the database.

• SYS.ANYDATASET

  A self-describing data set type. A SYS.ANYDATASET type contains a description of a given type plus a set of data instances of that type. An ANYDATASET can be persistently stored in the database.

In the past, developers would have simply stored their generic data in a VARCHAR2 - dates, numbers, everything. Now, with the introduction of ANYDATA, developers can put in a date and it stays a date. The system will enforce that it is, in fact, a valid date and allow you to perform date operations on it.
Using the previous scenario where the developer decides to store the same date in the VARCHAR2, there is no way to stop another developer from storing the string "Linux" in your "date" field.

---

A Simple Example

The following is a simple example of how to get started in using the SYS.ANYDATA type:

  create table test_any(id number, value sys.anydata);

  SQL> desc test_any 
  Name              Null?    Type 
  ----------------- ----- ---------------------------- 
  ID                      NUMBER 
  VALUE                   ANYDATA

  INSERT INTO test_any VALUES(1, sys.anydata.convertNumber(5));
  INSERT INTO test_any VALUES(2, sys.anydata.convertVarchar2('Oracle / Linux Application'));
  INSERT INTO test_any VALUES(3, sys.anydata.convertDate('14-FEB-1994'));

There are many SYS.ANYDATA type has many methods, one of which is getTypeName. This method provides the ability to see what kind of data is actually being stored in the SYS.ANYDATA type:

  SELECT  
      id Id
    , t.value.getTypeName() TypeName
  FROM test_any t;

     ID  TYPENAME
  -----  ------------
      2  SYS.NUMBER
      3  SYS.VARCHAR2
      4  SYS.DATE

Using SQL*Plus to display the contents of ANYDATA in a query is not so easy. The following PL/SQL function (getData) rectifies this problem and also gives you an insight into several more methods provided with the SYS.ANYDATA type.

  CREATE OR REPLACE FUNCTION getData(data IN SYS.ANYDATA)
    RETURN VARCHAR2
    AS
      l_varchar2   VARCHAR2(4000);
      l_rc         NUMBER;
    BEGIN

      CASE data.getTypeName
        when 'SYS.NUMBER' then
          l_rc := data.getNumber(l_varchar2);
        when 'SYS.DATE' then
          l_rc := data.getDate(l_varchar2);
        when 'SYS.VARCHAR2' then
          l_rc := data.getVarchar2(l_varchar2);
        else
          l_varchar2 := '** unknown **';
      END CASE;

      RETURN l_varchar2;

    END;
  /

---

  SELECT id, getData(value) GETDATA FROM   test_any;

       ID  GETDATA
  -------  -----------------------------------------------------
        2  5
        3  Testing Application
        4  14-FEB-94

Convert Numbers to Words

I guess a mundane exercise that most programmers cop at some stage is having to convert a number (123) into a text equivalent (ONE HUNDRED AND TWENTY THREE) - for cheques, group certificates, etc. Probably the code involved a loop stripping out the numerals and then applying a value according to the relative position within the overall value.
Although it winds a strange path via date functions, SQL*Plus actually provides a mechanism for automating much of this process. Executing the following :

    SELECT TO_CHAR ( TO_DATE ( TO_CHAR ( 103465, '99999999999') , 'J'),  'JSP') FROM dual;

returns a value of ONE HUNDRED THREE THOUSAND FOUR HUNDRED SIXTY-FIVE
If we break the statement into each component function, then what happens is :

• the inner TO_CHAR simply converts the number (which would probably be a numeric variable in practice) to CHAR so some magic can happen ...
• the TO_DATE converts the CHAR using the J (Julian day) format. (the Julian day is the number of days since January 1, 4712BC, which is when SQL*Plus was invented),
• having established the date value, we then convert that date back to a Julian day. Because the TO_CHAR in this case is used in DATE context, we can use the J mask to duplicate the original value, and append the SP (spell) format mask. 'Spell" does exactly that - it converts the number to words, hence the string value above.

SP can be used in a number of situations. For example, if SYSDATE is 26-AUG-98, then :

    SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual;    -- spells the day as Twenty-Six, 

and

    SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual;  --returns TWENTY-SIXTH

Some simple manipulations can be included with the base conversion to cover floating numbers or currencies (email brianm@lt.com.au for source), eg. 103465.27 becomes ONE HUNDRED AND THREE THOUSAND FOUR HUNDRED AND SIXTY-FIVE DOLLARS AND TWENTY-SEVEN CENTS.
One covenant however : if in your mad appreciation of this trivia you want to send me a cheque for more than $5,373,484.00, then you'll have to write it manually, or send more than one cheque!
SQL*Plus restricts Julian days to between 1 and 5373484, which won't be a problem for most applications, but should be borne in mind before using the technique in anger.
5373484 represents 31-Dec-9999, so this may be Oracle's way of introducing us to a Year 10K problem!

Display single column wise record for multiple row wise records

select grantee,table_name,
NVL (MAX (CASE privilege when ‘INSERT’ then ‘I’ else null end ),’ ‘) as “Insert”,
NVL (MAX (CASE privilege when ‘UPDATE’ then ‘U’ else null end ),’ ‘) as “Update”,
NVL (MAX (CASE privilege when ‘DELETE’ then ‘D’ else null end ),’ ‘) as “Delete”,
NVL (MAX (CASE privilege when ‘SELECT’ then ‘S’ else null end ),’ ‘) as “Select”,
NVL (MAX (CASE privilege when ‘EXECUTE’ then ‘E’ else null end ),’ ‘) as “Execute”,
NVL (MAX (CASE privilege when ‘ALTER’ then ‘A’ else null end ),’ ‘) as “Alter”
from DBA_TAB_PRIVS
where grantee  in (‘ALEYA’,'JAHANGIR’,'LUCKY’,'MAHAMUD’,'MOHI’,'MOJLISH’,'MOKTER’,'RAKIB’,'SAIFUL’,'SHARIFUL’,'TAPAN’,'JASIM’,'KAWSIK’,'ALEYA’,
‘ATMUTL’,'BACH’,'BACHINT’,'FIUINT’,'ISLBAS’,'ISLIMG’,'ISLITS’,'ISLSYS’,'MYBANK’,'ORBBBR’,'ORBITS’,'SMSGTWAY’,'STFOREX’,'STLBAS’,'STLIMG’,'STLSYS’,'WEBADMIN’,
‘BACHINT’,'ISLBAS’,'ISLIMG’,'ISLSYS’,'MYBANK’,'ORBBBR’,'SMSGTWAY’,'STLBAS’,'STLIMG’,'STLSYS’,'MAMUN_ERA’)
group by grantee,table_name
order by grantee