Duplicate rows from Oracle table

Remove duplicated including rows having "null"values

DELETE
FROM table_name t1
WHERE t1.rowid > ANY
  (SELECT t2.rowid
  FROM table_name t2
  WHERE (t1.col1 = t2.col1
  OR (t1.col1   IS NULL
  AND t2.col1   IS NULL))
  AND (t1.col2   = t2.col2
  OR (t1.col2   IS NULL
  AND t2.col2   IS NULL))
  );

Find and delete duplicate rows

DELETE
FROM table_name t1
WHERE rowid <>
  (SELECT MAX(rowid)
  FROM table_name t2
  WHERE t1.col1 = t2.col1
  AND t1.col2   = t2.col2
  );

Open Source Rich Internet Applications (RIA)

sweetdev-ria : http://sweetdev-ria.sourceforge.net/

Rico 2.1 : http://dowdybrown.com/dbprod/

zkoss : http://www.zkoss.org/

Oracle: Function to concatenate column output

 Type:

create or replace
TYPE        "STRINGAGGTYPE"                                          as object
    (
       theString varchar2(4000),
   
       static function
            ODCIAggregateInitialize(sctx IN OUT StringAggType )
            return number,
   
       member function
           ODCIAggregateIterate(self IN OUT StringAggType ,
                                value IN varchar2 )
           return number,
  
      member function
           ODCIAggregateTerminate(self IN StringAggType,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
  
      member function
           ODCIAggregateMerge(self IN OUT StringAggType,
                              ctx2 IN StringAggType)
           return number
   );

Function:

create or replace
FUNCTION stringAgg(input varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING StringAggType;

Now, you can run a query like this:

select deptno, stringAgg(ename) enames from emp group by deptno;

Result:

DEPTNO  ENAMES
----------  ------------------------------
        10  CLARK,KING,MILLER
        20  SMITH,FORD,ADAMS,SCOTT,JONES
        30  ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Oracle: functions to join and split strings in sql

Join:

create or replace
function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;
 /

Now, you can run a query like this:

select join(cursor(select ename from emp))from dual;

Split:

create or replace TYPE "SPLIT_TBL" as table of varchar2(32767);
/

create or replace
FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list VARCHAR2(32767):= p_list;
  BEGIN
    LOOP
      L_IDX   :=INSTR(L_LIST,P_DEL);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list:= SUBSTR(l_list,l_idx  +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;
 /

Now, you can run a query like this:  

SQL> select * fromtable(split('one,two,three'));


one
two
three

Ref: create functions to join and split strings in sql