Laurent Schneider

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 !

blog

I just received my book today.

It has about 300 pages.

Please post any comments here :

book book, oracle

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

amazon 0977671585

I am now tracking my package …

Information about shipment

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

book

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!

11g, Add new tag, Blogroll, certification

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<CTRL+.> e,D<CTRL+.> d 
where e.<CTRL+T><down><enter>=d.<CTRL+T><enter><CTRL+ENTER>

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

toad

It’s a great honor for me to be invited to speak in Sweden in December 10-11 !

More details on orcan.se ==> agenda

I am optimistic in getting a few copies of my book to give away

Add new tag, Blogroll, event, sql, xml

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>                                          

sql, xml

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

Largely inspired from xmlfiles.com

sql, 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

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.

sql, xml