Laurent Schneider

I have attended the sql certified expert beta exam this morning. There were a lot of errors in it, I added in the comment that they have to groundly review their regular expressions questions. There were a lot of rubbish question, but hardly any challenge, it is more like detecting the incorrect syntax. So I am deceived. I have to wait 3 months for the result, but I expect no more than 90%

They even have an exhibit with a table containing many columns with the same name (!)

Well, I hope they will improve with the comments I made when the production release will come out.

The time to answer the question is sufficient. I had only 139 questions in 3 hours, and I needed only 2 hours actually with plenty of time to review.

Blogroll, certification, sql

I read on this site that Oracle11g GA is for 2007 August

http://www.dba-oracle.com/t_oracle_11g_hits_the_streets.htm

11g, Add new tag, Blogroll

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not

SQL> create table t(x number);

Table created.

SQL> set transaction read only ;

Transaction set.

SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

If I wait one minute after my create table statement, it works

SQL> drop table t;

Table dropped.

SQL> create table t(x number);

Table created.

SQL> host sleep 60

SQL> set transaction read only;

Transaction set.

SQL> select * from t;

no rows selected

Blogroll, dba, sql

Oracle Certification Program: Exam Details

I immediately registered for this exam and will do it next week, I just cannot wait for this one…

Add new tag, Blogroll, certification, sql, sqlplus

I do update t set x=:a;

If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE.

I need to take care of null, I can update null with something, or something with null, but update null with null is also unnecessary.

SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.34
SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.32
SQL> update t set x=:a
  2  where x!=:a
  3  or (x is null and :a is not null)
  4  or (x is not null and :a is null);

0 rows updated.

Elapsed: 00:00:00.04

Blogroll, sql

I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only…

I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go

create or replace procedure p is
   rindex binary_integer;
   slno binary_integer;
   sofar number;
   totalwork number;
   target number;
begin
   totalwork := 5;
   sofar := 0;
   select object_id 
   into target 
   from user_objects 
   where object_name = $$PLSQL_UNIT;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
end;
/

Now I execute it

SQL> exec p

PL/SQL procedure successfully completed.

and monitor it

SQL> select sid,opname,target,sofar,totalwork,units
  2   from v$session_longops;
 SID OPNAME       TARGET   SOFAR TOTALWORK UNITS    
---- ------------ -------- ----- --------- ---------
 538 Executing... SCOTT.P      5         5 Statement

Blogroll, dba, plsql, sql