PL/SQL: Some helpful SQL queries -1

In this post I am just putting some helpful SQL queries which will be useful at some point in your SQL / PLSQL Programming life. Most of these are taken from the OTN Forum answers or from my project experiences.

#1. How to convert Rows into Columns. 

Source Data

Expected Output

Using Pivot:

 with t as  
 (select 1 id,'MONDAY' day,'10:00' start_time,'12:00' end_time from dual  
 union  
 select 1,'TUESDAY','11:00','12:00' from dual  
 union  
 select 1,'WEDNESDAY','09:00','12:00' from dual  
 union  
 select 2,'MONDAY','08:00','10:00' from dual  
 union  
 select 3,'TUESDAY','06:00','09:00' from dual)  
 select * from t  
 pivot   
 (  
   max(start_time) as start_time, max(end_time) as end_time  
   for day in ('MONDAY' "Mon",'TUESDAY' "Tue",'WEDNESDAY' "Wed")  
 )  
 order by 1;  

#2. How to convert Columns into Rows. 
Source Data

Expected output

For versions prior to 11g. If you want null values also, then remove the where clause.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select decode(val,1,column1,2,column2,3,column3,4,column4,column5) display_val  
 from  t  
 cross join (select level val from dual connect by level <= 5)  
 where decode(val,1,column1,2,column2,3,column3,4,column4,column5) is not null;  

For 11g or above - using PIVOT/UNPIVOT. If you don't want the null values, remove 'include nulls'.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select val disp_val   
 from t   
 unpivot include nulls (val for col in(column1, column2, column3,column4,column5));   

Refer:
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 
Pivot and Unpivot

#3. How to get the first 100 Numbers in a query.

 select rownum val  
 from dual  
 connect by rownum <= 100;  

#4. How to get all the dates between 2 dates.
I have used 01-Jan-2015 as the start date and 31-Jan-2015 as the end date.

 select (to_date('01-Jan-2015','DD-MON-YYYY') + level - 1) date_val  
 from dual  
 connect by level <= (to_date('31-Jan-2015','DD-MON-YYYY') - to_date('01-Jan-2015','DD-MON-YYYY') + 1);  

#5. How to remove duplicates from one column.
Source Data

Expected Output

Normally this is job of the client / front end tool. But some reason you want this output in the query itself, you can use the below query.

 with t as  
 (select 'ABC Company' company,'India' branch,'11111' contact from dual union all  
  select 'ABC Company','Australia','22222'  from dual union all  
  select 'ABC Company','America'  ,'33333'  from dual union all  
  select 'XYZ Company','Australia','000000' from dual union all  
  select 'XYZ Company','America'  ,'111111' from dual  
 )  
 select  (case when row_number() over (partition by company order by branch desc) = 1  
              then company  
         end) company  
        ,branch  
        ,contact  
 from t  

#6. How to get the cumulative total.
Source Data

Expected Output

The data has to be sorted with the ITEM_NO and then with the Date and the Cumulative sum for each Item. 

 with t as (  
  select 1 item_no,to_date('2015-10-30','YYYY-MM-DD') inv_date ,30  amt from dual union  
  select 2 item_no,to_date('2015-10-23','YYYY-MM-DD') inv_date ,100 amt from dual union  
  select 2 item_no,to_date('2014-10-23','YYYY-MM-DD') inv_date ,70  amt from dual union  
  select 1 item_no,to_date('2014-09-10','YYYY-MM-DD') inv_date ,50  amt from dual union  
  select 1 item_no,to_date('2013-08-17','YYYY-MM-DD') inv_date ,40  amt from dual  
 )  
 select item_no,to_char(inv_date, 'DD-Mon-YYYY') invoice_date,amt amount  
   ,sum(amt) over (partition by item_no order by item_no,inv_date) as cumulative_amount  
 from t  
 order by item_no, inv_date;  

Feel free to point out if anything is missing/wrong in this blog.

Forms & Reports: Short key to generate .rep file in Report Builder 10g and .fmx file in Forms Builder 10g

To generate the .fmx file from Forms Builder 10g, you can use the below keyboard shortcut,

<Ctrl> + T

To generate the .rep file from Report Builder 10g, you can use the below keyboard shortcut,

<Alt> + F and then R

To compile a Form or Report, you can use the below keyboard shortcut,

<Ctrl> + <Shift> + K



Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Sample code for UTL_FILE

In this post I will a sample code to write to a file from PL/SQL.

• Create a folder/directory (ex:- C:\test )manually in the file system.
• Create a directory object in the database by connecting as SYSDBA.

 CONN / as SYSDBA;  
 create or replace directory TEST as 'C:\test';  
 

• Grant execute privilege on UTL_FILE to the user/schema (ex:- HR) you are going to use, using SYSDBA.

 connect / as SYSDBA;  
 grant execute on utl_file to HR;  

• Now you can use the below code to write a file into that directory.

 declare
      v_file   utl_file.file_type;  
      v_line   varchar2(1000);  
      v_file_name constant varchar2(15) := 'aj_test.txt';  
 begin  
      v_file := utl_file.fopen('TEST', v_file_name, 'w', 5000);
  
      v_line := 'This is the first line!!!!';  
      utl_file.put_line(v_file, v_line);  

      v_line := 'This is the last line!!!!';  
      utl_file.put_line(v_file, v_line);  

      utl_file.fclose(v_file);  
 exception  
      when others then  
           raise;  
 end;  

Refer:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS069

Feel free to point out if anything is missing/wrong in this blog.

BI Publisher: Format Date in XML/BI Publisher

I have seen few questions in OTN on formatting Date in BI Publisher. One of the responses given in one of the thread looks like something which is not available easily(atleast for me) on googling. So I will post the solution here as it might help someone.

Refer : Extended Function Support in RTF Templates
OTN Thread : https://community.oracle.com/message/13125600#13125600

SQL Statement or XSL Expression	Usage	Description
format_date()	<?xdoxslt:format_date(./AnyDate,'yyyy-MM-dd','MM/dd/yyyy', $_XDOLOCALE, $_XDOTIMEZONE)?>	Reads date in one format and creates in another format.

First one ('yyyy-MM-dd') is the output format and the second one ('MM/dd/yyyy') is the input format.

Sample XML:

 <INVOICES>  
      <INVOICE_DETAILS>  
           <INVOICE_DATE1>13-Jun-2015</INVOICE_DATE1>  
           <INVOICE_DATE2>13/06/2015</INVOICE_DATE2>  
           <INVOICE_DATE3>06/13/2015</INVOICE_DATE3>  
      </INVOICE_DETAILS>  
      <INVOICE_DETAILS>  
           <INVOICE_DATE1>13-Jan-2015</INVOICE_DATE1>  
           <INVOICE_DATE2>13/01/2015</INVOICE_DATE2>  
           <INVOICE_DATE3>01/13/2015</INVOICE_DATE3>  
      </INVOICE_DETAILS>  
 </INVOICES>  

Sample RTF Code:

 <?xdoxslt:format_date(INVOICE_DATE1,'dd-MMM-yyyy HH:mm:ss','dd-MMM-yyyy',$_XDOLOCALE,$_XDOTIMEZONE)?>  

 <?xdoxslt:format_date(INVOICE_DATE2,'dd-MMM-yyyy HH:mm:ss','dd/MM/yyyy',$_XDOLOCALE,$_XDOTIMEZONE)?>  

 <?xdoxslt:format_date(INVOICE_DATE3,'dd-MMM-yyyy HH:mm:ss','MM/dd/yyyy',$_XDOLOCALE,$_XDOTIMEZONE)?>  

Output:

Few other helpful blogs on this topic:

Formatting Date in Oracle XML(BI) Publisher

How to keep your “dates” from going wild!

Feel free to point out if anything is missing/wrong in this blog

OAF: How to invoke Javascript in Oracle Applications Framework

In this post, I will show few options to invoke javascript from OA Framework pages.

Before showing the options, I will just copy paste the note relating to javascript from the OAF Developer Guide.

"UIX and the OA Framework are rapidly adding new features to provide a more interactive user experience (partial page rendering, automatic table totaling, and so on). You are certainly encouraged to leverage these features as they are released, however, you should not attempt to implement them yourself before they're ready. 

As a customer, you may add your own Javascript code to OA Framework applications through controller customizations using setter methods for Javascript events that are exposed on form web beans such as check boxes, text inputs, and so on. However, as with any customization, Oracle does not support or entertain bugs on such custom Javascript usage. As a customer, it is your responsibility to certify your custom Javascript code, ensuring that it does not break the functionality of the page and is compliant with usability, security and browser standards."

• Specify the javascript code directly on the bean.

In the below example I have specified a javascript alert on the onLoad event of the OABodyBean.

 OABodyBean bodyBean = (OABodyBean) pageContext.getRootWebBean();  
 String javaScriptStr = "javascript:alert('Javascipt invoked on PageLoad');";  
 bodyBean.setOnLoad(javaScriptStr);  

• Attach the javascript function into the page using a RawTextBean.

We create a OARawTextBean in the page declaratively/programmatically and attach a javascript method as the Text property of the Bean. You can attach any number of javascript functions.Then you can call the methods in that anywhere in the page. In the below example I am calling the js function from a Button click.

 String jsStr = "<script>                                          "+  
                "    function submitCustomOnClick()                "+  
                "    {                                             "+  
                "       alert('Custom On Click invoked [RawText]');"+  
                "    }                                             "+  
                "</script> " ;  
 OARawTextBean rawTextBean = (OARawTextBean)createWebBean(pageContext, "RAW_TEXT", null, null);  
 rawTextBean.setText(jsStr);  
 webBean.addIndexedChild(rawTextBean);
  
 OAButtonBean buttonBean = (OAButtonBean)webBean.findChildRecursive("jsButton");  
 buttonBean.setOnClick("submitCustomOnClick();");  

• Attach the javascript function into the page using putJavaScriptFunction() method.

You can attach one javascript function to the page using this function. Once added to the page, you can call from any bean in the page.

 String customJSFunction = "function customJSFunc(){ alert('Custom On Click invoked [putJavaScriptFunction]'); }";  
 pageContext.putJavaScriptFunction("customJSFunc",customJSFunction);
  
 OAButtonBean buttonBean = (OAButtonBean)webBean.findChildRecursive("jsButton");  
 buttonBean.setOnClick("customJSFunc();");  

• Create a .js file and attach the file to the page using putJavaScriptLibrary() method.

If you need to use many javascript functions, or you want to use same function in multiple pages, then you can create a .js file and put all the functions inside this file. You need to place this file under $OA_HTML. You can create a subfolder inside the $OA_HTML and put the file(s) inside that. You can then specify the file using the putJavaScriptLibrary() function.

The above file CustomJavaScript.js placed in a folder called customJS under $OA_HTML.

 pageContext.putJavaScriptLibrary("CustomJSLibs", "customJS/CustomJavaScript.js");  
 OAButtonBean buttonBean = (OAButtonBean)webBean.findChildRecursive("jsButton");  
 buttonBean.setOnClick("methodFromJSFile();");  

• You can also call javascript using the setAttributeValue, ex :- ON_CLICK_ATTR

 OAButtonBean buttonBean = (OAButtonBean)webBean.findChildRecursive("jsButton");  
 buttonBean.setAttributeValue(oracle.cabo.ui.UIConstants.ON_CLICK_ATTR, "alert('Custom On Click invoked [ON_CLICK_ATTR]');" );  

Related: Mukul's Oracle Technology Blog: JavaScript In OA Framework

Feel free to point out if anything is missing/wrong in this blog

OAF: How to Capture LOV event in Oracle Application Framework

In this post I will show a sample code to capture the LOV event in processFormRequest of controller in OA Framework.

To get the source LOV ID, you could use 2 methods.

• pageContext.getLovInputSourceId();(This method cannot be used to get the lovInputsourceId, if the LOV is inside a table/advancedTable region and you want to use this value to get the return Items using getLovResultsFromSession() .) 
• pageContext.getParameter(SOURCE_PARAM);(This method will not return the exact lov Id when the lov is placed inside a table/advanced table region. Refer the last section for details.)

To handle the lov event (IdLov) in the above page structure, use the below code:

 if(pageContext.isLovEvent())  
 {  
      String lovInputSourceId = pageContext.getLovInputSourceId();  
      if("IdLov".equals(lovInputSourceId)) {  
           System.out.println("Inside isLovEvent of IdLov");  
      }  
 }  

If you want to get the return values from the lov (if the lov is not inside a table/advancedTable) you can use the below code. If the lov is inside table/advancedTable, Refer the last section.

 import java.util.Hashtable;  
 import java.util.Enumeration;  

 if(pageContext.isLovEvent())  
 {   
      String lovInputSourceId = pageContext.getLovInputSourceId();  
      if("IdLov".equals(lovInputSourceId)) {  
           System.out.println("Inside isLovEvent of IdLov");  
           Hashtable lovResults = pageContext.getLovResultsFromSession(lovInputSourceId);  
           if(lovResults!=null)  
           {  
                String userId =(String)lovResults.get("IdLov"); // "IdLov" is the Return Item specified in the lovMap  
                System.out.println("userId:" + userId);  
                  
                // To get all the Return Values the below code can be used
                Enumeration e = lovResults.keys();  
                while (e.hasMoreElements()) {  
                     String key  = (String) e.nextElement();  
                     String value = (String)lovResults.get(key);  
                     System.out.println(key + " : " + value);  
                }  
           }  
      }  
 }  

If you need to handle specific events on the LOV, the following options are also available.

When you enter a correct value in the Lov and tab out, the lov Windows will not popup. The above code works fine in this scenario. Along with that there is one more event which gets fired  'lovValidate'.

 if(pageContext.isLovEvent())  
 {   
      String lovEvent = pageContext.getParameter(EVENT_PARAM);  
      if("lovValidate".equals(lovEvent)) {  
           String lovInputSourceId = pageContext.getLovInputSourceId();  
           if("IdLov".equals(lovInputSourceId)) {  
                System.out.println("Inside isLovEvent of IdLov");  
           }  
      }  
 }  

When you click on the torch icon, the isLovEvent() returns false. But to handle this event you can use 'lovPrepare'.

 if("lovPrepare".equals(lovEvent)) {  
      String lovInputSourceId = pageContext.getLovInputSourceId();  
      if("IdLov".equals(lovInputSourceId)) {  
           System.out.println("Inside Lov Prepare of IdLov");  
      }  
 }  

When you select a value from the LOV popup, 'lovUpdate' event gets fired. isLovEvent() returns true in this case.

 if(pageContext.isLovEvent())  
 {   
      String lovEvent = pageContext.getParameter(EVENT_PARAM);  
      if("lovUpdate".equals(lovEvent)) {  
           String lovInputSourceId = pageContext.getLovInputSourceId();  
           if("IdLov".equals(lovInputSourceId)) {  
                System.out.println("Inside isLovEvent of IdLov");  
           }  
      }  
 }  

If the LOV is inside a table/advancedTable region the SOURCE_PARAM will return <region>:<lov_Item_id>:<row_num>.This is because the uniqueId of the field gets generated dynamically when the page gets loaded.  

 if(pageContext.isLovEvent())  
 {   
      String lovInputSourceId = pageContext.getParameter(SOURCE_PARAM);
      // In this example lovInputSourceId comes as 'AdvancedTableRN:EmailLov:0'
      if(lovInputSourceId != null && lovInputSourceId.contains(":EmailLov:")) {  
           System.out.println("Inside isLovEvent of EmailLov from AdvancedTable");  
      }
      lovInputSourceId = pageContext.getLovInputSourceId();
      // In this example lovInputSourceId comes as 'EmailLov'  
 }  

If you want to get the return values from the lov when the lov is inside a table/advancedTable, you can use the below code. This code will work for the normal lov also. 

 import java.util.Hashtable;  
 import java.util.Enumeration;  

 if(pageContext.isLovEvent())  
 {   
      String lovInputSourceId = pageContext.getLovInputSourceId();  
      if("EmailLov".equals(lovInputSourceId)) {  
           System.out.println("Inside isLovEvent of EmailLov from AdvancedTable");  
           Hashtable lovResults = pageContext.getLovResultsFromSession(pageContext.getParameter(SOURCE_PARAM));  
           if(lovResults!=null)  
           {  
                String emailAddress =(String)lovResults.get("EmailLov"); // "EmailLov" is the Return Item specified in the lovMap  
                System.out.println("emailAddress:" + emailAddress);  
                  
                // To get all the Return Values the below code can be used
                Enumeration e = lovResults.keys();  
                while (e.hasMoreElements()) {  
                     String key  = (String) e.nextElement();  
                     String value = (String)lovResults.get(key);  
                     System.out.println(key + " : " + value);  
                }  
           }  
      }  
 }  

Feel free to point out if anything is missing/wrong in this blog.

OAF: Exception in thread "main" java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole

When we try to import a file into Oracle Apps R12.2.4 using the import script which comes with the Jdeveloper 10.1.3.5.0, it is throwing an error message as below:

 Exception in thread "main" java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole  
     at oracle.jdbc.driver.DMSFactory.<clinit>(DMSFactory.java:51)  
     at oracle.jdbc.driver.PhysicalConnection.createDMSSensors(PhysicalConnection.java:3821)  
     at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:632)  
     at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:230)  
     at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)  
     at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)  
     at java.sql.DriverManager.getConnection(DriverManager.java:582)  
     at java.sql.DriverManager.getConnection(DriverManager.java:207)  
     at oracle.adf.mds.tools.util.ConnectUtils.getDBConnection(ConnectUtils.java:166)  
     at oracle.jrad.tools.xml.importer.XMLImporter.importDocuments(XMLImporter.java:232)  
     at oracle.jrad.tools.xml.importer.XMLImporter.main(XMLImporter.java:88)  
 Caused by: java.lang.ClassNotFoundException: oracle.dms.console.DMSConsole  
     at java.net.URLClassLoader$1.run(URLClassLoader.java:202)  
     at java.security.AccessController.doPrivileged(Native Method)  
     at java.net.URLClassLoader.findClass(URLClassLoader.java:190)  
     at java.lang.ClassLoader.loadClass(ClassLoader.java:307)  
     at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)  
     at java.lang.ClassLoader.loadClass(ClassLoader.java:248)  
     ... 11 more  

Cause:
The jar file which has the oracle.dms.console.DMSConsole is not getting set in the class path.

I tried to set this jar file in the class path using the CLASSPATH environment variable (in several ways), but didn't get it working. Finally I modified the import.bat file and it worked. So I will mention the steps here.

Fix:
Open the import.bat file which is available under C:\.....\jdevbin\oaext\bin\import.bat.
Modify the below line  (line no: 121)

 set CLASSPATH=%JRAD_ROOT%\jdev\appslibrt\ebsuix.jar  

to

 set CLASSPATH=%JRAD_ROOT%\jdev\appslibrt\ebsuix.jar;%JRAD_ROOT%\lib\dms.jar  

Save the file and try the import again. 

Note:- If it didn't work, just try to restart the machine and try again.

Related blog: OAF: Import/Export Page/Personalization/Substitution using XMLImporter/JPXImporter in Oracle Application Framework

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: fnd_profile.value('ORG_ID') returns null

FND_PROFILE.value('ORG_ID') will return value only if the 'MO: Operating Unit' is set for the responsibility used.

If you need to get the ORG_ID in a MOAC enabled instance, 
use FND_GLOBAL.org_id or MO_GLOBAL.get_current_org_id instead.

From the reference given below:

"For MOAC-enabled instances, FND_PROFILE.VALUE('ORG_ID') will no longer convey the correct org context for a given session.  The customer *has to change* all FND_PROFILE.VALUE('ORG_ID') calls to either use MO_GLOBAL.get_current_org_id() (which is highly recommended by the MO Team or FND_GLOBAL.ORG_ID().  Do NOT use FND_PROFILE.VALUE('ORG_ID') for any MOAC-enabled instances."

To get the org_id in OA Framework code:

use appModule.getOADBTransaction().getOrgId() or pageContext.getOrgId(). 

instead of pageContext.getProfile("ORG_ID").

Reference: 
After Patch 8222387 Why Does Fnd_profile.Value('Org_id') Return No Value (Doc ID 784279.1)
Conc Programs Using Parameter Fnd_profile.Value(Org_id) Get Apps-Fnd-01436 (Doc ID 784489.1)


Feel free to point out if anything is missing/wrong in this blog.

OAF: How to create a PVO and use that for Spel Binding in Oracle Application Framework

In this post I will give an example to show how to create a PVO ("Application Properties" View Object) and use the attributes to change the properties of the bean dynamically. I will give same method to set the PVO Attribute values. This method can be used when there is only 1 attribute to be set or if there are multiple attributes.

Scenario:
I have created 2 tables in the page (say Table1 and Table2). I need to show Table1 when the page loads and hide Table2. Based on some conditions, I will need to change the Tables in screen dynamically. 

• Create a PVO with 2 attributes (1 for each table). Set the value of the attribute appropirately.
• Use Spel binding to associate the attribute to the table. Refer: OAF: Spel Binding

Create PVO with 2 attributes (I have created one additional Id column also, which can be set as the Key Attribute). Make sure that you set the Updatable property to 'Always'.

Add this VO to the Application Module and then add the below methods in the AMImpl.

 import oracle.jbo.domain.Number;  

 public void intializeXXCustomPVO()  
 {  
      OAViewObjectImpl xxCustomPVO = getXXCustomPVO1();  
      if(xxCustomPVO.getRowCount() == 0 )  
      {  
           Row xxCustomPVORow = xxCustomPVO.createRow();  
           xxCustomPVORow.setNewRowState(Row.STATUS_INITIALIZED);        
           xxCustomPVORow.setAttribute("Id",new Number(-1));  
           xxCustomPVO.insertRow(xxCustomPVORow);  
      }  
 }  

 public void setXXCustomPVOValues(String[] name,String[] val)  
 {  
      OAViewObjectImpl xxCustomPVO = getXXCustomPVO1();  
      xxCustomPVO.setRangeSize(-1);  
      Row row = xxCustomPVO.getRowAtRangeIndex(0);  
      for(int i = 0; i < name.length; i ++)  
      {  
           if("Y".equals(val[i]))  
           {  
                row.setAttribute(name[i],Boolean.TRUE);  
           }  
           else  
           {  
                row.setAttribute(name[i],Boolean.FALSE);  
           }  
      }  
 }  

Write the below code in the processRequest of the Controller. This will create a Row in the PVO and initialize the PVO attributes.

 import java.io.Serializable;  
 import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  

 OAApplicationModuleImpl appModule = (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);  
 appModule.invokeMethod("intializeXXCustomPVO");
  
 String[] name = {"RenderAttr1","RenderAttr2"};  
 String[] val  = {"Y","N"};
  
 Serializable[] params = {name, val};  
 Class[] paramTypes    = {String[].class, String[].class};
  
 appModule.invokeMethod("setXXCustomPVOValues", params, paramTypes);  

Write the below code in the processFormRequest based on your condition.Change the values of the val[] appropriately, based on your use case.

 OAApplicationModuleImpl appModule = (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);
  
 String[] name = {"RenderAttr1","RenderAttr2"};  
 String[] val  = {"N","Y"};
  
 Serializable[] params = {name, val};  
 Class[] paramTypes    = {String[].class, String[].class};
  
 appModule.invokeMethod("setXXCustomPVOValues", params, paramTypes);  

You can use the below syntax to set the Rendered property of the bean.
   ${oa.XXCustomPVO1.RenderAttr1}

   ${oa.XXCustomPVO1.RenderAttr2}


Feel free to point out if anything is missing/wrong in this blog.