5 Replies Latest reply: Nov 9, 2012 8:33 AM by 973208 RSS

    Binding additional parameters to a VO that already have a binding parameter

    973208
      I have an ADF project with a Form Based Authentication. I bind username of the login page to every View Object and filter all data from begining. All view objects have a bind variable with " adf.context.securityContext.userName" in their where clause. What I want to do is binding a second parameter to filter data once again after page is rendered with just userName condition.

      So query of my VO is basicly like "select name,date from table where name =:p_name and date=:p_date"

      Value for p_name is "adf.context.securityContext.userName" and it will be passed initially to VO. Than I want to pass the second parameter (p_date) from user input (like inout text),which I couldn't do it by ExecuteWithParams operation, and filter data once again.

      What I could do so far is rendering page with just userName,second part is still missing. Any suggestion?

      Thanks in advance

      Regards
      Murat
        • 1. Re: Binding additional parameters to a VO that already have a binding parameter
          Frank Nimphius-Oracle
          Hi,

          All view objects have a bind variable with " adf.context.securityContext.userName" in their where clause. What I want to do is binding a second parameter to filter data once again after page is rendered with just userName condition.


          Instead of adding this to the where clause, please create a View Criteria for the VO and then add the VO to the view object instances in the ADF BC data model (use the edit option on the view object instance). This way the authenticated username is always enforced on the query. The View criteria uses a bind variable that you set to the Groovy expression of the authenticated user, This is a much cleaner approach

          Frank
          • 2. Re: Binding additional parameters to a VO that already have a binding parameter
            973208
            I changed the query to "select * from table where date=:p_date" and created a view criteria as ( ( ( UPPER(name) = UPPER(:p_name) ) ) as you said but I'm getting this error:

            "Attempt to set a parameter name that does not occur in the SQL: p_name"

            If I set the "p_name" variable as "not required" I'm not getting this error but then the user executes the query with no restriction and lists all the data which user shouldn't see.

            Main problem is creating the same form that our customer use in it's own system,and second filtering shold be done by button. When I execute the query with additional parameter "adf.context.securityContext.userName" is wheter empty or not binded.

            Still couldn't handle the problem.
            • 3. Re: Binding additional parameters to a VO that already have a binding parameter
              Ranjith Chitharanjan
              hi,
              You can create a view criteria with two bind variables
              :p_date and : p_name

              and apply this criteria as the default criteria to the corresponding instance in the application module.
              then by default you will get a query with 1 where clause ie, "select * from table where date=:p_name" , because you specified the user name in the value field of the bind variable.
              Then on button click you can set the value of the bind variable :p_date and you can execute the vo query.

              Then you will get only the records belongs to the loggend in user and in the time period that you set for the :p_date..


              Ranjith
              • 4. Re: Binding additional parameters to a VO that already have a binding parameter
                973208
                Hi Ranjith,

                My plan was same with your suggestion but the problem is, on the button click action (after query executed with userName) value of the userName becomes null. I assigned it's value to an output text and after button click it comes as null,you can try it to see what I mean to say.

                I placed the parameters as binding parameter form and assigned value of p_name as "#{securityContext.userName}" and it didn't work as well,still getting null value from securityContext.userName.
                • 5. Re: Binding additional parameters to a VO that already have a binding parameter
                  973208
                  Frank,

                  I understand what you mean.I followed your suggestions with the help of this document tips : http://www.oracle.com/technetwork/developer-tools/adf/learnmore/59-table-filter-by-data-range-176653.pdf

                  Didn't apply it to the page yet but seems like working.

                  Thanks