Oracle SQL Developer 101: The Very Basics

We spend a lot of time here talking about tips, tricks, and cool things that you might have missed. These posts generally all assume you’ve been around the Oracle Database block. But many of the folks that ultimately end up here, have NEVER touched an Oracle Database before.

This post is for you. You have decided to use SQL Developer, which is good. Because I can think of at least 10 reasons why SQL Developer is perfect for the beginner.

SQL Developer is a client interface for the Oracle Database. If you have SQL Developer installed, you’ll need a database to interact with. You have 3 options:

• connect to a database that’s already been created and which you’ve been given an account – the easiest!
• create your own database – not recommended
• download our VM and use that to learn and play around with

I talk about the Virtual Machine (VM) option here. If you don’t have a database, and need one super quick…like, your-homework-is-due-tomorrow-morning-and-you’re-just-now-starting-QUICK, go read this.

If someone has given you an account, here’s how to get going.

Create a Connection

Right click in the connection tree, and select ‘New Connection.’

If you’re working with a newer version of Oracle Database, especially 12c, odds are you’ll need to know the Service Name. If the SID doesn’t work, just try putting that same ‘name’ in the Service box.

Also, if you have just created your own database, and you try to connect as SYS – you need to set the ‘Role’ dropdown selector to ‘SYSDBA.’ When you connect as SYS, you are GOD. You can screw stuff up. Create a new user. And then connect as that user and do your playing around stuff. We can help with creating users.

You might also have been given a TNSNames File

The TNSNames file contains a list of database services that you can connect to. It handles knowing what the database server, name, and ports are. Just tell SQL Developer WHERE that file is.

Once that’s set, change your connection type from Basic to TNS.

Then use the dropdown on Network Alias input to select the database you want to connect to.

Now hit ‘Save’, close the dialog. Your connection tree will have a new entry.

Expand it in the tree, you’ll get connected. Or you’ll see an error message. If you get an error message – address what is being complained about. I talk about common connection error messages here.

Otherwise, you’re IN.

The account you connected to, and the collection of the objects found in it, is known as your SCHEMA. Your schema could be empty. Or, it could have a bunch of tables in it.

To find out, expand the Tables tab.

Empty? Not supposed to be? Maybe you have access to stuff, and it’s not in your schema. Read this post.

Otherwise, you’ll see something like this:

You can change the ‘open object on single click’ behavior in the preferences. I recommend you actually do that. Here’s a list of other preferences I recommend you take a look at.

If you’ve made it this far, take a deep breath.

Getting the software installed – wait, need help with that? – and getting connected to Oracle is the hardest part of getting started. You just did that. Now you can worry about getting your objects built and your queries/code written.

Have questions? We have the Oracle Technology Network (OTN) with a set of Forums dedicated to SQL Developer users. Go check it out. If your question is about SQL or about how to do a conditional block in PL/SQL – be sure to go to the SQL or PL/SQL forum. And search the forums, your question has probably already been answered. A bunch of us also hang out on StackOverflow, Quora, Reddit, OraFaq, Oracle-L, Twitter, Facebook..hell, we’re all over the internet.

Taken that breath? Ready to go kick some butt? I have a ton of more 101 posts for you here.