Split Function with Regular Expression in Oracle 10g

Few days before, I was looking for split function in oracle (like java/C#), but did not find any. Then at first I find that Oracle 10g introduce regular expression support for string operation. You can visit http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm to know about this.

Then I utilize oracle pipelined function (to know more you can visit http://www.oracle-developer.net/display.php?id=207) and build my own Split(str varchar2, regex_of_token varchar2) which takes two arguments. First of which is the input string and the second one is the regular expression of the token. The two steps to build the method are -

1. Build a custom collection type for return type -
   

   CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024);

2. Then the function body -
   

   CREATE OR REPLACE FUNCTION SYS.split
   ( str IN varchar2, regex in varchar2)
   RETURN str_array PIPELINED IS
   next_token varchar2(1024) := '';
   token_index number := 1;
   BEGIN
   LOOP
       select regexp_substr(str,regex,1,token_index)
          into next_token from dual;
       exit when (next_token is null);
       pipe row(next_token);
       token_index := token_index+1;
   END LOOP;
   RETURN;
   END;
   /

Then to test this function, call the function in the following way-

select * from table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','([0-9]+(\.[0-9]+)?|([a-z]|[A-Z])+)'))

The first argument is sample string and second one is regular expression for number or alphabet but not alpha-numeric. If you want to split the string around ',' then the query will be -

select * from
table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','[^,]+'))

Drop table if it exists in Oralce

If you want to drop a table which is not in existence then surely you will get an error - table or view does not exist. If you put this type of code in a stored procedure then this error halt the whole procedure execution.

So, the better solution for this is to check whether the table exists or not. If it exists then drop that. The logic can be put in a stored procedure (not in a function as function cannot perform DML or DDL) then we can call this procedure from other procedure and we don't have to duplicate this code. So the procedure will be very simple like the following -

CREATE OR REPLACE procedure drop_table_ifexist(tableName varchar2) IS
tableCount NUMBER := 0;
BEGIN
select count(*) into tableCount
from user_tables
where table_name = upper(tableName);

--oracle store all table names in upper cases.

if(tableCount > 0) then
execute immediate 'drop table '||tableName;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END drop_table_ifexist;
/

This procedure will drop a table of its own if the table exists. Table name can be queried from two other tables user_objects (collection of all objects owned by this user) and all_objects (collection of all objects privileged by this user).

Schedule oralce procedure through crontab

In previous post - Schedule Java application through crontab, I mentioned that the process of scheduling other application is same as Java application.

To accomplish this, you have to set at least two environment variables. The first one is obviously the PATH. The other is ORACLE_HOME. You need to set ORACLE_SID if you want to use a database as default or if you don't have permission in tnsnames.ora file.

PATH=/usr/local/oracle/product/11.2.0/dbhome_1/bin
ORACLE_HOME=/usr/local/oracle/product/11.2.0/dbhome_1

export PATH
export ORACLE_HOME

These should be sufficient if you have permission in tnsnames.ora. Otherwise, the database should be installed in your workstation and ORACLE_SID should be settled to database service name.

Schedule Java Application through crontab

When we want to run java executable (java, javac, etc.) directly (without mentioning full path) then you have to set the path variable of OS. In linux, you can set path by update the path variable in bash profile. This can be done easily by writing the following commands -

PATH=/usr/local/jdk1.6.0/bin:
export PATH

For detail, you can see http://java.sun.com

Unfortunately, the script you schedule in cron will not get this path which you set in bash profile. So to run your script through cron, you have append the above commands in you script too. That is, you script should contain command like this -

PATH=/usr/local/jdk1.6.0/bin:
export PATH
java ...

It is worth mentionable here - this setting of path variable is applicable for all path setting not only for java path settings. For example, if you have to set oracle path for cron, you have to follow the same steps.

For setting java classpath you can see following article -
http://linuxlab.dk/tipsntricks/classpath