Author: Laurent Schneider

Congrats to Denes Kubicek for being the first journeyman on OTN. OTN stars Justin and Nicolas (18954 and 15321 posts) do not get this title, as we Oracle Ace are sticked to our Ace logos. OTN upgraded the forums recently, many posts about this : OTN Forums – Change the look and feel yourself OTN […]

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

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

What is the difference between rollbac and rollback? SQL> create table t as select 1 x from dual; Table created. SQL> update t set x=2; 1 row updated. SQL> savepoint a; Savepoint created. SQL> update t set x=3; 1 row updated. SQL> rollbac to savepoint a; Rollback complete. SQL> select * from t; X ———- […]

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

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

I noticed a side effect of SET LONGC today… The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80. So why not setting it to the maximum? Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE SQL> create […]

I just notice a new blog on blogs.oracle.com : Oracle Certification Blog This is probably the right place to ask questions or get news about certification. Out of the many questions I may ask here are just a few I am preparing. – When will the OCM upgrade for 9i OCM be available ? – […]

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

I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂 The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. […]

It is quite a while I have not posted about Linux. The reason is I have bought in new notebook three months ago with Vista and did not have a program to resize the partition… Do I need a program? NO, I do not !!! Vista has disk management that can resize my online partition. […]

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

One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz. It will ship in December 2008. http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585

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

Word 2007 allows to write a blog post and I thought I must try 🙂 I used Windows Live Writer to add this comment

I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need […]

I just noticed 10.2.0.4 is out. I started downloading 1,916,781 KB !

RTFM is not the best answer … man crontab SunOS 5.10 Last change: 10 Nov 2005 User Commands crontab(1) A crontab file consists of lines of six fields each. The fields are separated by spaces or tabs. The first five are integer patterns that specify the following: minute (0-59), hour (0-23), day of the month […]

warning: the script below is destructive and not 100% safe update: it is unsafe to drop SYS_ objects, check for instance note 579399.1 A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; […]

I answered a question on otn today about distinct. Reprased, how to select distinct collection? select job, collect(distinct deptno) deptnos from emp group by job; JOB DEPTNOS ——— ——————————————- ANALYST SYSTPTJCzBffh0AjgQ59n0o3QCA==(20) CLERK SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) MANAGER SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10) SALESMAN SYSTPTJCzBffh0AjgQ59n0o3QCA==(30) 5 rows selected. select distinct collect(distinct deptno) deptnos from emp group by […]

Hey, This plugin seems to do exactly what I am looking for, enable users to write code in comments. Preserve Code Formatting Please test here and give feedback Warning: this changed the behavior of old posts too, so I have to reformat all older posts to no longer use < in code 🙁

Unix users will enjoy this : 10.2.0.4 is available on Solaris and HPUX patchset 6810189 Still waiting for AIX…

Today I answered a question on otn regarding order by. Let’s build a different test case. You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column. SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A; Grant succeeded. SYS@LSC01> […]

I have decided to remove disqus comments. What I considered to be a bonus, that is CODE POSTING, appears to be worst than before, because the PRE tag does not work. The main reason I switched back to last week behavior is that the COMMENT FEED does not work any longer. And I hate changes […]

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way : SQL> create or replace directory alert as 2 ‘/app/oracle/diag/rdbms/lsc01/lsc01/alert’; Directory created. SQL> var c clob SQL> declare 2 b bfile […]

I just replaced the default wordpress comments by Disqus comments. Disqus enable you to track your comments add picture and other fancy staff. I have been looking for such a plugin for a very long time (and was too foul to develop it myself). The amazing bonus with Disqus is that you can post code! […]

I have enabled tracing in a session and now I want to retrieve the name of the tracefile. Ex: my session has sid 335. How do I retrieve trace file name from sqlplus ? select pid from v$process where addr in (select paddr from v$session where sid=335); PID ———- 47 Now I can use oradebug […]

blog.gralike.com Marco is one of the top expert with XML and Database. Congrats Marco for receiving the Ace trophee 🙂

What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will […]

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen Ok, I give a try. To make the query unreadable and unformatable I used no space, no new line, and I started by q’

Cursor sharing transform strings in bind variable. So if you do SELECT * FROM EMP WHERE ENAME=’SCOTT’; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0; This sometimes improved performance of application that do not use binds, for instance. Now let’s imagine your application generates trace files. The application generates a SELECT ‘LSC-11.1011.000.1110.1.0100.000.110’ […]