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

ADF InputText number conversion

To format the numbers in the ADF InputText, use the below code inside <af:inputText> </af:inputText>


<af:convertNumber type="number" minFractionDigits="0"
                            maxIntegerDigits="4" maxFractionDigits="2"
                            messageDetailConvertNumber="Invalid Number"
                            pattern="#.#" />

The example final code is :



 <af:inputText value="#{bindings.Gireesh.inputValue}" label="#{bindings.Gireesh.hints.label}"
                      required="#{bindings.Gireesh.hints.mandatory}" columns="#{bindings.Gireesh.hints.displayWidth}"
                      maximumLength="#{bindings.Gireesh.hints.precision}" shortDesc="#{bindings.Gireesh.hints.tooltip}"
                      id="it3" disabled="true" binding="#{expense.gireesh}">
                      <af:convertNumber type="number" minFractionDigits="0"
                            maxIntegerDigits="4" maxFractionDigits="2"
                            messageDetailConvertNumber="Invalid Number"
                            pattern="#.#" />
          <f:validator binding="#{bindings.Gireesh.validator}"/>
        </af:inputText>

ADF Applications with Webservice creation and consumption

The following ADF projects illustrates the features of creating a webservice out of Java Class for doing the database insertion and retrieval, and consuming the webservice in an ADF fusion application.

Create the database user with name gkm.

#sqlplus "/as sysdba"
$create user gkm identifies by welcome1;
$grant dba to gkm;

Then login with gkm user. and create a table using the below command,

CREATE TABLE "GKM"."ADMIN"
   ( "ID" NUMBER NOT NULL ENABLE,
"USERNAME" VARCHAR2(20 BYTE),
"PASSWORD" VARCHAR2(20 BYTE),
"FIRSTNAME" VARCHAR2(20 BYTE),
"LASTNAME" VARCHAR2(20 BYTE),
"CONTACTNUMBER" NUMBER,
"EMAILID" VARCHAR2(20 BYTE),
CONSTRAINT "ADMIN_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

The same script is available here

After that create an adf java application for updating the database.

You can download the project from here.

In this application, I am updating the database fields using a java method and retrieving all the values. Then right click on the particular java class and select 'generate Webservice'
In my project I have selected 'MyWS.java' and created the webservice.

Then start the weblogic application server and deploy the application.

I have created the war deployment profile and ear deployment profile and then deployed the project using ear deployment profile.

After the deployment is success, login into the weblogic admin console, and click on 'deployments'. the select the project and click on the webservice profile. Then click on the 'test' tab and get the wsdl url.

Once you have webservice URL, you can paste it in a browser and check whether it displays the xml content.

http://localhost:7101/myJavaWS/MyWSPort?WSDL

Create another adf fusion application. You can download the application from here.

Create the webservice client and proxy from the wsdl url. Create a java class and create the methods for updating the database and retrieving the database values.

convert this java class as data control.

Method for retieving the database :

    public List<Admin> getPersons() {
        MyWSService myWSService = new MyWSService();
        MyWS myWS = myWSService.getMyWSPort();
        List<wsc.Admin> myList = new ArrayList<wsc.Admin>();
        myList = myWS.getData("select * from admin");
        for (Admin ad : myList) {
            System.out.println(ad);
        }
        return myList;
    }

Method for updating the table :

   public void updateTable() {
        System.out.println("I am in update table");
        MyWSService myWSService = new MyWSService();
        MyWS myWS = myWSService.getMyWSPort();
        DCBindingContainer dcBindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
        DCIteratorBinding iterBind= (DCIteratorBinding)dcBindings.get("personsIterator");
        int id = (Integer)iterBind.getCurrentRow().getAttribute("id");
        String username = (String)iterBind.getCurrentRow().getAttribute("username");
        String password = (String)iterBind.getCurrentRow().getAttribute("password");
        String firstName = (String)iterBind.getCurrentRow().getAttribute("firstName");
        String lastName = (String)iterBind.getCurrentRow().getAttribute("lastName");
        int contactNumber = (Integer)iterBind.getCurrentRow().getAttribute("contactNumber");
        String emailId = (String)iterBind.getCurrentRow().getAttribute("emailId");
        String query="insert into admin values (" + id + ",'" + username + "','"
            +password + "','" + firstName + "','" + lastName + "'," +contactNumber
            + ",'" + emailId +"')";
        System.out.println(query);
        int i = myWS.updateTable(query);
        System.out.println(i);
    }

Create an adf jspx page and drag and drop the person data control to the page and select ADF form, select navigation contol.

Then drag and drop the 'updateTable' method from data control window to page and select ADF button.

Finally the page looks like below,

Run the ADF application and check the below screenshots.

(In my application, I have created an ADF taskflow and then consumed it in a page).