Category Archives: sql

not a group by expression?

How do I count all objects in the current schema?


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 1 09:58:46 2009

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

Connected to:
Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

SQL> select
  2    user,
  3    count(*)
  4  from
  5    user_objects
  6  where
  7    status='INVALID';
  user,
  *
ERROR at line 2:
ORA-00937: not a single-group group function

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

It does not seem to work … What’s wrong?

locking and database link

1!=1

21 Replies

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function.

I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic).

To limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !


SQL> select * from dual where 1/3*3=1
no rows selected.

Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.

perl :


$ perl -e 'print 1 if 1/3*3==1'
1

OK, I tried in java


public class X {
  public static void main(String[] args){
    if (1/3*3<1) {
      System.out.println("<\n");
    }
  }
}
$ javac X.java
$ java X
<

In java it seems different too.

So what is 1/3*3 in Oracle?


SQL> set numw 50
SQL> select 1/3*3 from dual;

                                             1/3*3
--------------------------------------------------
         .9999999999999999999999999999999999999999

Surprised?

package version control

5 Replies

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :


select 
  env,name,type,line,text 
from (
  select 
    env,name,type,line,text,
    count(distinct text) over(partition by name,type,line) c
  from (
    select 
      'TEST' env,name,type,line,text 
    from 
      user_source 
    union all
    select 
      'PROD' env,name,type,line,text 
    from 
      user_source@prod
  )
)
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round

I also explained TOAD method in how to compare schema a few months ago.

commit suicide;

8 Replies

Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed.

You have to try this :


SQL> alter session set events 'immediate crash';
alter session set events 'immediate crash'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

A very decent method for killing yourself

ORA-2070

2 Replies

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

10 Replies

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

date or timestamp literals

3 Replies

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

4 Replies

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

16 Replies

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

8 Replies

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

How to tune something doing nothing?

3 Replies

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

3 Replies

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
     (
       '<?xml version="1.0"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/ENAME">
              <xsl:value-of select="text()"/>;</xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>'
     )
  ).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

2 Replies


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

select in HTML format, XQUERY variant

3 Replies

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>');

XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('<TR>
--------------------------------------------------
<?xml version="1.0"?>                             
<table>                                           
  <tr>                                            
    <th>DEPTNO</th>                               
    <th>DNAME</th>                                
    <th>LOC</th>                                  
  </tr>                                           
  <tr>                                            
    <td>10</td>                                   
    <td>ACCOUNTING</td>                           
    <td>NEW YORK</td>                             
  </tr>                                           
  <tr>                                            
    <td>20</td>                                   
    <td>RESEARCH</td>                             
    <td>DALLAS</td>                               
  </tr>                                           
  <tr>                                            
    <td>30</td>                                   
    <td>SALES</td>                                
    <td>CHICAGO</td>                              
  </tr>                                           
  <tr>                                            
    <td>40</td>                                   
    <td>OPERATIONS</td>                           
    <td>BOSTON</td>                               
  </tr>                                           
</table>

select in HTML format

4 Replies

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(
      '<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
  <html>
    <head>
      <title>Table DEPT</title>
    </head>
    <body>
    <p>Table DEPT in HTML format</p>
    <table border="1">
      <tr>
        <th align="right">DEPTNO</th>
        <th align="left">DNAME</th>
        <th align="left">LOC</th>
      </tr>
<xsl:for-each select="/ROWSET/ROW">
      <tr>
        <td align="right"><xsl:value-of select="DEPTNO"/></td>
        <td align="left"><xsl:value-of select="DNAME"/></td>
        <td align="left"><xsl:value-of select="LOC"/></td>
      </tr>
</xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>'
        )
      )
    , 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

5 Replies

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(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<html>
  <head>
    <title>Employee table</title>
  </head>
  <body>
    <p>The table of departments</p>
    <table border="1">
      <tr>
        <td>10</td>
        <td>ACCOUNTING</td>
        <td>NEW YORK</td>
      </tr>
      <tr>
        <td>20</td>
        <td>RESEARCH</td>
        <td>DALLAS</td>
      </tr>
      <tr>
        <td>30</td>
        <td>SALES</td>
        <td>CHICAGO</td>
      </tr>
      <tr>
        <td>40</td>
        <td>OPERATIONS</td>
        <td>BOSTON</td>
      </tr>
    </table>
  </body>
</html>'));

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.

Flying toasters and dense_rank

2 Replies

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

2 Replies

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?

3 Replies


$ 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

Cycling

10 Replies

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

About case sensitivity

1 Reply

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase.

SQL> SELECT DUMMY FROM DUAL;
D
-
X

SQL> select dummy from dual;
D
-
X

Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and columns.


/* Formatted on 2008/09/19 10:00 
(Formatter Plus v4.8.8) */
SELECT dummy
  FROM DUAL;

Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
                    *
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!


SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

puzzled by Toad

1 Reply

one colleague just showed me how to insert duplicate rows in a table that has a primary key.


create table t(x number primary key);[F5]
Table created.
insert into t values (1);[F5]
1 row created.
insert into t values (1);[F5]
1 row created.

Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

Let’s look at Toad.ini (or View – ToadOptions – Transaction)


[SETTINGS]
ALWAYSROLLBACK=1
THREADQUERIES=1

Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

Let’s prove it :


select count(*) from t where x=1;[F5]

  COUNT(*)
----------
         0

1 row selected.

what is faster, select count(*) or select count(pk) ?

6 Replies

Oh no, not another post about COUNT(*) and COUNT(1)

Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.


SQL> create table t as select rownum id,
  2    lpad('a',4000,'a') a,
  3    lpad('b',4000,'b') b,
  4    lpad('c',4000,'c') c,
  5    lpad('d',4000,'d') d
  6  from dual connect by level<10000;

Table created.

SQL> create index i on t(id);

Index created.

SQL> alter table t add primary key (id) 
  2    using index i disable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'I')

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select count(id) from t;

 COUNT(ID)
----------
      9999

Elapsed: 00:00:00.01
SQL> select count(*) from t;

  COUNT(*)
----------
      9999

Elapsed: 00:00:01.43

My count(*) just takes too long… Why that?

I have no index on a not null column. The primary key is disabled.

Of course the count(pk) does not count the rows with pk=null, but it is faster

read uncommitted

6 Replies

the default isolation level is READ COMMITTED. It means, a session read the committed data.

Session 1:


SQL> set transaction isolation level read committed;

Transaction set.

Session 2:


SQL> update emp set sal=4000 where ename='SCOTT';

1 row updated.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

Session 2:


SQL> commit;
Commit complete.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      4000
SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;
Commit complete.

When the session 1 reads the salary of Scott, it gets the value that is committed in the database.

Another isolation level is SERIALIZABLE.

Session 1:


SQL> set transaction isolation level serializable;

Transaction set.

Session 2:


SQL> update emp set sal=5000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> update emp set sal=sal+1;
update emp set sal=sal+1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> roll
Rollback complete.
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      5000

SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.

In session 1, the isolation level of the transaction is set to SERIALIZABLE. Session 2 update the salary of Scott to 5000 and commits. The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction. The session 1 can then read committed data and update the salary to 3000.

Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question :

- Is it possible in Oracle to read uncommitted data from another session?

Let’s try

Session 1:


SQL> var rc number
SQL> set autop on
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> exec :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1), 
  DBMS_XA.TMNOFLAGS)

PL/SQL procedure successfully completed.

        RC
----------
         0

SQL>
SQL> UPDATE emp SET sal=6000 WHERE ename='SCOTT';

1 row updated.

SQL> exec :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), 
  DBMS_XA.TMSUSPEND)

PL/SQL procedure successfully completed.

        RC
----------
         0

Session 2:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> var sal number
SQL> var rc number
SQL> set autop on
SQL> begin
  :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),DBMS_XA.TMRESUME);
  SELECT SAL INTO :SAL FROM EMP WHERE ENAME='SCOTT';
  :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), DBMS_XA.TMSUCCESS);
  :rc:=DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(1));
end;
/

PL/SQL procedure successfully completed.

       SAL
----------
      6000

        RC
----------
         0

SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

So yes, you can read uncommitted data in a global transaction from another session. But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

return size of to_char

4 Replies

The width of a columns is known before execution.

Well, in most cases…


SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH') 
  FROM DUAL;
TO_CHAR(
--------
SAMEDI

The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.


SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT

Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED 
EIGHTY-EIGHT

again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

let’s try with spelling the nanoseconds :


select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION

here 78 is a really bad guess… the nanoseconds could be 100 character long !

What happened then :


select to_char(timestamp '0001-01-01 00:00:00.777777777',
  'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
               *
ERROR at line 1:
ORA-01877: string is too long for internal buffer

The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

It simply means Oracle sets the width column to something too small to contain the result.

By setting the column to something longer, the query should work…


select cast(to_char(timestamp '0001-01-01 00:00:00.
  777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN

Ok, let’s do something perverse

Let set the CURSOR_SHARING to FORCE !

Have a look :


SQL> alter session set cursor_sharing='FORCE';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION

the width of the column is evaluated to 75. 75 however is not the maximum length!


SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
       *
ERROR at line 1:
ORA-01801: date format is too long for internal buffer

Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

Let’s revert to EXACT for confirmation.

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION

SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED 
SEVENTY-SEVEN

secondmax

12 Replies

How do I retrieve the second highest salary of emp?

There is no right or wrong treatment of duplicates, there are only buggy specifications…

There are plenty of ways to do this.

An Oracle 7 approach would be

SELECT MAX(SAL) 
FROM EMP, (
  SELECT MAX(SAL) MAXSAL FROM EMP) 
WHERE SAL<MAXSAL;

Using TOP-n queries I could use

 
SELECT SAL 
FROM (
  SELECT ROWNUM R,SAL FROM (
    SELECT SAL FROM EMP ORDER BY SAL DESC)) 
WHERE R=2;

Using ROW_NUMBER (or RANK or DENSE_RANK), I could use


SELECT SAL 
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP) 
WHERE R=2;

Another approach would be to create a SecondMax aggregate function, as the following example from the doc
Example 11-12 How to Create and Use a User-Defined Aggregate Function

SELECT SecondMax(sal)
FROM emp;

Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier


SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC) 
FROM EMP;

Epoch

2 Replies

How do I get the current number of milliseconds since 1970-01-01.

I still have no access to oracle.com so I created a new account to answer this question.

Either you use the difference between timestamp ’1970-01-01 00:00:00 +00:00′ and current_timestamp, or you can use java, which is more portable.

YMMV

create function epoch return number as language java name 
  'java.lang.System.currentTimeMillis() return int';
/ 
select epoch from dual;
           EPOCH
----------------
   1214562599878

Hey Scott, where have you been ?

6 Replies

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

12 rows selected.

Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt


(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
  'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found 
  where a numeric was expected

Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Welcome back Scott!

Laurent Schneider

Oracle Certified Master

Category Archives: sql

locking and database link

1!=1

package version control

commit suicide;

ORA-2070

dynamic database link

date or timestamp literals

Tuning query over database link

track DDL changes

high cost

How to tune something doing nothing?

old-fashion listagg

HOT

select in HTML format, XQUERY variant

select in HTML format

select from xml