A Place to Learn Oracle, No Setup Required

I answered this question on Quora yesterday: What are the best resources for learning Oracle Database?

The biggest learning curve to learning Oracle Database, SQL, PL/SQL, and all the other cool technologies that come with our database, is figuring out how to download, install, and create a database FIRST. And then of course you’ll have to configure a connection in either our command-line interface (SQL*Plus or SQLcl), our GUI/IDE (SQL Developer), or one of our web interfaces (EM Express, Enterprise Manager, or Application Express.)

That’s a lot of work to do to just try a bit of SQL or PL/SQL. Shouldn’t you just be able to go somewhere on-line and starting doing this stuff?

Yes, and we built that for you. So my answer to that Quora question was: Oracle LiveSQL.

There’s not much to worry about setting stuff up. You just need to go there – livesql.oracle.com.

And then you’ll see this:

But basically you can pick one of two paths. I chose the first path, and that’s what I’ll talk about today.

Here’s the two simple queries I ran:

SELECT sysdate FROM dual;
 
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') FROM dual;

Easy enough for you Jeff, You Already Know SQL!

True. But there’s an entire library of sample things you can go play with. We’ll talk about that later. For now, let’s investigate the mechanics of the online query sandbox you get.

If we click over on the ‘Session’ item in your navigation console…

So that first query, I asked to see the date. It only showed me the date. And then I used a to_char() function to bring back the date in a specific date format, that included the time. But why did the first attempt not show me the time?

Well, when you don’t use an explicit date format in your query, you get the default date format for your session. This is controlled via a National Language Set or NLS parameter. We can see those here.

OK, playing with DATEs is cool, but let’s do something more real. Let’s see what objects I have.

But, if you read the fine print: you can also explore read-only sample schemas.

One of those is Human Resources, also known as, HR.

Knowing this I’m going to copy the employees table to my own account.

DROP TABLE peeps;
 
CREATE TABLE peeps AS SELECT * FROM hr.employees;
 
SELECT employee_id, first_name, last_name, to_char(hire_date, 'DD-MM-YYYY'), salary, commission_pct
FROM peeps
WHERE commission_pct IS NOT NULL
ORDER BY commission_pct DESC
fetch FIRST 10 ROWS ONLY; -- FETCH FIRST ROWS ONLY is new SQL syntax for Oracle Database 12c

So this is kinda cool…I should save this.

Now I can recall this myself, or I can share it with you!
Jeff’s PEEPS example

Note: if you don’t have an Oracle Single Sign On account, you’ll need to create one now. It takes about 5 minutes. Once you have it, you can save your work on Oracle LiveSQL (plus a bunch of the other advanced stuff), AND you’ll be able to download cool resources like:

• a VirtualBox Appliance with Oracle Database 12c Enterprise Edition
• Oracle SQL Developer
• any other Oracle product you want to try out

I have a table now, what can I do with it?

Let’s say you’re a Java developer, and you want to connect to Oracle, and access a table. And let’s ALSO say it’s this table you just created in LiveSQL. Oracle owns Java, they should make that pretty easy, right?

In fact we do, let’s show you how to do that.

Let’s go back to the Schema page and click on our table. And then look over to the right, there’s sample code for you to query and insert data via various programming interfaces. So we’ll click on Java. Voila!

Ok, but you know SQL already. And I don’t know much. Help?

The LiveSQL interface is pretty simple and very intuitive. I just showed you a very simple scenario. If we had taken the blue pill at the beginning – clicked on the library link instead – we’d get to a page like this:

That’s right, ‘hello world’ – because that’s where we always start when working with a new language. So let’s do this!

and…drumroll…

There’s so much more I could show you. But this has already turned into a very long post. And like I said, this site is pretty much self-explanatory. Have fun learning!