Split values ;///; Pyramid

To split values

CREATE OR REPLACE Function Tokenizer(p_string In VarChar2,
p_separators In VarChar2)
Return varchar2_table Pipelined
Is
v_strs dbms_sql.varchar2s;
Begin
/*SELECT Tokenizer('1E030N,898989,6565,3535,67676',',') FROM DUAL;*/
With sel_string As (Select p_string fullstring From dual)
Select substr(fullstring, beg+1, end_p-beg-1) token
Bulk Collect Into v_strs
From (Select beg, Lead(beg) Over (Order By beg) end_p, fullstring
From (Select beg, fullstring
From (Select Level beg, fullstring
From sel_string
Connect By Level <= length(fullstring)
)
Where instr(p_separators,substr(fullstring,beg,1)) >0
Union All
Select 0, fullstring
From sel_string
Union All
Select length(fullstring)+1, fullstring
From sel_string)
)
Where end_p Is Not Null
And end_p > beg + 1;
For i In v_strs.first..v_strs.last Loop
PIPE ROW(v_strs(i));
End Loop;
RETURN;
End Tokenizer;

----------------------------------------------------------------
Check this query

select wmsys.wm_concat(case when Rn <=46 then Rn end) over(order by Rn) str
from (select RowNum as rn from all_catalog where RowNum <= 46);

Standard objects in Oracle ;///; Generate Series

To get the list of standard objects in Oracle

SQL> desc sys.standard;

-----------------------------------
To generate Series


CREATE OR REPLACE FUNCTION generate_series ( p_start number, p_end number, p_step number )
RETURN number
AS
BEGIN
/* select generate_series(10,10,9) from dual*/
DECLARE
v_i number;
begin
v_i := CASE WHEN p_start IS NULL THEN 1 ELSE p_start END;

DECLARE v_step number;
begin
v_step := CASE WHEN p_step IS NULL OR p_step = 0 THEN 1 ELSE p_step END;

DECLARE
v_terminating_value number;
begin
v_terminating_value := p_start + ABS( p_start- p_end) / ABS( v_step) * v_step;

-- Check for impossible combinations
IF NOT ( ( p_start > p_end AND SIGN( p_step) = 1 )
OR
( p_start < p_end AND SIGN( p_step) = -1 )) then

-- INSERT INTO Integers ( [IntValue] ) VALUES ( v_i )
IF ( v_i = v_terminating_value ) then

v_i := v_i + v_step;
end if;
end if;

RETURN v_i;
END;
END ;
END;
end ;

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

To get diff data 's of similar id in a row

-----------

create or replace type string_agg_type as object (
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;

end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

select deptno, stragg(ename)
from scott.emp
group by deptno
/

From 10g onwards No need for user defined function and type , We can use Oracle supplied function

select deptno, wmsys.wm_concat(ename)
from scott.emp group by deptno

In Oracle 10g  wm_concat is an oracle undocumnented function 
 
From 11g onwards , We can use Oracle supplied listlagg function .

select distinct listagg(orcl, ',')   within group(order by orcl) over(partition by 1) orcl  from
 ( select 'ORACLE9i' orcl from dual
 union
 select 'ORACLE10g' from dual
union select 'ORACLE11g' from dual )  c ;

Instead of wm_concat  in Oracle 10 g

SELECT DEPARTMENT_ID , RTRIM(XMLAGG(XMLELEMENT(E, FIRST_NAME || ',')).EXTRACT('//text()'), ',') ENAMES FROM EMPLOYEES GROUP BY DEPARTMENT_ID ;

 SELECT SUBSTR(SYS_CONNECT_BY_PATH(username, ';'), 2) csv FROM (SELECT username, ROW_NUMBER() OVER(ORDER BY username) rn, COUNT(*) OVER() cnt FROM all_users where rownum <= 3) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1;

To delete duplicate records

DELETE FROM INTERSECTION e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM INTERSECTION f
WHERE e.NUM = f.NUM AND E.NAME=F.NAME);

This query will not work for a version-enabled  view .

Audit feature in Oracle 10g

TO audit what users are doing in Database

Assuming that the "audtest" user is to be audited:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audtest BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,DELETE TABLE BY audtest BYACCESS;
AUDIT EXECUTE PROCEDURE BY audtest BY ACCESS;
-----------------------------------------
These options audit all DDL & DML issued by "audtest", along with some system events.

* DDL (CREATE, ALTER & DROP of objects)
* DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
* SYSTEM EVENTS (LOGON, LOGOFF etc.)

------------------------------------------------------------
select s.name,d.userid,d.userhost,d.terminal,d.obj$name,d.spare1,d.ntimestamp#
from sys.aud$ d,sys.audit_actions s
where d.obj$creator=user_name and s.action=d.action# ;

--------------------------
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
-----------------------------------------
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'