Setting up an oracle database for testing

oracle.md

Setting up database for testing

1. To build the image (since not hosted on dockerhub), follow the instructions on this page
   • If the dockerfile is incorrect, it's probably them just failing at README's. Try docker build -f Dockerfile.xe -t oracle/database:11.2.0.2-xe .
   • The README also provides more info. Under that page, search for the section referring to this specific image.
2. Do a test run: docker run -d -e ORACLE_PWD=oracle --shm-size="1g" -p 1521:1521 -p 8080:8080 oracle/database:11.2.0.2-xe (More details on --shm-size: oracle/docker-images#458)
   • The default database name will be 'xe.'
3. Add cx_Oracle / sqlalchemy to your system. Example here

Client installation (Debian)

A couple of things you still have to do to get this working on the client side.

1. Install the following libraries:

• wget
• unzip
• libaio-dev

2. Do the installation and env setup below. (I wouldn't be surprised if any of that changes though)

# Oracle DB connection dependencies
# Alternative source link in case the oracle one disappears:
# https://storage.googleapis.com/artifacts.sightmachine-178216.appspot.com/instantclient-basiclite-linux.x64-21.1.0.0.0.zip
RUN mkdir -p /opt/oracle && \
    wget -O /opt/oracle/instantclient.zip https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basiclite-linux.x64-21.1.0.0.0.zip && \
    unzip /opt/oracle/instantclient.zip -d /opt/oracle && \
    rm /opt/oracle/instantclient.zip
ENV LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1

Database-specific features

Oracle SQL is somewhat different from the regular SQL language in a few ways.

• If you're using 11g or older and want to use SQL Limit, you can use the example below. ROWNUM is a generated column. Note that below

   SELECT *
     FROM (SELECT * FROM table ORDER BY any_column ASC)
    WHERE counter > :counter AND ROWNUM <= 500

• If you have some existing LOAD DATA syntax to convert:
  • Data Types - They have to be converted so do some search.
  • Control file sample - https://docs.oracle.com/cd/B10501_01/server.920/a96652/ch05.htm
  • Control file syntax - https://docs.oracle.com/cd/A87860_01/doc/server.817/a76955/ch05.htm
  • If you're inserting fixture data into the docker image entrypoint, it's likely you'll have to specify system.table instead of just table where system is the username.

This also works with 18.4 xe but the complication is the image is a lot larger and takes a lot longer to setup. https://github.com/deeTEEcee/minimized-oraclexe-image is a fork I added that adds migration support to the minimized version.