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:

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;
/

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

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;
/

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