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.

Now, in your data control pallet, you can see the available methods.

Now drag and drop each method with its Input and output parameters on a JSF page. Use adf:form for input items. Now, run the page and see the result.

If you have query or any further information please leave a comment. If possible I’ll try to resolve those.

More oracle ADF tutorials are here Oracle ADF