How to send HTML based e-mail notification using PL/SQL

Hi,

The following code illustrates an example to send html based e-mail notification through PL/SQL.

The Declare Block:

declare
begin
sendmail.send ('eafin20@norelay.com',
'eafin20@norelay.com',
'Subject: Testing',
'Hello '
|| ',
|| Please find attached the '
|| ' Code. '
|| 'This file has been uploaded to '
|| 'BlogSpot.Com'
);
end;

The Package :

Note : You would have to specify the SMTP server as per the instance in :
utl_smtp.open_connection('10.210.42.82',25)
utl_smtp.helo(c, '10.210.42.82')

create or replace PACKAGE sendmail IS
procedure common (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
c out utl_smtp.connection);

procedure send (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
p_body varchar2 default null);

end sendmail;


create or replace PACKAGE BODY sendmail IS
procedure common (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
c out utl_smtp.connection)
is v_recipient varchar2(1000);
begin
c := utl_smtp.open_connection('10.210.42.82',25);--make connection to smtp
utl_smtp.helo(c, '10.210.42.82'); --identify the domain of the sender
utl_smtp.mail(c, p_sender); --start a mail, specify the sender
utl_smtp.rcpt(c, p_recipient); --identify recipient
utl_smtp.open_data(c); --start the mail body
utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf);
utl_smtp.write_data(c, 'To: ' || p_recipient || utl_tcp.crlf);
utl_smtp.write_data(c, 'Subject: ' || p_subject || utl_tcp.crlf);
exception when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(c);
raise;
when others then
raise;
end common;
procedure send (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
p_body varchar2 default null)
is c utl_smtp.connection;
begin
common(p_sender, p_recipient, p_subject, c);
utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);
utl_smtp.write_data(c, utl_tcp.crlf || p_body);
utl_smtp.close_data(c);
utl_smtp.quit(c);
exception when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(c);
raise;
when others then
raise;
end send;
end sendmail;

You can use the HTML tags in the p_body parameter as per your format.

Thanks,
Gaurav

How to upload excel to the database table

Hi,

We create an item type for file Upload and the coding is as under :

Suppose you want to insert the data into table directly from excel so create a EO based VO.

handle the click og Go button in Controller:

OAApplicationModule am = (OAApplicationModule) pageContext.getApplicationModule(webBean);

OAViewObjectImpl vo = (OAViewObjectImpl) am.findViewObject("TempVO1");

if (pageContext.getParameter("Go")!=null)

{

DataObject fileUploadData =(DataObject)pageContext.getNamedDataObject("FileUploadItem");

String fileName = null;

String contentType = null;

Long fileSize = null;

Integer fileType = new Integer(6);

BlobDomain uploadedByteStream = null;

BufferedReader in = null;

try

{

fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");

contentType =(String)fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);

in = new BufferedReader(new InputStreamReader(uploadedByteStream.getBinaryStream()));

fileSize = new Long(uploadedByteStream.getLength());

System.out.println("fileSize : "+fileSize);

}

catch(NullPointerException ex)

{

throw new OAException("Please Select a File to Upload", OAException.ERROR);

}

try

{

//Open the CSV file for reading

String lineReader="";

long t =0;

String[] linetext;

while (((lineReader = in.readLine()) !=null) )

{

//Split the deliminated data and

if (lineReader.trim().length()>0)

{

System.out.println("lineReader : "+lineReader.length());

linetext = lineReader.split(",");

t++;

//Print the current line being

System.out.println(t + " - " +

linetext[0].trim() + " - " + linetext[1].trim() + " - " +

linetext[2].trim() + " - " + linetext[3].trim() + " - " +

linetext[4].trim() + " - " + linetext[5].trim() );

for(Row row = (Row)vo.first(); row!= null;  row = (Row)vo.next())

{

  if(linetext[0].trim().equals(row.getAttribute("EmpId")))

  {

    row.setAttribute("EmpName",linetext[1].trim());

    row.setAttribute("Department",linetext[2].trim());

    row.setAttribute("Designation",linetext[3].trim());

    row.setAttribute("Manager",linetext[4].trim());

    row.setAttribute("Residence",linetext[5].trim());

  }

}

}

}

}

catch (IOException e)

{

  throw new OAException(e.getMessage(),OAException.ERROR);

}

am.getTransaction().commit();

throw new OAException("File Uploaded SuccessFully!!!",OAException.CONFIRMATION);

}

}

Thanks,

Gaurav

How to capture the Go button for the Query region and edit the where cause

Hi,

On query region we can capture the Go button click and execute our own search along with Standard Search.

     OAApplicationModule ap = (OAApplicationModule)pageContext.getApplicationModule(webBean);
    OAQueryBean bean = (OAQueryBean)webBean.findIndexedChildRecursive("queryRN");
    if(bean!=null)
    {
      String id = (String)bean.getGoButtonName();
      if(pageContext.getParameter(id)!=null)
      {
          ap.invokeMethod("initsearch",params);
       }

public void initsearch(String fromnum,String tonum,String fromdate,String todate)
 { 
    String whereClause = null;
    int whereClausecount = 0;
    OAViewObject vo = (OAViewObject)getReprintInvoiceVO1();
    vo.setWhereClause(null);
    vo.setWhereClauseParams(null);
   if(fromnum!=null&&!("".equals(fromnum.trim())))
    {
      whereClause = "INVOICE_NUMBER >= :1" ;
      vo.setWhereClauseParam(whereClausecount,fromnum);
      whereClausecount++;
    }
    if(tonum!=null&&!("".equals(tonum.trim())))
    {
      
      if(whereClause!=null)
      {
       
        whereClause = whereClause +" AND INVOICE_NUMBER <= :2";
        vo.setWhereClauseParam(whereClausecount,tonum);
        whereClausecount++;
      }
      else
      {
       
        whereClause = "INVOICE_NUMBER <= :1";
        vo.setWhereClauseParam(whereClausecount,tonum);
        whereClausecount++;
      }   
    }
    if(fromdate!=null&&!("".equals(fromdate.trim())))
    {
     if(whereClause!=null)
     {
       if((fromnum!=null&&!("".equals(fromnum.trim())))&&(tonum!=null&&!("".equals(tonum.trim()))))
       {
         whereClause = whereClause +" AND INVOICE_DATE > = :3";
         vo.setWhereClauseParam(whereClausecount,fromdate);
         whereClausecount++; 
       }
       else
       {
         whereClause = whereClause +" AND INVOICE_DATE > = :2";
         vo.setWhereClauseParam(whereClausecount,fromdate);
         whereClausecount++;
       }
      
     }
     else
     {
        whereClause = " INVOICE_DATE >= :1";
        vo.setWhereClauseParam(whereClausecount,fromdate);
        whereClausecount++;
     }
    }
    if(todate!=null&&!("".equals(todate.trim())))
    {
     if(whereClause!=null)
     {
       if((fromnum!=null&&!("".equals(fromnum.trim())))&&(tonum!=null&&!("".equals(tonum.trim())))&&(fromdate!=null&&!("".equals(fromdate.trim()))))
       {
         whereClause = whereClause +" AND INVOICE_DATE < = :4";
         vo.setWhereClauseParam(whereClausecount,todate);
         whereClausecount++;
       }
       else
       {
         whereClause = whereClause +" AND INVOICE_DATE < = :3";
         vo.setWhereClauseParam(whereClausecount,todate);
         whereClausecount++;
       }
     }
     else
     {
        whereClause = " INVOICE_DATE <= :1";
        vo.setWhereClauseParam(whereClausecount,todate);
        whereClausecount++;
     }
    }
   
    vo.setWhereClause(whereClause);
}

How to send entire table rows into PL/SQL Package at one go..

Hi,

We need to pass the entire table records into table PL/SQL Package using table type objects and then insert into tables. Here is the code :


CREATE OR REPLACE PROCEDURE xx_test(p_table IN INVOICE_TBL,x_error OUT error_tbl) IS
j NUMBER := 0;
BEGIN
     x_error := error_tbl();
 
  FOR i IN  p_table.FIRST .. p_table.LAST LOOP
     INSERT INTO invoice_table(invoice_date
             ,invoice_num
           ,invoice_type
           ,invoice_id )
      VALUES(TO_DATE(p_table(i).invoice_date,'YYYY/MM/DD')
             ,p_table(i).invoice_num
       ,p_table(i).invoice_type
       ,TO_NUMBER(p_table(i).invoice_id)
       );
   COMMIT;
   j:= j + 1;
   x_error.EXTEND;
   x_error(j):= error_rec('Error in saving record number '||j);
  END LOOP;
 
END;

CREATE OR REPLACE TYPE invoice_tbl IS TABLE OF invoice_rec;

CREATE OR REPLACE TYPE invoice_rec AS OBJECT invoice_date VARCHAR2(40)
         ,invoice_num VARCHAR2(240)
         ,invoice_type VARCHAR2(40)
         ,invoice_id VARCHAR2(20)  );
CREATE OR REPLACE TYPE error_rec AS OBJECT (error VARCHAR2(240));
CREATE OR REPLACE TYPE error_tbl IS TABLE OF error_rec;

create table invoice_table (invoice_date DATE,
  invoice_num  VARCHAR2(240),
  invoice_type VARCHAR2(20),
  invoice_id NUMBER);

import java.sql.Types;
import oracle.sql.ARRAY;

public void Pass()
 {
    /*How to pass VO Rows to PL/SQL*/
      String invoice_id    = null;
      String invoice_num = null;
      String invoice_type    = null;
      String invoice_date       = null;
     
     
     
      OAViewObject vo = getinvoiceVO1();
      invoiceVORowImpl rowi = null;
      int fetchedRowCount = vo.getRowCount();
      System.out.println("Fetched Row Count is "+fetchedRowCount);
      RowSetIterator selectIter = vo.createRowSetIterator("selectIter");
      STRUCT finalStruct = null;
      Object []  attrField = new Object[4];
      Object []  errorarrobj;
      Object []  errorrecobj;
      ARRAY error;
      ArrayList lineTableData = new ArrayList();
      Connection conn = getOADBTransaction().getJdbcConnection();
      try
      {
      ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("INVOICE_TBL",conn);
      StructDescriptor voRowStructDesc = StructDescriptor.createDescriptor("INVOICE_REC",conn);
      ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("ERROR_TBL",conn);
      StructDescriptor voRowStructDesc1 = StructDescriptor.createDescriptor("ERROR_REC",conn);
      if (fetchedRowCount > 0)
      {
      selectIter.setRangeStart(0);
      selectIter.setRangeSize(fetchedRowCount);
      for (int i = 0; i < fetchedRowCount; i++)
      {
          rowi = (invoiceVORowImpl)selectIter.getRowAtRangeIndex(i);
          invoice_id = (String)rowi.getInvoiceId().toString();
          invoice_num = (String)rowi.getInvoiceNum();
          invoice_type = (String)rowi.getInvoiceTypeLookupCode();
          invoice_date = (String)rowi.getInvoiceDate().toString();
          System.out.println("Invoice Date is "+invoice_date);
          //take all
          attrField[0] = (String)invoice_date;
          attrField[1] = (String)invoice_num;
          attrField[2] = (String)invoice_type;
          attrField[3] = (String)invoice_id;
          //assign all
         finalStruct  = new STRUCT(voRowStructDesc, conn, attrField);
          lineTableData.add(finalStruct);
      }
      }
      selectIter.closeRowSetIterator();
      System.out.println("Size of arraylist :"+lineTableData.size());
      oracle.sql.ARRAY passArray = new oracle.sql.ARRAY(descriptor, conn, (Object[]) lineTableData.toArray());
         try
          {
            System.out.println("Calling Procedure");
            CallableStatement cstmt = conn.prepareCall("{call XX_TEST(:1,:2)}");
            cstmt.setArray(1,passArray);
           System.out.println("Passed Array"); 
            cstmt.registerOutParameter(2,Types.ARRAY,"ERROR_TBL");
            cstmt.execute();
            error = (ARRAY)cstmt.getArray(2);
            cstmt.close();
            errorarrobj = (Object [])error.getArray();
            System.out.println("Number of rows="+errorarrobj.length);
            System.out.println("Printing results...");
             for (int i=0; i             {
              errorrecobj = ((STRUCT)errorarrobj[i]).getAttributes();
              System.out.println();
              System.out.print("Row "+i);
              System.out.print("|"+errorrecobj[0]);
             }
           
         }
        catch(Exception e)
         {
            throw OAException.wrapperException(e);
         }
     
      }
      catch(java.sql.SQLException ex)
      {
           throw OAException.wrapperException(ex);
      }
 }

How to see debug logging mesages on jdeveloper console

Navigate to Project properties -> Run/Debug -> Launch settings -> Java options
Add this in the end of java options value.... with one space... .
-Djbo.debugoutput=console
After this run ur page again... this will show the detailed logs of what is happeing

Thanks,
Gaurav

How to set system date -1 on a View Attribute

 Hi,
 The following code sets the sysdate-1 on a View Attribute.

             int MILLIS_IN_DAY = 1000 * 60 * 60 * 24;
              Date date = new Date();
              oracle.jbo.domain.Date oracleDate = null;
              try
              {
                SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
                String prevDate = dateFormat.format(date);

                java.util.Date newdate = dateFormat.parse(prevDate);
                oapagecontext.writeDiagnostics(this,"Today dd-MM-yyyy = " + dateFormat.format(newdate),1);
                long javaMilliseconds = (long)(newdate.getTime()- MILLIS_IN_DAY);

                java.sql.Date javaSqlDate = new java.sql.Date(javaMilliseconds);
                oapagecontext.writeDiagnostics(this,"Java Sql Date is "+ javaSqlDate,1);
                oracleDate = new oracle.jbo.domain.Date(javaSqlDate);
              }
              catch(Exception e)
              {
                e.printStackTrace();
              }
             
              oapagecontext.writeDiagnostics(this,"Oracle Date is "+ oracleDate,1);
              row.setAttribute("DateStart",oracleDate);

Thanks,
Gaurav

Steps to add a new Object Attribute to AK Region:---

1) Choose the AK Developer responsibility.


2) Click on “Define Attributes”


3) Now define a new Attribute: IBE_OS_ORDER_HOLDS

4) Save Changes and Click on “Define Regions”:


5) Query for region IBE_ORD_SUM_R


6) Now click on “Object” and then “Object Attributes”.


7) Now add a new Attribute which you defined above and match it with the
Column of the view Here I have matched IBE_OS_ORDER_HOLDS with column ORDER_HOLDS
Of the view: IBE_ORDER_SUM_V

8) Save changes and close the form and then Click on “Region Items” in “Define Regions” form


9) Now add a new Object Attribute IBE_OS_ORDER_HOLDS which we created earlier.


Please make sure Node Display and Queryable Checkboxes are clicked in this.

10) Save Changes and Bounce Apache.


This way new column will be added to ibeCOtdOrdSumMain.jsp.

Thanks,
Gaurav

How to generate XML from Oracle SQL Query

Hi,

PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results. It retrieves an XML document as a CLOB or XMLType value.

Just execute the SQL Statement :

select dbms_xmlgen.getxml('select user_name from fnd_user where rownum < 4') from dual

and in DBMS_OUTPUT you will get the xml data hierarchy and save it as .xml.

Thanks,
Bijender Singh

How to split rows in Table or Advance Table???

There has been a Requirement given that there is a split icon present against each row in an Advance Table.
As the user clicks on Split Icon...the parent row should get split into two and the new Row should be inserted right below the parent row.

As an OAF Standard New Row gets inserted at the top of the View Rows.

But we can insert it right below the parent row.

Invoke a method in AM as the User press Split Icon and pass the Id of the row to be splitted.
and loop through the VO rows and identify the row to be splitted.
Then set the current row and insert the new row.


for(xxinvoiceVORowImpl row = (xxinvoiceVORowImpl)vo1.first();
row != null;
row = (xxinvoiceVORowImpl)vo1.next())
{
if(row.getAttribute("InvoiceId")!=null)
{
String invoice = (String)row.getAttribute("InvoiceId").toString();
if(inv.equals(invoice))
{
vo1.setCurrentRow(row);
vo1.next();
xxinvoiceVORowImpl newrow = (xxinvoiceVORowImpl)vo1.createRow();
vo1.insertRow(newrow);
break;
}
}
}

This will insert the row right below the Row on which Split icon was clicked.

Thanks,
Gaurav

CSS Types

Hi,

Found a very good Document regarding the various CSS types used.

Would like to share..

http://www.oracle.com/technology/tech/blaf/specs/textStandards.html

Thanks,
Gaurav