SQL to convert Comma Separated String to Rows

Convert Comma separated List to rows

SELECT TRIM( SUBSTR ( txt , INSTR (txt, ',', 1, level ) + 1 , INSTR (txt, ',', 1, level+1 ) - INSTR (txt, ',', 1, level) -1 ) ) AS u
FROM
  ( SELECT ','||'comma,separated,text'||',' AS txt FROM dual
  )
  CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1;

SQLResult

Source

Programmatically Populate View Objects Based on ArrayList of custom Bean Class In Oracle ADF

Lets say you have a Custom Bean Class EmpData defined as:

EmpData

And you wish to use the same as a View Object, Then below is the method for the same:

First create a Proxy View Object Class:
ProxyDataSourceObject

Now Create a View Object where it is populated programmatically:
Programmatic_VO

Programmatic_VO_attribs

Programmatic_VO_Java

EmpDataCustomVO

Now Edit the View Object Impl Class:

EmpDataCustomVOImpl

Now you can easily use the View Objects as a normal View Object to be used in View Controller.

Cons: The getDataFromService method will be called again and again whenever the View Object is needed. So try to keep the source separate and apply some tuning logic to the same in order to improve performance of system.

Original Referrence Source

SQL Method to generate random Date or text in Oracle SQL

This function will provide a random string with uppercase and lowercase characters

create or replace function random_str(v_length number) return varchar2 is
    my_str varchar2(4000);
begin
    for i in 1..v_length loop
        my_str := my_str || dbms_random.string(
            case when dbms_random.value(0, 1) < 0.5 then 'l' else 'x' end, 1);
    end loop;
    return my_str;
end;
/
 
select random_str(30) from dual;

Source: StackOverflow Question

Function to get Random Data in range of 2 dates:

CREATE OR REPLACE FUNCTION random_date(
    startdate date,
    enddate   date)
  RETURN DATE
IS
  my_str DATE;
BEGIN
  my_str:=TO_DATE( TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(to_date(startdate),'J') ,TO_CHAR(to_DATE (enddate),'J') ) ),'J' );
  RETURN my_str;
END;
/

ADFUtils Class: Best for Accessing Components in from the Managed Bean

import java.util.Iterator;
import java.util.Map;

import javax.el.ELContext;
import javax.el.ExpressionFactory;
import javax.el.MethodExpression;
import javax.el.ValueExpression;

import javax.faces.application.ViewHandler;
import javax.faces.component.UIComponent;
import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;

import oracle.adf.model.BindingContext;
import oracle.adf.model.DataControlFrame;
import oracle.adf.view.rich.context.AdfFacesContext;

import org.apache.myfaces.trinidad.render.ExtendedRenderKitService;
import org.apache.myfaces.trinidad.util.Service;


public class ADFUtils {
    private ADFUtils() {
    }

    /**
     * This method returns any UI component whose id is passed.
     * @param id the id of the component
     * @return the UIComponent
     */
    public static UIComponent getUIComponent(String id) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        if (facesContext != null) {
            UIViewRoot viewRoot = facesContext.getViewRoot();
            if (viewRoot != null) {
                return viewRoot.findComponent(id);
            }
        }
        return null;
    }


    /**
     * Refreshes the page/viewRoot.
     * @return the viewId of the current page
     */
    public static String refreshViewRoot() {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        String viewId = facesContext.getViewRoot().getViewId();
        ViewHandler viewHandler =
            facesContext.getApplication().getViewHandler();
        UIViewRoot viewroot = viewHandler.createView(facesContext, viewId);
        viewroot.setViewId(viewId);
        facesContext.setViewRoot(viewroot);
        return viewId;
    }

    /**
     * Invokes the specified function with the specified params.
     * @param functionName the name of the js function
     * @param params the params to be passed
     */
    public static void runJsFunction(String functionName, String params) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ExtendedRenderKitService extRenderKitSvc =
            Service.getRenderKitService(facesContext,
                                        ExtendedRenderKitService.class);
        extRenderKitSvc.addScript(facesContext,
                                  functionName + "('" + params + "');");
    }

    /**
     * Invokes the specified function with the specified params.
     * @param functionName the name of the js function
     */
    public static void runJsFunction(String functionName) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ExtendedRenderKitService extRenderKitSvc =
            Service.getRenderKitService(facesContext,
                                        ExtendedRenderKitService.class);
        extRenderKitSvc.addScript(facesContext, functionName + "();");
    }

    // Get the Component based on given UIComponent ID and refresh the component

    public static void refreshComponent(String pComponentID) {
        UIComponent component = findComponentInRoot(pComponentID);
        refreshComponent(component);
    }


    // Get Faces Context, Get Root Component, Find the given Component From the root component

    public static UIComponent findComponentInRoot(String pComponentID) {
        UIComponent component = null;
        FacesContext facesContext = FacesContext.getCurrentInstance();
        if (facesContext != null) {
            UIComponent root = facesContext.getViewRoot();
            component = findComponent(root, pComponentID);
        }
        return component;
    }


    // Refresh the Component

    public static void refreshComponent(UIComponent component) {
        if (component != null) {
            AdfFacesContext.getCurrentInstance().addPartialTarget(component);
        }
    }

    // Get the specific  component from a root component tree.

    public static UIComponent findComponent(UIComponent root, String id) {
        if (id.equals(root.getId()))
            return root;

        UIComponent children = null;
        UIComponent result = null;
        Iterator childrens = root.getFacetsAndChildren();
        while (childrens.hasNext() && (result == null)) {
            children = (UIComponent)childrens.next();
            if (id.equals(children.getId())) {
                result = children;
                break;
            }
            result = findComponent(children, id);
            if (result != null) {
                break;
            }
        }
        return result;
    }
  
    /**
     * Programmatic evaluation of EL.
     *
     * @param el EL to evaluate
     * @return Result of the evaluation
     */
    public static Object evaluateEL(String el) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ELContext elContext = facesContext.getELContext();
        ExpressionFactory expressionFactory =
            facesContext.getApplication().getExpressionFactory();
        ValueExpression exp =
            expressionFactory.createValueExpression(elContext, el,
                                                    Object.class);

        return exp.getValue(elContext);
    }

    /**
     * Programmatic invocation of a method that an EL evaluates to.
     * The method must not take any parameters.
     *
     * @param el EL of the method to invoke
     * @return Object that the method returns
     */
    public static Object invokeEL(String el) {
        return invokeEL(el, new Class[0], new Object[0]);
    }

    /**
     * Programmatic invocation of a method that an EL evaluates to.
     *
     * @param el EL of the method to invoke
     * @param paramTypes Array of Class defining the types of the parameters
     * @param params Array of Object defining the values of the parametrs
     * @return Object that the method returns
     */
    public static Object invokeEL(String el, Class[] paramTypes,
                                  Object[] params) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ELContext elContext = facesContext.getELContext();
        ExpressionFactory expressionFactory =
            facesContext.getApplication().getExpressionFactory();
        MethodExpression exp =
            expressionFactory.createMethodExpression(elContext, el,
                                                     Object.class, paramTypes);

        return exp.invoke(elContext, params);
    }

    /**
     * Sets a value into an EL object. Provides similar functionality to
     * the  tag, except the from is
     * not an EL. You can get similar behavior by using the following...

     * setEL(to, evaluateEL(from))
     *
     * @param el EL object to assign a value
     * @param val Value to assign
     */
    public static void setEL(String el, Object val) {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ELContext elContext = facesContext.getELContext();
        ExpressionFactory expressionFactory =
            facesContext.getApplication().getExpressionFactory();
        ValueExpression exp =
            expressionFactory.createValueExpression(elContext, el,
                                                    Object.class);

        exp.setValue(elContext, val);
    }
}

Source: Forum

Alternate Link:Alternate, includes Application Module and Binding Utils as well

How to show Popup programmatically in Oracle ADF

When trying to open a Popup on JSF/JSFF page programmatically through Managed Bean, use the following method and pass the ID of the popup to the method:

 public void openPopup(String popupId) {
         ExtendedRenderKitService erkService =
         Service.getService(FacesContext.getCurrentInstance().getRenderKit(),
         ExtendedRenderKitService.class);
         erkService.addScript(FacesContext.getCurrentInstance(),
         "var hints = {autodismissNever:true}; " +
         "AdfPage.PAGE.findComponent('" + popupId +
         "').show(hints);");
 }
OR

On the button , write a method on the action event.
 public void ShowPopup(ActionEvent actionEvent) {
          RichPopup.PopupHints hints = new RichPopup.PopupHints();
          this.getPopUp().show(hints);
 
 }

If you want to hide the popup..you can write like this

  public void HidePopup(ActionEvent actionEvent) {
           RichPopup.PopupHints hints = new RichPopup.PopupHints();
           this.getPopUp().hide();
 
  }

General use is when you wish to call the a popup from a button press but first want to execute some actions/processings in the screen on the button click.

In the button on the JSFF, just add a actionListener and then call the openPopup() method from the action listener or any other method you wish to.

Source: Forum

How to create dynamic trigger in Oracle?

Thanks to http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:59412348055

Problem:

I’m trying to create a generic before update trigger
which will compare all :old.column_values to all
:new.column_values. If the column_values are different, then I
would like to log the change to a separate table. When I try to
compile :old., Oracle return an
“(1):PLS-00049: bad bind variable ‘NEW.” Can you recommend a
dynamic way to accomplish this? Thanks in advance.

Solution:

:new and :old are like bind variables to the trigger, they are not ‘regular’ variables.
you cannot dynamically access them, only ‘statically’.

I suggest you consider writing a stored procedure or sqlplus script to write a trigger
that statically references the new/old values. For example, if you wanted to save in a
table the time of update, who updated, table updated, column modified and new/old values,
you could code a sql
plus script like:

--------------------------------------------------------------------
create table audit_tbl
( timestamp date,
who varchar2(30),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000)
)
/

create or replace package audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 );

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date );

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number );
end;
/


create or replace package body audit_pkg
as

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;

end audit_pkg;
/
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-------------

That will build the generic table and package plus generate a trigger that would look
like:

SQL> @thatscript dept


create or replace trigger aud#dept
after update on dept
for each row
begin
audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME);
audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC);
end;
/

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

I was getting the above exception when I was trying to set a CLOB data in the database using java.

More understanding about this exception can be get from

http://www.coderanch.com/t/302117/JDBC/java/java-sql-SQLException-ORA-unsupported

The mistake I was doing and getting the same exception was
While preparing the PreparedStatement Object, i was passing the query like this

pstmt = conn.prepareStatement(sqlQuery.toString());  

And while executing it, again I was giving the query in the overloaded method.

// Please note that DO NOT USE pstmt.executeUpdate(String) overloaded method   
// That will give you this exception :
// java.sql.SQLException: ORA-03115: unsupported network datatype or representation
int rowsUpdated = pstmt.executeUpdate();

Use the executeUpdate method without arguments and not the one with a String argument

After making this change, I didn’t get this exception.

Exact root cause is still unknown to me as well.

If someone understands it better, please explain it to me as well.

Also to set the clob data in the database you can use the following

String xml= getXml();   
InputStream is = new ByteArrayInputStream(xml.getBytes());
pstmt.setAsciiStream(++psCount, is, xml.length());

How to fill a increasing values in a column of a table?

How to fill a increasing values in a column of a table?

Lets assume we have a table
Table name : test

Name Value
Yogesh 0
Yogesh 0
Yogesh 0
Yogesh 0
Yogesh 0
Suresh 0
Suresh 0

Requirement : to insert 1, 2, 3 …. corresponding to values Yogesh

Query to Update :

create sequence seq start with 1 increment by 1;
update test set Value=seq.nextval where Name='Yogesh';
commit;

Output :

Name Value
Yogesh 1
Yogesh 2
Yogesh 3
Yogesh 4
Yogesh 5
Suresh 0
Suresh 0

What do you think of the following query?

What do you think of the following query?

select keyword from v$reserved_words;

At first glance, it looks as if it is an invalid query.
But No, it is not an invalid query.
It gives you all the keywords that are being used in Oracle.
But MIND IT !!, you must have the access to this view to run this query, else you’ll get a message : Table or View Does not exist.

V$reserved_words is a view.

to_char and to_date function in oracle

Source : http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Therefore, when you issue the query
select b from x;

you will see something like:
B
———
01-APR-98

Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example,
SELECT TO_CHAR(b, ‘YYYY/MM/DD’) AS b
FROM x;

returns the result:
B
—————————————————————————
1998/04/01
The general usage of TO_CHAR is:
TO_CHAR(, ”)

where the  string can be formed from over 40 options. Some of the more popular ones include:
  , for example.

MM Numeric month (e.g.07)
MON Abbreviated month name (e.g.JUL)
MONTH Full month name (e.g.JULY)
DD Day of month (e.g.24)
DY Abbreviated name of day (e.g.FRI)
YYYY 4-digit year (e.g.1998)
YY Last 2 digits of the year (e.g.98)
RR Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM) Meridian indicator
HH Hour of day (112)
HH24 Hour of day (023)
MI Minute (059)
SS Second (059)