ADF - Call PL/SQL function in Managed Bean

In this blog we will see a very common use case of how to call pl/sql function in managed bean.

We can achieve this use case using  Application Module Client Interface

Here’s step by step procedure to call pl/sql function in managed bean using Application Module Client Interface.

1 . Create ApplicationModuleImpl class.

   

2.   Write following line of code to call database function (sampleMethod is a pl/sql function which return number.)

    public BigDecimal sampleMethod(BigDecimal param1, BigDecimal param2) {

        CallableStatement plsqlBlock;

        plsqlBlock = null;

        String statement = "BEGIN ?:= YOUR_FUNCTION_NAME( ?, ?); END;";

        plsqlBlock = getDBTransaction().createCallableStatement(statement, 0);

        try {

            plsqlBlock.registerOutParameter(1, Types.DECIMAL);

            plsqlBlock.setBigDecimal(2, param1);

            plsqlBlock.setBigDecimal(3, param2);

            plsqlBlock.executeUpdate();

            return plsqlBlock.getBigDecimal(1);

        } catch (SQLException sqlException) {

            throw new SQLStmtException(CSMessageBundle.class, CSMessageBundle.EXC_SQL_EXECUTE_COMMAND, statement,

                                       sqlException);

        } finally {

            try {

                plsqlBlock.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

For procedure add below code

    public BigDecimal sampleMethod(BigDecimal param1, BigDecimal param2) {

        CallableStatement plsqlBlock;

        plsqlBlock = null;

        String statement = "BEGIN YOUR_PROCEDURE_NAME( ?, ?,?); END;";

        plsqlBlock = getDBTransaction().createCallableStatement(statement, 0);

        try {

           

            plsqlBlock.setBigDecimal(1, param1);

            plsqlBlock.setBigDecimal(2, param2);

            plsqlBlock.registerOutParameter(3, Types.DECIMAL);

            plsqlBlock.executeUpdate();

            return plsqlBlock.getBigDecimal(3);

        } catch (SQLException sqlException) {

            throw new SQLStmtException(CSMessageBundle.class, CSMessageBundle.EXC_SQL_EXECUTE_COMMAND, statement,

                                       sqlException);

        } finally {

            try {

                plsqlBlock.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

Note: the difference between function and procedure is of ' ? '. For function 1st ? will be output and in case of procedure last ? will be output. 

3.   Add function to Client Interface.

4. Bind component on jspx page as MethodAction

5.  Write following lines of code to execute ClientInterface Method action on Manged Bean.(in ActionListener of your button)

       BindingContext bCtx = BindingContext.getCurrent();

       DCBindingContainer dcbContainer = (DCBindingContainer) bCtx.getCurrentBindingsEntry();

       BindingContainer bContainer = (BindingContainer) dcbContainer;

       OperationBinding opBinding = (OperationBinding) bContainer.getOperationBinding("sampleMethod");

        opBinding.getParamsMap().put("param1", 10);                                                                          

        opBinding.getParamsMap().put("param2", 20);

BigDecimal result= opBinding.execute();                                                                                                             

In this way you can call pl/sql function or procedure using Application Module Client Interface.                   

Hope this will be useful!!                                                                                                                                                 

ADF- Download file using fileDownloadActionListener

If you want to download file, you can achieve this using fileDownloadActionListener in ADF application.

Below are the steps to download file.

 1.    Add fileDownloadActionListener to your “Download” button:

                                <af:button text="DownLoad" id="b5"  inlineStyle="float: right;">

                                                <af:fileDownloadActionListener filename="abc.txt"   contentType="text/plain; charset=utf-8"

                                                           method="#{<your method>}"/> </af:button>

2.     In your managed bean method add below code:

        

         public void yourmethod(FacesContext facesContext, OutputStream outputStream){

            File file = new File("E:\\templates\\abc.txt");

            FileInputStream fdwd;

            byte[] bt;

            try {

                fdwd = new FileInputStream(file);

                int checkline;

                while ((checkline = fdwd.available()) > 0) {

                    bt = new byte[checkline];

                    int rst = fdwd.read(bt);

                    outputStream.write(bt, 0, bt.length);

                    if (rst == -1)

                        break;

                }

                outputStream.flush();

            } catch (IOException e) {

                ….

            }

      }

Note: This will download file from your file system.

3.  If you want to download file from database, write below code:

                     public void downloadChart(FacesContext facesContext, OutputStream outputStream)                             {

                          // get row containing file that you want to download

                         blob = (BlobDomain) row.getAttribute("DataFile");

                           try {

                             IOUtils.copy(blob.getInputStream(), outputStream);

                            blob.closeInputStream();

                                outputStream.flush();

                          } catch (IOException e) {

                               e.printStackTrace();

   FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR,                  e.getMessage(), "");

                            facesContext.addMessage(null, msg);

                          }

                       }

Note: you have to get the row containing file.

Hope this will be helpful

Ravi

ADF – Assign value to bind Variable using Groovy Expression

In some cases, we need to assign default value to bind variable which can be another view object’s current row’s attribute value. We can do this using groovy expression.

Below is the groovy expression.

if (adf.object.applicationModule.findViewObject('<YourVO>').currentRow != null)

{ return adf.object.applicationModule.findViewObject('<YourVO>').currentRow.<nameofAttribute>}

Note: <nameofAttribute> is View Object's attribute name

Hope this will be useful

Ravi

ADF: Insert row at the bottom of table

In most scenarios there is a requirement to insert row at bottom of table.

Lets see how can we achieve this.

Step 1-  Generate VOimpl class of your View Object.

Step 2- Go to VOimpl class and add below code

        @Override

    public void insertRow(Row row) {

        //go to the end of Rowset if it has rows

        Row lastRow = this.last();

        if (lastRow != null) {

            //insert new row at the end and make it current

            int indx = this.getRangeIndexOf(lastRow) + 1;

            this.insertRowAtRangeIndex(indx, row);

            this.setCurrentRow(row);

        } else { // empty Rowset

            super.insertRow(row);

        }

    }

In this way you can insert row at the bottom of table.

Hope this will be helpful.

Ravi

ADF- Send email using Java Mail API

We will be looking in this article as how we can send mail from ADF Application. Very often we require to send mail notification in our application.

So lets see how we can do this

1.     Add this two jars in your application

·         javax.mail-1.5.0

·         commons-email-1.4

2.     Configure Mail Session on your Weblogic server

              Note: JNDI Name is required to read the properties.

              Add below properties in JavaMail Properties:

                        mail.smtp.password=yourpassword

                        mail.smtp.port=465

                        mail.smtp.user=yourmailid

                        mail.smtp.host=smtp.googlemail.com

3.     Then Add below code in your “Send Email” buttons action   

          InitialContext ic = new InitialContext();

        Session session = (Session) ic.lookup("mail/IOTMailSession");

        Properties props = session.getProperties();

        System.out.println(" PROPERTIES LIST: ");

        props.list(System.out);

        String mailhost = props.getProperty("mail.smtp.host");

        System.out.println(" mailhost = " + mailhost);

        String user = props.getProperty("mail.smtp.user");

        System.out.println(" user = " + user);

        String password = props.getProperty("mail.smtp.password");

        System.out.println(" password = " + password);

      

        Email email = new SimpleEmail();

        email.setHostName(mailhost);

        email.setSmtpPort(465);

        email.setAuthenticator(new DefaultAuthenticator(user, password));

        email.setSSLOnConnect(true);

        email.setFrom(user);

        email.setSubject("TestMail");

        email.setMsg("This is a test mail ... :-)");

        email.addTo("abc@gmail.com");

        email.send();

Hope This willbe useful

Ravi

ADF - Total Sum of a Column in a Table

We will be looking in this article as how we can add Total of a column in a table. Very often we require to have total of a column in a table. For example , we might need total sum of Salary column in the table.

We will be leveraging power of Groovy in accomplishing this task.

So lets see how we can do this.

Use Case :- Add a Total Salary Attribute below Salary column in Employees Table.

Model Project

Considering that we have EmployeesView VO object which is based on Employees EO, go to attributes of VO and click add new attribute. Select "Add new Attribute". It will by default be transient. Give this attribute  name as TotalSal.

Now go to View Accessors tab and click on green + sign to create new View Accessor.

Select EmployeesView from the first window and shuttle it so that you can see like below.

Do ok and it should be like below screenshot.

Now go to attributes tab and select the new transient attribute (TotalSal).

Give its Default value as Expression and give Groovy expression as EmployeesView1.sum("Salary") .

View Project

Create a new page. Drag and Drop EmployeesVO from data control and create a new read only table "without" TotalSal Attribute.

Now go to Salary Column in Structure and do a right click and select footer from facet as shown below.

Now from EmployeesView1 in DataControl, drag and drop TotalSal column in this new footer facet.

Now go to bindings and create a new binding for this variable.

 And change the value of footer output text with this binding.

Save all and run this page.

Hope this was useful.

Happy Learning !!

Ravi Dolas