Sunday, January 01, 2006

I was stumbling upon...

I was “stumbling upon” and hit this site. It reminded me of my all time favorite “oh, I wish it didn’t work that like” things in PLSQL.

SQL> create table t ( x int );
Table created.

SQL> begin
2 for i in 1 .. 2
3 loop
4 insert into t values ( i );
5 end loop
6 rollback;
7 end;
8 /
PL/SQL procedure successfully completed.

SQL> select * from t;


Neat, eh? The first C thing written on that page with the comment reminded me of it, a similar issue.

PLSQL has the same issue as C with the comment though (as would many/most languages I think)

SQL> declare
2 a number := 1;
3 b number := 2;
4 c number := 3;
5 d number := 4;
6 begin
7 a := b; /* this is a bug
8 c := d; /* c=d will never happen */
9 dbms_output.put_line( 'D = ' || d );
10 dbms_output.put_line( 'C = ' || c );
11 end;
12 /
D = 4
C = 3
PL/SQL procedure successfully completed.

#7 on that referenced page on C reminds me lots of zero divide issues people have:

SQL> create table t ( x int, y int );
Table created.

SQL> insert into t values ( 1, 0 );
1 row created.

SQL> select /*+ RULE */ * from t where y > 0 and x/y > 0;
select /*+ RULE */ * from t where y > 0 and x/y > 0
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select /*+ RULE */ * from t where x/y > 0 and y > 0;
no rows selected

But best of all about that page? It is a top 10 with 17 things… I liked that as my top 5 things done wrong presentation now has 7 things in it (and I never have changed the title…)


Blogger Kalita said....

Once I had dbms_sql.close_cursor in a procedure and that procedure was after an end loop... It took me a while to find out why the number of open cursors were execeeding. It was such a relief :-)

Sun Jan 01, 11:14:00 PM EST  

Blogger Kim Nørby Andersen said....

Haha, I just can't help thinking of the multiple times, where you are staring blindly at your code and thinking WTF?!?! Then a colleague comes over, adds a semicolon, pipe, paranthesis or whatever, and everything works perfectly :)

Or the brilliant times, where you are that colleague ;)

And happy new year, everybody!

Mon Jan 02, 02:13:00 AM EST  

Anonymous Todor Botev said....

Why is the effect in the last example (zero devision) there? Is the evaluation of the predicates right-to-left? Is it always so or only when the Rule-Based Optimizer comes into play (though the RULE hint)?

Mon Jan 02, 06:40:00 AM EST  

Blogger Thomas Kyte said....

I used the rule hint to make that example "predicable".

RBO tends to evaluate tables from right to left in the from clause and predicates from the bottom up. so, to make this reproduce for pretty much everyone - I used the RBO. The CBO does it however it feels like doing it.

(but an application that was accidently working "just fine" with the RBO might get the zero divide simply by gathering statistics and having the optimizer evaluate the predicate in a different order!)

Mon Jan 02, 09:38:00 AM EST  

Blogger Marc Soth said....

could you please explain me the first example. Sorry, but I cannot figure it out.

I can understand the second and third example but not the first one.

Thanks a lot


Mon Jan 02, 10:46:00 AM EST  

Blogger Thomas Kyte said....

Marc Soth said...

In the first example, I am missing a semi-colon on line 5:

4 insert into t values ( i );
5 end loop
6 rollback;
7 end;
8 /

hence the "word" rollback becomes part of the LOOP - not the SQL comand 'rollback', that code is not any different than:

4 insert into t values ( i );
5 end loop my_loop_name_here;
6 end;
8 /

(loops may have names like that! and rollback, commit, and many others are valid identifiers...)

Not that I would recommend doing this, but the following is perfectly "valid"

ops$tkyte@ORA10GR2> create table commit ( rollback number );

Table created.

ops$tkyte@ORA10GR2> create or replace procedure p
2 as
3 commit number := 1;
4 rollback number := 0;
5 begin
6 insert into commit (rollback) values ( p.commit );
7 insert into commit (rollback) values ( p.rollback );
8 end;
9 /

Procedure created.

ops$tkyte@ORA10GR2> exec p;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from commit;


Mon Jan 02, 11:01:00 AM EST  

Blogger Marc Soth said....

thanks a lot again for your information. In the meantime I've looked in the documentation an found new informations for me (

I've a further question about the label_names. Why can I use an optionally label_name at the end of the loop statement, without a label_name at the beginning.

I always thought, that a label is just only at the beginning and the label at the end of the loop is just only for documentations.

Thanks a lot for your answer.


Mon Jan 02, 11:21:00 AM EST  

Blogger Thomas Kyte said....

Why can you use the label there? Only because the language permits it - it is a documentation thing I assume (more verbose code).

and for the curious - what keywords can be used as identifiers?

ops$tkyte@ORA10GR1> begin
2 for x in ( select keyword from v$reserved_words order by keyword )
3 loop
4 begin
5 execute immediate 'declare ' || x.keyword || ' number; begin null; end;';
6 dbms_output.put_line( x.keyword );
7 exception
8 when others then null;
9 end;
10 end loop;
11 end;
12 /

The list of what you can use is much longer than the list of those you cannot...

Mon Jan 02, 11:30:00 AM EST  

Blogger Marc Soth said....

Thanks a lot for your fast reply, and I've learned today some nice things.



Mon Jan 02, 11:50:00 AM EST  

Anonymous Anthony Wilson said....

Hi Tom, Happy New Year.

I was interested in the V$RESERVED_WORDS view, I didn't know about that one. I thought "now I finally have a resource for creating the ultimate PL/SQL syntax file for VIM".

However, the view seems to include keywords (and reserved words) on a fairly ad-hoc basis. For example, here's a few that I tried off the top of my head which were not included:

BINARY_INTEGER (although binary_float and binary_double are included)
RANK (dense_rank is in there)

I thought this was strange. Any idea why this is so?

Tue Jan 03, 02:59:00 AM EST  

Blogger Thomas Kyte said....

v$reserved_words only has SQL (not plsql) reserved words...

and in 10g - has extra columns to indicate when and where each word may or may not be used.

Tue Jan 03, 06:57:00 AM EST  

Anonymous Anonymous said....

Tom,, Just wanted to Wish you and all other readers a very Happy News Year 2006.

Tom, you are my hero any day.

Tue Jan 03, 07:13:00 AM EST  

Anonymous Anthony Wilson said....

Ah... I was misled. Extract from 10gR1 docs:


This view gives a list of all the keywords that are used by the PL/SQL compiler

Seems to have been corrected in 10gR2 doco. And darned if they aren't all SQL keywords... there's just a lot I have never used.

Do you know of any equivalent resource for PL/SQL keywords?

Tue Jan 03, 08:20:00 AM EST  

Blogger Rahul said....

/* c=d will never happen */

This is what GUI tools are great for. Checked this out in Raptor. All the commented pieces appear "greyed" out so it is immediately obvious.

Wed Jan 04, 04:25:00 PM EST  

Anonymous darcy said....

My favourite pet peeve in PL/SQL is that the following is not flagged as an error at compile time:

if vInDate = NULL then

Every Oracle newbie does this at least once, and some oldbies with not enough coffee in them, too. ;)

Wed Jan 04, 07:51:00 PM EST  


<< Home