Convert Comma separated String to Rows in Oracle SQL

Many times we need to convert a comma separated list of terms in a single string and convert it rows in SQL query.

for example

 India, USA, Russia, Malaysia, Mexico

Needs to be converted to:

 Country
 India
 USA
 Russia
 Malaysia
 Mexico

The following SQL script can help in this:

just replace the required values with your string and your delimiter.

Apache Commons DbUtils Mini Wrapper

This is a very small DB Connector code in Java as a wrapper class to Apache DBUtils.

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

  • No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
  • Automatically populate Java Bean properties from Result Sets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the Result Set can be represented by one fully populated bean instance.

DbUtils is designed to be:

  • Small – you should be able to understand the whole package in a short amount of time.
  • Transparent – DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast – You don’t need to create a million temporary objects to work with DbUtils.

DbUtils is not:

  • An Object/Relational bridge – there are plenty of good O/R tools already. DbUtils is for developers looking to use JDBC without all the mundane pieces.
  • A Data Access Object (DAO) framework – DbUtils can be used to build a DAO framework though.
  • An object oriented abstraction of general database objects like a Table, Column, or Primary Key.
  • A heavyweight framework of any kind – the goal here is to be a straightforward and easy to use JDBC helper library.

Wrapper:

Oracle ADF Features

Oracle Application Development Framework (Oracle ADF) is an advanced Rapid Application Development(RAD) Framework for J2EE applications mainly targeted for B2B Applications.

It is a proprietary software maintained by Oracle in 3 flavors:

Features-

  • RAD Framework with most commonly used components of any Front End Application available as inbuilt feature
  • Model-View-Controller (MVC) design pattern and offers an integrated solution that covers all the layers of this architecture with solution to such areas as:
    • ORM and Data Persistence
    • Reusable Task-flow approach,
    • Rich Web user interface elements,
    • Data binding to UI,
    • Security
    • Customization via sandboxes etc
  • Separation of Business Logic implementation details, via metadata and use of this metadata-driven architecture enables developers to focus on the business logic and user experience.
  • Drag-n-Drop features, via JDeveloper IDE, provides the ease to use and customize.
  • ADF Skin Editor: New ADF Skin editor for visually creating and modifying the appearance of ADF Faces applications.(JDeveloper 11G and above)
  • JSF 2.0: JSF 2.0 support in the IDE and for ADF applications. (JDev 11g R2 release)
  • ADF Faces and Data Visualization Tools (DVT) support touch gestures for interactivity on the touch-enabled devices, such as touch support for drag and drop, tooltips, data tips, and context menus.
  • ADF Faces and DVT components are certified on iPad. Additionally, ADF DVT Graph and Gauge components support rendering in HTML5.
  • MDS is metadata service that is used to store metadata information. When you use ADF, it generates lot of internal files about its own components and DB binding details. All these are stored internally by ADF into MDS.
  • ADS (Active Data service)- This is used in ADF to push any data changes automatically from the back end into the portal layer. This is different from Ajax which is used to send request from UI to data service layer.

Reset Sequence in Oracle SQL back to 0

Some times we need to reset the sequence values in the database back to 0.
Here is a small procedure to reset any sequence.

create or replace
procedure reset_sequence(p_seq in varchar2)
is
l_value number;
begin
-- Select the next value of the sequence

execute immediate
'select ' || p_seq ||
'.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence,
-- with value = the current value of the sequence

execute immediate
'alter sequence ' || p_seq ||
' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to
-- take its current value back to 0

execute immediate
'select ' || p_seq ||
'.nextval from dual' INTO l_value;

-- Set the increment back to 1

execute immediate
'alter sequence ' || p_seq ||
' increment by 1 minvalue 0';
end;
/

Whatever sequence you wish to reset, call the procedure as:

begin
    reset_sequence('SEQ_1');
    reset_sequence('SEQ_2');
    reset_sequence('SEQ_3');
    reset_sequence('SEQ_4');
end;
/

How to create a Custom ADF Component

In this tutorial, we will create a custom ADF Component.

Create new files and package structure as shown:

Structure
Create a Custom ADF Component

Source Code of the required files is as below:

Final Output:

final
Create a Custom ADF Component

How to add inbuilt task flows in WebCenter Portal

In this tutorial, I will add an internal task flow to Web Center Portal. For demo, i will use the login task flow of the Web Center Portal.

Retrieve GET parameters from URL in Managed Bean in Oracle ADF

For obtaining parameter from URL in java code in Oracle ADF use the below code
URL:
http://127.0.0.1:7101/Application-ViewController-context-root/faces/page.jspx?paramName=Testing123
Use the code:
String test = FacesContext.
getCurrentInstance().getExternalContext().getRequestParameterMap().get("paramName");

How to create new Portal in WebCenter Portal

In this tutorial, we will create a small portal application in the Oracle WebCenter Portal.

How to start up Oracle WebCenter Portal

In this small tutorial we just start up the Web Center Portal and Navigate around.

Prerequisite: Installation should be complete

Steps:

  1. Navigate to Oracle Middleware Home(in my case C:/Oracle/mw10.3.6)
  2. Navigate to the Application Domain(C:Oraclemw10.3.6user_projectsdomainsportal_domain)
  3. Execute startWeblogic.cmd(or .sh in case of linux)
  4. Once the Server comes to running state, navigate to bin folder in the same folder and execute the following command

    startManagedWeblogic.cmd WC_Spaces http://localhost:7001

  5. The script would ask you about the username and password of the weblogic user.
  6. Once in running state, open browser and enter the following URL:

    http://localhost:8888/webcenter

Result: