MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle External Table

without comments

Supporting my student labs requires the ability to use external tables. I didn’t have a chance to test external tables when I adopted Oracle’s 18c Docker installations. The following are the instructions for the macOS version, which work. Unfortunately, I couldn’t sort out a way to implement external tables in the Windows version of Oracle’s 18c Docker installation. If somebody knows the way to do that, feel free to drop me a note or a URL to where the information may be found.

These two blog posts explain Oracle’s external tables:

External Tables
External Tables with Preprocessing

These instructions build on my base macOS instructions, which you can find in this earlier Install, configure, and use an Oracle Docker Container blog post. You can find the existing Oracle virtual directories when you connect as the system user and query the dba_directories view. I used the following SQL*Plus formatting commands for the query:

SET PAGESIZE 99
COL directory_name FORMAT A24
COL directory_path FORMAT A54

and the following SQL query:

SELECT directory_name
,      directory_path
FROM   dba_directories;

It returns the following results:

DIRECTORY_NAME		 DIRECTORY_PATH
------------------------ ------------------------------------------------------
ORA_DBMS_FCP_LOGDIR	 /opt/oracle/product/18c/dbhomeXE/cfgtoollogs
SDO_DIR_WORK		 /ade/b/2794046351/oracle/work
SDO_DIR_ADMIN		 /ade/b/2794046351/oracle/md/admin
ORA_DBMS_FCP_ADMINDIR	 /opt/oracle/product/18c/dbhomeXE/rdbms/admin
XMLDIR			 /opt/oracle/product/18c/dbhomeXE/rdbms/xml
XSDDIR			 /opt/oracle/product/18c/dbhomeXE/rdbms/xml/schema
ORACLE_OCM_CONFIG_DIR2	 /opt/oracle/product/18c/dbhomeXE/ccr/state
ORACLE_OCM_CONFIG_DIR	 /opt/oracle/product/18c/dbhomeXE/ccr/state
OPATCH_INST_DIR 	 /opt/oracle/product/18c/dbhomeXE/OPatch
DATA_PUMP_DIR		 /opt/oracle/admin/XE/dpdump/
OPATCH_SCRIPT_DIR	 /opt/oracle/product/18c/dbhomeXE/QOpatch
OPATCH_LOG_DIR		 /opt/oracle/product/18c/dbhomeXE/rdbms/log
ORACLE_BASE		 /opt/oracle
ORACLE_HOME		 /opt/oracle/product/18c/dbhomeXE
 
14 rows selected.

In that prior post, you will find instructions for creating an environment file. The following instructions leverage the $ORACLE_BASE environment variable, which points to the /opt/oracle directory in the Docker environment.

Here are the steps to complete the external file setup, including how to position the physical comma-separated value (CSV) files in the available but otherwise hidden Docker directories. Hidden might be the wrong word choice but they’re not visible from the host macOS operating system. You have to connect to the Docker instance as the root user.

The following Docker command, used in the prior blog post, connects as the root user:

docker exec -it videodb bash

The instructions start before connecting to and after connecting to the Docker instance:

  1. Put the transaction_upload.csv and transaction_upload2.csv files into the following macOS host directory: 

    /Users/<installuser>/oracle/student

    which matches to the following internal Docker directory:

    /home/student
  2. As the connected root user, change directory to the $ORACLE_BASE (/opt/oracle) directory with the following command: 

    cd $ORACLE_BASE

  3. As the connected root user, make an upload directory inside the $ORACLE_BASE (/opt/oracle) directory with the following command: 

    mkdir upload

    As the root user, change the ownership of the upload director from root as the owner and primary group to oracle as the owner and oinstall as the primary group with the following command:

    chown oracle:oinstall upload

  4. As the connected root user, copy the transaction_upload.csv and transaction_upload2.csv files from the /home/student directory to the $ORACLE_BASE (/opt/oracle) directory with the following command: 

    cp ~student/transaction_upload*.csv $ORACLE_BASE/upload/.

  5. As the connected root user, change from the $ORACLE_BASE (/opt/oracle) directory to the upload subdirectory with the following command: 

    cd upload

    As the root user, change the ownership of the transaction_upload.csv and transaction_upload2.csv files from root as the owner and primary group to oracle as the owner and oinstall as the primary group with the following command:

    chown oracle:oinstall transaction_upload*.csv

  6. As the connected Oracle system user, create the upload virtual directory with the following command: 

    CREATE DIRECTORY upload AS '/opt/oracle/upload';

    As the connected Oracle system user, grant read and write privileges to the Oracle c##student user with the following command:

    GRANT READ, WRITE ON DIRECTORY upload TO c##student;

  7. As the connected Oracle c##student user, create the transaction_upload externally managed table with the following command: 

    CREATE TABLE transaction_upload
( account_number          VARCHAR2(10)
, first_name              VARCHAR2(20)
, middle_name             VARCHAR2(20)
, last_name               VARCHAR2(20)
, check_out_date          DATE
, return_date             DATE
, rental_item_type        VARCHAR2(12)
, transaction_type        VARCHAR2(14)
, transaction_amount      NUMBER
, transaction_date        DATE
, item_id                 NUMBER
, payment_method_type     VARCHAR2(14)
, payment_account_number  VARCHAR2(19))
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD':'transaction_upload.bad'
      DISCARDFILE 'UPLOAD':'transaction_upload.dis'
      LOGFILE     'UPLOAD':'transaction_upload.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('transaction_upload.csv'))
REJECT LIMIT UNLIMITED;

    As the connected Oracle c##student user, query the transaction_upload table to verify that you can read the external file source through the virtual upload directory.

    COL  record  FORMAT 99,999  HEADING "Record|Count"
SELECT TO_CHAR(COUNT(*),'99,999') AS record
FROM   transaction_upload;

    It should return the following value:

    Record
Count
-------
 11,520

  8. As the connected Oracle c##student user, create the transaction_reversal externally managed table with the following command: 

    CREATE TABLE transaction_reversal
( transaction_id          NUMBER
, transaction_account     VARCHAR2(15)
, transaction_type        VARCHAR2(30)
, transaction_date        DATE
, transaction_amount      NUMBER
, rental_id               NUMBER
, payment_method_type     NUMBER
, payment_account_number  VARCHAR2(20)
, created_by              NUMBER
, creation_date           DATE
, last_updated_by         NUMBER
, last_update_date        DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD':'transaction_upload2.bad'
      DISCARDFILE 'UPLOAD':'transaction_upload2.dis'
      LOGFILE     'UPLOAD':'transaction_upload2.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('transaction_upload2.csv'))
REJECT LIMIT UNLIMITED;

    As the connected Oracle c##student user, query the transaction_reversal table to verify that you can read the external file source through the virtual upload directory.

    COL  record  FORMAT 99,999  HEADING "Record|Count"
SELECT TO_CHAR(COUNT(*),'99,999') AS record
FROM   transaction_reversal;

    It should return the following value:

    Record
Count
-------
  1,170

Unfortunately, the file permission on the Windows version of the Oracle Docker 18c installation make it more difficult to install.

Written by maclochlainn

November 14th, 2020 at 10:57 pm

Posted in Database Design,DBA,DevOps,Docker,Linux,Mac OS X,macOS,Oracle,Oracle 18c

«
»