Multirow Inserts

Uncategorized
| May 28, 2007
While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?INSERT INTO table (column1, column2)VALUES (value1, value2), (value1, value2);ERROR at line 1:ORA-00933: SQL command not properly endedUnlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support

What Makes a Great Oracle Blog?

Uncategorized
| May 25, 2007
Along the side of my page, you'll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. Studying them, I think you'll find that each of them share the same core qualities listed below.1. AccuracyAccuracy is an absolute must. Just

Returning into clause and post statement triggers

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case (more...)

What is Timeout?

The problem with semi documented APIs like DBMS_SPACE is that some of the subprograms might not work as you might expect them to.
For example the object_space_usage procedure has a parameter called timeout_value which I would assume to mean the amount of time after the procedure will abort if it cannot complete its space calculations. But this does not work as I would expect it to

SQL> set timing on
SQL> set serveroutput on

SQL> declare
v_space_used number;
v_space_allocated number;
begin
dbms_space.object_space_usage('SCOTT','EMP','TABLE',0,v_space_used, v_space_allocated,'',TRUE,5);
dbms_output.put_line('SPACE USED = '||v_space_used);
dbms_output.put_line('SPACE ALLOCATED = '||v_space_allocated);
end;
/

SPACE USED = 0
(more...)

Pipelined functions in oracle 9i

Uncategorized
| May 17, 2007
Functions can now stream data as virtual tables. May 2002 (updated May 2007)

APEX reports and SQL Developer

Both Kris Rice and Dimitri Gieles blogged about new APEX reports in SQL Developer 1.1.3 . Minor detail: those reports are build for Application Express version 3.0.1 , which is not released yet.

I installed the new version of SQL Developer, and tried to run the APEX (more...)

ANSI Joins

Uncategorized
| May 4, 2007
Like most of us, I still join tables in my SQL queries the old-school way. Simply put:SELECT whateverFROM table1 t1, table2 t2WHERE t1.id = t2.idAND t1.value > 10;But increasingly often I run into people who use ANSI joins instead. They were either introduced to SQL with Oracle 9 (or Sybase 12, etc), and were taught to use the SQL standard way, or else they made the conversion at some point in