java.sql.SQLException: Missing IN or OUT parameter at index:: 1 with ADF BC

While creating the search forms in the ADF using ADF BC, we are creating the bind variable in the view object.

Some times the bind variable is not created properly, and causes the exception, java.sql.SQLException: Missing IN or OUT parameter at index:: 1


There are two options that bind variable is required for developing ADF Search forms from ADF BC.

1. Search Form created from 'Execute with Params ' operation - Direct Query

In this case, bind variables 'required' property shoud be selected.

2. Search Form created from view criteria.

In this case, bind variables 'required' property should not be selected.

ADF ViewObject Query customization using a binding variable

The user can interact with database using ADF ViewObject. ViewObject returns a query that we can convert as datacontrol for accessing in the ADF pages.

By default ADF ViewObject query retuens select query with select all the fields from the table. But if we want to customize our query and wants to add any clauses,
we can do this by adding a new bind variable.

Edit the ViewObject query and and add the clause.

Example :

Initial Query :

SELECT Profile.USERNAME, 
        Profile.FIRSTNAME, 
        Profile.MANAGERID, 
        Profile.LASTNAME, 
        Profile.EMAILID, 
        Profile.COMPANY, 
        Profile.ADDRESS, 
        Profile.POBOX, 
        Profile.COUNTRY, 
        Profile.CONTACT, 
        Profile.MANAGER, 
        Profile.USERID, 
        Profile.DESIGNATION, 
        Profile.DEPARTMENT, 
        Profile.SALARY, 
        Profile.IMAGE, 
      Profile.BLOGURL, 
        Profile.ROWID
FROM PROFILE Profile

The customized Query :

SELECT Profile.USERNAME, 
        Profile.FIRSTNAME, 
        Profile.MANAGERID, 
        Profile.LASTNAME, 
        Profile.EMAILID, 
        Profile.COMPANY, 
        Profile.ADDRESS, 
        Profile.POBOX, 
        Profile.COUNTRY, 
        Profile.CONTACT, 
        Profile.MANAGER, 
        Profile.USERID, 
        Profile.DESIGNATION, 
        Profile.DEPARTMENT, 
        Profile.SALARY, 
        Profile.IMAGE, 
      Profile.BLOGURL, 
        Profile.ROWID
FROM PROFILE Profile
WHERE Profile.USERNAME = :Loggeduser

Add a new binding variable with the following fields:\
Name : Loggeduser
Type : String
Value Type : Expression
value : adf.object.viewObject.loggeduser

After that go to the java tab, Create Java class for :"generate view object class" and select the checkbox, "include binding variable accessors"

And add the logic for the loggeduser. In this example, I have added the logic for returning the currently logged in user.

Java Class : ProfileViewImpl

package myportal.model;

import oracle.adf.share.ADFContext;
import oracle.adf.share.security.SecurityContext;

import oracle.jbo.server.ViewObjectImpl;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Thu Jan 10 09:13:01 GST 2013
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class ProfileViewImpl extends ViewObjectImpl {
    /**
     * This is the default constructor (do not remove).
     */
    public ProfileViewImpl() {
    }


    /**
     * Returns the bind variable value for Loggeduser.
     * @return bind variable value for Loggeduser
     */
    public String getLoggeduser() {
        SecurityContext securityContext=ADFContext.getCurrent().getSecurityContext();
        if(securityContext != null) 
        {
            System.out.println("SecurityContext is initialized");
            return securityContext.getUserName();
        }
        else 
        {
            System.out.println("SecurityContext is not initialized, returning weblogic");
          return "weblogic";
        }
       
    }

    /**
     * Sets <code>value</code> for bind variable Loggeduser.
     * @param value value to bind as Loggeduser
     */
    public void setLoggeduser(String value) {
        setNamedWhereClauseParam("Loggeduser", value);
    }
}

ADF code for retrieving currently logged in user

We can retrieve the currently logged user in two ways.

1. By el : retrieving the user details in front end pages using expression language.

#{securityContext.userName}

2. By managed bean java code.

import statements :
              import oracle.adf.share.ADFContext;
              import oracle.adf.share.security.SecurityContext;

SecurityContext securityContext=ADFContext.getCurrent().getSecurityContext();
String username=securityContext.getUserName();