where c not in (:b1, :b2, … , :b9999)

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in


create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND


SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where 
c!=:b1 and
c!=:b2 and
...
c!=:b9999;

FOO
---
foo

Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

Book review: oracle sql developper

Sue Harper is the product manager for Oracle SQL developer. She is also the author of a book, Oracle SQL Developer 2.1 .

A bunch of Oracle blogger received a free online version of the book with the task to write a review.

I have read a few chapters and here are my general comments.

buy from amazon
+ The picture on the book is very nice, not related to SQL Developer, but shot by Sue :) .

- There is no chapter on unit testing, which is one of the newest feature of SQL Developer. It is not very mature yet, but it is a good addition and free.

+ The book is not a marketing joke, the positioning against tool like TOAD is quite the way I see it.
page 1: The skeptics mentioned are ever concerned that Oracle is not really interested in the product,
backing up this concern with the fact that SQL Developer is free.

+ Even as a sqlplus/sh fanatic I did not feel ignored
page 6: We’re aware that you’ll never fully move from the command-line

- If you are a bit of a gray hair security admin you will be shocked by statements like page 21: create a connection for SYSTEM and page 22: Select the Save Password checkbox. This makes life easy. Hmm, do you give SYSTEM access to your developers and do your security policies recommend/allow them to save the password locally?

+ Still SQL Developer is good tool and it is driven by community. Save password is no longer the default (an old thread). You can post your own suggestions like Use Wallet for Logins to the SQL Developer Exchange.

+ There are plenty of good features in this tool and the book covers them. I like reports for instance.

= The book is still a book about click-click-click. Most of my readers here know how to drag and drop and resize windows.

PS: if it was not obvious in my comments, yes I do like this book!