Author: Laurent Schneider

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 […]

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, […]

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 […]

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

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), […]

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 | | […]

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 […]

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> […]

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 […]

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 […]

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 […]

I just received my book today. It has about 300 pages. Please post any comments here :

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 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, […]

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 […]

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 […]

It’s a great honor for me to be invited to speak in Sweden in December 10-11 ! I am optimistic in getting a few copies of my book to give away 🙂

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

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 ‘ ) ) , […]

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 […]

duplicate of 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 ? […]

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 […]

Whether you need to write in Chinese אני אוהב אותך Reverse your characters ʎuunɟ Play some chess ♔ or do some maths ≲∀∃∄∑ Unicode can helps you. It can be stored in the database, in email, in plain-text files. Read more : – Unicode Home Page

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, […]

I just downloaded and installed osdm Have a look at my first screen of the ERD generated from my Scott schema

$ 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 […]

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 […]

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 […]

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 […]

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 […]

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 […]