Creating Object type and usage : Example-I

You  can create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods that implement the type.

bkd@Oracle>CREATE OR REPLACE TYPE Studentobj as object (
  2  id number,
  3  dob date,
  4  member function getage return number);
  5  /

Type created.

If the a type contains member functions/procedures, the the procedural work needs to be defined in the TYPE BODY(similar to create specification and package BODY)

bkd@Oracle>ed
Wrote file afiedt.buf

  1  create or replace type body studentobj as
  2     member function getage return number as /* subprogram associated with the object type that is referenced as an attribute*/
  3     begin
  4     return trunc(months_between(sysdate , dob)/12);
  5     end;
  6* end;
bkd@Oracle>/

Type body created.

bkd@Oracle>create table student
  2  ( roll number,
  3    studentinf studentobj);

Table created.

bkd@Oracle>insert into student values( 1, studentobj( 123,sysdate-365*5));

1 row created.

bkd@Oracle>select p.id , 

                              p.studentinf.roll, 

                              p.studentinf.dob, 

                              p.studentinf.getage() /* method constructor  studentinf.getage()*/
                     from student p;

        ID STUDENTINF.ROLL STUDENTIN P.STUDENTINF.GETAGE()
---------- ------------- --------- ------------------------------------------------------------------
         1                      123       25-AUG-07                     4

Regards

BKD

Getting distinct values from a column of data type CLOB

-- What I have  learnt today:  
bkd@Oracle>drop table t;

Table dropped.

bkd@Oracle>create table t
  2  as
  3
bkd@Oracle>create table t (col clob);

Table created.

bkd@Oracle>insert into t
  2  (select 'A' from dual
  3  UNION ALL
  4  select 'B' from dual
  5  union all
  6  select 'C' from dual
  7  union all
  8  select 'A' from dual );

4 rows created.

bkd@Oracle>select col from t;

COL
--------------------------------------------------------------------------------
A
B
C
A

bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?

Workaround:

bkd@Oracle>select a.col from t a
  2  where not exists ( select 1 from t b
  3                     where a.rowid < b.rowid
  4                     and dbms_lob.compare(a.col,b.col)=0);

COL
--------------------------------------------------------------------------------
B
C
A

Regards
BKD

Analytical Function Example -II 

RANGE Windows

The general syntax of the <window_clause> is

[ROW or RANGE] BETWEEN <start_expr> AND <end_expr>

<start_expr> can be any one of the following

    UNBOUNDED PECEDING
    CURRENT ROW
    <sql_expr> PRECEDING or FOLLOWING.

    <end_expr> can be any one of the following
    UNBOUNDED FOLLOWING or
    CURRENT ROW or
    <sql_expr> PRECEDING or FOLLOWING.

For RANGE type windows the definition is in terms of values before or after the current ORDER. We will take this up in details latter.
The ROW or RANGE window cannot appear together in one OVER clause.


RANGE Windows

For RANGE windows the general syntax is same as that of ROW:

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]


SQL> select * from t100 order by deptid,sal;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         3       1000         10
         2       3000         10
         8       3000         20
         9       4000         20
         3       5000         20
         9       6000         20
         4       7000         20
         5       1000         30

9 rows selected.

SQL> select empid, deptid,
  2      sal,
  3      count(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) cnt1,
  4      sum(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) val1,
  5      count(sal) over ( partition by deptid order by sal range between 1 preceding and (sal/2) following ) cnt2,
  6      sum(sal) over ( partition by deptid order by sal range between  1 preceding and (sal/2) following ) val2
  7   from
  8   t100
  9  order by deptid
 10  /

     EMPID     DEPTID        SAL       CNT1       VAL1       CNT2       VAL2
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         10       1000          0                     2       2000
         3         10       1000          0                     2       2000
         2         10       3000          2       2000          1       3000
         8         20       3000          0                     2       7000
         9         20       4000          0                     3      15000
         3         20       5000          0                     3      18000
         9         20       6000          1       3000          2      13000
         4         20       7000          1       3000          1       7000
         5         30       1000          0                     1       1000

9 rows selected.


select  l,
    sum(l) over (  order by l range between 1 preceding and (l/2) preceding) val1,
    count(l) over (  order by l range between 1 preceding and (l/2) preceding) cnt1,
    sum(l) over (  order by l range between 1 preceding and (l/2) following) val2,
    count(l) over (  order by l range between 1 preceding and (l/2) following) cnt2,
    sum(l) over (  order by l range between unbounded preceding and (l/2) preceding) val3,
    count(l) over (  order by l range between unbounded preceding and (l/2) preceding) cnt3,
    sum(l) over (  order by l range between 1 preceding and (l/3) following) val4,
    count(l) over (  order by l range between 1 preceding and (l/2) following) cnt4,
    sum(l) over (  order by l range between 1 following and (l/3) following) val5,
    count(l) over (  order by l range between 1 following and (l/2) following) cnt5
from
g10
/
Output:

         L       VAL1       CNT1       VAL2       CNT2       VAL3       CNT3       VAL4       CNT4       VAL5       CNT5
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1                     0          1          1                     0          1          1                     0
         2          1          1          6          3          1          1          3          3                     1
         3                     0          9          3          1          1          9          3          4          1
         4                     0         18          4          3          2         12          4          5          2
         5                     0         22          4          3          2         15          4          6          2
         6                     0         35          5          6          3         26          5         15          3
         7                     0         40          5          6          3         30          5         17          3
         8                     0         34          4         10          4         34          4         19          2
         9                     0         27          3         10          4         27          3         10          1
        10                     0         19          2         15          5         19          2                     0

Regards,
BKD

Example: Analytical Function -I

SQL> ed
Wrote file afiedt.buf

  1  create table t100
  2  as(
  3  select 1 empid, 1000 sal, 10 deptid from dual
  4  union all
  5  select 2 empid, 3000 sal, 10 deptid from dual
  6  union all
  7  select 3 empid, 5000 sal, 20 deptid from dual
  8  union all
  9  select 4 empid, 7000 sal, 20 deptid from dual
 10  union all
 11  select 5 empid, 1000 sal, 30 deptid from dual
 12* )
SQL> /

Table created.

Elapsed: 00:00:00.68
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
SQL> insert into t100 values(3,1000,10);

1 row created.

Elapsed: 00:00:00.09
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
         3       1000         10

6 rows selected.

 select
   row_number() over (order by empid) sl,
   n.*  ,
   row_number() over ( partition by deptid order by  empid) rn,
   max(sal) over() maxsal,
   max(sal) over ( partition by deptid ) maxsal_dept,
   max(sal) keep(dense_rank last order by sal) over ( partition by deptid ) deptmaxsal,
   first_value(sal ignore nulls)  over ( partition by deptid order by sal desc) fvalue,
   last_value(sal ignore nulls)  over ( partition by deptid order by sal desc) lvalue,
   dense_rank()   over (  order by sal) assignedrank,
   rank() over(order by sal) rank,
   sum(sal) over (order by sal rows between current row and 1 following) sum_1,
   sum(sal) over (order by sal rows between 1 preceding and 1 following) sum_2,
   sum(sal) over (order by sal rows between 1 preceding and current row) sum_3
 from
 t100 n
/
  Useful links:
  http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
  http://psoug.org/reference/analytic_functions.html      
        

Reading Explain Plan  -  Example 1

SQL> ED

Wrote file afiedt.buf

  1  select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*)

  2  from employees

  3  where salary <3000

  4  and job_id='PU_CLERK'

  5* group by department_id

SQL> /

DEPARTMENT_ID   COUNT(*)

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

           30          4

SQL> SET LINESIZE 130

SQL> SELECT *

  2  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*) from

employees where salary <3000 and job_id='PU_CLERK' group by

department_id

Plan hash value: 1062631129

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  HASH GROUP BY               |            |      1 |     11 |      1 |00:00:00.01 |       2 |   888K|   888K|  393K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |      1 |   1920 |      4 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |      1 |  46729 |      5 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("SALARY"<3000)

   3 - access("JOB_ID"='PU_CLERK')

1. Operations 1 and 2 have a single child each (2 and 3, respectively);  the execution starts with operation 3 as child feeds parent ( of course there is some exception Rules) and starts with the lowest level
2. Operation 3, scans the index EMP_JOB_IX  by applying the access predicate
"JOB_ID"='PU_CLERK‘.
It found 5 rowids (see column A-Rows) from the index and passes them to its parent operation (2).
3. Operation 2 accesses the table employees through the 5 rowids passed from operation 3.
For each rowid, a row is read. Then, it applies the filter predicate "SALARY"<3000. This filter leads to the exclusion of one row. The data of the remaining 4 rows are passed to its parent operation (1).
4. Operation 1 performs a GROUP BY on the rows passed from operation 2. The resulting
set is reduced to 1. Since this is the last operation, the data is sent to the caller.

Regards
BKD

ED:  Formatted