ORA-2070

Today I received an error which was totally new to me. And totally unexpected…

For the purpose of this post, I reduced it to a minimum
CREATE TABLE LSC_T1
( x number);
CREATE TABLE LSC_T2
( x number);
with c as (select nls_charset_decl_len(1, x)
from lsc_t1 join lsc_t2
using (x))
select 1 from c
union
select 1 from c;

Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context

My basic query was actually joining two dictionary views, and NLS_CHARSET_DECL_LEN is used in USER_TAB_COLUMNS.CHAR_COL_DECL_LENGTH.
Kind of
with c as (
select table_name, column_name, constraint_name
from user_tab_columns
join user_constraints
using (table_name)
where constraint_type='P'
)
select
constraint_name,column_name
from c
union all
select
column_name,constraint_name
from c;
Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context

I spent some time to understand the reason for getting an ORA-2070 in a simple query that did seem to be totally unrelated to charset. The database used for this test is 9.2.0.8/Solaris.

It is quite easy to avoid the bug, for instance by not using the ANSI join 😉

When analyzing such a defect, my approach is to simplify the query as much as possible. Very often on forums you see users posting queries of more than 100 lines. Those users just expect the community to debug their code. Posting a tiny reproducible test case is key to receive a proper solution!

dynamic database link

How do I select thru a database link, where the database link is not fixed?

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from LSC_T@:db_link where x>0;
select x from LSC_T@:db_link where x>0;
*
ERROR at line 1:
ORA-01729: database link name expected

My solution using dynamic SQL
Remote databases DB02

create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;

Remote databases DB03

create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;

Local database DB01
create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2)
return lsc_t_c is
rc lsc_t_c;
begin
execute immediate
'select lsc_t_o(x) from '
|| dbms_assert.qualified_sql_name(str) bulk collect into rc;
return rc;
end;
/

Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection

Ok, let’s try
SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
||:db_link)) where x>0;
X
----------
2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;
X
----------
3

Seems to work 🙂

SOUG-Tagung 09/2

I am impatient to speak in the swiss oracle user group next month

March 18, 2009, Baden-Daettwil
http://www.soug.ch

I will be presenting an XML session regarding selecting XML from the database.

select xmlserialize(document xmlcol) from agenda;

 
12:00

Empfang und Registrierung
SOUG Sekretariat

12:30

Stehlunch
im Foyer Seminarraum
Schottky

13:00

21.
Ordentliche Generalversammlung der SOUG,
Seminarraum Schottky
Ehrung des SOUG Speaker of the Year 2008, Seminarraum
Schottky
 

Track 1:
Oracle DB und Entwicklung
Moderation Manfred
Drozd, In&Out AG

Track 2:
Oracle
Applications
Moderation Michele
Savino, FI-Solutions AG

14:00



XML in the Oracle Database



Use SQL to retrieve and insert XML data in the database. Learn
about the SQL functions, the XMLTYPE and various techniques to
improve your performance when dealing with XML documents.

Laurent Schneider,
Credit Suisse


Oracle
Applications Portfolio Strategie, Mehrwert und Eco System

Durch die
Akquisition von über 50 Firmen hat sich Oracle vom Technologie
Leader zum Partner für Business Applikationen entwickelt. Dank
seiner kompletten, offenen und integrierten Plattform, ermöglicht
Oracle eine Reduktion der Integrationskosten. Industriespezifische
Blueprints ermöglichen den Kunden eine effiziente Geschäftsprozess
Modellierung

Beat Brunschwiler,
Oracle

14:45



Optimiertes Backup mit RMAN und der LiteSpeed Engine für Oracle

Der Vortrag
gibt zunächst einmal einen Überblick über die unterschiedlichen
Backupverfahren (RMAN, Hotbackup, Coldbackup) und die damit
verbundenen Vor- und Nachteile. Am Beispiel von RMAN mit LiteSpeed
Engine for Oracle wird dann erklärt, wie ein Backup optimiert
werden kann und welche Auswirkungen dies auf ein Recovery hat.

Johannes Ahrends,
Quest Software


Oracle
Application Integrations-Architektur

Ein grosser
Teil des Implementierungsaufwandes wird in IT Projekten für die
Integration von Applikations-Silos investiert. Dadurch erschwert
sich die Anspassung von neuen Geschäfts-anforderungen. Die
Applikation Integration Plattform standardisiert die Integration,
vom Geschäftsprozess bis zum Daten-Layer. Innovation kann dadurch
schnell umgesetzt werden

Jean-Claude
Haupfleisch, Oracle
15:30 Pause
(ca. 30′)

16:00



SQL Plan Baseline 



Diese Technologie ersetzt ab Oracle 11g die früheren Stored
Outlines.

Chris Antognini,
Trivadis



Siebel CRM Referenz Projekt



Beispiel einer Siebel CRM Implementierung bei einem Schweizer
Versicherer.

Lukas
Ehrensperger, ec4u
16:45 Apéro und Informationsaustausch
– Wir danken dem Sponsor Quest

date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
X D
---------- -------------------
2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS')
X D
---------- -------------------
2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
2 1 INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.
SQL> select * from lsc_t where
d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where
cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where
d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

X D
---------- -------------------
2000-01-01 06:00:00

Converting to date would deliver wrong result.

Tuning query over database link

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision.

Here is the test case :
DB01

create database link lsc_link_2 using 'DB02';
create table lsc_small_1 as
select rownum x from dual connect by level<100; alter table lsc_small_1 add primary key(x); create table lsc_big_1 as select rownum x from dual connect by level<1000000; alter table lsc_big_1 add primary key(x);

DB02

create table lsc_small_2 as
select rownum x from dual connect by level<100; alter table lsc_small_2 add primary key(x); create table lsc_big_2 as select rownum x from dual connect by level<1000000; alter table lsc_big_2 add primary key(x);

and here are the two test queries (run on DB01)

select count(*) from lsc_big_1 b1
join lsc_small_2@lsc_link_2 s2 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2
join lsc_small_1 s1 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:10.31

As shown above, first one is 100x faster. Anything to tune? For sure!

The trick is to execute the join remotely when the remote table is much larger than the local one!

select /*+ DRIVING_SITE(b2) */ count(*)
from lsc_big_2@lsc_link_2 b2
join lsc_small_1 s1 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30),
sql_txt varchar2(4000));

create or replace trigger audit_ddl_trg after ddl on schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent='TRUNCATE')
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/

Thanks Yas for your comment, I added the SQL text

high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :

--------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 |
| 1 | NESTED LOOPS | 18E| 15E| 18E (0)|999:59:59 |
| 2 | NESTED LOOPS | 18E| 15E| 99P (3)|999:59:59 |
| 3 | NESTED LOOPS | 4451T| 31P| 12T (3)|999:59:59 |
| 4 | NESTED LOOPS | 544G| 3045G| 1490M (3)|999:59:59 |
| 5 | NESTED LOOPS | 66M| 254M| 182K (3)| 00:36:31 |
| 6 | COLLECTION I | | | | |
| 7 | COLLECTION I | | | | |
| 8 | COLLECTION IT | | | | |
| 9 | COLLECTION ITE | | | | |
| 10 | COLLECTION ITER | | | | |
| 11 | COLLECTION ITERA | | | | |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen:

To bash or not to bash

I have been inspired by Chen to talk about bash…

I have been using ksh for many years, and I mean ksh88 not ksh93. The main reason is, I want my script to run the same way in any Unix flavor.

ksh93 has never been too much popular. I used it a few time to sleep half a second


echo sleep 0.5| /usr/dt/bin/dtksh

ksh has a lot of nice features. I just used one of them in my script :

$ typeset -u name
$ read name?"Enter your name : "
Enter your name : Laurent
$ echo $name
LAURENT

Way easier to force a variable to be uppercase rather than using echo|tr etc

Bash has some nice features too, but unfortunately every OS release come with a different bash version, which is the same pain as perl when you want to write a script that last for a decade or two.

Ok, just4fun

$ mkdir -p {a..z}/{1..9}
... create directories a/1 a/2 ... z/8 z /9
$ [[ text =~ t..t ]]
... check if text matches regular expression t..t
$ echo ${text/pattern/string}
... replace pattern by string

The first two commands require bash3, the last is just fine with bash2.

Have fun shell-scripting 🙂

How to tune something doing nothing?

To end this year in glory, I tried to replace a row-level trigger (LSC_TR1) into a statement level trigger (LSC_TR2)


SQL> create global temporary table lsc_t1(x number primary key)
2 on commit delete rows;
Table created.
SQL> create table lsc_t2(x number primary key);
Table created.
SQL> create table lsc_t3(x number primary key);
Table created.
SQL> create or replace function f return number deterministic is
2 begin sys.dbms_lock.sleep(1); return 10; end;
3 /
Function created.
SQL> create or replace trigger lsc_tr1 after insert on lsc_t1 for each row
2 begin
3 if (:new.x > f)
4 then
5 insert into lsc_t2(x) values (:new.x);
6 end if;
7 end;
8 /
Trigger created.
SQL> create or replace trigger lsc_tr2 after insert on lsc_t1
2 begin
3 insert into lsc_t2(x) select x from lsc_t1 where x > f;
4 end;
5 /
Trigger created.
SQL> insert into lsc_t3 values(1);
1 row created.
SQL> insert into lsc_t3 values(5);
1 row created.
SQL> insert into lsc_t3 values(20);
1 row created.
SQL> insert into lsc_t3 values(50);
1 row created.
SQL> insert into lsc_t3 values(100);
1 row created.
SQL> insert into lsc_t3 values(200);
1 row created.
SQL> insert into lsc_t3 values(1000);
1 row created.
SQL> commit;
Commit complete.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:07.05

The current trigger LSC_TR1 is very slow, it is doing some processing after each row. Since it is a temporary staging table, it makes no sense to process each row, I have written the trigger LSC_TR2 to boost the performance.

Have a look.


SQL> truncate table lsc_t2;
Table truncated.
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:01.03

Much faster ! LSC_TR1 just takes ages. What a glorious year 2008 🙂

Well, the table LSC_T3 is empty most of the time, and the insert is running again and again…


SQL> truncate table lsc_t3;
Table truncated.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:00.01
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:01.03

Suddenly my tuning exercice turned to be a disaster, in this example it is about 63 times slower 🙁

I could possibly count the rows in the LSC_TR2 trigger, but then it will still be a bit slower when processing very few rows (in this example with 1 row).

So I will keep this AFTER EACH ROW trigger until next year 😉

old-fashion listagg

Yesterday I had my first session about XML, today I have one about SQL Model

Ok, it was the first time I spoke about XML so I did not really now where to focus. XML is so big, you have XQUERY, XPATH, dozens of XML functions in the database.

One of the XML function is called XMLTRANSFORM and transforms XML according to XSLT

I had a fun demo about XSLT to create a semi-column separated list :

select
deptno,
xmltransform
(
sys_xmlagg
(
sys_xmlgen(ename)
),
xmltype
(
'


;

'
)
).getstringval() listagg
from emp
group by deptno;


DEPTNO LISTAGG
------- --------------------------------------
10 CLARK;KING;MILLER;
20 SMITH;FORD;ADAMS;SCOTT;JONES;
30 ALLEN;BLAKE;MARTIN;TURNER;JAMES;WARD;

HOT


CREATE TABLE T(X NUMBER PRIMARY KEY) ORGANIZATION HEAP;

I have read the SQL reference more than once -believe me- but I did not notice this syntax until today… an HOT table is a simple table and this clause is optional. An IOT, or INDEX ORGANIZED TABLE, is a table where you mostly select thru an index. Good candidates for IOT are tables where you mostly access rows thru a full index scan.

Compare :


SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION HEAP;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 609992009

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (0)| 0
| 1 | NESTED LOOPS | | 2 | 46 | 3 (0)| 0
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (0)| 0
|* 3 | INDEX UNIQUE SCAN| SYS_C0010139 | 1 | 13 | 0 (0)| 0
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
LSC_SELECTED_EMPLOYEES TABLE
SYS_C0010139 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES;

Table dropped.

with

SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES2 (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION INDEX;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES2 using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 3539129569

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (
| 1 | NESTED LOOPS | | 2 | 46 | 3 (
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (
|* 3 | INDEX UNIQUE SCAN| SYS_IOT_TOP_23608 | 1 | 13 | 0 (
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES2"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
SYS_IOT_TOP_23608 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES2;

Table dropped.

The plan is the same, in IOT, you have only one segment, the INDEX, and in HOT, you have two segments, the INDEX and the TABLE.

Surely a good candidate for IOT 🙂

New WordPress

I am using godaddy as hosting provider. It is fairly cheap and reliable, and when I moved from blogspot to my own domain, I realised the “economic” plan I was using did not allow to keep on using blogspot on my own domain (something with active/passive ftp).

The wordpress tool they offered worked well, but is no longer supported and they provided no upgrade path to 2.6.x, so when I installed 2.6, it just deleted all my posts, anyway I had a backup and managed to import (as sql statements) my posts in the new blog… lucky!

Please mention me any annoyance you may have with this new one !

my book is in Louisville

I just could not wait any longer for my free copies so I ordered my own book on amazon :mrgreen:

amazon 0977671585

I am now tracking my package …

Information about shipment

Ship Carrier: UPS
Tracking Number: 1Z5R89396630405037
Status: In transit
Order #: 104-6746451-0852226
Shipment Date: November 24, 2008
Destination: BIRMENSDORF, CH
Estimated Arrival: November 28, 2008

Track your package

Date Time Location Event Details
November 25, 2008 02:00:00 AM LOUISVILLE KY US Arrival Scan
November 24, 2008 08:59:00 PM PHOENIX AZ US Departure Scan
November 24, 2008 06:59:00 PM PHOENIX AZ US Arrival Scan
November 24, 2008 06:37:00 PM PHOENIX AZ US Departure Scan
November 24, 2008 04:43:00 PM PHOENIX AZ US Shipment received by carrier
November 24, 2008 01:19:48 PM US Shipment has left seller facility and is in transit

one more OCE certification

I have passed my Oracle 9i Certified Master exam in 2004. Since then the 10g exam has been in preparation.

Well, according to dba10gocm_upgrade the OCM Upgrade exam content has not been finalized.

There is also an OCM Member restricted website. In case you have the password, you can read :
More Great Benefits Coming Soon . . .

Soon=Forever+a few centuries?

According to Paul answer to my comment on his blog : we are developing a one-day upgrade exam for 10g OCM to 11g OCM and will include a streamlined path for 9i OCM to 11g OCM

Ok, I am a bit pessimistic about the future of OCM. As OCM Gregory Guillou posted on my previous blog post :
It doesn’t really make any sense to have an upgrade exam for the what ? 50 9i OCM ?

Since about last year there is a way more succesful exam suite. The Oracle Certified Expert. I did myself pass the rac and sql ones.

Ok, the news :
There is a new Oracle Certified Expert exam which is called : Oracle Database 11g Performance Tuning Certified Expert

The exam is in Beta now, good luck to you!

<CTRL> + .

Sometimes the simplest tricks just ease your life the nice way.

While typing code in TOAD, I just found out those two handy keyboard shortcuts

<CTRL> + . = Autocompletes tablenames
<CTRL> + T = Popups the list of column names

I want to select everything from emp and dept joined on deptno.


SELECT*FROM E e,D d
where e.=d.

Especially useful when table and column is not EMP.DEPTNO but WWRTM_CUOTDSOTM_IWUWH_ML.H2OUUP2_NIAGASTSGM_IMUMTUCI 🙂

select in HTML format, XQUERY variant

Same as Select in html format, but with XMLTABLE instead of XMLTRANSFORM


select
xmlroot(
xmlelement(
"table",
xmlconcat(
xmltype(
'<tr><th>DEPTNO</th><th>DNAME</th><th>LOC</th></tr>'),
xmlagg(column_value)
)
),version '1.0'
)
from
xmltable('
for $f in ora:view("LSC_DEPT")
return
<tr>
<td>{$f/ROW/DEPTNO/text()}</td>
<td>{$f/ROW/DNAME/text()}</td>
<td>{$f/ROW/LOC/text()}</td>
</tr>');

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

select in HTML format

Last Wednesday I selected data from an HTML table : select from xml

Today, let’s try the opposite, generate an HTML table from a SQL query


select
XMLSERIALIZE(
DOCUMENT
XMLROOT(
XMLTRANSFORM(
XMLTYPE(
CURSOR(
SELECT * FROM DEPT
)
),
XMLTYPE.CREATEXML(
'




Table DEPT

Table DEPT in HTML format


DEPTNO DNAME LOC




'
)
)
, VERSION '1.0')
)
from DUAL;

Table DEPT in HTML format

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

Largely inspired from xmlfiles.com

select from xml

My wife is so happy that an African president is ruling the world for the next four years !

Ok, here is a simple exercice :

How do you select from an HTML file ?

Take an example

The table of departments

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


create table T of XMLTYPE;

insert into T values(
XMLTYPE(
'


Employee table

The table of departments

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


'));

select deptno, dname, loc
from T t,
xmltable(‘/html/body/table/tr’
passing value(t) columns
deptno number path ‘/tr/td[1]’,
dname varchar2(10) path ‘/tr/td[2]’,
loc varchar2(10) path ‘/tr/td[3]’
);


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

xmltable is a 10gR2 features. For those with 9i, use TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),’/html/body/table/tr’))) instead.

Note the html file must be in XHTML format, some rewritting may imply. SQLPLUS for example does not generate html files that are valid XHTML documents, for instance because of the <p> tags that to not have a </p> tag. Note that xml tags are case sensitive.

echo does not accept end of arguments operator

Let’s start with an example :

$ cat AI
#!/usr/bin/bash
while :
do
echo "What's your name ?"
read a
if [ ! $a ]
then
break
fi
echo "Your name is :"
echo $a
echo
done
echo "Bye"

$ ./AI
What's your name ?
Jo
Your name is :
Jo

What's your name ?
Jack
Your name is :
Jack

What's your name ?
-e
Your name is :

What's your name ?

Bye

This artificial intelligence is not very intelligent, it cannot recognize me if I am called “-e” (it is Friday, have a look at Little Bobby Tables ).

Most unix tools consider
-- signals the end of options and disables further option processing

But not echo 🙁
$ touch -e
touch: invalid option -- e
Try `touch --help' for more information.
$ touch -- -e
$ ls -l -e
ls: invalid option -- e
Try `ls --help' for more information.
$ ls -l -- -e
-rw-r--r-- 1 lsc dba 0 Oct 31 15:44 -e
$ rm -e
rm: invalid option -- e
Try `rm ./-e' to remove the file `-e'.
Try `rm --help' for more information.
$ rm -- -e
$ echo -e

$ echo -- -e
-- -e

So, what’s the solution? well, probably not using “echo”, for example printf

$ (echo “What’s your name ?”
read a
echo “Your name is :”
printf “%s\n” “$a”)
What’s your name ?
-e
Your name is :
-e

Flying toasters and dense_rank

Have fun with this caps-lock user question :

asktom:Logic behind the DENSE_RANK

This is one more statement on how to not use order by

like in

select ename, deptno, row_number() over (order by 1)
from emp order by 2;
ENAME DEPTNO ROW_NUMBER()OVER(ORDERBY1)
---------- ---------- --------------------------
CLARK 10 1
KING 10 2
MILLER 10 3
JONES 20 4
FORD 20 5
ADAMS 20 6
SMITH 20 7
SCOTT 20 8
WARD 30 9
TURNER 30 10
ALLEN 30 11
JAMES 30 12
BLAKE 30 13
MARTIN 30 14

According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny 🙂

Stored outlines

Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on
select empno from emp where ename='SCOTT';

Outline created.

SQL> create unique index i on emp(ename)
tablespace my_slow_tape_drive;

Index created.

SQL> set timi on
SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Elapsed: 01:45:59.95

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
2 1 INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older 😉 ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline


SQL> alter session set use_stored_outlines=emp_scott;

Session altered.

Elapsed: 00:00:00.00
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text
from v$sql
where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30 select empno from emp where
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where

The plan using the outline is now used

where is my database link listed?


$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X

Hey, why does this work???

Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more 😉

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM

updatedb does not work with samba drives and cygwin

updatedb and locate are parts of the findutils package, which exists since a long time on most unix / linux flavors.

Something I never get worked is to search for files on network drives with Cygwin, I always got :

find: /cygdrive/y/Favorites/Links/del changed during execution of find (old inode number -474324984, new inode number -44545478
4, filesystem type is system) [ref 1114]
find: /cygdrive/y/Favorites/Links/del changed during execution of find (old inode number -513303800, new inode number -47432498
4, filesystem type is system) [ref 1114]

which is because the file system is mounted and unmounted on demand and get new inodes.

So I wrote a hack in /usr/bin/updatedb


# $find $NETPATHS $FINDOPTIONS …
# lschnei2
(
for d in c: y:
do
cd $d
/cygdrive/c/OS/system32/attrib /s
done
) |
sed ‘s,………..\(.\):,/cygdrive/\1,;y,\\,/,’ |
tr ‘\r’ ‘\0’ | tr -d ‘\n’

Then I can search efficiently


$ time updatedb

real 0m18.273s
user 0m3.806s
sys 0m7.332s

$ time locate hosts
/cygdrive/C/B28359_01/rac.111/b28252/img/cluster_hosts_table.gif
/cygdrive/C/B28359_01/rac.111/b28252/img_text/cluster_hosts_table.htm
/cygdrive/C/OS/system32/drivers/etc/hosts
/cygdrive/C/OS/system32/drivers/etc/lmhosts.sam
/cygdrive/Y/.ssh/known_hosts

real 0m0.261s
user 0m0.265s
sys 0m0.015s

Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i


SQL> create table lsc_t as
2 select 1 parent, 2 child from dual
3 union all select 2,3 from dual
4 union all select 4,5 from dual
5 union all select 5,6 from dual
6 union all select 6,4 from dual;

Table created.

SQL> select parent,child
2 from lsc_t
3 where level=50
4 connect by parent=prior child
5 and level<=50 6 and prior dbms_random.value != dbms_random.value; PARENT CHILD ---------- ---------- 5 6 6 4 4 5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

Oracle Streams

If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo

Oracle OpenWorld Unconference

11g release 1 patchset 1

I just notice on Sven Blog that 11.1.0.7 is available. I have recently upgraded my connection at home so it took a bit less than half an hour to download this 1.5G patchset


$ wget -O p6890831_111070_Linux-x86-64.zip http://oracle-updates.oracle…
–10:17:40– http://oracle-updates.oracle.com/ARUConnect/p6890831_111070_Linux-x86-64..
Resolving oracle-updates.oracle.com… 87.248.199.23, 87.248.199.24
Connecting to oracle-updates.oracle.com|87.248.199.23|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 1,613,366,248 (1.5G) [application/zip]

100%[==================================>] 1,613,366,248 891.73K/s

10:47:31 (879.89 KB/s) – `p6890831_111070_Linux-x86-64.zip’ saved [1613366248/1613366248]

$ unzip p6890831_111070_Linux-x86-64.zip
$ cd Disk1
$ ./runInstaller
Starting Oracle Universal Installer…
Preparing to launch Oracle Universal Installer from
Oracle Universal Installer, Version 11.1.0.7.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

$ su –
root’s password:
# /u00/app/oracle/product/11.1/db_1/root.sh
Running Oracle 11g root.sh script…
# exit
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Sat Sep 20 11:10:35 2008

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

Connected to an idle instance.

SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
SQL> set time on
11:42:21 SQL> sho parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 200M
11:42:25 SQL> alter system set sga_target=300m scope=spfile;

System altered.

11:42:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11:42:43 SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
11:43:01 SQL> @?/rdbms/admin/catupgrd
…
11:54:03 SQL> Rem END catupgrd.sql
11:54:03 SQL> startup
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2159272 bytes
Variable Size 226495832 bytes
Database Buffers 79691776 bytes
Redo Buffers 4812800 bytes
Database mounted.
Database opened.
11:56:28 SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION
———————————– ——– ———-
Oracle Ultra Search VALID 11.1.0.7.0
Oracle XML Database VALID 11.1.0.7.0
Oracle Text VALID 11.1.0.7.0
Oracle Expression Filter VALID 11.1.0.7.0
Oracle Rules Manager VALID 11.1.0.7.0
Oracle Workspace Manager VALID 11.1.0.7.0
Oracle Database Catalog Views VALID 11.1.0.7.0
Oracle Database Packages and Types VALID 11.1.0.7.0
JServer JAVA Virtual Machine VALID 11.1.0.7.0
Oracle XDK VALID 11.1.0.7.0
Oracle Database Java Packages VALID 11.1.0.7.0

My first try with 200Mb sga target did not succeeded. But with 300Mb sga target it worked fine.