Monthly Archives: December 2011

Using comma separated string as a bind variable for SQL query with IN clause

Environment (JDeveloper 11.1.2.0.0, hr schema)

Bind variables are place holders in the SQL string whose value  can be easily changed at runtime without altering the query itself. Since the query text doesn’t change from execution to execution, the database can efficiently reuse the same parsed statement each time (parse once and execute many times). But how can we define a bind variable for IN clause, as an example suppose that we want to pass a comma-separated string of department IDs and execute the query based on our IN clause, and here is the query we wish to execute look like:

select * from departments where department_id IN (:comma-separated-list)

Fortunately, I have found how to do this based on  Steve Muench’s example #126.Using Comma-Separated String Bind for Variable IN List and in this post I am only repeating what Steve has shown before for the sake of sharing. Our goal is to pass a comma-separated string of department IDs at run time and execute our query.

To support bind variable IN clause we need to add the function below to our schema, this function accepts a comma-separated string argument and returns a NUM_TABLE as its result and this allows us to make our bind variable of String type.

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
RETURN num_table
AS
l_tab   num_table := num_table();
l_text  VARCHAR2(32767) := p_in_list || ',';
l_idx   NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
/

our DepartmentsView should look like this

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
WHERE DEPARTMENT_ID IN (SELECT * FROM TABLE( CAST ( in_number_list(:CommaSeparatedListOfDeptId) as num_table)))

where CommaSeparatedListOfDeptId is a bind variable of type String.

DepartmentsView

you can test the view using the ADF Model Tester and pass a comma-separated string of departments IDs as shown below:

Oracle ADF Model Tester

Advertisements

2 Comments

Filed under ADF

Access an Instance of a Managed Bean from another Managed Bean

Environment (JDeveloper 11.1.2.0.0)

Lately we had a case in which we needed to access an instance of  pageFlowScope bean from a  requestScope bean, this is a common use case and many developers may need to do the same. In this post I will explain how to do this using two options.

Firstly suppose we have two java classes one of them registered as  pageFlowScope bean and the other is registered as requestScope bean as shown below.

package view.managed;
public class PageFlowScopeClass {
public PageFlowScopeClass() {
super();
}
private String someData;
public void setSomeData(String someData) {
this.someData = someData;
}
public String getSomeData() {
return someData;
}
}
package view.managed;
public class RequestScopeClass {
    public RequestScopeClass() {
        super();
    }
}

both classes are registered in adfc-config.xml file

<?xml version="1.0" encoding="windows-1252" ?>
<adfc-config xmlns="http://xmlns.oracle.com/adf/controller" version="1.2">
  <managed-bean id="__1">
    <managed-bean-name>PageFlowScopeBean</managed-bean-name>
    <managed-bean-class>view.managed.PageFlowScopeClass</managed-bean-class>
    <managed-bean-scope>pageFlow</managed-bean-scope>
  </managed-bean>
  <managed-bean id="__2">
    <managed-bean-name>RequestScopeBean</managed-bean-name>
    <managed-bean-class>view.managed.RequestScopeClass</managed-bean-class>
    <managed-bean-scope>request</managed-bean-scope>
  </managed-bean>
</adfc-config>

The first option to access an instance of PageFlowScopeBean from RequestScopeBean is by adding a managed property to the RequestScopeBean, and here are the steps to do:

  1.  open the adfc-config.xml file, click on Overview menu, select the Managed Beans node, select RequestScopeBean and click the add managed property icon as shown below.

    Add Managed Property

  2. enter the name,class,and the value  for the property in our case these field should be:
    Name: myPageFlowBean
    Class: view.managed.PageFlowScopeClass
    value: #{pageFlowScope.PageFlowScopeBean}
    Now adfc-config.xml will be something like this

    <?xml version="1.0" encoding="windows-1252" ?>
    <adfc-config xmlns="http://xmlns.oracle.com/adf/controller" version="1.2">
    <managed-bean id="__1">
    <managed-bean-name>PageFlowScopeBean</managed-bean-name>
    <managed-bean-class>view.managed.PageFlowScopeClass</managed-bean-class>
    <managed-bean-scope>pageFlow</managed-bean-scope>
    </managed-bean>
    <managed-bean id="__2">
    <managed-bean-name>RequestScopeBean</managed-bean-name>
    <managed-bean-class>view.managed.RequestScopeClass</managed-bean-class>
    <managed-bean-scope>request</managed-bean-scope>
    <managed-property id="__5">
    <property-name>myPageFlowBean</property-name>
    <property-class>view.managed.PageFlowScopeClass</property-class>
    <value>#{pageFlowScope.PageFlowScopeBean}</value>
    </managed-property>
    </managed-bean>
    </adfc-config>
  3. in RequestScopeClass create an instance variable of type PageFlowScopeClass with the same name that exists in managed properties  and generate accessors; a getter and a setter for this variable. Now the RequestSCopeClass should be
    package view.managed;
    
    public class RequestScopeClass {
        private PageFlowScopeClass myPageFlowBean;
    
        public RequestScopeClass() {
            super();
        }
    
        public PageFlowScopeClass getMyPageFlowBean() {
            return myPageFlowBean;
        }
    
        public void setMyPageFlowBean(PageFlowScopeClass myPageFlowBean) {
            this.myPageFlowBean = myPageFlowBean;
        }
    }
  4. now you can get the PageFlowScopeBean instance by calling getMyPageFlowBean() method.

The other option to access an instance of PageFlowScopeBean is to use ExpressionFactory class  as shown below

package view.managed;

import javax.el.ELContext;
import javax.el.ExpressionFactory;
import javax.el.ValueExpression;
import javax.faces.application.Application;
import javax.faces.context.FacesContext;

public class RequestScopeClass {
public RequestScopeClass() {
super();
}

public PageFlowScopeClass getInstanceOfPageFlowScopeBean() {
FacesContext fctx = FacesContext.getCurrentInstance();
Application application = fctx.getApplication();
ExpressionFactory expressionFactory = application.getExpressionFactory();
ELContext context = fctx.getELContext();
ValueExpression createValueExpression = expressionFactory.createValueExpression(context, "#{pageFlowScope.PageFlowScopeBean}",PageFlowScopeClass.class);
PageFlowScopeClass pageFlowScopeInstance =(PageFlowScopeClass) createValueExpression.getValue(context);
return pageFlowScopeInstance;
}
}

to get an instance of PageFlowScopeBean all you need is just to call  getInstanceOfPageFlowScopeBean() method.

8 Comments

Filed under ADF