Skip to content

How to Set Up and Test Using Oracle

Overview

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

Oracle Overview

This document contains information on how to navigate the Oracle environment and use the tools provided 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. Choosing Character Set

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. Changing Character Set

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 can be found here.

Creating a Database (PDB)

  1. Open the Database Configuration Assistant.
  2. Select Manage Pluggable Databases.

Manage Pluggable Databases

  1. Click on Next.
  2. Select Create a Pluggable Database.

Create Pluggable Database

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

Enter User Name

  1. Click on Next.
  2. Leave the default selections on the following page.
  3. Click on Next.
  4. Enter the name of the database, new admin user for the database, and password.

Enter Database Details

  1. Accept the default on the next page and click on Next.
  2. Click on Finish on the summary screen.
  3. Once done, click on Close – the database should be ready to be accessed.

Client Installation

  1. Select the Instant Client for Microsoft Windows to download from the page Oracle Instant Client Downloads.
  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 step 2 into the folder created in step 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 instantclient_19_8_64bit) to use and click on Finish.

Add DSN

  1. Enter the connection information and click OK.

The DSN is configured and ready to access the database.

See Also

What's New in DataFlex 2021