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.

PL/SQL Challenge first anniversary

One year ago Steven Feuerstein launched a competition called “The Pl/SQL Challenge“.
The idea was to let Oracle PL/SQL developers test their knowledge, learn and win prices by taking a daily quiz.

The format seems to be a success: every day more than 1000 PL/SQL developers test their knowledge about the language, in total more than 5800 users from 119 countries… a worldwide success!

But it does not stop with doing a quiz, a lot of questions(and answers) are discussed on the blog.
And those discussions can also lead to something, like an enhancement request for the PL/SQL language  by Bryn Llewellyn(PL/SQL Product Manager at Oracle).

Steven did a great job with the PL/SQL Challenge and it seems like it keeps on rolling: version 2 of the website is coming up with even more challenges for SQL and APEX.

To Steven and his team of reviewers and developers: thanks for the nice quizzes and keep up te great work!
To all players: have fun and good luck!

 

My quiz on the PL/SQL Challenge

On friday my quiz was on the PL/SQL Challenge.

Topic: “Guidelines for Designing Triggers: Avoid Non-Transactional Logic in DML Triggers”

Yes, about “statement restart”
My idea about the quiz was “making developers aware of the Oracle behaviour”.
And I hope a lot of PL/SQL developers learned from it.

It wasn’t easy to come up with the question, the way of asking, the correct words, the answers…
But after a lot of mailing between Steven and the reviewers we finally had a quiz.

A lot people had it wrong, but I hope they don’t mind and are now aware of statement restart.

For more information and discussion about the quiz: PL/SQL Challenge blog

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