SQL functions


Decode function has the functionality of an IF-THEN-ELSE statement. Decode does a value-by-value substitution. For every value that is given in the DECODE function it makes if then check and matches the value.
Syntax-Decode (Value, if-search-1,result1,if-search-2,result2,if-search-3,result3,if-search-1,default-result)

select empname,Decode(empid,1001,20000,1002,12000,1003,15000,null) from emp;
This query is selecting the emp name and salary on basis of emp id. if emp id is 1001 then salary is 20000
and if emp id is 1002 then salart is 12000 and so on.


NVL function can substitute a value when null value is there.For ex-
select NVL(empname,vinay) from emp.
This query will select empname from table and if empname is null ,it will return you ‘VINAY’.


This function is extract a part of string from string.
Syntax for function is – Substr(String,starting_point,end_point)
Substr(‘Vinay is best’,5,3) – result (y i)
Substr(‘Vinay is best’,4,7) – result (ay is b)
Substr(‘Vinay is best’,7) – result (is best)
Substr(‘Vinay is best’,-7,5) – result (is be) when negative value is there,Starting point will start frm . end and then move backward
Substr(‘Vinay is best’,-4,3) – result (bes)

WildCard Search using Bind variable :-
Now i am showing the wild card functionality in select query using the bind variable.

Select empname and empsalary from emp where UPPER(nvl(employeeid,”)) like

This query is working on wild card search functionality. you can search data by using ‘*’ instead of ‘%’.I am using NVL,UPPER and DECODE function together to give you better understanding. This query will run as wild card search query. If user don’t give any empid, it will have a ” value through NVL function. In decode when empid is ” then it will return as ‘%’ that mean all record. If empid is not ” then value in bind variable surrounding by ‘%’ and replace function is replacing the ‘*’ to ‘%’.In this way, it is working like wild card search.If user enter *32* then any empid having 32 anywhere will returned.


This function will compare the expression. And if they are equal then it will return null. Otherwise it will
Return first value .
for example–
NULLIF(5,5) result would be — NULL
NULLIF(3,4) result would be — 3
NULLIF(6,3) result would be — 6

Partial Page Rendering (PPR) In Oracle ADF

PPR is Partial Page Rendering, similarly to Ajax. It is used to dynamically refresh the part of page.It is done with the use of partial triggers.

For better understanding , i will explain by an example.

In Any .jspx page we have one table (table1) and one command button(button1) label ‘Delete’. On clicking the Delete

button it will call the procedure and delete some records from table. After deleting the records the table should refresh. For this we can make the ‘AutoSumbit’ property of an Button to “TRUE” .Copy the Unique id of Command button i.e. Button1 and paste in the partial trigger property of the table.

Only table will refresh dynamically.

This is short example of PPR in ADF.

OAF versus ADF

OAF versus ADF

I have seen many forums asking about Difference between OAF and ADF. May be my post will clear some points on this

OAF – OAF is a framework for extending Oracle E-Business Suite. If you want to customize and Oracle EBS system you need to use OAF. OAF is a e-biz web development framework mainly for Oracle applications. If you want to develop new pages and extensions for Oracle applications 11i and R12, then use OA framework. OAF is based on MVC model while ADF has four layers (MVC+Business Services). OA framework is an Oracle Applications Specific framework. If you build for Oracle Applications – e.g. extending it – then it is good to use their framework because of the integration points to the existing application

ADF-ADF is a framework for developing custom applications .If you want to build a new system that has some level of integration with Oracle EBS you can do it with ADF and use SOA to talk to the back-end EBS system. Oracle ADF is at the heart of the Fusion technology stack. ADF  techstack is intended to utilize the benefits of Service Oriented Architecture (SOA) while building the Applications.ADF does not support oracle apps. It is used SOA related application. In ADF 11g apps will also be supported. ADF is based on JSR-227, which is supposed to be turned into a J2EE standard. we can say ADF as a next version of OAF with JSF – Java Server Faces, more J2EE architecture,
more dynamic page refreshing and handling etc.