Hot to set up and test using Oracle

Overview

Oracle 18c XE (Express Edition) introduces a different concept of container database (CDB) and pluggable databases (PDB). CDB adds a layer on top of PDBs, the accessible databases. PDBs are the databases we want to create and manipulate when testing.

This document contains information on how to navigate the Oracle environment and use the tools provided in order to create databases and ODBC data sources to access them.

Download and install Oracle 18c XE and Oracle SQL Developer.

Unicode in Oracle

Starting from Oracle Database 12c Release 2, the default database character set for a database created using Oracle Universal Installer (OUI) or Oracle Database Configuration Assistant (DBCA) is the Unicode character set AL32UTF8. [link: https://docs.oracle.com/en/database/oracle/oracle-database/20/nlspg/choosing-character-set.html#GUID-162F0087-2F35-424B-8033-1820828150E5]

Oracle Database uses the database character set for:

Data stored in SQL CHAR data types (CHAR, VARCHAR2, CLOB, and LONG)
Identifiers such as table names, column names, and PL/SQL variables
Entering and storing SQL and PL/SQL source code

The character encoding scheme used by the database is defined as part of the CREATE DATABASE statement. All SQL CHAR data type columns (CHAR, CLOB, VARCHAR2, and LONG), including columns in the data dictionary, have their data stored in the database character set. In addition, the choice of database character set determines which characters can name objects in the database. SQL NCHAR data type columns (NCHAR, NCLOB, and NVARCHAR2) use the national character set.

After the database is created, you cannot change the character sets, with some exceptions, without re-creating the database. [link: https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/choosing-character-set.html#GUID-EA913CC8-C5BA-4FB3-A1B8-882734AF4F43]

To check what character set is currently being used, use the following SQL statement:

select * from NLS_DATABASE_PARAMETERS where parameter like '%SET%';

From the result, look for the value next to NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET. The list of Oracle supported character sets [link: https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/appendix-A-locale-data.html#GUID-344E34CA-DD16-4979-8CF1-6D0A7AEBAA4A] will describe what the returned values mean.

Creating a Database (PDB)

1. Open the Database Configuration Assistant

2. Select Manage Pluggable databases

3. Click on Next

4. Select Create a Pluggable database

5. Select XE and enter system for User name and respective password

6. Click on Next

7. Leave the default selections on the following page

8. Click on Next

9. Enter the name of the database, new admin user for the database and password

10. Accept the default on the next page and click on Next

11. Click on Finish on the summary screen

12. Once done, click on Close – the database should be ready to be accessed

Client Installation

1. Select theInstant Client for Microsoft Windows to download from the page https://www.oracle.com/database/technologies/instant-client/downloads.html

2. On the Oracle Instant Client page, download the Basic (or Basic Light) package and the ODBC Package

3. Create a new folder to unzip packages into (e.g. C:\OracleClient\instantclient_19_8_64bit)

4. Unzip both packages downloaded in 2 into the folder created in 3

5. From the new folder, right-click on odbc_install.exe and select to run it as Administrator

6. The ODBC driver Oracle in folder_name (e.g. Oracle in instantclient_19_8_64bit) will be listed in the respective ODBC Data Source Administrator under the Drivers tab

Client DSN

1. Open ODBC Data Source Administrator

2. Under System DSN, click on Add

3. Select the Oracle driver (e.g. Oracle in instaclient_19_8_64bit) to use and click on Finish

4. Enter the connection information and click OK