Create a virtual table with numbers

Sometimes I need a virtual table with increasing number.

So a table with contents:


For this purpose a number of implementations are used on projects I worked on.


For example:

* A real table with increasing number.

* select rownum on all_objects where rownum < 1000;

* A pipelined function that returns numbers from a for loop

* select rownum
from (SELECT 1 FROM dual GROUP BY CUBE(1,2,3,4,5,6) )
where rownum < 50;

But today a post on the Amis weblog ( gave me a more elegant solution using connect by level.

SELECT level
FROM dual

I will use that the next time.

XPath slow

I’m working on a SOA project that handles a lot of XML messages.
Every message that goes through the system will be logged.
To make it uniform all messages are transformed to SOAP messages,
and when receiving SOAP messages we need to get the important fields
from a message in order to log these.

I used the JAMon tool to monitor the performance of handling these messages, and it turned out that the parsing of XML with XPath and Java 5 took (relatively) much time. Parsing a message took 19ms on average. And we already cached the XPathExpression.
It’s a very simple XML message with 1 root element and 10 child elements.
I needed to extract the value of 8 of these elements.

I tried to rebuild the XPath expression with different methods for extracting fields from an XML document with the following results:
Traversing with DOM: 21 times as fast
Using JDOM: 17 times as fast
Using indexOf string manipulation: 881 times as fast!

So using indexOf to extract fields in XML documents can be much faster!

Calculating aggregates on a timestamp

Today I needed to use aggregates on the Oracle TIMESTAMP datatype.

We had a table DTM_MESSAGES with the columns received and responded,
both with datatype timestamp.

This table is used to measure the responsetime of a webservice.

When we substract these 2 columns with the query:

select received- responded from dtm_messages

We get a time-interval as response, not a number.

Because of this we can not use aggregate functions like sum or avg on the
difference between 2 timestamps.

select avg(received- responded) from dtm_messages

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL TO SECOND.

If we want to use an aggregate function, we need to convert this all to seconds.
Then we can use an aggregate function again.


select avg(
(extract(day from responded)-extract(day from received))*86400
+  (extract(hour from responded)-extract(hour from received))*3600
+   (extract(minute from responded)-extract(minute from received))*60                      +   (extract(second from responded) -extract(second from received))) avg_seconds        from dtm_messages

works fine

Java StringTokenizer

Today I discovered that StringTokenizer is considered a legacy class by Sun. It’s retained for compatibility reasons although its use is discouraged in new code. Sun recommends the use of the split method in the String class or using the java.util.regex package instead.

By using the split method you lose the option to include the seperator-character in the split set. For parsing EDI I wanted to split an EDI segment on seperator characters + or : but I want to make sure that the seperator characters are included in the split set, so I can check if the right seperator-character was used in the original file. With StringTokenizer this can easily be done by calling java.util.StringTokenizer tokenizer=new java.util.StringTokenizer (segment,”+:”,true);

Job scheduling from Oracle 10g with dbms_scheduler

In the old days we used DBMS_JOB to schedule jobs in the database.
For more complex scheduling a program like Redwood Scheduler was used.

In Oracle 10g Oracle introduced DBMS_SCHEDULER.
The DBMS_JOB package is now depricated and in Oracle 10g it’s only
provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not
be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed.
Also binary and shell-scripts can be scheduled.

The new scheduler has 3 main components:
– A schedule
– A program
– A job


If you have DBA rights you can do all the scheduling.
For administering job scheduling you need the priviliges belonging to
To create and run jobs in your own schedule you need the ‘CREATE JOB’

With DBMS_JOB you needed to set an initialization parameter to start a
job coordinator
background process. With Oracle 10g DBMS_SCHEDULER this is not needed
any more.

If you want to user resource plans and/or consumer groups you need to
set a system parameter:



Getting started quickly

To quickly get a job running, you can use code like this:

begin   dbms_scheduler.create_job(       job_name => 'DEMO_JOB_SCHEDULE'      ,job_type => 'PLSQL_BLOCK'      ,job_action => 'begin package.procedure(''param_value''); end; '      ,start_date => '01/01/2006 02:00 AM'      ,repeat_interval => 'FREQ=DAILY'      ,enabled => TRUE      ,comments => 'Demo for job schedule.'); end; /

This schedules a pl/sql block to be executed daily starting 1/1/2006
02:00 AM.

You can schedule things like this, but DBMS_SCHEDULER can reuse

You can build a schedule using components like program, schedule, job,
job class
and window. We will now discuss these components in detail.


The program component is a collection of metadata about what will be run by the scheduler.
This includes information such as the program name, the type of program, and information about
arguments passed to the program.

Code example

begin    dbms_scheduler.create_program (        program_name => 'DEMO_JOB_SCHEDULE'       ,program_type => 'STORED_PROCEDURE'       ,program_action => 'package.procedure'       ,number_of_arguments => 1       ,enabled => FALSE       ,comments => 'Demo for job schedule.');     Š   dbms_scheduler.define_program_argument (        program_name => 'DEMO_JOB_SCHEDULE'       ,argument_position => 1       ,argument_name => 'kol1'       ,argument_type => 'VARCHAR2'       ,default_value => 'default'     );     dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE'); end; /

The parameter program_type can have one of the following values:

dbms_scheduler also allows to execute shell scripts (Windows: *.bat
files) and


A schedule defines the frequence and date/time when a job is executed.

Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.
example code

begin   dbms_scheduler.create_schedule(Š      schedule_name => 'DEMO_SCHEDULE'     , start_date =>  '01/01/2006 22:00:00'     , repeat_interval => 'FREQ=WEEKLY'     , comments => 'Weekly at 22:00'); END; /  To drop the schedule: begin   dbms_scheduler.drop_schedule(      schedule_name => 'DEMO_SCHEDULE'      , force => TRUE ); end; /

Calendar expresions can have one of these values:



A job specifies what needs to executed and when. This can be a pl/sql procedure, c program, shell script
or other program.
You can specify a program and a schedule as part of a job definition, or you can use an existing program or schedule.
A job can belong to only 1 job class.

Code example

begin   dbms_scheduler.create_job(        job_name => 'DEMO_JOB1'      , program_name =>'DEMO_JOB_SCHEDULE'      , schedule_name =>'DEMO_SCHEDULE'      , enabled => FALSE      , comments => 'Run demo program every week at 22:00');     dbms_scheduler.set_job_argument_value(         job_name => 'DEMO_JOB1'       , argument_position => 1       , argument_value => 'param1');     dbms_scheduler.enable('DEMO_JOB1');     commit;  Šend; / Or start shell script  begin    dbms_scheduler.create_job    (       job_name      => 'RUN_SHELL1',       schedule_name => 'DEMO_SCHEDULE',       job_type      => 'EXECUTABLE',       job_action    => '/home/test/',       enabled       => true,       comments      => 'Run shell-script'    ); end; /

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g.
It’s also possible to use a number of views that have been created in
Oracle 10g.
We will discuss some of these views here.


To show details on job run: select log_date ,      job_name ,      status ,      req_start_date ,      actual_start_date ,      run_duration from   dba_scheduler_job_run_details
To show running jobs: select job_name ,      session_id ,      running_instance ,      elapsed_time ,      cpu_used from dba_scheduler_running_jobs;
To show job history:  select log_date  ,      job_name  ,      status  from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;  show all jobs and their attributes: select * from dba_scheduler_jobs show all program-objects and their attributes select * from dba_scheduler_programs;  show all program-arguments: select * from   dba_scheduler_program_args;


Return a file with a Servlet

Here is some sample java code.
This code returns a file from a Servlet:

package com.javaoracleblog.servlets;
import*; import javax.servlet.*; import javax.servlet.http.*;
/**  * Servlet to download text to a file.  *  * @author Edwin Korsten  * @version 1.0  */ public class Download extends HttpServlet {
 /**   * Return the contents of the requested file.   */  public void doGet(HttpServletRequest request, HttpServletResponse response)    throws IOException, ServletException {   PrintWriter out = null;   try {
   String fileName = "test.txt";    response.setContentType("application/x-msdownload");    response.addHeader("Content-disposition", "attachment; filename=\""      + fileName + "\"");    out = response.getWriter();    out.write("This is the contents of the file");   } catch (Exception e) {    if (out == null) {     response.setContentType("text/html");     out = response.getWriter();    }    out.write("”      + “Error trying to get file : “);    out.write(e.toString());    out.write(”");   }   out.close();  }
 public void doPost(HttpServletRequest request, HttpServletResponse response)    throws IOException, ServletException {   doGet(request, response);  }

Creating CBO statistics in Oracle

Since I am using the Cost Based Optimizer in Oracle (Oracle 7.3), I created the statistics needed for the optimizer with the command ‘ANALYZE TABLE’.I recently found out that since Oracle 7.4 this is not the optimal statement for creating statistics.

In Oracle 7.3.4 and Oracle 8.0 you should use the DBMS_UTILITY.ANALYZE_SCHEMA command.

From 8i you should use the DBMS_STATS.GATHER_SCHEMA_STATS command.

Analyze table

The analyze table can be used to create statistics for 1 table, index or cluster.


ANALYZE table tableName {compute|estimate|delete) statistics  options

ANALYZE table indexName {compute|estimate|delete) statistics  options

ANALYZE cluster clusterName {compute|estimate|delete) statistics options

Code examples   ANALYZE table scott compute statistics;  ANALYZE table scott estimate statistics sample 25 percent;  ANALYZE table scott estimate statistics sample 1000 rows;  analyze index sc_idx compute statistics;  analyze index sc_idx validate structure;


With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for
all the tables,
clusters and indexes of a schema.


Note: It’s also possible to analyze the whole database with the



From Oracle 8i the DBMS_STATS package is the preferred method
Oracle list a number of benefits to using it including parallel
execution, long term storage of statistics
and transfer of statistics between servers. Once again, it follows a
similar format to the other methods:


exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample
, method_opt,degree,granularity,cascade,stattab, statid,options,statown
,no_invalidate, gather_temp,gather_fixed);

Code examples:   exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);  exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);  EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);  EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');  EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');   exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Note: It’s also possible to gather statistics for the whole database
with the
DBMS_STATS.gather_database_stats; command.

Transfering statistics between database.

It can be very handy to use production statistics on your development
so that you can forecast the optimizer behavior.

You can do this the following way:

1. Create the statistics table.

,stat_tab => ‘STATS_TABLE’
, tblspace => ‘STATS_TABLESPACE’);

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>’SYSTEM’,stat_tab =>

2. Export statistics to statistics table


3. Import statistics into the data dictionary.


4. Drop the statistics table.


Create excel from JDBC resultset

Here is some simple code to transform a JDBC resulset to an excel-sheet.
You can also use a tool like Apache POI, but this one is easy and simple.

package com.javaoracleblog.servlets;
import; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.ResultSet; import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.math.BigDecimal;
public class ExcelWriter {
 protected String excelXmlHeader() {   return ("/n”>”>n”     + “    n”     + ” n”     + “n”     + ” n”     + ”
  for (int j = 0; j < columnNames.length; j++) {    result.append("
” + columnNames[j] + “


 public static void main(String[] args) {   try {    String driverName = "oracle.jdbc.driver.OracleDriver";    Class.forName(driverName);
   Connection conn;    String url = "jdbc:oracle:thin:@orakt02:1521:aridev01";    conn = DriverManager.getConnection(url, "ari", "ari");    Statement stmt = conn.createStatement();    ResultSet rs = stmt.executeQuery("SELECT * FROM ari_config");
   PrintWriter out;    try {     out = new PrintWriter(new OutputStreamWriter(       new FileOutputStream("c:/test.xls")));     ExcelWriter ew = new ExcelWriter();     ew.writeResultset(out, rs);     out.close();
   } catch (FileNotFoundException e) {     // TODO Auto-generated catch block     e.printStackTrace();    }
  } catch (SQLException e) {    // TODO Auto-generated catch block    e.printStackTrace();   } catch (ClassNotFoundException e) {    // TODO Auto-generated catch block    e.printStackTrace();   }