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

Executing a PL SQL stored procedure from an OA Framework page is a very common requirement and that’s the reason why we will discuss this today. There are many ways to do this, you can use a callablestatment and execute a package or you can also create a concurrent program and then call that concurrent program from OA Framework page to execute the PL SQL stored procedure.
We will take a look at the first method here i.e. calling a PL SQL stored procedure using a callablestatment from OA Framework page.

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. Also I assume that the name of the AM is xxTrialAM.
Let’s do this in steps for easy understanding and remembrance.

notePlease note that all the lines that come in italics are the java code lines, this is done for easy segregation 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;

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();

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();END;”;
callablestatement=(OracleCallableStatement)dbtrans.createCallableStatement(query_String,0);

try
{
callablestatement.execute();
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 from an OA Framework page. In my next post we will take a look at how to execute a PL SQL procedure, which takes parameters and return values, from OA Framework page.
Till then, take care and thanks for reading 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *