Oracle Technology Days 2006 Zurich Switzerland

I will be at the exhibition booth from LC Systems on the Oracle Technology Days, Thursday 16th March, 2006.

raptor early adopter release 4 is out

Well, it is now renamed to SQL Developer.

There is now an expected "Save password" checkbox to save the password... apart from the name, it looks quite similar to raptor ;-)

to_char(interval)

There is no to_char function available for intervals.

Or at least it does not work as expected

SQL> select to_char(interval '1234' second, 'HH24:MM') from dual;
TO_CHAR(INTERVAL'12
-------------------
+00 00:20:34.000000


I just write my own one, with some new format elements

For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

here it is


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



SQL>
SQL> create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
2 -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
3 ret varchar2(4000);
4 f varchar2(4000);
5 i interval day(9) to second(9);
6 begin
7 if (f_fmt is null or f_int is null) then
8 return null;
9 end if;
10 f := upper(f_fmt);
11 if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
12 raise_application_error(-20001,'Invalid format');
13 end if;
14 if (extract(day from i)<0) then
15 ret:='-';
16 i:=f_int*(-1);
17 else
18 ret:='';
19 i:=f_int;
20 end if;
21 while (f is not null) loop
22 if (f like 'DDD%') then
23 ret:=ret||to_char(extract(day from i),'FM999999999999999999');
24 f:=substr(f,4);
25 elsif (f like 'HHH%') then
26 ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
27 f:=substr(f,4);
28 elsif (f like 'HH%') then
29 ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
30 f:=substr(f,3);
31 elsif (f like 'MMM%') then
32 ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
33 f:=substr(f,4);
34 elsif (f like 'MM%') then
35 ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
36 f:=substr(f,3);
37 elsif (f like 'SSS%') then
38 ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
39 f:=substr(f,4);
40 elsif (f like 'SS%') then
41 ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
42 f:=substr(f,3);
43 elsif (f like 'FF%') then
44 ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
45 f:=substr(f,3);
46 elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
47 ret:=ret||substr(f,1,1);
48 f:=substr(f,2);
49 else
50 raise_application_error(-20001,'Invalid format : '||f_fmt);
51 end if;
52 end loop;
53 return ret;
54 end;
55 /

Function created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
2 -- valid formats are YYY, MMM, MM
3 ret varchar2(4000);
4 f varchar2(4000);
5 i interval year to month;
6 begin
7 if (f_fmt is null or f_int is null) then
8 return null;
9 end if;
10 f := upper(f_fmt);
11 if (translate(f,'XYM,.:;/- ','X') is not null) then
12 raise_application_error(-20001,'Invalid format');
13 end if;
14 if (extract(year from i)<0) then
15 ret:='-';
16 i:=f_int*(-1);
17 else
18 ret:='';
19 i:=f_int;
20 end if;
21 while (f is not null) loop
22 if (f like 'YYY%') then
23 ret:=ret||to_char(extract(year from i),'FM999999999999999999');
24 f:=substr(f,4);
25 elsif (f like 'MMM%') then
26 ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
27 f:=substr(f,4);
28 elsif (f like 'MM%') then
29 ret:=ret||to_char(extract(month from i),'FM999999999999999999');
30 f:=substr(f,3);
31 elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
32 ret:=ret||substr(f,1,1);
33 f:=substr(f,2);
34 else
35 raise_application_error(-20001,'Invalid format : '||f_fmt);
36 end if;
37 end loop;
38 return ret;
39 end;
40 /

Function created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
53561:15

SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
2231 17:15:59

SQL> select tocharym(interval '25' month, 'YYY:MM') from dual;

TOCHARYM(INTERVAL'25'MONTH,'YY
------------------------------
2:1

SQL> select tocharym(interval '-25' month, 'MMM') from dual;

TOCHARYM(INTERVAL'-25'MONTH,'M
------------------------------
-25

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


patch 10.1.0.5 is out

check on metalink for availability
patch 4505133

grid control release 2

I am waiting for eomgc 10gR2 for AIX so I check the download page often. Instead of adding new plateforms, I notice they just removed Solaris !
Oracle Enterprise Manager 10g Grid Control Release 2 (10.2.0.1) for Solaris Operating System (SPARC)

google cache