Remove Duplicate values from array

Oracle Database Version 10g and up allows to eliminate duplicate values in array elements using MULTISET operation.
Below is the example
Script
Set serveroutput on ;

DECLARE
   TYPE T_Sampleid IS TABLE OF VARCHAR2 (20);

   arr_sampleid    t_sampleid := T_Sampleid ();
   arr_sampleid2   t_sampleid := T_Sampleid ();
BEGIN
   --Adding 6 samples...
   arr_sampleid.EXTEND;
   arr_sampleid (1) := 'sample G001';
   arr_sampleid.EXTEND;
   arr_sampleid (2) := 'sample G002';
   arr_sampleid.EXTEND;
   arr_sampleid (3) := 'sample G003';
   arr_sampleid.EXTEND;
   arr_sampleid (4) := 'sample G001'; -- duplicate
   arr_sampleid.EXTEND;
   arr_sampleid (5) := 'sample G002'; -- duplicate.
   arr_sampleid.EXTEND;
   arr_sampleid (6) := 'sample G004';

   -- Let's display values
   DBMS_OUTPUT.put_line ('Display all samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;

   -- same array into array 2
   arr_sampleid2 := arr_sampleid;

   -- Identify distinct and return to main arr_sampleid
   arr_sampleid := arr_sampleid MULTISET UNION DISTINCT arr_sampleid2;

   DBMS_OUTPUT.put_line ('Display DISTINCT samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;
END;
/
Output
processing sample: G11-A15214-02-TA0001 1 1
Display all samples...
sample G001
sample G002
sample G003
sample G001
sample G002
sample G004
Display DISTINCT samples...
sample G001
sample G002
sample G003
sample G004

As you can see in the output, Second set don't have duplicates.

Source: http://oraenablement.wordpress.com/2011/06/21/remove-duplicate-values-from-array/ 

Splitting a comma delimited string

Splitting a comma delimited string

SQL> with test as

  2  (

  3  select 1 id, 'joey,anthony,marvin' str from dual union all

  4  select 5 id, 'tony,glenn' str from dual union all

  5  select 8 id, 'john' str from dual

  6  )

  7  select id

  8       , str

  9       , regexp_substr (str, '[^,]+', 1, rn) split

 10    from test

 11    cross

 12    join (select rownum rn

 13            from (select max (length (regexp_replace (str, '[^,]+'))) + 1 mx

 14                    from test

 15                 )

 16         connect by level <= mx

 17         )

 18   where regexp_substr (str, '[^,]+', 1, rn) is not null

 19   order by id

 20  ;

        ID STR                 SPLIT

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

         1 joey,anthony,marvin joey

         1 joey,anthony,marvin marvin

         1 joey,anthony,marvin anthony

         5 tony,glenn          tony

         5 tony,glenn          glenn

         8 john                john

Source: http://nuijten.blogspot.in/2011/08/splitting-comma-delimited-string-regexp.html

 

Get number of records in all tables

if you're content with a possibly estimated count as of the last time statistics were gathered, you can do something like this:

SELECT    table_name
,         num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

If you want the exact number at run-time, then you need dynamic SQL.  Here's one way to do that:

SELECT    table_name
,         EXTRACTVALUE ( XMLTYPE ( dbms_xmlgen.getxml (  'SELECT  COUNT (*) AS num '
                                                      || 'FROM ' || owner || '.' || table_name
                                                      )
                                 )
                       , '/ROWSET/ROW/NUM'
                       )     AS num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

[   an XML document can look like this:

<ROWSET>
  <ROW num="1">
    <X>Y</X>
  </ROW>
</ROWSET>

or it can look like this:

<ROWSET><ROW num="1"><X>Y</X></ROW></ROWSET> ]

Either way, you get results like:

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                                    4
EMP                                    14
BONUS                                   0
SALGRADE                                5

select  table_name,
        xmlcast(xmlquery(('count(ora:view("' || owner || '","' || table_name || '"))') returning content) as int) cnt
  from  dba_tables
  where owner = 'HR'
  order by table_name
/

TABLE_NAME                            CNT
------------------------------ ----------
COUNTRIES                              25
DEPARTMENTS                            27
EMPLOYEES                             107
JOBS                                   19
JOB_HISTORY                            10
LOCATIONS                              23
REGIONS                                 4
7 rows selected.

SQL Model Clause


bkd@Oracle>select employee_id,
  2               first_name,
  3               salary
  4        from employees
  5        where department_id=20;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

Using Model Clause,
bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules()
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules(first_name[employee_id]='BKD')
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 BKD                       13000
        202 BKD                        6000

bkd@Oracle>---aah!!!!
bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD')
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[8888]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       8888                         1425000
       9999 BKD

Here, we can see that if employee id is already exists then it overwrites the row.     

--creating new row with rule
bkd@Oracle>ed
Wrote file afiedt.buf
  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[9999]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD                     1425000

Use 'return updated rows'  to return only the new and updated ones:

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     return updated rows
  8     dimension by (employee_id)
  9     measures( first_name, salary)
 10     rules(first_name[9999]='BKD', salary[9999]=1425000)
 11  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
       9999 BKD                     1425000

Now let's see what happened when we remove the filter on department and add the department_id to

measures.. 

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary,
  4     department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     dimension by (employee_id)
 10     measures( first_name, salary,department_id )
 11     rules(first_name[9999]='BKD', salary[9999]=1425000)
 12  order by department_id
 13  /

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000 

Using the department_id in partition by results...

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2          first_name,
  3          salary,
  4          department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     partition by (department_id)
 10     dimension by (employee_id)
 11     measures( first_name, salary )
 12     rules(first_name[9999]='BKD', salary[9999]=1425000)
 13* order by department_id
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000            10
       9999 BKD                     1425000            20
       9999 BKD                     1425000            30
       9999 BKD                     1425000            40
       9999 BKD                     1425000            50
       9999 BKD                     1425000            60
       9999 BKD                     1425000            70
       9999 BKD                     1425000            80
       9999 BKD                     1425000            90
       9999 BKD                     1425000           100
       9999 BKD                     1425000           110

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000

12 rows selected. 

Regards,

BKD

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