news toad

Toad 11.5 is out

The latest Toad is now in production, 11.5, get it from

If you have an old license key, 9.6 or older, it may complain at installation time, just ignore. It will be fine at run time.

Enhanced TAB browsing experience, nicer and more visible colors for your connection (production=red…), read-only connections.

Currently it still requires a 32bit clients, even when running on a 64bit Operating System.


Close tabs in Toad

Sometimes you wait a feature for so long that even if it is small and common, you feel greatly happy

In Toad Greatly replace the “right-click” + “close tab”

11g 11gR2 java ldap security sql developer toad

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

– Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

– Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

– Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

– Configure the clients


– test it!
tnsping first

C:\> tnsping db01

TNS Ping Utility for 64-bit Windows: Version - Production on 10-NOV-2011 14:42:16

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
OK (20 msec)

I wrote a simple java program to check the connection :

import java.sql.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
OracleDataSource ods = new OracleDataSource();
ResultSet res = ods.
prepareCall("select 'Hello World' txt from dual").

C:\> set PATH=C:\oracle\product\11.2.0\client_3\bin
C:\> javac -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar\oracle\product\11.2.0\client_3\network\admin HelloWorld
Hello World

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error

import java.sql.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
ResultSet res = DriverManager.
getConnection("jdbc:oracle:thin:@ldap://,cn=OracleContext", "scott", "tiger").
prepareCall("select 'Hello World' txt from dual").

C:\>java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld
Exception in thread "main" java.sql.SQLException: I/O-Fehler: JNDI Package failure avax.naming.NamingException: [LDAP:error code 1 - 000004DC: LdapErr: DSID-0C0906DC, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v1db0 ]; remaining name 'cn=db01,cn=OracleContext'
at oracle.jdbc.driver.T4CConnection.logon(
at oracle.jdbc.driver.PhysicalConnection.(
at oracle.jdbc.driver.T4CConnection.(
at oracle.jdbc.driver.T4CDriverExtension.getConnection(
at oracle.jdbc.driver.OracleDriver.connect(
at java.sql.DriverManager.getConnection(
at java.sql.DriverManager.getConnection(
at HelloWorld.main(

As the error message says, the ldap server requires a bind

Let’s try to bind

import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
OracleDataSource ods = new OracleDataSource();
Properties prop = new Properties();
prop.put("", "simple");
prop.put("","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
prop.put("", "my_ad_pw");


ResultSet res = ods.
prepareCall("select 'Hello World' txt from dual").

This works!

news toad

Toad 11 is out!

OpenWorld season is open, while Oracle announced linux database appliance, Quest launched Toad11 ! I have been actively beta-testing toad 11 and I am excited to download it. 421Mb for the 11.0 Commercial installer, 41Mb for the READ-ONLY version 🙂

dba security sql developer sqlplus toad

On using Toad against a database

I got this question once again today in a previous post.

What’s wrong by using Toad against a database?

The worst case scenario:
– some non-technical staff is clicking around in your production database with read-write access 🙁

The best-case scenario :
– nobody has access to your database 🙂

Here is a short list on how you could protect your data :
– Give the right privilege to the right person. DBA role to the DBA, CREATE TABLE/CREATE INDEX to the developer, INSERT/UPDATE/DELETE to the application
– Restrict access to your database server. Use some firewall. Allow only the dba workstation and the application server to the Production environment

What if the end-user PC needs access to the Production database with a powerfull user? This often happend in real world. A fat client is installed on the PC, the password is somehow hardcoded, the privileges granted to the hardcoded user are uterly generous…

It is not a bad practice in this case to block access to the database server to Toad/SQLPLUS and thelike. This will very ineffeciently prevent some garage-hacker from corrupting your database, but it will prevent your sales / marketing colleagues from deleting data, locking tables and degrading performance. This could be done by some login triggers or, my preference, some administrative measures like information, auditting and sanctions.

dba toad

This version of TOAD provides READONLY access!

This Toad 11 beta new feature made me so happy !

Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only.

For any reason, bored, ill, under pressure, tired, you may screw up your database one of those days.

I feel so good that I can now setup my prod connection to be read-only. It is REALLY cool.

Setting the connection read-only is a TOAD feature. As it is in beta it may not be 100% bullet-proof. Mostly it allows you to do only SELECT in the editor, even if you are logged in as SYSTEM or SYS.

Anything comparable in SQL Developer?


<CTRL> + .

Sometimes the simplest tricks just ease your life the nice way.

While typing code in TOAD, I just found out those two handy keyboard shortcuts

<CTRL> + . = Autocompletes tablenames
<CTRL> + T = Popups the list of column names

I want to select everything from emp and dept joined on deptno.

where e.=d.

Especially useful when table and column is not EMP.DEPTNO but WWRTM_CUOTDSOTM_IWUWH_ML.H2OUUP2_NIAGASTSGM_IMUMTUCI 🙂

Blogroll sql toad

puzzled by Toad

one colleague just showed me how to insert duplicate rows in a table that has a primary key.

create table t(x number primary key);[F5]
Table created.
insert into t values (1);[F5]
1 row created.
insert into t values (1);[F5]
1 row created.

Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

Let’s look at Toad.ini (or View – ToadOptions – Transaction)


Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

Let’s prove it :

select count(*) from t where x=1;[F5]


1 row selected.


Blogroll dba toad

How to compare schema

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options?

I found this handy feature in Toad :
1) I create my objects on a separate database with the ddl I received from development
2) I compare the schema they use with the schema I created in Toad
–> Database –> Compare –> Schema
I select the options I want:
–> functions, indexes, packages, procedures, triggers, tables, view
I select the Reference and Comparison connections/schemas. Then I click compare
3) I receive the result
(only) 29 differences
4) the real bonus, I receive a script to update the live data according to the script I received. Undocumented change should never happen, so I do some communication with the developers

drop index foo;
drop table bar;
alter table gaz drop column bop;
alter table gaz modify (quux null);

this is not going to be blind-executable, some change are simply impossible to implement, but for my little test, I was happy to discover that function

I have been using ERwin for this purpose before, but the version I have (4.1) is very buggy and does not support a lot of syntaxes (ex: deferred constraints, create view v as select cast(1 as number(1)) x from dual, etc…). Also ERwin can compare only with the current model, so no direct comparison between 2 database schema.

Add new tag Blogroll toad

Toad 9 is available

In the Metreon Theater, Quest announced toad 9. Steven Feuerstein presented one feature : Unit Testing in TOAD. Go to, unless you are a vi man