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<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 :)

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

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

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

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.