Display Calender in SQL and PLSQL

Today I will present how to display calendar specific month from oracle SQL or PLSQL like below image

So I will present the solution to do this

First I will create Object type to handle week days( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)

 CREATE OR REPLACE TYPE WEEK_DAY AS OBJECT  
   (SUN NUMBER (2),  
   MON NUMBER (2),  
   TUE NUMBER (2),  
   WED NUMBER (2),  
   THU NUMBER (2),  
   FRI NUMBER (2),  
   SAT NUMBER (2));  

ADF ::: Creating View Criteria Programmatically

We can add view criteria programmatically at run-time to ViewObject. So that we not only depend on Design View Criteria.

The following is an example used to create View Criteria in Application Module Impl

ViewObject empVO= this.findViewObject("EmpVO");
ViewCriteria vc = empVO.createViewCriteria();
ViewCriteriaRow vcRow = vc.createViewCriteriaRow();

// ViewCriteriaRow attribute name is case-sensitive.
// ViewCriteriaRow attribute value requires operator and value.
// Note also single-quotes around string value.
ViewCriteriaItem enameItem= vcRow.ensureCriteriaItem("Ename");
enameItem.setOperator("=");
enameItem.getValues().get(0).setValue("Mahmoud Elsayed");
vc.add(vcRow);

empVO.applyViewCriteria(vc);

Thanks

ADF ::: Handle af:query search Button Programatically

The following code snippets is to execute search button in ADF Query search button programmatically without interact from User

    public void onQueryListener(QueryEvent queryEvent) {
        // Add event code here...
        try{
        FacesContext fcsCtx = FacesContext.getCurrentInstance();
        ELContext elCtx = fcsCtx.getELContext();
        ExpressionFactory expFactory = fcsCtx.getApplication().getExpressionFactory();
        MethodExpression mthdExp =
            expFactory.createMethodExpression(elCtx, "#{bindings.EmpVOCriteriaQuery.processQuery}", null,
                                              new Class[] { queryEvent.getClass() });
        mthdExp.invoke(elCtx, new Object[] { queryEvent });
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }

Thanks

OAF : NewRowState of New Rows

By default, entity objects are created with the row state of STATUS_NEW, and BC4J adds them to its validation.  In this case, any event that triggers a validation or database post sequence includes these entity objects.

As per OAF Model Coding Standards, always circumvent this behavior by explicitly calling the setNewRowState(STATUS_INITIALIZED) method on its containing ViewRowImpl immediately after you insert the newly created row. This sets the state of any associated entity objects to STATUS_INITIALIZED.

When you do this, BC4J removes the corresponding entity objects from the transaction and validation listener lists, so they will not be validated or posted to the database. As soon as the user makes a change (an attribute "setter" is called), the entity object's state changes to STATUS_NEW, and BC4J returns it to the validation/post lists. You can also call setNewRowState(STATUS_NEW) on the ViewRowImpl to change the state manually at any time.

Let's trace some scenarios 

Scenario 1: Row has been modified in the UI before submit. Row State could be anything. 

• The RowState becomes New.
• ValidateEntity will get called before processFormRequest().

Scenario 2: Called row.setNewRowState(Row.STATUS_NEW) after inserting a new row. Row has not been modified from UI. 

 

ValidateEntity will not get called before processFormRequest() but will get called on commit and postChanges.

 

Scenario 3: Called row.setNewRowState(Row.STATUS_INITIALIZED) after inserting a new row. Row has not been modified from UI.

• ValidateEntity will not get called before processFormRequest() or on commit or postChanges.
• The Row will be ignored by the Framework and removed from the Cache.
• Even the mandatory fields in the Entity Object are not checked.

Please make sure setting the row state (Row.setNewRowState) should be done after adding the row to VO as recommended in the OAF Standard.

Thanks

OAF: Parameters

Oracle Application Framework (OAF) can pass parameters between pages and there are three types of parameters
1- Request
The scope of request parameters is finalized after  HTTP request.

Examples of Request Parameters

• URL Parameters
• Input Bean values (Message Text Input, Choice , Check etc) and Hidden Field Values (form values) in case of post
• Event triggering bean and the action in case of post.

Request values are accessed using OAPageContext.getParameter() method.

2- Transaction

Transaction has wider scope than Request which finalize with ending of database transaction.

You can create transaction parameter using OAPageContext.putTransactionValue() , ((OADBTransactionImpl)getTransaction()).putValue() .

Get transaction parameter values using OAPageContext.getTransactionValue() , ((OADBTransactionImpl)getTransaction()).getValue()

3- Session
Session has wider scope than Transaction which his life time is until user log out from application.

You can create Session parameter using OAPageContext.putSessionValue() ,  OAPageContext.putSessionValueDirect() .

 

Get transaction Session values using OAPageContext.getSessionValue();

URL Parameters Encryption and Encoding

We can encrypt parameters when  passing in URL in the following formats

1- {@Attr}  encodes. Changes Mahmoud Elsayed to Mahmoud %Elsayed
2- {!Attr} encrypts parameter value, Get parameter value in Controller using OAPageContext.getDecryptedParameter()
3- {$Attr} plain token substitution (no encoding or encryption), Get parameter value in Controller using OAPageContext.getParameter()

 

Global parameters in URL
1- {@@RETURN_TO_MENU}  Used for E-Business Suite Personal Home Page. Same as OAWebBeanConstants.RETURN_TO_MENU_URL.

2- {@@RETURN_TO_PORTAL} - Return the user to a launching Portal page. Same as OAWebBeanConstants.RETURN_TO_PORTAL_URL.

Thanks

Display Detail Rows as One Column in Master

Sometimes, it is required to display detail rows as a single column in master row.
For example in scott schema you have two tables ( DEPT, EMP) which relation is 1-M .

If required to display Employees names separated by comma  as one column per every DEPTNO, ÷In order to the final output like the following.





The easiest way to get output of previous diagram is the following SQL query.

    SELECT d.deptNo,
           MAX (SUBSTR (SYS_CONNECT_BY_PATH (d.eName, ','), 2)) employees
      FROM (SELECT deptNo,
                   eName,
                   ROW_NUMBER ()
                      OVER (PARTITION BY deptNo ORDER BY deptNo, eName)
                      rnum
              FROM scott.emp) d
START WITH d.rnum = 1
CONNECT BY d.rnum = PRIOR d.rnum + 1 AND PRIOR d.deptNo = d.deptNo
  GROUP BY d.deptNo

Thanks

ADF & OAF ::: Missing IN or OUT parameter

If the bind variable required property wrongly defined into View Object, then it will cause following exception
"java.sql.SQLException: Missing IN or OUT parameter at index:: 1 "

To avoid this exception, you have to keep in mind when you are creating bind variable.
 1- If you are directly passing the bind variable in view object main query then in this case bind variable require property should be selected.

2- If you are using bind variable in view criteria only  then in this case the bind variable require property should not be selected.

Thanks