Laurent Schneider

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

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

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

check on metalink for availability
patch 4505133

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