Category Archives: sql developer

execute Sybase procedures from Oracle SQL Developer

Oracle SQL Developer provides access to data and code from other database systems than Oracle

If you run Sybase procedures (or Microsoft SQL Server I suppose), you cannot use the BEGIN / DECLARE / EXEC in anonymous blocks


begin
  print 'HELLO'
end



Error starting at line : 1 in command -
begin
  print 'HELLO'
end
Error report -
Incorrect syntax near the keyword 'begin'.



exec p



Error starting at line : 1 in command -
exec p
Error report -
Incorrect syntax near the keyword 'BEGIN'.

Ô rage ô désespoir!

But actually there is a way to twist the syntax so that it does not look like an oracle exec !

But in fact you create a procedure with CREATE PROCEDURE and end the procedure with a slash. Within the procedure you can use all the keywords you want. To call a procedure, do not use the exec keyword


set echo on

drop procedure p1
/

drop procedure p2
/

create procedure p1 as
return 123
/

create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
/

p2



> drop procedure p1
procedure P1 dropped.
> drop procedure p2
procedure P2 dropped.
> create procedure p1 as
return 123
PROCEDURE P1 compiled
> create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
PROCEDURE P2 compiled
> p2
123

Okay, it looks a bit like a sqlplus mutant, but it does the trick sometimes when you need to only call a procedure and get it’s return code.

The sames applies for other rdbms, here db2


set echo on 

drop procedure p
/

CREATE PROCEDURE P
LANGUAGE SQL 
P1: BEGIN 
  DECLARE X int; 
END P1 
/

call p



> drop procedure p
procedure P dropped.
> CREATE PROCEDURE P
LANGUAGE SQL 
P1: BEGIN 
  DECLARE X int; 
END P1 
PROCEDURE P compiled
> call p
call p

UPDATE: check dermotoneill for additional tricks!

Configure ORACLE_HOME for SQL Developer

The release V4.0 of sql developer is available for download : oracle.com/technetwork/developer-tools/sql-developer/downloads
The doc is there : docs.oracle.com/cd/E39885_01/index.htm

And read Jeff Smith twit’s and blog

I requested some time ago a 64bit Windows version with JDK on Oracle SQL Developer Exchange and once again it got accepted.

Okay, I have on my PC two Oracle homes : one for 32 bits and one for 64 bits. The 32 bits is first in PATH.

When I start SQL Developer, I could not use the OCI thick driver which is required when connecting via LDAP or TNSNAMES. SQL Developer was failing with error :
ocijdbc11.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform

Apart from changing the path in the Environment variables or in .bat file, it is possible to add the following line in sqldeveloper/bin/sqldeveloper.conf (where dbhome_2 is a 64bits home):
AddVMOption -Djava.library.path=C:\oracle\product\12.1.0\dbhome_2\bin

If you need to connect to Sybase ASE (or MSSQL), download the driver here : http://sourceforge.net/projects/jtds and add it as Datasbase/Third-Party Driver

For MySQL go there : http://dev.mysql.com/downloads/connector/j/ and download the Platfrom Independant ZIP file.

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
sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

ldap.ora
DEFAULT_ADMIN_CONTEXT = “DC=example,DC=com”
DIRECTORY_SERVER_TYPE = AD

- test it!
tnsping first


C:\> tnsping db01

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

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

Used parameter files:
C:\oracle\product\11.2.0\client_3\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
_DATA=(SID=DB01)))
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();
    ods.setDriverType("oci");
    ods.setTNSEntryName("DB01");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


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 HelloWorld.java
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar -Doracle.net.tns_admin=C:\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://example.com:389/db01,cn=OracleContext", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


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(T4CConnection.java:419)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at HelloWorld.main(HelloWorld.java:5)

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();
    ods.setDriverType("thin");
    Properties prop = new Properties();
    prop.put("java.naming.security.authentication", "simple");
    prop.put("java.naming.security.principal","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
    prop.put("java.naming.security.credentials", "my_ad_pw");
 
    ods.setConnectionProperties(prop);

    ods.setURL("jdbc:oracle:thin:@ldap://w2k8.local:389/db01,cn=OracleContext,DC=example,DC=com");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

This works!

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.

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba.

But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally.

Still no session browser, but I am delighted that the database administrators are not getting forgotten !

Thanks Surachart for spreading the news!

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!