Calling Pl/Sql Package,Function,sql Statement in Oaf Code either in AM or in CO

First import the following packages

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

Calling a Function:
 public void callConcProg(String StrParamString)

 {

String StrRequestId=null;

String programcall=null;

programcall="begin :1 := FND_REQUEST.SUBMIT_REQUEST("+StrParamString+"); end;";

            CallableStatement cs =  getOADBTransaction().createCallableStatement(programcall,-1); 

            try

            { 

            cs.registerOutParameter(1,Types.VARCHAR); 

           //Need to define what type of output is that valid types are Number, Varchar ,Date etc.

            cs.execute(); 

            getOADBTransaction().commit();

           //After execute we can get the value of output value using the following syntax. 

           StrRequestId = cs.getString(1); 

            cs.close(); 

            } 

            catch (SQLException sqle) 

            { 

            throw new OAException("Error in Calling",sqle.toString());

            }

            if(!StrRequestId.equals("0")) 

             {

              errMsg1.add(new OAException(StrRequestId+" request submitted successfully",

              OAException.CONFIRMATION));

             }

        }

Calling a pl/Sql Procedure:

 public void excuteUpload() 

 throws SQLException {  

 OADBTransaction transaction = getOADBTransaction(); 

CallableStatement cs=transaction.createCallableStatement

("begin PACKAGE.UPLOAD_FILE("+transaction.getUserId()+") ; end;", -1);

                     try 

                     {

                       cs.execute();

                       cs.close();

                     }

                     catch(Exception e) 

                     {

                     throw new OAException("Error in Api PACKAGE.UPLOAD_FILE"+e.toString());    

                     }

                     }

Calling a Sql Query;

public void getDBName ( ) 

{

OADBTransaction transaction =this.getOADBTransaction();  

String Query="SELECT NAME FROM V$DATABASE";

try{

PreparedStatement stmt = transaction.createPreparedStatement(Query,0);

for(ResultSet resultset = stmt.executeQuery(); resultset.next();)  

{  

StrDBName        = resultset.getString(1);

//here getString(1) refers first column if you have many u need to use

//getString(2),getString(3) like that and if you had other data types you can use

// getDate(),getNumber() etc.

}  

}  

catch(Exception e) 

{

throw new OAException("Error in Query:"+Query+e,OAException.ERROR);

}

}