Query to convert Column data into a Row

The following query is very useful for converting the data in a single column into a row so the all the values can be extracted from a table into variables of different name. Using cursor it is difficult to get all the values from single column into variables of different name. 

Example of sample data available - 

Required format into which data needs to be converted -

Query which can be used for above purpose - 


SELECT  MAX(DECODE(level,1,regexp_substr(str,'[^,]+',1,level))) AS val1 ,
 MAX(DECODE(level,2,regexp_substr(str,'[^,]+',1,level))) AS val2 ,
 MAX(DECODE(level,3,regexp_substr(str,'[^,]+',1,level))) AS val3 ,
 MAX(DECODE(Level,4,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val4 ,
 MAX(DECODE(level,5,regexp_substr(str,'[^,]+',1,level))) AS val5 ,
 MAX(DECODE(level,6,regexp_substr(str,'[^,]+',1,level))) AS val6 ,
 MAX(DECODE(level,7,regexp_substr(str,'[^,]+',1,level))) AS val7 ,
 MAX(DECODE(level,8,regexp_substr(str,'[^,]+',1,level))) AS val8 ,
 MAX(DECODE(Level,9,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val9 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val10 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val11 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val12 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val13 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val14 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val15
   INTO l_value_id1 ,
 l_value_id2 ,
 l_value_id3 ,
 l_value_id4 ,
 l_value_id5 ,
 l_value_id6 ,
 l_value_id7 ,
 l_value_id8 ,
 l_value_id9 ,
 l_value_id10 ,
 l_value_id11 ,
 l_value_id12 ,
 l_value_id13 ,
 l_Value_Id14 ,
 l_value_id15
   FROM (
         SELECT Listagg (column_to_convert,',') Within Group (Order By column_to_convert) Str
    FROM Table_Name
   WHERE 1 = 1
   GROUP BY group_by_column
   ) Tmp

   CONNECT BY regexp_substr(str,'[^,]+',1,level) IS NOT NULL;

OAF - How to Display multiple errors?

Sometimes you have to show all the error messages at one shot,instead of showing the error messages one by one. In that case, we have to use bundled exception to show multiple error message. Below are the steps:

1. Add the import statements:

import com.sun.java.util.collections.ArrayList;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.common.MessageToken;

2. Declare an array list.

ArrayList errorMsg= new ArrayList();
3. Raise OAException.
for (int i=0; i< hMember.size(); i++)
{
MessageToken[] token = {
new MessageToken("USER", getOADBTransaction().getUserName()),
new MessageToken("NUM",hMember.getChildNumber())
};
errorMsg.add(new OAException((String)(getOADBTransaction().getMessage("XXCH","XXCH_NO_ACCESS",token))));
}
OAException.raiseBundledOAException(errorMsg);

OAF - Dynamically assigning VO and VO attributes

Below code sample search the component on a page. Replaces the default VO with new/custom VO. Then maps the VO attribute to the identified component.

OAMessageStyledTextBean oamessagestyledtextbean = (OAMessageStyledTextBean)oawebbean.findIndexedChildRecursive("ProjectNum");

oamessagestyledtextbean.setViewUsageName("VOInstanceName");

oaformvaluebean4.setViewAttributeName("XXProjectNum");