Do SQL and PLSQL in your own free database in Oracle Cloud


Hi, my dear followers. May I share my recent discovery which I am using now when training SQL and PLSQL in workshops or bootcamps. Why SQL? Because I am sure these skills are must have when you are in IT. They are like driving license – you can live without it however it is so comforting to have it for lifetime.

It has always been a challenge for students and teachers to have an environment for self learning and training. Lucky us who are working with relational databases and can exploit them also for learning. For others – till my recent discovery – I had been suggesting the https://livesql.oracle.com/ to open a SQL session keeping in mind all the data created by you will be lost after you close the session. So the only option we had was to save our scripts locally and copy paste run them every time.

Luckily now we have a progressive and long term solution – our own autonomous Oracle Cloud database which we can connect to with a lot of different tools. I will use SQL Developer as an example here.

Set up Oracle account and starting the database takes some time (well, 15 minutes?) but this is a once off task so we invest a bit of time for a happy future together 24/7 :) This will be your “personal” database – do there any of study projects, bachelor thesis, learning, experimenting, anything. I am using my two Cloud databases when sharing SQL and PLSQL tips and tricks.

  1. Create Oracle Cloud account: qpen https://cloud.oracle.com and press ‘Sign Up for Free Cloud Tier’

Fill in the data required and press ‘Verify my email’

You will receive an email asking to confirm your Oracle cloud account. By clicking on the link received you will be redirected to registration form. Enter required information (I have set namesurname as Oracle Account name), choose ‘Home Region’ as ‘Germany Central (Frankfurt)’ and press ‘Continue’.

In next page you will be asked to provide address information and also your payment card details.

!!! Don’t worry – Oracle requests this for identification purposes as lots of software providers do. 1 EUR will be reserved or charged from your bank account and returned within a few minutes.

When done press ‘Start my free trial.’ If everything went successfully you will see Oracle Cloud start page similar to one in screenshot below.

2. Create and start our own free transaction database. Press ‘Create an ATP Database‘. You will see quite a long form where you can keep all the defaults and the only must-have is the password for your database ADMIN account. Fill it (and save somewhere nearby because you will soon need this password when setting connection from SQL Developer)

and press ‘Create Autonomous Database

Within some seconds you will have your database up and running. The metrics window will open

So – we have started our own free Oracle Cloud transactional database. Voila!

Now let’s connect to this database with Oracle SQL Developer.

3. Download and start Oracle SQL developer from https://www.oracle.com/tools/downloads/sqldev-downloads.html. Choose version appropriate to OS on your computer.

When downloaded, extract the zip file and launch sqldeveloper application file (run sqldeveloper.exe), no need to install anything additional. If everything went successfully you will see program window similar to one in screenshot below.

Let’s configure SQL Developer to connect to our Oracle Cloud database. To link them, we will use Oracle Wallet file.

4. Create a new Wallet file: open the database in the Oracle Cloud page and click ‘DB Connection

NB: here’s a quick recap: Open https://cloud.oracle.com -> Log in -> click on ‘hamburger’ icon (three horizontal lines in the upper left corner) -> Choose ‘Oracle Databases’ -> Autonomous database -> Click on hyperlink in ‘Display names’ columns, something like DB20211234567

You will have the Details window opened where click please ‘DB Connection

A new Wallet window will open and you should click ‘Download Wallet‘ there:

enter and re-enter a new wallet admin password (this is not the same Admin password which you set when creating the database – however you can reuse it if you want to)

and choose a local folder to download the wallet to

5. Configure SQL Developer connection to our Oracle Cloud database.

Open SQL Developer (click sqldeveloper.exe) and press the green cross in the upper left corner. A new connection setup window will be opened. Choose ‘Cloud Wallet‘ as your Connection Type

and [Browse] the Wallet file you downloaded to your local folder

Fill also ‘Service Name’ (I named the connection ‘My Oracle Cloud DB2’ (because I already have DB1, this is my second free database there in the Cloud)

As ‘Username’ enter ‘ADMIN’ and enter your database admin password (not the Wallet password, but the database ADMIN password). Check also ‘Save Password’ for your comfort not to enter the password again.

Press [Test] and within a second you should have Status: Success

When you see Success, press ‘Save’. If you don’t – you can try to reach me out I will try to help. Albeit usually everyone succeeds.

Right click on the connection and choose ‘Open SQL worksheet’

Congratulations – you have connected to your Cloud database and  are ready to deep dive into the wonderful SQL and PLSQL world there! :) Do anything you want from creating tables to mastering Expert level SQL and PLSQL.

Well, speaking of deep dive –

6. I suggest these online resources for your SQL learning:

https://datubazes.wordpress.com/sql-pamati/ the blog by Gints Plivna – best of the best ever local source (in Latvian) 

https://www.coursera.org/learn/intro-sql – did you know you can enjoy Coursera courses for free? When you are ready to click Enroll Course choose instead the small letters below the button ‘Audit course’. Well, you will not have final certificate and some classroom labs but you will access all the learning video and readings as if you were a student there.

Udemy offers only paid resources and price is about 15 EUR per course.

https://www.udemy.com/course/the-complete-sql-bootcamp/ (beginner level. I have not seen this course myself)

https://www.udemy.com/course/oracle-analytic-functions-in-depth/ (advanced level. I have bought this to polish Oracle window functions and liked a lot).

You might also enjoy challenging yourself here (in Russian) https://www.sql-ex.ru/ – a lot of different complexity challenges there. I once welcomed an intern in my project who had on his own achieved high level in this site and I must admit his SQL skills were truly impressive.

7. Expand your skills with PLSQL (Procedural Language for SQL)

When you have at least some SQL basic skills you might decide to start PLSQL studies. This also is a very interesting world, full of loops, functions, cursors, packages, variables. triggers, exception handling, dynamic SQL, built in Oracle functions and many many more.

When programming SQL and PLSQL – Sky is the limit.

P.S. You are welcome to apply for SQL and PLSQL bootcamps – upcoming in July and August ‘2021. Learn more by doing.

Data Management (July)

Data management (August)

 SQL:

  • structure and objects
  • queries and joins
  •  data modelling

PL/SQL:

  • functions, procedures, packages and triggers
  • variables, cursors, custom data types and arrays
  • control statements and exception handling
  • coding conventions and guidelines

There is chance I will be one of your mentors and teachers there if the project I am assigned to will let me dedicate some time for giving back to industry. See you there!

One response to this post.

  1. Posted by MārtiņšŠ on 05/05/2021 at 18:27

    Urrā! Jauns ieraksts!

    Publicējis 1 person

    Atbildēt

Mans viedoklis:

Fill in your details below or click an icon to log in:

WordPress.com logotips

You are commenting using your WordPress.com account. Log Out /  Mainīt )

Google photo

You are commenting using your Google account. Log Out /  Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out /  Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out /  Mainīt )

Connecting to %s

%d bloggers like this: