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.041.
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
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 22.214.171.124.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 126.96.36.199.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 188.8.131.52.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 184.108.40.206.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.
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.
|Connection Name||Creative Doing – local|
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).
- 1.Lukaszewski A. Install Oracle 11gR2 Express Edition on Ubuntu 14.04 (64-bit). creative-doing.de. http://www.creative-doing.de/category/oracle/database/nstall-oracle-11gr2-express-edition-on-ubuntu-14-04-64-bit. Published July 31, 2017. Accessed July 31, 2017.