The Inbound File Adapter can be used for polling and processing new files in a specified directory. With the Adapter Configuration Wizard you can create this File Adapter Service quite easily. Especially if you follow an example! In step 7 of the wizard I decided to deviate from my example […]
Jurgen Kemmelings
New in Oracle 11g: Read Only Tables
In Oracle 11g database it is possible to make a table really read only, also for the owner of that table. Until now a table could only be made read only to other users, by granting only the select privilege to these users. But the owner of the table was […]
New in Oracle 11g: wait added to lock table syntax
In Oracle 11g you can add a wait clause to the lock table command in which you can specify how many seconds you want to wait to get the lock on the table. SQL> lock table t in exclusive mode wait 10; lock table t in exclusive mode wait 10 […]
New in Oracle 11g: PL/SQL Function Result Cache
A new feature in the Oracle 11g database is the ‘Function Result Cache’. When you create a function with the new clause ‘RESULT_CACHE’ the result of the function is stored in the cache for each parameter value it is called with. When the function is called again with the same […]
New in Oracle 11g: the FOLLOWS Clause in Create Trigger Statement
The order in which triggers of the same type fire is arbitrary. But in Oracle 11g the create trigger statement has a FOLLOWS clause. With the FOLLOWS clause you can specify after which other trigger of the same type the trigger should fire. For example, if you have two triggers, […]
New PL/SQL Compiler Warning in Oracle 11g
The PL/SQL compiler in Oracle 11g will give you a warning if it encounters a when others exception without a raise or raise_application_error. SQL> alter session set plsql_warnings=’enable:all’; Session altered. SQL> create or replace procedure testje as 2 begin 3 dbms_output.put_line(‘why do something?’); 4 exception 5 when others 6 then […]
New PL/SQL Feature in Oracle 11g: the use of sequence.nextval
Another small new feature in Oracle 11g PL/SQL: you can directly use sequence.nextval (and sequence.currval). It is not necessary anymore to do something like select sequence.nextval into l_seq from dual in your PL/SQL block. A small example:Â declare l_seq number; begin l_seq := oe.orders_seq.nextval; dbms_output.put_line(‘Value of orders_seq.nextval = ‘||to_char(l_seq)); end; […]
New PL/SQL Feature in Oracle 11g: CONTINUE
A minor new PL/SQL feature in Oracle 11g is the new statement ‘continue’. With the continue statement you can skip to the next iteration in a loop. A small example: begin for i in 1..3 loop dbms_output.put_line(‘i=’||to_char(i)); if ( i = 2 ) then continue; end if; dbms_output.put_line(‘Only if i […]
Using unique function based index as check constraint
Case: In a table there is a column customer_code and a column status. The allowable values of status are I, P, C, E. The status indicates that a record is just inserted (I), in process (P), completed (C) or in error (E). The constraint that has to be implemented: the […]
AskTom Live with Tom Kyte! – day two
A short report of the second day of the OakTable Seminar AskTom Live with Tom Kyte! The day started with the last part about analytic functions that we didn’t finish yesterday. Analytic functions are so ‘brutally great’ that it is all very much worthwhile. It appears that once you have […]
Oracle OpenWorld
Last Friday I visited the Oracle OpenWorld conference in Amsterdam. Here’s a short report of my visit. The day started with the general keynote session ‘Technology Inspired Collaboration’ by Ron Tolido. He introduced Maslow’s Pyramid into the IT world. It was interesting, but I had come for the more technical […]