Display a blob

I have a table with a blob


create table t(b blob);
insert into t values ('585858');

In 11g sql*plus, I can display raw data

select b from t;
B
------
585858

Ok, but if I want to display XXX (the character content)


select utl_raw.cast_to_varchar2(b) from t;
UTL
--- 
XXX

However, in sql, a raw cannot be more than 2000 bytes long.

Another way to print your blob content is to use DBMS_LOB.CONVERTTOCLOB


var c clob
set autoprint on
declare
  b blob;
  dest_offset integer := 1 ;
  src_offset  integer := 1 ;
  lang_context integer:= 1 ;
  warning integer;
begin
  select b into b from t for update;
  dbms_lob.createtemporary(:c,true);
  dbms_lob.converttoclob(
    :c, b, DBMS_LOB.LOBMAXSIZE,
    dest_offset, src_offset,
    1, lang_context, warning);
end;
/
C
---
XXX

On associativity, transitivity and reflexivity

Addition is supposed to be associative.
a+(b+c)=(a+b)+c

This may be wrong in Oracle when dealing with months and days


with t as (select
 interval '1' month a,
 date '2007-09-26' b,
 interval '7' day c
from dual)
select a+(b+c),(a+b)+c 
from t;

A+(B+C)     (A+B)+C
----------- -----------
03-NOV-2007 02-NOV-2007

The equality is supposed to be transitive
if (a=b and b=c) then (a=c)
However, in Oracle the comparison operator equal may imply some transformation


with t as (select '.0' a, 0 b, '0.' c from dual) 
select 
  case when a=b then 'YES' else 'NO ' end "A=B",
  case when b=c then 'YES' else 'NO ' end "B=C",
  case when a=c then 'YES' else 'NO ' end "A=C"
from t;
A=B B=C A=C
--- --- ---
YES YES NO

The equality operator is also supposed to be reflexive
a=a

This is unfortunately wrong with null

with t as (select null a from dual) 
select case when a=a then 'YES' else 'NO ' end "A=A" 
from t;
A=A
---
NO

:mrgreen:

OOW schedule

I have start building my schedule. There are about 1722 sessions to chose from this year, so choice is difficult!

Due to jet-lag, I mostly cannot do all what I planed. And I also like to spent some time by the exhibitors and the boosts.

Ok, the one I will not miss :
Steven Feuerstein : Break Your Addiction to SQL!
Amit Ganesh : Oracle Database 11g: The Next-Generation Data Management Platform
Bryn Llewellyn : Doing SQL from PL/SQL: Best and Worst Practices
Thomas Kyte : The Top 10–No, 11–New Features of Oracle Database 11g
Lucas Jellema : The Great Oracle SQL Quiz

SIG SOUG: times ten

I have been to a SOUG last Thursday.

We first had a presentation from Thomas Koch about performance in Zurich Kantonalbank. As I have been working as a DBA for about two years in that bank, I already had my opinion about performance there :?

The second presentation was about TimesTen. I must say I have never used Times Ten. So I was glad to hear Stefan Kolmar from Oracle presenting the product. Ok, here it is in a few lines.

In TimesTen, the whole database is in the memory. TimesTen is an Oracle Product and a Database, but it is not an Oracle Database. The objective must be to have a response time in microseconds and hundred of thousands of transactions per second. You have a log buffer, and you can decide to asynchronously dump the buffer to file.

Let me try to explain the example from Stefan :
You have a mobile phone company. Foreign call can be fairly expensive, so those transactions will be synchronously dumped to the disk. Local calls cost about 1 Euro in average. So if you dump the log to disk every ten transactions, in case of a failure an average of 5 Euros will not be billed. In this way you can select the transaction to have synchronous and the one to have asynchronous. It looks promising, but probably not for critical businesses like banking where you are required to guarantee zero data-loss.

There is an additional functionality in TimesTen which is called “cache for Oracle”. It is a layer between the client and the database. It does not offer the same functionality as Oracle. For example, you cannot do PL/SQL. But it may offer microsecond access.

I will document two examples :
1) read only
You have a flight reservation company. Flight reservation are very important, so they will be in the database (no data loss). Flight schedule are read-only for the client. They will be cached in TimesTen. So when accessing the timetables, it will be ultra-fast. When booking, it may take a few seconds.

2) on demand
You have a call center. When a customer phone, all data relative to the customer (history, name, contracts, contract details) are immediately loaded from the database in TimesTen. So when the Call Center employee asks for any info, they are immediately available

How much does it cost? Check on store.oracle.com

For a tiny database up to 2Gb it is 6000$/processor for 3 years. More options, more money…

Please RTFOM !

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

So as I was getting an ORA-55611, I opened a SR. The support engineer pointed me to the online documentation where I was astonished to read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA.

Well, Read The Fine Online Manual !!!

The second tar is related to long retention (about the age of the earth)


SQL> alter flashback archive fba01 
  modify retention 4106694757 year;

Flashback archive altered.

SQL> select retention_in_days
  from DBA_FLASHBACK_ARCHIVE;
RETENTION_IN_DAYS
-----------------
                1

:mrgreen:

isNumber in sql

I tried this in 11g
TABLE T

X
123
-1.2e-3
abc


select x, 
  to_number(
    xmlquery('number($X)' 
      passing x as x 
      returning content)) n 
from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

it is quite a common task to extract numbers from varchar2 and to dig out poor quality data.

select x, to_number(x) from t;
ERROR:
ORA-01722: invalid number

A well-known PL/SQL approach would be to use exception. Ex:


create or replace function f(x varchar2) 
return number is 
begin return to_number(x); 
exception when others then return null; 
end;
/
select x, f(x) n from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

another approach in plain sql could involve CASE and REGEXP

select x,
case when
regexp_like(x,
‘^-?(\+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
then to_number(x)
end n
from t;
X N
——- ———-
123 123
-1.2e-3 -.0012
abc

installing OID 10.1.4.2 Preview 1

Download oracle-oid-10.1.4.2.0-1.0.i386.rpm
Download oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Install the rpm
# rpm -i oracle-*.i386.rpm

In SLES 10, there is no /bin/cut, let’s create a link as root to avoid a mistake when running config-oid.sh
# ln -s /usr/bin/cut /bin/cut

Run the configure script as root
# /etc/init.d/oracle-oid configure
That’s all folks! It created an Oracle XE 10gR2 database, and configured a running database. Excellent!

LDAP Server is running and configured.

$ ldapsearch  cn=orcladmin dn
cn=orcladmin, cn=Users, dc=com

There is a nice video to run on linux : oracleauthenticationservices_demo.vvl
Save the file, set the display, then

$ chmod +x oracleauthenticationservices_demo.vvl
$ ./oracleauthenticationservices_demo.vvl

It shows also how to use Oracle LDAP Server OID to identify your Linux users with the preview of Oracle Authentication Service

Oracle Database 11g: The Top Features for DBAs and Developers

I am always delighted to read the top features by Arup Nanda.

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Now we have automatic partition creation :-D


create table t(d date) 
partition by range(d) 
interval(interval '1' month) 
(partition p1 values less than (date '0001-01-01'));

One partition must be created manually, here the partition will contain all dates from 1-JAN-4712BC to 31-DEC-0000 (which is not a legal date by the way)

There is also new syntax to query the partition

SQL> insert into t values (date '2000-01-10');

1 row created.

SQL> insert into t values (date '2000-01-20');

1 row created.

SQL> insert into t values (date '2000-03-30');

1 row created.

SQL> select * from t partition for (date '2000-01-01');
D
-------------------
10.01.2000 00:00:00
20.01.2000 00:00:00

Note the syntax can be used in any form of partitioning. Here in a list-list composite


SQL> create table t(x number, y number) 
  partition by list(x) 
  subpartition by list(y) 
    subpartition template (
      subpartition sp1 values(1),
      subpartition sp2 values(2)) 
  (partition values(1), partition values(2));

Table created.

SQL> insert into t values(1,2); 
1 row created.

SQL> select * from t subpartition for (1,2);
         X          Y
---------- ----------
         1          2

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

SQL> create table t(x clob)
  partition by system (
    partition p1, 
    partition p2, 
    partition p3, 
    partition p4);

Table created.

So far this seems fine. So what the problem? You cannot insert in that table!

SQL> insert into t values(1);
insert into t values(1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method

so you must define in which partition you want to add data. For example round robin. Or random. Whatever.


SQL> insert into t partition (P1)  values ('x');

1 row created.

SQL> insert into t partition (P2)  values ('y');

1 row created.

If you want to use bind variable, you can use dataobj_to_partition


SQL> select object_id 
  from user_objects 
  where object_name='T' 
    and subobject_name is not null;
 OBJECT_ID
----------
     55852
     55853
     55854
     55855

SQL> var partition_id number
SQL> exec :partition_id := 55852

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.
SQL> exec :partition_id := 55853

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.

Actually, SYSTEM partitioning is misleading, YOU are responsible for choosing the partition in which you want to insert, not the system :mrgreen: