All things Oracle

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

• Alex Nuijten -Database Development – Oracle Ace Director
• Cary Millsap – Software Development – Oracle Ace Director
• Dimitri Gielis – Oracle Application Express – Oracle Ace Director
• Martin Giffy D’Souza – Oracle Application Express – Oracle Ace
• Roel Hartman -  Oracle Application Express – Oracle Ace Director
• Scott Spendolini – Oracle Application Express – Oracle Ace Director

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.

Statement restart

Today I explained the “statement restart” problem to a colleague.
Every database developer has to know about this one…

Short: Any statement can be restarted!
This means that when you do one update statement, oracle CAN restart the statement.
Every code in a trigger can be executed multiple times, so watch out with package variables and autonomous transactions in triggers.

Some code to test it:

CREATE TABLE test_trigger(val NUMBER)
/

CREATE OR REPLACE PACKAGE global_var
IS
g_val NUMBER;
END global_var;
/

CREATE OR REPLACE TRIGGER test_trig
BEFORE UPDATE
ON TEST_TRIGGER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
global_var.g_val := global_var.g_val + 1;
END ;
/

BEGIN
global_var.g_val := 0;
END;
/

INSERT
INTO test_trigger
( val
)
VALUES
( 0
)
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

BEGIN
FOR i IN 1..100000
LOOP
UPDATE test_trigger
SET val = val +1;
END LOOP;
END;
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

What’s the value of your global variable?

Or like Tom Kyte says: “Triggers or evil!”
More on this topic by Tom Kyte: That old restart problem again