A Place to Learn Oracle, No Setup Required

thatjeffsmith Database Stuff 5 Comments

Tell Others About This Story:

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.

So, what is Oracle LiveSQL?

So, what is Oracle LiveSQL?

Or in other words, it's a cool place to go run Oracle SQL and PL/SQL w/o worrying about getting a database first.

Or in other words, it’s a cool place to go run Oracle SQL and PL/SQL w/o worrying about getting a database first.

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:

You won't see the text balloons and scribbling - I added those.

You won’t see the text balloons and scribbling – I added those.

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

type stuff, hit the 'Go' button, see the output - errors, results, GLORY!

type stuff, hit the ‘Go’ button, see the output – errors, results, GLORY!

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…

a history of everything I've done, including their results - way cool.

a history of everything I’ve done, including their results – way cool.

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.

Tip: ALWAYS describe how you want a DATE or TIMESTAMP displayed in the query itself, don't rely on the session NLS settings.

Tip: ALWAYS describe how you want a DATE or TIMESTAMP displayed in the query itself, don’t rely on the session NLS settings.

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

Darn, I don't have anything :(

Darn, I don’t have anything 🙁

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.

So I went back to the query area and wrote some code...

So I went back to the query area and wrote some code…

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.

Oh that's funny, I meant to call that 'PEEPS...' We'll fix that in POST :)

Oh that’s funny, I meant to call that ‘PEEPS…’ We’ll fix that in POST 🙂

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:

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!

I'm lazy, so if you want to write code for me, I'm all for that.

I’m lazy, so if you want to write code for me, I’m all for that.

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:

I've filtered to the 'PL/SQL' section, and I'm going to click on the Hello World item.

I’ve filtered to the ‘PL/SQL’ section, and I’m going to click on the Hello World item.

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

So I just click the 'Go' button again? YES.

So I just click the ‘Go’ button again? YES.

and…drumroll…

Sweet.

Sweet.

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!

Related Posts

Tell Others About This Story:

Comments 5

  1. Hi Jeff, the URL in the top part of the article is incorrect – https//livesql.oracle.com The : has been omitted

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *