{{{
#!text/x-rst
==============================
Preparing an Oracle test setup
==============================

This document attempts to ease the task of running Django's (or your own app's) test suite against Oracle by providing a step by step setup guide to achieve that.

Using the Oracle Developer Day pre-installed VM
===============================================

Oracle provides pre-built virtual machine images with Oracle Database 12c Release 2 Enterprise Edition (12.2.0.1.0) for developers in form of an OVA archive which can be imported by VirtualBox. The archive can be downloaded `here`__ (or alternately a different one can be chosen from `this list`__) after signing up for an *Oracle Developer Network* account.

.. __: https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
.. __: https://www.oracle.com/technetwork/community/developer-vm/index.html

The server will probably be somewhat slower than if you install it directly on the host machine, but using a VM has a slightly simpler setup, there's a somewhat smaller chance you'll pollute your host system completely and in case something goes wrong, you can always just remove the virtual machine, import a fresh copy and start again.

#. Download the OVA file from https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and import it into VirtualBox (File -> Import Appliance). You'll have to accept an extensive license agreement.

#. By default, the VM will be configured to have one NAT network interface and active port forwarding on port 1521. The database server itself starts automatically and listens on port 1521 (which is the default port for the Oracle listener), therefore you can connect to Oracle server using ``127.0.0.1:1521``. VM image uses ``orcl12c`` as Oracle *SID* and ``orcl`` as pluggable DB.

#. The server comes with more than thirty user accounts created beforehand. Password for all accounts is ``oracle``.  Crucial users are ``sys`` and ``system`` because both of them have "*sysdba*" privilege (which means full control over DB and privileges to do everything). You can use them to create new test accounts in ``orcl`` container.

   As for other credentials on the virtual machine, if you want to login as a regular user, just use ``oracle``/``oracle``, the root password is ``oracle`` as well. However, in order to run the test suite, you just need to boot the machine up, you don't need to start anything manually.

#. Install *instantclient* on your host system (the one where you'll be running the test suite). On Debian or Ubuntu you can download the RPM packages ("Instant Client Package - Basic" and "Instant Client Package - SQL*Plus" for 12.2.0.1.0) from https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html and install using alien::

   $ sudo alien -i oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
   $ sudo alien -i oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

   After installing the client, you need to add links to Oracle shared libraries and ``sqlplus``::

   $ sudo -- bash -c 'echo "/usr/lib/oracle/12.2/client64/lib/" > /etc/ld.so.conf.d/oracle.conf'
   $ sudo ldconfig
   $ sudo ln -s /usr/bin/sqlplus64 /usr/bin/sqlplus

#. Next install ``cx_Oracle`` in your testing virtualenv::

    $ pip install cx_Oracle

#. Create main user::

    $ sqlplus sys/oracle@localhost/orcl as sysdba
    SQL> ALTER SESSION SET CONTAINER=orcl;
    SQL> CREATE USER django IDENTIFIED BY django;
    SQL> GRANT DBA TO django;
    SQL> QUIT

#. Fill ``DATABASES`` in settings file::

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.oracle',
            'NAME': '127.0.0.1:1521/orcl',
            'USER': 'django',
            'PASSWORD': 'django',
            'TEST': {
                'USER': 'default_test',
                'TBLSPACE': 'default_test_tbls',
                'TBLSPACE_TMP': 'default_test_tbls_tmp',
            },
        },
        'other': {
            'ENGINE': 'django.db.backends.oracle',
            'NAME': '127.0.0.1:1521/orcl',
            'USER': 'django',
            'PASSWORD': 'django',
            'TEST': {
                'USER': 'other_test',
                'TBLSPACE': 'other_test_tbls',
                'TBLSPACE_TMP': 'other_test_tbls_tmp',
            },
        },
    }

#. Run tests::

    $ ./manage.py test
    Creating test database for alias 'default'...
    Creating test user...
    Creating test database for alias 'other'...
    Creating test user...
    ...
    ----------------------------------------------------------------------
    Ran XXX tests in XXXs

    OK
    Destroying test database for alias 'default'...
    Destroying test user...
    Destroying test database tables...
    Destroying test database for alias 'other'...
    Destroying test user...
    Destroying test database tables...
}}}