Useful functions in Oracle 11g Part01

Introduction

In this article, i have given example four useful function in oracle

• Simulate Lead Analytical Function (Manual Lead)
• PIVOT data (rows to columns)
• Comma Separated String To Rows 10g Onwards
• Comma Separated String To Rows 11g

Simulate Lead Analytical Function (Manual Lead)

http://nimishgarg.blogspot.com/2010/06/oracle-simulate-lead-analytical.html

ORIGINAL QUERY

select 
    deptno, empno, ename, 
    lead(ename) over(partition by deptno order by ename) lead_emp 
from scott.emp 
order by deptno, ename

SIMULATED QUERY

select deptno, empno, ename,(select ename from(select ename, deptno, rownum rn from(select empno, ename, deptno from scott.emp order by deptno, ename)) a where a.rn = emp.rn + 1 and a.deptno=emp.deptno
) lead_emp
from (select empno, ename, deptno, rownum rn from (select * from scott.emp order by deptno, ename)) emp

PIVOT data (rows to columns)

http://nimishgarg.blogspot.com/2010/06/oracle-pivot-data-rows-to-columns.html

ON ORACLE 11g

select * from(select 
        sal,deptno,jobfromscott.emp
)pivot
(sum(sal) for deptno in (10,20,30,40))

 BEFORE ORACLE 11g

select*from(select job,sum(decode(deptno,10,sal)) dept10,sum(decode(deptno,20,sal)) dept20,sum(decode(deptno,30,sal)) dept30,sum(decode(deptno,40,sal)) dept40from scott.empgroup by job)order by 1;

 Output 

Comma Separated String To Rows 10g Onwards

http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

with mytable as(select 'nimish' col,'1,2,3,4,5' txt from dualunionselect 'garg' col,'6,7,8,9' txt from dual
)select     
    mytable.col,   
    regexp_substr ( mytable.txt, '[^,]+', 1, n) as num 
from   
    mytable,(selectlevel nfrom(selectmax ( length (txt) - length (replace (txt, ',')))as max_commasfrom   
                    mytable)connect by level <= 1 + max_commas) ctr
wherectr.n<= 1 + length (txt) - length (replace (txt, ','))order by 
    mytable.col,ctr.n

Output 

Comma Separated String To Rows 11g

http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

FOR NUMBERS

with mytable as(select 'smith' ename,'1,2,3,4,5' mytext from dualunionselect 'ward' ename,'2,4,6,7' mytext from dual
)select ename,(column_value).getnumberval() 
from mytable,  xmltable(mytext)

Output

 FOR STRINGS USE

with mytable as(select 'smith' ename,'"a","b","c","d","e"' mytext from dualunionselect 'ward' ename,'"b","d","f","g"' mytext from dual
)select ename,(column_value).getstringval() 
from mytable,  xmltable(mytext)

 That's  all, i hope this is help to all you, in next part i will add more functions tips.