How to use SQLcl to connect to Oracle Cloud ATP databases

SQLcl (short for: SQL command-line) is a relatively new light-weight SQL command-line tool from Oracle, similar to the much older SQL*Plus tool, but easier to install and with some nice new features. In this blog post I will explain how you can use it to connect to Oracle ATP cloud databases.

Step 1: download and install SQLcl

First, start by downloading and installing the SQLcl software. Installation is as simple as extracting the ZIP file to a new folder (a bit similar to the “installation” of SQL Developer). SQLcl is a Java-based tool, so you also need to have at least version 8 of the Java Runtime Environment (JRE) installed.

Once installed, you can easily test it by going into the “bin” folder where you extracted the software and then run the following command:

sql /nolog

Example from my computer (I’m on Windows 10):

c:\>cd "Program Files (x86)"/sqlcl/bin

c:\Program Files (x86)\sqlcl\bin>sql /nolog

SQLcl: Release 20.2 Production on Tue Jul 21 12:10:01 2020

Copyright (c) 1982, 2020, Oracle. All rights reserved.


SQL>

Step 2: download Oracle ATP client credentials (Oracle Wallet)

To be able to connect from your client computer to an Oracle Cloud database you first need to download the client credentials (the so called “Oracle Wallet”) from the Oracle Cloud portal site. Note that if you already have a working SQL Developer connection, you probably already have this wallet file somewhere on your system.

Log in to the Oracle Cloud portal and click on the name of the database you want to connect to. Now click on the tab “DB Connection”. The following pop-up window will appear:

You now have the choice between “Instance Wallet” (a wallet for the selected database instance) and “Region Wallet” (a wallet for all the databases in the current region). Select “Instance Wallet” and click on “Download Wallet“.

You are now asked to create a complex password for your wallet:

Download the ZIP file and put it in a local folder on your client computer.

Step 3: connect from SQLcl to Oracle Cloud ATP

To connect with SQLcl to your Oracle Cloud ATP database, use the following steps:

• Go into the SQLcl “bin” folder and run the following command:
  • sql /nolog
• Configure the SQLcl session to use the previously downloaded Oracle Wallet:
  • set cloudconfig C:\Temp\Wallet_MATTHIASDB.zip
• Connect to the Cloud database using the required schema and service name (note that the service names are listed in the tnsnames.ora file in the zipped Oracle Wallet file)
  • connect HR@matthiasdb_medium

Example from my computer:

c:\Program Files (x86)\sqlcl\bin>sql /nolog

SQLcl: Release 20.2 Production on Tue Jul 21 12:20:35 2020

Copyright (c) 1982, 2020, Oracle. All rights reserved.


SQL> version

Oracle SQLDeveloper Command-Line (SQLcl) version: 20.2.0.0 build: 20.2.0.174.1557
SQL> set cloudconfig C:\Temp\Wallet_MATTHIASDB.zip
Operation is successfully completed.
Operation is successfully completed.

Using temp directory:C:\Users\Matthias\AppData\Local\Temp\oracle_cloud_config3396158891494649054
SQL> connect HR@matthiasdb_medium
Password? (**********?)

Connected.
SQL>

Now try to run some SQLcl commands!

SQL> set sqlformat ansiconsole

SQL> desc employees

Name Null? Type
_________________ ___________ _______________
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL> info employees
TABLE: EMPLOYEES
LAST ANALYZED:2020-07-08 22:01:07.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.

Type “help” at the command-line if you need help on a specific command. Type “version” to see the version of SQLcl you are using. Note that you can also use the keyboard arrows to scroll between previously executed commands (something that is not possible with SQL*Plus).

That’s it! You can now use SQLcl to connect to Oracle databases in the Cloud.

HTH

Matthias

Installation of HR sample schema on free Oracle ATP cloud database

In this blog post I will show how you can install the sample HR (Human Resource) schema on a Oracle ATP (Autonomous Transaction Processing) database in the Oracle Cloud.

Prerequisites:

• Oracle SQL Developer with connectivity to a Oracle ATP database (please see one of my previous blog posts if you need help with this)

Step 1: download the Sample Schemas scripts from GitHub

Download and extract the Oracle Database Sample Schemas source code zip file from https://github.com/oracle/db-sample-schemas/releases/tag/v19.2

Step 2: create the HR database user

Connect with SQL Developer to your Oracle ATP database using the ADMIN user, and execute the following scripts to create the HR user with the necessary grants:

CREATE USER hr IDENTIFIED BY "&pass";

ALTER USER hr DEFAULT TABLESPACE &tbs
QUOTA UNLIMITED ON &tbs;

ALTER USER hr TEMPORARY TABLESPACE &ttbs;

GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr;

GRANT execute ON sys.dbms_stats TO hr;

&pass = Password for the HR user (must be complex: more than 12 bytes long, contain upper and lower case characters and not contain the username)

&tbs = default tablespace for the HR user (for example: DATA)

&ttbs = temporary tablespace for the HR user (for example: TEMP)

You should see the following script output:

User HR created.

old:ALTER USER hr DEFAULT TABLESPACE &tbs
QUOTA UNLIMITED ON &tbs
new:ALTER USER hr DEFAULT TABLESPACE DATA
QUOTA UNLIMITED ON DATA

User HR altered.

old:ALTER USER hr TEMPORARY TABLESPACE &ttbs
new:ALTER USER hr TEMPORARY TABLESPACE TEMP

User HR altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Step 3: create the HR schema objects

Use SQL Developer to connect to your Oracle ATP database with the newly created HR user (see step 2), and load and execute the following sql files in this order as scripts (see step 1):

• db-sample-schemas-19.2\human_resources\hr_cre.sql (for creating the objects)
• db-sample-schemas-19.2\human_resources\hr_popul.sql (for populating the tables)
• db-sample-schemas-19.2\human_resources\hr_idx.sql (for creating the indexes)
• db-sample-schemas-19.2\human_resources\hr_code.sql (for creating the procedures)
• db-sample-schemas-19.2\human_resources\hr_comnt.sql (for creating the comments)
• db-sample-schemas-19.2\human_resources\hr_analz.sql (for gathering the schema statistics)

That’s it! You should now have a fully functional HR schema in your Oracle ATP database.

HTH

Matthias

 

 

SQL Developer 20.2: download, installation and connectivity to Oracle ATP cloud databases

SQL Developer 20.2, the latest version of Oracle’s free database management & SQL development tool, has been released recently. This version succeeds SQL Developer version 19.4 (smart readers may notice that Oracle has skipped version 20.1).

The following new features are included with this new version:

• Debugger – option to use DBMS_DEBUG for database debugging
• Load options include SET LOADFORMAT and SET LOADMETHOD for more flexibility on loading table data
• ORDS installer supports:
  • REST SQL and SQL Developer Web configuration
  • Custom JDBC urls and TNS
  • Allows non-SYSDBA user to install/upgrade, repair and uninstall ORDS
• SQLcl Liquibase new commands: ROLLBACKSQL, DIFF, DBDOC, VERSION
• Liquibase changeSet IDs are now generated as a hash of the content
• Explain Plan/Autotrace Search
• Include Orakafka ZIP in all distributions
• Java Home updated to 8u221
• Oracle NoSQL plugin updated to version 19.3.12
• Database Copy added ‘Ignore Storage’ to avoid tablespace dependencies
• SQL Editor Code template options panel moved to code editor page
• Code formatter new setting for number of commas to allow before a linebreak on PL/SQL call
• Added SET CODESCAN to look for SQL Injection vulnerabilities
• TABLE IMPORT enhanced to ignore virtual columns

Let’s see if we can easily install it and create a connection to a Oracle ATP database in the cloud.

Step 1: download & install

SQL Developer 20.2 can be downloaded here: https://www.oracle.com/tools/downloads/sqldev-downloads.html

Note that there are multiple versions available, please pick the correct version for your operating system (I’m on Windows 64-bit and usually take the version with JDK 8 included). Before you can download the software you need to agree to the Oracle License Agreement (beware that some countries have limited rights to use software from Oracle) and log in with an Oracle account:

Now before you extract the downloaded zip file, please compare the MD5 and SHA-1 hash of the file with the hash published on the download page. This to make sure the software is genuine. On Windows you can do this with a tool called “File Checksum Integrity Verifier” (FCIV). You can download it here: https://support.microsoft.com/en-us/help/889768/how-to-compute-the-md5-or-sha-1-cryptographic-hash-values-for-a-file

Most extraction utilities also have built-in hash comparison tools.

Once you have verified that the two hashes are correct, extract the zip file into a new folder.

To start SQL Developer open the folder “sqldeveloper” and double-click on “sqldeveloper.exe“. If you had a previous installation of SQL Developer, it will ask you to import preferences (database connections and settings) from a previous version. Click No if you want to start with a clean installation.

Step 2: connect to Oracle ATP cloud database

To connect from a local installation of SQL Developer to a Oracle ATP cloud database, you first need to download the client credentials (Oracle Wallet) from the Oracle Cloud portal site.

Log in to the Oracle Cloud portal and click on the name of the database you want to connect to. Now click on the tab “DB Connection”. The following pop-up window will appear:

You now have the choice between “Instance Wallet” (a wallet for the selected database instance) and “Region Wallet” (a wallet for all the databases in the current region). Select “Instance Wallet” and click on “Download Wallet“.

You are now asked to create a complex password for your wallet:

When you have downloaded the zipped Wallet file, go to SQL Developer and click on the green “+” sign > New Database Connection. In the database connection window fill in the required information (connection name, username, password) and select “Cloud Wallet” as Connection Type. Browse to the Instance Wallet zip file that you downloaded earlier. You will now see that the “Service” drop down list will have some predefined database service names (this is from the documentation):

• tpurgent: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
• tp: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
• high: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
• medium: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).
• low: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

Let’s take the “medium” service name and click on Test to test the connection.

If the connection test was successful, click on Save to save the connection – it will appear in the Connections window under Oracle Connections. Click on the plus sign to open the connection to your database. Now try to run some queries!

This concludes my blog post where I showed how you can install the latest version of SQL Developer and connect to a Oracle ATP database in the cloud.

HTH

Matthias

 

 

 

 

 

How to request an (always free!) Oracle APEX Cloud Database

Last year Oracle released its “Oracle Cloud Free Tier” program. This means that you can get your own Oracle cloud database for free – and what’s more, it’s *always* free, so the offer will not expire after a year and you are charged nothing except if you start using one of the paid options. In this blog post I will explain how you can request a free Oracle cloud database and install APEX on top of it. Please note that you need to enter a valid credit card during the sign-in process (a very small amount will be withdrawn and refunded, but nothing will be charged as long as you stay within the capacity limits of the free program).

At the time of writing of this blog post, the following is included in the free cloud offering:

• Databases
  • Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.
• Storage
  • 2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.
• Compute
  • 2 virtual machines with 1/8 OCPU and 1 GB memory each.
• Additional Services
  • Load Balancer: 1 instance, 10 Mbps bandwidth. Monitoring: 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications: 1 million sent through HTTPS per month, 1,000 sent through email per month. Outbound Data Transfer: 10 TB per month.

New accounts also have access to a 30-day trial period for an expanded set of services.

Step 1: Oracle Cloud Account Signup

• click on “Start for free” on the intro page https://www.oracle.com/cloud/free/
• Enter your Country/Territory and e-mail address, read the Terms of Use and click Next.
• Select “Personal Use” as Account Type and choose a unique Cloud Account Name (this will be used in service URL’s). Now for the Home Region you need to select a region where the “Always Free Eligible” option is available, I selected Germany Central (Frankfurt) because that’s the closest one to my own region (Belgium).

• Fill in your name and address details, and a valid mobile phone number. Click on “Next: Verify Mobile Number“.
• You should now receive a text message with a verification code. Enter the verification code in the Code text field (note that you only have two minutes for this) and click on “Verify Code“.
• In the next screen, enter a strong password and click on “Next: Payment Information“.
• You will get a message before entering the credit card details:
• Enter your credit card details and click on Finish.
• Check the checkbox to agree with the Terms and Conditions, and click on “Complete Sign-Up“. Your account will now be created.
• You will now receiver a confirmation e-mail. Click on “Sign In To Oracle Cloud” in the e-mail, or go to https://www.oracle.com/cloud/free/ and click on “Sign In To Oracle Cloud“.
• For the Cloud Account Name, enter the unique Cloud Account Name that you entered during the registration process (see above). Enter the strong password (see above) when it is requested.
• You should now see the Oracle Cloud home page. Well done! :-)

Step 2: Create a free Oracle ATP Database

ATP stands for “Autonomous Transaction Processing“. It’s Oracle’s self-driving, self-repairing and self-securing Cloud Database, based on Oracle version 18c (at the time of writing this blog post).

Now let’s create our first free ATP database:

• Go to https://www.oracle.com/cloud/free/ and sign in with your Cloud Account Name.
• Click on “Create an ATP database“.
• Fill in the following information:
  • Compartment: leave as is.
  • Display name: a user-friendly name, for example “My first ATP database”.
  • Database name: for example, “MATTHIASDB”.
  • Workload type: Transaction Processing
  • Deployment type: Shared Infrastructure
  • Configure the database: make sure to leave the “Always Free” option to ON, so that only free options can be selected.
  • Choose database version: 18c
  • OCPU Count should be 1, Storage (TB) should be 0.02 (so 20 GB), and Auto scaling OFF.
  • Fill in a password for the ADMIN database account.
  • Choose network access: allow secure access from everywhere
  • License type: License Included
  • Click on “Create Autonomous Database”.
  • You will now come to an overview screen where you will see the message “ATP Provisioning…”. This means that the creation of the database is in progress.
  • After a while (it took only a couple of minutes when I tried) it should turn to green, your free ATP database is now ready!

Step 3: Use SQL Developer Web to connect to your free Oracle ATP Database

SQL Developer Web is the cloud version of the SQL Developer desktop application (which is also free, btw). You can use it to connect to your ATP database and browse database objects or run SQL queries.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Now click on “Open SQL Developer Web”.
• Log in with user ADMIN and the password you created earlier (see above). The SQL Developer Web environment should now load into your web browser.
• Now try to run a query, for example put “select * from v$version” in the Worksheet window and click on the green arrow. You should see some output in the Query Result window:
• You can also browse database object using the Navigator window on the left side.

Please note that SQL Developer Web is a very basic tool compared to the desktop version of SQL Developer (which already exists for a very long time). It’s also possible to connect the desktop version of SQL Developer to your ATP Cloud Database, see for example here: https://dgielis.blogspot.com/2019/09/free-oracle-cloud-3-connecting-with-sql.html

Step 4: Access APEX in your free Oracle ATP Database

APEX (Oracle Application Express) is a low-code, web-based development platform that is included with Oracle ATP. The good news is that it’s already pre-installed, so when you have your ATP database ready, no additional steps are needed to install or configure APEX. At the time of writing, the version of APEX that is included with Oracle ATP is APEX 19.2.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Click on “Open APEX“.
• Log in with the password of the ADMIN database account (see above).
• Click on “Create Workspace” to create your first APEX Workspace. In this example, I entered MATTHIAS as database user and MY_WORKSPACE as Workspace name.
• Now in the Admin menu (top right of the page), click on “Sign Out“.
• Click on “Return to Sign In Page” and enter the Workspace name, user name and password that you just created.
• Now click on “App Gallery”, “Sample Interactive Grids” and “Install App” to install the Sample Interactive Grids application in your workspace.
• If the installation went fine, click on App Builder and then on the small arrow next to the Sample Interactive Grids application to run it. Log in with the Workspace user that you created earlier (in my case, user MATTHIAS).
• That’s it! You have now successfully installed your first APEX application in your free Oracle ATP Database!
•  The cool thing is that you now use this URL from everywhere, so you just created your first public APEX 19.2 application on Oracle Cloud!

In a future blog post I will explain some more things that you can do with your free Oracle Cloud account.

HTH

Matthias