Now that we've shown you JSON type, JSON_Transform and other cool features you may want to try it out yourself. LiveSQL is great - it has many (JSON) tutorial, scripts – but it is on release 19c and may not be upgraded to 20c; this is because 20c is a preview release. But there is way to get hands on a 20c database - I repeat myself here, but want to be clear: 20c preview release. This blog shows step by step how to get it:
You need an Oracle Cloud account. If you don't have one yet, then this is may be a good time to register. I was asked for my credit card, but this is for identification only. After registration I got a one-month trial (and also some cloud credits – this may depend on the region). After successfully registering you should see a screen like this:
You first need to create a VCN - a virtual cloud network. Think of this as your own network in the cloud where different services (nodes) are connected while shielded from other users. The menu in the top-left corner gets you to 'networking'
Here, I clicked on 'Start VCN Wizard' and created a VCN with Internet connectivity
I kept the default for 'subnets' etc. My final configuration looked like this:
After finishing this step, you should see a page that your VCN is available
Now, on this page go to 'Security Lists' and select the 'default security list' for your network. Here we add a new 'ingress' rule to open port 1521. This is the port we want to use later to connect to the database service. Add a new rule: source is 0.0.0.0/0 (open to any ip), protocol is IP, Source Port Range is 'All', Destination Port Range is 1521. This is how it looks for me:
Now, we're ready to set up the 20c database (did I already say it's a preview release?). Go back to the main page and select 'Bare Metal, VM, and Exadata in the menu.
Now we 'create a DB-System'
It is important to select 'Virtual Machine' and 'Logical Volume Manager'! Only this gives you access to 20c.
You can choose an availability domain. It is possible (and it happened to me) that my first chosen domain had no free resources. If this is the case, you will see an error later and have to repeat the process from here with another domain. I also picked the 'Standard Edition' as it should have everything that I need to demonstrate JSON type.
In order to secure your connection (it's mandatory) you need to create a pair of encryption keys
and provide the public key here. This sounds complex but it's actually easy; there are many ways to generate such a pair. I have used the ssh-keygen command.
On the next screen you should be able to name your database and pick the 20c (Preview) version. If you do not see 20c then you may have selected incorrect options on the previous screen.
You also set the sys password here (write it down, you need it later).
Now, your database should be provisioning
And after a few minutes your database should be up and running
Now we need to connect to it. First, we need to know the public ip address of the database server. For this click on 'Nodes' on this screen (it's on the bottom left under Resources). Here you should see an entry for 'Public Ip Address'. Second, we need the database service name, under 'Resources' on the left side select 'Databases' then select your new database and select the 'Database Connection' button which will open a window where you can copy the connection string (choose Easy Connect')
The 'Easy Connect' string will look like this
The string that follows the port number 1521 is the service name we will need to connect from SQL Developer. Copy it somewhere. The slash after 1521 is NOT part of the service name.
Now we add a connection to this service in SQL Developer: Your 'public ip address' is the host, 1521 the port, and the string you just copied is the service name
Now, hopefully if you click test, you should see a 'success'.
You could run the following SQL to test out a new 20c feature:
) from dual;
This calls the JSON type constructor to build an instance of JSON Type which is then printed (as text) using JSON_SERIALIZE.
Note for Java programers:
If you ssh into your database machine (username 'opc') then you find the 20c jdbc.jar here:
Using 'scp' you can copy it to your local machine and write a Java-JDBC program.
You find the 20c doc here:
Json Developer guide: https://docs.oracle.com/en/database/oracle/oracle-database/20/adjsn/index.html
SQL Reference: https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/index.html