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.