Working with PL/SQL procedures in ADF BC Model 1

Working with PL/SQL procedures in ADF BC Model

I believe this is very common use case when we need to invoke some PL/SQL procedure in our Java code in ADF BC model. The standard approach for that is to use JDBC callable statement. For example, we have a PL/SQL procedure:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
aIntegerParam Number,
aDateParam Date,
aOutParam out Varchar2
)

And Java method invoking this procedure using JDBC callable statement is going to look like this:

public String execPLSQLProcedure(String aStringParam, Integer aIntParam,

Date aDateParam) {

DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();

CallableStatement statement =

dbti.createCallableStatement(("BEGIN "+"somePLSQLProcedure(?,?,?,?);" +

"END;"), 0);

try {

statement.setString(1, aStringParam);

statement.setInt(2, aIntParam);

statement.setDate(3, aDateParam);

statement.registerOutParameter(4, Types.VARCHAR);

statement.execute();

return statement.getString(4);

} catch (SQLException sqlerr) {

throw new JboException(sqlerr);

} finally {

try {

if (statement != null) {

statement.close();

}

} catch (SQLException closeerr) {

throw new JboException(closeerr);

}

Don't you think this piece of code is a little bit ugly? It is doing a prosaic thing - invoking an extremely simple PL/SQL procedure and it is so complicated. Moreover, there is no any control as for number and types of the procedure's parameters. So, writing this code each time whenever I need to invoke a PL/SQL procedure gets me a bit annoyed, and I developed some set of helper classes and interfaces in an effort to make it much easier. For example, a method invoking the same PL/SQL procedure should look like this:

public String execSmartPLSQLProcedure(String aStringParam,

Integer aIntParam,

Date aDateParam) {

IStoredProc proc =

StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");

proc.setStringValue("aStringParam", aStringParam);

proc.setIntegerValue("aIntegerParam", aIntParam);

proc.setDateValue("aDateParam", aDateParam);

proc.execute();

return proc.getStringValue("aOutParam");

}

Much better, isn't it?
Let's consider an example of invoking of a PL/SQL function. We have a PL/SQL function:

create or replace function somePLSQLFunction
(aStringParam Varchar2,
aIntegerParam Number,
aDateParam Date
) return Varchar2

And a Java method invoking this function is very simple:

public String execSmartPLSQLFunction (String aStringParam,

Integer aIntParam,

Date aDateParam) {

IStoredProc proc =

StoredProc.getStoredProc(getDBTransaction(), "somePLSQLFunction");

proc.setStringValue("aStringParam", aStringParam);

proc.setIntegerValue("aIntegerParam", aIntParam);

proc.setDateValue("aDateParam", aDateParam);

proc.execute();

return proc.getStringValue("RESULT");

}

And what about working with complex SQL types? For example we have some SQL type representing an array of Varchar2:

CREATE OR REPLACE TYPE varchar2_array_type AS TABLE OF VARCHAR2(2000)

And our PL/SQL procedure accepts a parameter of this type:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
aIntegerParam Number,
aDateParam Date,
aArrayParam varchar2_array_type,
aOutParam out Varchar2
)

So our Java code should look like this:

public String execSmartPLSQLProcedure(String aStringParam,

Integer aIntParam,

oracle.jbo.domain.Date aDateParam,

ArrayList<String> aArrayParam) {

IStoredProc proc =

StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");

proc.setStringValue("aStringParam", aStringParam);

proc.setIntegerValue("aIntegerParam", aIntParam);

proc.setDateValue("aDateParam", aDateParam);

proc.setArrayValue("aArrayParam", aArrayParam, "VARCHAR2_ARRAY_TYPE");

proc.execute();

return proc.getStringValue("aOutParam");

}

One of the principal features of this approach is that you know all necessary information about the procedure's parameters right after the execution of StoredProc.getStoredProc(...) . So you have the full list of parameters containing their names and data types. In order to retrieve this information from the database StoredProc class uses DatabaseMetaData method getProcedureColumns. This technique is described in details in this post.
The list of parameters allows us to control their names and types actually before execution of the procedure. Furthermore, we can assume that names of attributes of our Entity and names of parameters of PL/SQL procedures (inserting, updating and deleting the data) are equal and we can implement some method in an EntityImpl class performing DML operations:

private void executeDMLprocedure(String procedureName) {

IStoredProc storedProc =

StoredProc.getStoredProc(getDBTransaction(), procedureName);

for (IStoredProcParam param : storedProc.getParams()) {

if (param.isIn() || param.isInOut()) {

param.setParamValue(getAttribute(param.getParamName()));

}

storedProc.execute();

for (IStoredProcParam param : storedProc.getParams()) {

if (param.isOut())

populateAttribute(getEntityDef().getAttributeIndexOf(

param.getParamName()),

param.getParamValue());

}

The source code of this tool is available here. It can be deployed into ADF library and, perhaps, it can make your life a bit easier.

ADF Call Stored Procedure with Out Parameters

Step1 Create DB Procedure

stored_db_proc(Param1 IN VARCHAR2,
                           Param2 IN DATE,
                           x_errCode OUT VARCHAR2,
                           x_errMsg OUT VARCHAR2);

Step 2  Create Public/Protected method in ApplicationModule to call

import oracle.jbo.server.DBTransaction;

public String[] callStoredProcOutParam(String p_param1,String p_param2Date) {

String _xErrCode;

String _xErrMsg;

DBTransaction trx =(DBTransaction)getTransaction();

CallableStatement st = null;

try {

// 1. Define the PL/SQL block for the statement to invoke

String stmt = "begin stored_db_proc (?,?,?,?); end;";

// 2. Create the CallableStatement for the PL/SQL block

st = trx.createCallableStatement(stmt,0);

// 3. Register the positions and types of the OUT parameters

st.registerOutParameter(3,Types.VARCHAR);

st.registerOutParameter(4,Types.VARCHAR);

// 4. Set the bind values of the IN parameters

st.setObject(1,p_param1);

st.setObject(2, p_param2Date);

// 5. Execute the statement

st.executeUpdate();

//6. Set value of _xErrCode property using first OUT param

_xErrCode = st.getString(3);

//7. Set value of _xErrMsg property using 2nd OUT param

_xErrMsg = st.getString(4);

// 8. Return the result

return new String[] {_xErrCode,_xErrMsg} ;

} catch (SQLException e) {

throw new JboException(e);

} finally {

if (st != null) {

try {

// 9. Close the JDBC CallableStatement

st.close();

}

catch (SQLException e) {

e.getStackTrace();

} } } }

Step3 Expose callStoredProcOutParam method in PageDefBindings

Add method in Application Module Client

Double click AppMOdule àCkick Java à Client Interface

(Note : This will add Interface
public class csvAMClient extends ApplicationModuleImpl implements csvAM)

Refresh Data Control to see newly added method.

Open Page Def Bindings, Click + Button , Select Method Action,

Click on DataControl Select Method to be included.

Step 4 In Managed Bean Listner method

import oracle.binding.DataControl;
import oracle.binding.OperationBinding;

import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCBindingContainer;

........

FacesContext context = FacesContext.getCurrentInstance();

Application app = context.getApplication();

DCBindingContainer binding = (DCBindingContainer) app.evaluateExpressionGet(context, "#{bindings}",DCBindingContainer.class);

// Call AM insContData Method

OperationBinding callAmMethod =

binding.getOperationBinding("callStoredProcOutParam ");

callAmMethod.getParamsMap().put("p_param1",Val1);

callAmMethod.getParamsMap().put("p_param2Date ",castToJBODate(Val2));

callAmMethod.execute();

Object ReturnValue[] = (Object[])callAmMethod.getResult();

String _errCode = (String)ReturnValue[0];

String _errMsg = (String)ReturnValue[1];

Invoking/Calling Store Procedures from Oracle ADF Application

This post shows how you can call a pl/sql stored procedure from ADF business component. Here I have shown three scenario -> a. how you can call a procedure which does not have any input and output parameter, b.
calling a procedure which has only input parameter but no output parameter and c. calling a procedure with both i/p and o/p parameter.

Double click on your existing application module component. Select the Java node. Now select the Application Module Class: AppModuleImpl > Generate Java File selectbox. Click ok. Double click on Application Module Implementation java file [The file with “Impl” name]. A Java file will open.

CALLING A PROCEDURE WITHOUT INPUT AND OUTPUT PARAMETER

Let’s take one simple procedure without Input and Output parameter like the following one ->

procedure sampleProcedure is

begin

insert into address (name,phone) values ('d','d');

commit;

end sampleProcedure;

For calling this procedure, write one method in your AppModuleImpl.java class as follows ->

public void callStoreprocedureWithoutInput() {

String stmt = "begin SAMPLEPROCEDURE; end;";

PreparedStatement st = null;

try {

st = getDBTransaction().createPreparedStatement(stmt, 0);

st.executeUpdate();

st.close();

} catch (Exception e) {

e.printStackTrace();

}

Calling a Procedure with only Input Parameters

Suppose we have a simple procedure which takes some Input parameters.

PROCEDURE SAMPLEPROCEDURE4(nm varchar2,ph varchar2) as

BEGIN

insert into Address (Name,Phone) values (nm,ph);

commit;

END;

For calling this procedure write one method in your AppModuleImpl.java class as follows ->

public void callStoredProcedureWithInput(String name, String phone) {

PreparedStatement st = null;

Object[] bindVariables={name,phone};

try {

st=getDBTransaction().createPreparedStatement("begin SAMPLEPROCEDURE4(?,?) ;end;", 0);

if (bindVariables != null) {

for (int z = 0; z < bindVariables.length; z++) {

st.setObject(z + 1, bindVariables[z]);

}

st.executeUpdate();

} catch (SQLException e) {

throw new JboException(e);

} finally {

if (st != null) {

try {

// 5. Close the statement

st.close();

} catch (SQLException e) {

}

Here we have added the bind variables in PreparedStatement using setObject() function. After that we have executed the query.

CALLING A PROCEDURE WITH INPUT AND OUTPUT PARAMETERS

Suppose we have a package where our function Function1 is defined as follows ->

package newPackage is

-- Public function and procedure declarations

function Function1(p_name varchar2) return varchar2;

end newPackage;

For calling this procedure write one method in your AppModuleImpl.java class as follows ->

public String callStoreProcedureWithInputOutput(String name){

CallableStatement st = null;

try {

String stmt = "begin newPackage.Function1(?); end;";

st = getDBTransaction().createCallableStatement(stmt,0);

st.registerOutParameter(1,Types.VARCHAR);

st.setObject(1,name);

st.executeUpdate();

st.getString(3);

System.out.println(" 1 "+st.getString(3));

return st.getString(3);

} catch (SQLException e) {

throw new JboException(e);

} finally {

if (st != null) {

try {

st.close();

}catch (SQLException e) {}

}

EXPOSING APPLICATION MODULE CODES IN PRESENTATION/VIEW LAYER

Double click on Application Module component. Select Client Interface node. Place all available written method in selected site.

In Oracle Adf we will doing DML operation by calling the Procedure.Here the syntax of calling
Stored procedure is as follow:

Here Procedure name is -- Vinayprocedure(empno,job,name)

StoredProcedureCall call = new StoredProcedureCall();

call.setProcedureName("Vinayprocedure");

call.addUnamedArgument("empno");

call.addUnamedInOutputArgument("job", "job", String.class);

call.addUnamedOutputArgument("name");

DataModifyQuery query = new DataModifyQuery();

query.setCall(call);

query.addArgument("empno");

query.addArgument("job");

query.addArgument("name");

session.addQuery("Vinayprocedure ", query);

Vector parameters = new Vector();

parameters.addElement(new Integer(7369));

parameters.addElement("engineer");

parameters.addElement("Vinay");

Object data = session.executeQuery(query, parameters);

When we are calling data from some view object, we can use this code :-

VinayViewObject1Iterator= view object iterator.

StoredProcedureCall call = new StoredProcedureCall();

call.setProcedureName("Vinayprocedure2");

call.addNamedArgument("empno");

call.addNamedArgument("job");

call.addNamedArgument("name");

DataModifyQuery query = new DataModifyQuery();

query.setCall(call);

query.addArgument("empno");

query.addArgument("job");

query.addArgument("name");

Vector parameters = new Vector();

DCIteratorBinding iteratorobject= findIterator("VinayViewObject1Iterator");

RowSetIterator rowObject = iteratorobject.getRowSetIterator();

rowObject.setRangeSize(rowObject.getRowCount());

RowKeySet keysetobject = this.getTable().getSelectionState();

Set keySetobject = keysetobject.getKeySet();

Iterator itrobject = keysetobject.iterator();

while (itrobject.hasNext()) {

Integer intr = (Integer)itr.next();

VinayViewObjectRowImpl row =

(VinayViewObjectRowImpl)keysetobject.getRowAtRangeIndex(intr.intValue());

parameters.addElement(row.getAttribute(“empno)); parameters.addElement(row.getAttribute(“job”));

parameters.addElement(row.getAttribute(“name”));

session.addQuery("Vinayprocedure2", query);

Object data = session.executeQuery(query, parameters);

parameters.clear();

Oracle ADF - JDeveloper 12

Sunday, 29 October 2017