Categories
Blogroll sql

avoid unnecessary updates

I do update t set x=:a;

If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE.

I need to take care of null, I can update null with something, or something with null, but update null with null is also unnecessary.


SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.34
SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.32
SQL> update t set x=:a
2 where x!=:a
3 or (x is null and :a is not null)
4 or (x is not null and :a is null);

0 rows updated.

Elapsed: 00:00:00.04

Categories
Blogroll dba plsql sql

v$session_longops in 10gR2

I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only…

I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go

create or replace procedure p is
rindex binary_integer;
slno binary_integer;
sofar number;
totalwork number;
target number;
begin
totalwork := 5;
sofar := 0;
select object_id
into target
from user_objects
where object_name = $$PLSQL_UNIT;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
end;
/

Now I execute it

SQL> exec p

PL/SQL procedure successfully completed.

and monitor it

SQL> select sid,opname,target,sofar,totalwork,units
2 from v$session_longops;
SID OPNAME TARGET SOFAR TOTALWORK UNITS
---- ------------ -------- ----- --------- ---------
538 Executing... SCOTT.P 5 5 Statement

Categories
Blogroll sql

rename column_value


SQL> create or replace type t is
2 table of varchar2(12);
3 /
Type created.

SQL> create or replace function f return t is
2 begin return t('foo'); end;
3 /
Function created.

SQL> select * from table(f);

COLUMN_VALUE
------------
foo

What is this column_value field? It is a pseudo-column. But you may want to have an user-defined column name.


SQL> create or replace type o is
2 object(BAR varchar2(12));
3 /
Type created.

SQL> create or replace type t is
2 table of o;
3 /
Type created.

SQL> create or replace function f return t is
2 begin return t(o('foo')); end;
3 /
Function created.
SQL> select * from table(f);

BAR
------------
foo

Categories
Blogroll plsql sql

to divide or to multiply

warning, this test is cpu intensive, do not try on your productive server

One user on the developpez.net French forums asked today about rewritting a division in a multiplication for tuning. Like select avg(sal)/2 from emp; in select avg(sal)*.5 from emp;.

Well, I had to test this ! I execute 41055 divisions in a plsql loop. To avoid incrementation, I divide by 1.014… and multiply by 0.986… in a way that the result keep the same all over the loop.


SQL>
SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop
Elapsed: 00:00:00.42
SQL> set timi off
SQL> print y

Y
--------------------------------
9.9879215917842541374103299E-126

SQL> exec :z := power(2,-104)*2e31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y<1e-125) loop :y:=:y*:z; end loop Elapsed: 00:00:00.28 SQL> set timi off
SQL> print y

Y
--------------------------------
9.9879215917842541374103299E-126

It has been difficult to find an example with clear difference and not too weird :twisted:

The operation divide is slower than multiply, probably to catch divide by zero errors...

Categories
Blogroll sql

Column qualification best practice

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns.

Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column is added, renamed or removed, so long this column is not related to your query your code should keep working. A typical example is when the dba add a LAST_MODIFICATION_DATE and a trigger to automatically fill that column for auditing purpose. It should never make your application fail.

0) sample table

create table t1(x number, y number);
create table t2(a number, b number);
create table t3(x number, z number);

1) insert into table
bad:insert into t1 values(1,2);
good:insert into t1(x,y) values(1,2);

bad:insert into t1
select * from t2;

good:insert into t1(x,y)
select t2.a,t2.b from t2;

bad:insert all
when a>0 then into t1
select * from t2;

good:insert all
when a>0 then into t1(x,y)
select t2.a,t2.b from t2;

2) merge
bad:merge into t1
using t2 on (x=a)
when matched then
update set y=b
where (b>y)
delete
where (b>y)
when not matched then
insert values(a,b)
where (b>0);

good:merge into t1
using t2 on (t1.x=t2.a)
when matched then
update set y=t2.b
where (t2.b>t1.y)
delete
where (t2.b>t1.y)
when not matched then
insert (x,y) values(t2.a,t2.b)
where (t2.b>0);

Not prefixing the column here will bug as soon as a column a or b is added to table t1

2) joins

just never use natural join in production

bad:select *
from t1 natural join t3;

good:select x, t1.y, t3.z
from t1 join t3 using (x);

bad:select *
from t1,t2
where x=a;

good:select t1.x,t1.y,t2.a,t2.b
from t1,t2
where t1.x=t2.a;

It is not about good looking code, it is about data quality and stability 😎

Categories
Blogroll sql xml

select from a comma-separated string

This is one question I solved today with XML on the developpez.net/forums

I have a table T

Un,Trois,Cinq,Six
Un,Deux,Quatre
Trois
Sept,Huit
Un,Six

I want to select un,deux,trois,quatre,cinq,six,sept,huit.

I could well have written a plsql function like

create or replace type c as object (value varchar2(40));
/
create or replace type t_c as table of c;
/
sho err
create or replace function f(
list varchar2,
sep varchar2 default ',')
return t_c pipelined is
i number:=1;
begin
loop
if (instr(list,sep,1,i)>0)
then
if (i=1) then
pipe row(c(substr(list,
1, instr(list,sep)-1)));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1,
instr(list,sep,1,i)-
instr(list,sep,1,i-1)-1)));
end if;
else
if (i=1) then
pipe row(c(list));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1)));
end if;
return;
end if;
i:=i+1;
end loop;
end;
/
select distinct value
from t,
table(f(c))
;

or anything using recursion or whatever.

I just tried with XML


SQL> select distinct extractvalue(column_value,’/x’)
2 from t,
3 table(xmlsequence(extract(xmltype(
4 ‘<list><x>’||replace(
5 c,’,’,'</x><x>’)||
6 ‘</x></list>’),
7 ‘/list/x’)));

EXTRACTVALUE(COLUMN_VALUE,’/X’)
——————————-
Trois
Un
Quatre
Cinq
Six
Deux
Sept
Huit

8 rows selected.

Categories
Blogroll dba sql

dbms_xplan and v$sql_plan

do not miss yas comment !

tested in 10.2


create or replace procedure
explain_plan(hash_value number)
is begin
insert into plan_table
select
null,
(select nvl(max(plan_id),0)+1 from plan_table),
timestamp,
remarks,
operation,
options,
object_node,
object_owner,
object_name,
object_alias,
null,
object_type,
optimizer,
search_columns,
id,
parent_id,
depth,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
other,
other_xml,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates,
projection,
time,
qblock_name
from v$sql_plan
where hash_value=explain_plan.hash_value;
for f in (
select PLAN_TABLE_OUTPUT from table(dbms_xplan.DISPLAY))
loop
dbms_output.put_line(f.PLAN_TABLE_OUTPUT);
end loop;
end;
/

SQL> set lin 200 trims on pages 0 serverout on
SQL> select ename from emp where empno=7788;
ENAME
----------
SCOTT

SQL> select hash_value from v$sql where sql_text like
2 'select ename from emp where empno=7788%';
HASH_VALUE
----------
1019401098

SQL> exec explain_plan(1019401098)
Plan hash value: 4066871323
------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)

PL/SQL procedure successfully completed.

Categories
Blogroll sql

rename

Views:

SQL> create view v1 as select * from dual;

View created.

SQL> rename v1 to v2;

Table renamed.

Tables:

SQL> create table t1 as select * from dual;

Table created.

SQL> rename t1 to t2;

Table renamed.

Sequences:

SQL> create sequence s1;

Sequence created.

SQL> rename s1 to s2;

Table renamed.

Synonyms:

SQL> create synonym syn1 for dual;

Synonym created.

SQL> rename syn1 to syn2;

Table renamed.

Constraints :

SQL> create table t(x number not null);

Table created.

SQL> select constraint_name
2 from user_constraints
3 where table_name='T';

CONSTRAINT_NAME
------------------------------
SYS_C0018686

SQL> alter table t rename constraint SYS_C0018682 to X_NOT_NULL;

Table altered.

Indexes :

SQL> create index i1 on t(x);

Index created.

SQL> alter index i1 rename to i2;

Index altered.

Columns :

SQL> alter table t rename column x to y;

Table altered.

Partitions :

SQL> create table t (x number, y number)
2 partition by range(x)
3 subpartition by hash(y) (
4 partition p1 values less than(1)
5 (subpartition sp1)
6 ) ;

Table created.

SQL> alter table t rename partition p1 to p2;

Table altered.

Subpartitions:

SQL> alter table t rename subpartition sp1 to sp2;

Table altered.

Triggers:

SQL> create trigger tr1 after update on t
2 begin null; end;
3 /

Trigger created.

SQL>
SQL> alter trigger tr1 rename to tr2;

Trigger altered.

For the dba, you can also rename datafile, logfile and even tablespaces (10g)

Categories
dba sql

constraints for referential integrity

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ?


SQL> create table continent(
2 name varchar2(10),
3 constraint continent_pk primary key(name));

Table created.

SQL> create table country(
2 name varchar2(10),
3 continent varchar2(10),
4 constraint country_pk
5 primary key(name),
6 constraint country_continent_fk
7 foreign key(continent) references continent);

Table created.

SQL> insert into continent values('Africa');

1 row created.

SQL> insert into country values('Benin', 'Africa');

1 row created.

CONTINENT

NAME
Africa

COUNTRY

NAME CONTINENT
Benin Africa

So in this default constellation, I cannot delete a parent row where the child exist.


SQL> delete continent where name='Africa';
delete continent where name='Africa'
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - child record found

I could specify CASCADE to delete the child rows automatically

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 on delete cascade;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.

CONTINENT

NAME

COUNTRY

NAME CONTINENT

but this is dangerous. When I read 1 row deleted., I am not informed that I have deleted rows in the child table.

I could rather set the column to null


SQL> rollback;

Rollback complete.

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 on delete set null;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.

CONTINENT

NAME

COUNTRY

NAME CONTINENT
Benin  

Ok, but what If I need to rename Africa to EMEA ? I can do this neither on the child nor on the parent


SQL> rollback;

Rollback complete.

SQL>
SQL> update continent
2 set name='EMEA'
3 where name='Africa';
update continent
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - child record found

SQL> update country
2 set continent ='EMEA'
3 where continent ='Africa';
update country
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - parent key not found

So I could defer the constraint validation to the commit

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 deferrable initially deferred;

Table altered.

SQL> update continent
2 set name='EMEA'
3 where name='Africa';

1 row updated.

SQL> update country
2 set continent='EMEA'
3 where continent ='Africa';

1 row updated.

SQL> commit;

Commit complete.

CONTINENT

NAME
EMEA

COUNTRY

NAME CONTINENT
Benin EMEA

You can use triggers too, but constraints are more efficient than triggers.

Categories
Blogroll sql

insert/update/delete/merge LOG ERRORS

the following works only on 10gR2

Table PRODUCTCATEGORY:

CATEGORY (PK)
Fruit
Fish

Table TMP_PRODUCT:

PRODUCT CATEGORY
Banana Fruit
Chicken Meat

Table PRODUCT:

PRODUCT (PK) CATEGORY (FK)

Now I want to load product


SQL> insert into product select * from tmp_product;
insert into product select * from tmp_product
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.SYS_C0016368)
violated - parent key not found

Well, there is a missing category. I will fix it later. I want to load the valid data now.


SQL> exec dbms_errlog.create_error_log('PRODUCT')

PL/SQL procedure successfully completed.

SQL> insert into product select * from tmp_product
2 log errors reject limit unlimited;

1 row created.

Table PRODUCT:

PRODUCT (PK) CATEGORY (FK)
Banana Fruit

Now I have successfully loaded my valid data. I need to fix the problem. Let’s identify the offending row :


SQL> select ORA_ERR_OPTYP$,ORA_ERR_MESG$,CATEGORY
2 from err$_product;

ORA_ERR_OPTYP$ ORA_ERR_MESG$ CATEGORY
-------------- -------------------------------- --------
I ORA-02291: integrity constraint Meat
(AUDBA.SYS_C0016368) violated -
parent key not found

1 row selected.

Let’s create the missing category manually


SQL> insert into productcategory(category) values ('Meat');

1 row created.

SQL> insert into product
2 select * from tmp_product
3 where category='Meat';

1 row created.

Table PRODUCTCATEGORY:

CATEGORY (PK)
Fruit
Fish
Meat

Table PRODUCT:

PRODUCT (PK) CATEGORY (FK)
Banana Fruit
Chicken Meat
Categories
Blogroll sql

MERGE syntax

With merge you can insert, update or both

I want to import changes from one table in another table.

Table T1:

ID NAME
1 Apple
2 Orange

Table T2:

ID NAME
1 Pineapple
3 Lemon

To insert the missing row from T2 into T1, I could need the following subquery with INSERT.
insert into t1
select * from t2
where not exists (
select * from t1
where t1.id=t2.id);

The same code would be done in Merge with
merge into t1 using t2
on (t1.id=t2.id)
when not matched then
insert values(t2.id,t2.name);

And what about updating the T1 table with the T2 values
update t1
set (name)=(select name from t2 where id=t1.id)
where id in (select id from t2);

with merge :
merge into t1 using t2 on (t1.id=t2.id)
when matched then
update set name=t2.name;

Quite nice, is not it! I find this syntax very intuitive.

Apart from syntax looking, Merge can also combine insert and update in once, which make it more stable (only one statement) and more performant (table t2 is read only once).
merge into t1 using t2 on (t1.id=t2.id)
when not matched then
insert values(t2.id,t2.name)
when matched then
update set name=t2.name;

ID NAME
1 Pineapple
2 Orange
3 Lemon
Categories
Blogroll dba sql

How to avoid ORA errors when dropping inexistant objects

There were a similar question in the otn forums today. Ok, when I have to run a script in production, the operators complain about errors like ORA-00942 table or view does not exist. Of course I can provide some documentation to explain what can be ignored, but then they then tend to ignore all ORA errors.

A script to create table t will drop table t if existant. There is no CREATE OR REPLACE TABLE command. So I will simply check the dictionary and drop only if existant.

exec for f in (select 1 from user_tables where
table_name='T') loop execute immediate
'drop table t cascade constraints'; end loop
create table t(x number);

For context, directory, function, indextype (9iR1), java, library, operator, outline, package, procedure, synonym (9iR2), trigger, type, view, it is possible to use the create or replace syntax.

create or replace public synonym x for y;

For indexes (in case they are not dropped with the table)

exec for f in (select 1 from user_indexes where
index_name='I') loop execute immediate
'drop index i'; end loop
create index i on t(x);

For sequences

exec for f in (select 1 from user_sequences where
sequence_name='S') loop execute immediate
'drop sequence s'; end loop
create sequence s;

Let’s try

SQL> exec for f in (select 1 from user_tables where tabl

PL/SQL procedure successfully completed.

SQL> create table t(x number);

Table created.

SQL> exec for f in (select 1 from user_indexes where ind

PL/SQL procedure successfully completed.

SQL> create index i on t(x);

Index created.

SQL> exec for f in (select 1 from user_sequences where s

PL/SQL procedure successfully completed.

SQL> create sequence s;

Sequence created.

End of complaints from production people :mrgreen:

Categories
Blogroll sql

csv part 3

In part 1, I tried with pure SQL+XML, but empty columns were missing. In Part 2, I had a mix between PL/SQL and XML functions. Now I would like to publish the solution Vadim Tropashko posted in the OTN Forums. It is a NO XML pipelined function. I did a bit formatting in it. And I pipe clobs.

Update: I added the column header


create or replace type strings as table of clob;
/

create or replace function CSV(txt varchar2)
return strings pipelined is
tmp clob;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2(4000);
begin
curid := dbms_sql.open_cursor;
dbms_sql.parse(curid, txt, dbms_sql.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
FOR i IN 1 .. colcnt LOOP
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
END LOOP;
if DBMS_SQL.execute(curid) = 0 THEN
dbms_lob.createtemporary(tmp, true);
FOR i IN 1 .. colcnt LOOP
if (i>1) then
dbms_lob.writeappend(tmp, 1, ';');
end if;
dbms_lob.writeappend(tmp,
length(desctab(i).col_name),
desctab(i).col_name);
END LOOP;
pipe row(tmp);
dbms_lob.freetemporary(tmp);
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
dbms_lob.createtemporary(tmp, true);
FOR i IN 1 .. colcnt LOOP
if (i>1) then
dbms_lob.writeappend(tmp, 1, ';');
end if;
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
if (namevar is not null)
then
dbms_lob.writeappend(tmp, length(namevar),
namevar);
end if;
END LOOP;
pipe row(tmp);
dbms_lob.freetemporary(tmp);
END LOOP;
END IF;
DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

select * from table(csv('select * from emp'));

EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
7369;SMITH;CLERK;7902;17-DEC-80;800;;20
7499;ALLEN;SALESMAN;7698;20-FEB-81;1600;300;30
7521;WARD;SALESMAN;7698;22-FEB-81;1250;500;30
7566;JONES;MANAGER;7839;02-APR-81;2975;;20
7654;MARTIN;SALESMAN;7698;28-SEP-81;1250;1400;30
7698;BLAKE;MANAGER;7839;01-MAY-81;2850;;30
7782;CLARK;MANAGER;7839;09-JUN-81;2450;;10
7788;SCOTT;ANALYST;7566;19-APR-87;3000;;20
7839;KING;PRESIDENT;;17-NOV-81;5000;;10
7844;TURNER;SALESMAN;7698;08-SEP-81;1500;0;30
7876;ADAMS;CLERK;7788;23-MAY-87;1100;;20
7900;JAMES;CLERK;7698;03-DEC-81;950;;30
7902;FORD;ANALYST;7566;03-DEC-81;3000;;20
7934;MILLER;CLERK;7782;23-JAN-82;1300;;10

Categories
Blogroll sql xml

csv with XML revisited…

Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select

Ok, here is a my PL/SQL table function.


create or replace type tv as table of
varchar2(4000);
/

create or replace function CSV(sqlQuery varchar2)
return tv pipelined is
ctx dbms_xmlgen.ctxhandle;
begin
ctx:=dbms_xmlgen.newcontext(sqlquery);
dbms_xmlgen.setnullhandling(ctx,dbms_xmlgen.empty_tag);
for f in (select
cast(
xmltransform(column_value,
XMLTYPE(‘<xsl:stylesheet version=”1.0″ xmlns:xsl’||
‘=”http://www.w3.org/1999/XSL/Transform”>’||
‘<xsl:output method=”text”/><xsl:variable name’||
‘=”new_line” select=””&#xA;”” /><xsl:template’||
‘ match=”ROWSET”><xsl:apply-templates select=”‘||
‘ROW”/></xsl:template><xsl:template match=”ROW”‘||
‘><xsl:for-each select=”*”><xsl:value-of select’||
‘=”.”/><xsl:if test=”position() != last()”><xsl’||
‘:value-of select=””,””/></xsl:if></xsl:for-‘||
‘each><xsl:value-of select=”$new_line” /></xsl:’||
‘template></xsl:stylesheet>’
)) as varchar2(4000))
c from table(xmlsequence(dbms_xmlgen.getxmltype(ctx)
.extract(‘ROWSET/ROW’))))
loop
pipe row(f.c);
end loop;
end;
/

select * from table(csv(‘select * from emp’));
COLUMN_VALUE
————————————————
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

Categories
Blogroll sql xml

csv format with select *

One more trick with xml.

I want to get a semi-column separated format without having to specify the columns


alter session set nls_date_format=’YYYY-MM-DD’;

Session altered.

select regexp_replace(column_value,’ *<[^>]*>[^>]*>’,’;’)
from table(xmlsequence(cursor(select * from emp)));

;7369;SMITH;CLERK;7902;1980-12-17;800;20;
;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30;
;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30;
;7566;JONES;MANAGER;7839;1981-04-02;2975;20;
;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30;
;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30;
;7782;CLARK;MANAGER;7839;1981-06-09;2450;10;
;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20;
;7839;KING;PRESIDENT;1981-11-17;5000;10;
;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30;
;7876;ADAMS;CLERK;7788;1987-05-23;1100;20;
;7900;JAMES;CLERK;7698;1981-12-03;950;30;
;7902;FORD;ANALYST;7566;1981-12-03;3000;20;
;7934;MILLER;CLERK;7782;1982-01-23;1300;10;

Categories
Blogroll dba sql

v$sql and bind variable

When you see something like

select * from t where x = :1

you may wonder what is :1

Ok, here is a quick join I tested in 10gR2

SQL> var y varchar2(255)
SQL> exec :y:='SCOTT'

PL/SQL procedure successfully completed.

SQL> select job from emp where ename=:y;
JOB
---------
ANALYST

SQL> select sql_text,name,value_string,datatype_string
  2  from v$sql_bind_capture join v$sql using (hash_value)
  3  where sql_text like
  4    'select job from emp where ename=:y%';
SQL_TEXT                              NAME VALUE DATATYPE_STRING
------------------------------------- ---- ----- ---------------
select job from emp where ename=:y    :Y   SCOTT VARCHAR2(2000)
Categories
data modeling sql

updatable views and instead of triggers

I am data-modeling those days.

Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) must be selectable and editable. Someone, the little ones are views of the big one, or the big one is a view of the little ones.

Plan 1: there is one big table and many small views

create table global(
type varchar2(15),
id number,
description varchar2(10),
primary key(type,id));
create or replace view fruit as
select id,description
from global
where type='fruit';
create or replace view meat as
select id,description
from global
where type='meat';

plan 2: one view which union all all little tables

create table fruit(
id number,
description varchar2(10),
primary key(id));
create table meat(
id number,
description varchar2(10),
primary key(id));
create or replace view global as
select 'fruit' type,id,description
from fruit
union all
select 'meat',id,description
from meat;

Is this fine? As long as I only select rows, both are possible. However, a select * from fruit will take much longer if fruit is a view and the rows are retrieved from a huge table, probably per Index scan.

In the solution 2, I keep small lookup tables, and it should be faster to retrieve data from those. However, the UNION ALL view is not updatable.

In solution 1, the small views seem updatable, but it will not work.

Let’s see

SQL> create table global(
2 type varchar2(15),
3 id number,
4 description varchar2(10),
5 primary key(type,id));

Table created.

SQL> create or replace view fruit as
2 select id,
3 description
4 from global
5 where type='fruit';

View created.

SQL> insert into fruit values(1,'Mango');
insert into fruit values(1,'Mango')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AUDBA"."GLOBAL"."TYPE")

I cannot tell the view to default the type to fruit when inserting in fruit

In plan 2, the view is not updatable at all

SQL> create table fruit(
2 id number,
3 description varchar2(10),
4 primary key(id));

Table created.

SQL> create table meat(
2 id number,
3 description varchar2(10),
4 primary key(id));

Table created.

SQL> create or replace view global as
2 select 'fruit' type,id,description
3 from fruit
4 union all
5 select 'meat',id,description
6 from meat;

View created.

SQL> insert into global values(
2 'meat',1,'beef');
insert into global values(
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

So the solution is called INSTEAD OF trigger. An instead of trigger is a very special trigger specific to views. It will make all views updatable, how magic!

As a DBA, I am not very confident with triggers, because the developer is doing the consistency check, no more the Oracle Engine. It will therefore be much less performant and it may contain errors. Here I write a trigger for each case. They certainly have bugs. I am myself not convinced by using triggers at all. Those kind of DML home-made rewrites are just too dangerous, but what else can I do…

OK, plan 1: one instead of trigger for every single view

create or replace trigger fruit_insteadof
instead of insert or update or delete on fruit
begin
if inserting then
insert into global
values('fruit',:new.id,:new.description);
elsif deleting then
delete from global
where type='fruit'
and id=:old.id
and description=:old.description;
elsif updating then
update global
set type='fruit',
id=:new.id,
description=:new.description
where type='fruit'
and id=:old.id
and description=:old.description;
end if;
end;
/

Plan 2: One instead of trigger for the global view
create or replace trigger global_insteadof
instead of insert or update or delete on global
begin
if inserting then
if (:new.type='fruit') then
insert into fruit values(
:new.id,:new.description);
elsif (:new.type='meat') then
insert into meat values(
:new.id,:new.description);
else
raise_application_error(-20001,
'Trigger cannot insert into '||:new.type);
end if;
elsif deleting then
if (:old.type='fruit') then
delete fruit where
id=:old.id
and description=:old.description;
elsif (:old.type='meat') then
delete meat where
id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot delete from '
|| :old.type);
end if;
elsif updating then
if (:new.type!=:old.type) then
delete from global
where type=:old.type
and id=:old.id
and description=:old.description;
insert into global values(
:new.type,:new.id,:new.description);
elsif (:new.type='fruit') then
update fruit set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
elsif (:new.type='meat') then
update meat set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot update '||:old.type);
end if;
end if;
end;

I am not at all convinced about the data consistency in such an approach.

Well, I will try to push a solution with no GLOBAL table. We probably do not need it.

Categories
Blogroll sql xml

How do i store the counts of all tables …

How do i store the counts of all tables …

My answer to the question above using dbms_xmlgen

SQL> select
  2    table_name,
  3    to_number(
  4      extractvalue(
  5        xmltype(
  6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
  7        ,'/ROWSET/ROW/C')) count
  8  from user_tables;

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

Categories
Blogroll sql

Anagram Help – Oracle9i Enterprise …

Anagram Help – Oracle9i Enterprise …

I just posted a solution on the OTN forums where I am using a regular table function and a recursive procedure to generate anagrams

select * from table(anagram.f('ABC'));

COLUMN_VALUE
------------
ABC
ACB
BAC
BCA
CAB
CBA
Categories
Blogroll sql

10’000 columns in a query

You cannot have more than 1000 columns in a view, but what about a query?

I tried

select 1,2,3,4,....,10000 from dual;

it seems to work fine. However, when increasing, I am facing various errors :

select 1,2,3,4,....,17000 from dual;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15201], 
[], [], [], [], [], [], []

or even

select 1,2,3,4,....,50000 from dual;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Categories
Blogroll dba sql

variable in a view

Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session

Here we go

SQL> create or replace package p is n number; end p;
  2  /

Package created.

SQL> create or replace function f return number is 
  2  begin return p.n; end;
  3  /

Function created.

SQL> create or replace view v as select ename from 
  2  emp where empno=f;

View created.

SQL> select * from v;

no rows selected

SQL> exec p.n:=7788

PL/SQL procedure successfully completed.

SQL> select * from v;

ENAME
----------
SCOTT
Categories
Blogroll java sql

How to load BLOB in the database?

I have been asked yesterday how to read and write blobs in the database.

With java :
read from an input stream

InputStream myBlobInputStream = 
  connection . 
    createStatement() . 
      executeQuery("select myBlob from t") . 
        getBlob(1) . 
          getBinaryStream();

write to an output stream

OutputStream myBlobStream = 
  connection .
    createStatement() .
      executeQuery("select myBlob from t for update") . 
        getBlob(1) . 
          getBinaryOutputStream();

where connection is your java.sql.connection.

You could also use PL/SQL and the DBMS_LOB API. There is a complete book (306 pages!) in the doc about working with large objects : Application Developer’s Guide – Large Objects.

Sometimes, you can use plain SQL.

SQL> create table t(x BLOB);
Table created

SQL> insert into t values(utl_raw.cast_from_number(1));
1 row inserted

SQL> select utl_raw.cast_to_number(x) from t;
UTL_RAW.CAST_TO_NUMBER(X)
-------------------------
                        1

A smart move may be to use SQL Loader. You can specify one file per row

LOAD DATA INFILE '/tmp/x.txt' INTO TABLE "T"
(name filler char(255), x lobfile(name) terminated by EOF)

and your import file /tmp/x.txt will look like

x.gif
y.gif

but you could also load a 10000 long characters column from your input file in a CLOB column, just by specifying VARCHARC(4,10000) as a datatype

Categories
Blogroll sql

Paul Moen article on MONTHS_BETWEEN

I have been shocked by Paul Moen article on MONTHS_BETWEEN.

Here is my own case :
SQL> select months_between( date '2000-03-01', date '2000-02-28') * 31 from dual;
4

Incredible! I have always been very careful with months_between because of the documented end_of_month behaviour (there is one month between 30-APR and 31-MAY), but I did not know the fractional part of add_months was based on a 31-day month 👿

update: but it is documented

Categories
Blogroll sql

-0

Is -0 a meaningful output ?


SQL> set numf 9
SQL> select -.1 from dual;
-.1
---
-0

not really 😡

Categories
Blogroll sql

length(”)=null?

What is the length of an empty string?

According to Oracle documentation,

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls

And therefore the length of null is null, not 0 (there are no character value with a length of zero :oops:).

However, this is not true for clobs 😈


SQL> create table t(x clob);
Table created.
SQL> insert into t values (empty_clob());
1 row created.
SQL> select x,length(x) from t where x is not null;
X LENGTH(X)
- ----------
0

Categories
Blogroll dba sql xml

search for a string in all tables of a schema

this is often asked on the forums. I also needed this a while ago while reverse engineering a database model.

Here is my today solution:

1) select * and extract the first column found per table with regexp (10g)

SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: 20
TABLE_NAME COLUMN_NAME
———- ——————————
DEPT       <DEPTNO>20</DEPTNO>
EMP        <DEPTNO>20</DEPTNO>

SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: KING
TABLE_NAME COLUMN_NAME
———- ——————————
EMP        <ENAME>KING</ENAME>
BONUS      <ENAME>KING</ENAME>

2) add a where condition. much slower of course, because scanning the table more often. somehow nicer output. More sensible to datatypes. Here for number.

SQL> select table_name, column_name from (select rownum,table_name, column_name, dbms_xmlgen.getxml(‘select 1 from “‘||table_name||'” where “‘||column_name||'”=&number’) x from user_tab_columns where data_type=’NUMBER’) where length(x)!=0;
Enter value for number: 3000
TABLE_NAME COLUMN_NAME
———- ——————————
EMP        SAL
BONUS      COMM
SALGRADE   HISAL

Categories
11g Blogroll sql

pivot table

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.

Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT

Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)

select person.name,
  property.type,
  property.value
from person, property
where 
  person.id=property.person;

NAME TYPE     VALUE
---- -------- ------
John gender   male
Mary category junior
Mary gender   female

for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)

select name, 
  gender.value gender, 
  category.value category
from person,
  property gender,
  property category
where
  person.id = gender.person(+) 
  and gender.type(+)='gender'
  and person.id = category.person(+)
  and category.type(+)='category';

NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male

By using the Tom Kyte method described on asktom, I could have used aggregation.

select name,
  max(decode(type,'gender',value)) gender,
  max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;

NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior

To do the opposite, I posted once in a forum

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
pivot (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
unpivot (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !

Categories
Blogroll sql

difference between two dates

How can i get the difference in days between two dates, d1 and d2 ?

for example :

with t as (select
  to_date('2000-02-01','YYYY-MM-DD') d1,
  to_date('2000-01-23','YYYY-MM-DD') d2
  from dual)
select d1,d2,d2-d1
from t;
D1         D2              D2-D1
---------- ---------- ----------
01.02.2000 23.01.2000         -9

How can i get the difference in hours:minutes:seconds between two dates, d1 and d2 ?
Use the interval datatype. To have a format different than the default +000000000 00:00:00.000000, use extract


with t as (select
to_date(‘2000-02-01 11:22:02’,
‘YYYY-MM-DD HH24:MI:SS’) d1,
to_date(‘2000-01-23 12:00:03’,
‘YYYY-MM-DD HH24:MI:SS’) d2
from dual)
select d1,d2,case
when d1d2 then
‘-‘||
(extract(day from ((d1 – d2)
day to second))*24 +
extract(hour from ((d1 – d2)
day to second)))||’:’||
extract(minute from ((d1 – d2)
day to second))||’:’||
extract(second from ((d1 – d2)
day to second))
when d1=d2 then
‘0:0:0’
end “D2-D1”
from t
/
D1 D2 D2-D1
——————- ——————- ———-
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59

Categories
Blogroll sql

KEEP DENSE_RANK versus ROW_NUMBER()

I often see questions like

How do you get the row of each department with the highest salary

In case you only want 1 row, you have two modern solutions :
Analytics, which is trend, and KEEP, which is not very known

The old fashion would be something like where s in (select max())

Ok, let’s start with analytics

SQL> select ename,deptno,sal
  2  from (select ename,deptno,sal,
  3  row_number() over (partition by deptno
  4  order by sal desc,empno) r from emp)
  5  where r=1;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

and the KEEP method, which is a special aggregation

SQL> select max(ename) keep (dense_rank first
  2  order by sal desc,empno) ename,
  3  deptno,max(sal) sal
  4  from emp group by deptno;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

the second one should be more performant

SQL> select count(*) from emp2;
  COUNT(*)
----------
    917504

SQL> select max(ename) keep (dense_rank first
  2  order by sal desc,empno) ename,
  3  deptno,max(sal) sal
  4  from emp group by deptno;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

Elapsed: 00:00:01.00
SQL> select ename,deptno,sal
  2  from (select ename,deptno,sal,
  3  row_number() over (partition by deptno
  4  order by sal desc,empno) r from emp)
  5  where r=1;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

Elapsed: 00:00:01.43
Categories
Blogroll dba sql

select last rows

I just read about a query to retrieve last modification row of a date


SQL> SELECT ora_rowscn FROM tab_test;

ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.

SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;

3 rows updated.

SQL> commit;

Commit complete

SQL> SELECT ora_rowscn FROM tab_test:

ORA_ROWSCN
----------

351744
351744
351744
371423
371423
371423

6 rows selected.

conclusion of the author : very neat to retrieve last modification date.

Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.

What should I do?
test it!


14:52:07 SQL> create table t as
select rownum x from all_objects;

Table created.

14:56:23 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 4238

let’s update one row


14:54:12 SQL> update t set x=-1 where rownum=1;

1 row updated.

14:55:04 SQL> commit;

Commit complete.

14:58:03 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 3580
04.08.2006 14:57:14.00 658

what? I updated one row, why did it updated so many rows? Let’s look at the block

14:58:16 SQL> select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

BLOCK_NUMBER TIMESTAMP COUNT(*)
------------ ---------------------- ----------
651 04.08.2006 14:56:23.00 658
652 04.08.2006 14:56:23.00 658
653 04.08.2006 14:56:23.00 658
654 04.08.2006 14:56:23.00 658
655 04.08.2006 14:56:23.00 658
656 04.08.2006 14:56:23.00 290
650 04.08.2006 14:57:14.00 658 <=====================

ok, what has the doc to say about this :

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking

Well, let's try again with row dependencies.


15:04:53 SQL> drop table t;

Table dropped.

15:04:55 SQL> create table t rowdependencies as select rownum x from all_objects;

Table created.

15:05:28 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4241

15:05:42 SQL> update t set x=-1 where rownum=1;
1 row updated.

15:05:58 SQL> commit;

Commit complete.

15:06:09 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4240
04.08.2006 15:06:08.00 1

sounds better! probably not 100% reliable but way better, it seems almost usable ...

Categories
Blogroll dba sql

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