How to execute a parameterized PL SQL stored procedure from an OA Framework page?

Today, we will discuss on how to call a PL SQL stored procedure, which takes in and returns parameters, from an OA Framework page controller.

Before starting the discussion, I assume that you have a valid PL SQL package created and compiled in the database, also I assume that the name of the PL SQL package is xx_trial_package and the name of the procedure is xx_trial_proceudure, and the procedure takes an integer (person_id as first parameter) and returns two strings (employee_number and full_name as second and third parameter) respectively. Also I assume that the name of the AM is xxTrialAM.

Let’s do this in steps for easy understanding and remembrance.
note Please note that all the lines that come in italics are the java code lines, this is done for easy segregating code from text in the post.

Step 1: First step is to import your application module xxTrialAM into the controller (say xxTrialPGController) and then import the following packages also into your controller.

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.jdbc.OracleCallableStatement;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

Step 2: Now, before the processrequest method in the controller create handles for xxTrialAM, OADBTransaction , OracleCallableStatement and also create a new variable of String type as shown below
private xxTrialAM am ;
public OADBTransaction dbtrans;
public OracleCallableStatement callablestatement
String query_String = new String();
String employee_number = new String();
String full_name = new String();

Step 3: In the processrequest method after the
super.processRequest(pageContext, webBean);
line write the following lines of code
am = (xxTrialAmImpl)pageContext.getApplicationModule(webBean);
dbtrans = am.getOADBTransaction();

Step 4: Now, as mentioned earlier the String type that we have created will come into picture.
Just write the code as
query_String = “BEGIN xx_trial_package.xx_trial_procedure(:1,:2,:3);END;”;
callablestatement=(OracleCallableStatement)dbtrans.createCallableStatement(query_String,0);
try
{
callablestatement.setInt(1,pageContext.getEmployeeId());

/*Here the setInt() method is used as the parameter that we have to pass to the procedure is of the type Integer. If the parameter is of String type then use setString method. If the parameter is of some other type then use the appropriate setter method. Also note that in the setInt() method in the example the first parameter is 1 which corresponds to the 1st parameter in the procedure.*/

callablestatement.registerOutParameter(2,OracleTypes.VARCHAR,0);
/*This line registers the parameters that will be given out by the procedure after successful execution. Note that in the registerOutParameter() method the number 2 corresponds to the 2nd parameter from the procedure.*/
callablestatement.registerOutParameter(3,OracleTypes.VARCHAR,0);
callablestatement.execute();
employee_number = callablestatement.getString(2);

/* in this line we are getting the value of the out parameter number 2 into a local variable.*/
full_name = callablestatement.getString(3);
callablestatement.close();
}
catch (SQLException e)
{
System.out.println(“The Error is ” + e);
}

And it’s done.
You have just now executed a PL SQL stored procedure, which takes parameters and return values, from OA Framework page controller.
Till my next post, take care and thanks for reading 🙂

Tagged , , . Bookmark the permalink.