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.

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

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;