SQL Developer Connection to Local Database

In this tutorial I am going to  show you how you can create a connection to a local Oracle database with the SQL Developer. The SQL Developer is a very powerful and free integrated development environment (IDE) for developing and managing source code in an Oracle database (Oracle Cooperation n.d.). Even I am used to eclipse and prefer this IDE in any other development projects I have to admit that with none of the available eclipse plugins I was able to gain more benefits than with the SQL Developer.

Before you start reading this tutorial I recommend you to have a look to another post in which I describe how to install the Oracle database on Ubuntu 14.04^​1​.

Start Local Database first!

Before you move to the next sections you should first ensure that the local database is running. sudo /etc/init.d/oracle-xe start

Database Configuration

Before we start to create a connection to our local Oracle database I want to create a new database user. I will also use this user for further demonstration purposes in my blog. Since I assume you have the system privileges you need to create a new user you should be able to execute the creation and grant commands:

adam@adam-desktop:~$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 30 20:21:09 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> CREATE USER cdo IDENTIFIED BY pass1234;
User created.

SQL> GRANT CONNECT,RESOURCE,DBA TO cdo;           
Grant succeeded.

SQL> GRANT CREATE SESSION TO cdo;
Grant succeeded.

SQL> exit;

Lets test whether the cdo user (‘cdo‘ stands for creative-doing) was created successfully and whether we are able to connect with the user’s credentials.

adam@adam-desktop:~$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 30 20:32:22 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: cdo
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create table test_table (attribute1 integer, attribute2 integer);
Table created.

SQL> select * from test_table;  
no rows selected

SQL> drop table cd.test_table;
Table dropped.

SQL> exit;

Everything seems to work! Lets create the connection in our SQL Developer.

Connection Creation

With the new cdo user we have got a database user which we can use for any testing or developing purposes. We start to create the connection in SQL Developer with opening the context menu in the connection view.

The SQL Developer prompts us for the connection data:

You need to enter the connection Hostname and SID (Oracle System ID) as well as the user’s credentials. Name and save the connection after running a test.

You will find a new connection entry in the connection view. Now you can execute some SQL queries.

Overview over your Database Objects

The Connections Tree is one of the most using views in the SQL Developer. It gives you a complete overview of all objects, for instance which tables, data types, packages, … , are defined in your database (for the specific user).