Calling PL/SQL Function from OAF Page

To call a stored pl/sql function from within controller or an application module, you need to follow this steps:

1- Create CallableStatement with “PL/SQL” block that containing stored function
2- Bind any (IN or OUT) parameters
3- Execute the statement
4- If you have an OUT parameter you need to retrieve it’s values to variable
5- Close opened statement

OAApplicationModule oaapplicationmodule1 = pageContext.getApplicationModule(webBean);
OADBTransaction oadbtransaction1 = oaapplicationmodule1.getOADBTransaction();
String cntAsetNumPerAsetKyFC = 
          "BEGIN :1 := hr_general.decode_location (:2); END;";
OracleCallableStatement oracleCallableStmt = 
(OracleCallableStatement)oadbtransaction1.createCallableStatement(cntAsetNumPerAsetKyFC, oadbtransaction1.DEFAULT);
//Register your function output...
oracleCallableStmt.registerOutParameter(1, Types.VARCHAR, 0, 2);
//Your input parameter below...
oracleCallableStmt.setString(2, 1245);
//execute the function...
oracleCallableStmt.execute();
//get the value that function return...
String locationName = oracleCallableStmt.getString(1);
//close callabe statement...
oracleCallableStmt.close();