Data … as usual

All things about data by Laurent Leturgez

Generate a random date in Oracle

Leave a comment Posted by on September 10, 2009

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let’s see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);
Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select * from t;
D
----------------------
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00
Development, PL/SQL

Event for crashing an oracle session

Leave a comment Posted by on September 8, 2009

Thanks to Grimm for this information relayed by Laurent Schneider.

This is a tricky method for crashing a session without disconnecting the user :

Session 1 :

SQL> begin
2 while 2>1 loop
3 null;
4 end loop;
5 end;
6 /

Session 2 (logged as user SYS or as user who has privilege to execute the packageDBMS_SYSTEM.SET_EV) :

SQL> exec dbms_system.set_ev(sid,serial#,10237,4,'');
PL/SQL procedure successfully completed.

Session 1 :

ERROR at line 1:
ORA-01013: user requested cancel of current operation

The session is not killed, user is still connected –

If you want to perform other queries, administrator have to invalidate this event (exec dbms_system.set_ev(sid,serial#,10237,0,”);) otherwise you will encountered some “ORA-00604: error occurred at recursive SQL level 1”

SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Internals, Oracle

Newer posts →