This section describes the generic manual initialization method that makes it possible to create a clean eZ Publish database on your Oracle server and/or import data from an existing MySQL database to Oracle. Both Linux/UNIX and Windows users can perform manual initialization.

In order to run eZ Publish on an Oracle database, you must have an Oracle user account with sufficient privileges. If you do not have such an account, proceed with the first step of the instructions below to create a new Oracle user for eZ Publish. When you have such a user, all the remaining steps must be done using this account.

Suppose you have a running Oracle instance named "orcl" that you can access as user "scott" with password "tiger", and you want to run eZ Publish on it. Here are the steps to achieve this goal:

1. Environment Variables

Set all the needed environment variables by eg. running `oraenv'.

. oraenv

Note: You may need to consult with your DBA to know which SID to enter.

2. Create an Oracle user with sufficient privileges (optional)

In order to create a new Oracle user for eZ Publish, connect to the Oracle instance as the database administrator (or any other Oracle user that has the "CREATE USER" system privilege) and execute the following SQL query:

Here user "system" (DBA) has password "sys":

cat <<EOF | sqlplus system/sys@orcl

 CREATE USER scott IDENTIFIED BY tiger QUOTA UNLIMITED ON SYSTEM;
 GRANT CREATE    SESSION   TO scott;
 GRANT CREATE    TABLE     TO scott;
 GRANT CREATE    TRIGGER   TO scott;
 GRANT CREATE    SEQUENCE  TO scott;
 GRANT CREATE    PROCEDURE TO scott;

Replace "SYSTEM" with the name of the default tablespace for users if it is configured. (The default tablespace for users can be specified in Oracle Database server software version 10 or higher. Previous versions default to the SYSTEM tablespace if a user is created without the DEFAULT TABLESPACE option.)

This will create an Oracle account with username "scott" and password "tiger". If the user "scott" already exists, you will see the following error message:

ORA-01920: user name 'SCOTT' conflicts with another user or role name

If the Oracle server is version 10g or later, new users might be created with a default tablespace that is not SYSTEM. You should then modify the above sql command accordingly to grant unlimited quota on the correct tablespace, and possibly specify a customised default tablespace for the user.

3. Create the "md5_digest" function

The database extension requires a custom function called "md5_digest" to be stored in the database. This function returns an MD5 hash (checksum) generated for the supplied string input. To add this function, connect to the database using the Oracle account created for eZ Publish and execute the SQL query located in the "extension/ezoracle/sql/md5_digest.sql" file. The following example shows how this can be done assuming that user "scott" can connect to an Oracle instance called "ORCL" and has the "CREATE PROCEDURE" privilege.

1.   Navigate into the eZ Publish installation directory.
2.  Create the "md5_digest" function using the following shell command:

sqlplus scott/tiger@orcl < extension/ezoracle/sql/md5_digest.sql

4. Create the "bitor" function

Another custom function that needs to be added is called "bitor". This function returns the result of a bitwise OR operation performed on two numeric arguments. To add this function, connect to the database using the Oracle account created for eZ Publish and execute the SQL query located in the "extension/ezoracle/sql/bitor.sql" file. The following example shows how this can be done assuming that user "scott" can connect to an Oracle instance called "ORCL" and has the "CREATE PROCEDURE" privilege.

1.  Navigate into the eZ Publish installation directory.

2.  Create the "bitor" function using the following shell command:

   sqlplus scott/tiger@ORCL @extension/ezoracle/sql/bitor.sql
   

5. Initialize the database structure and import pre-defined data

IIt is possible to create a clean eZ Publish database on your Oracle server or import data from an existing MySQL database to Oracle. Follow the instructions given in the corresponding subsection below.

5.1. Start from Scratch

You can start from scratch by using the schema bundled with the eZ Publish installation, and filling it with the basic data.

1. Navigate into the eZ Publish installation directory.

2. Import the schema:

./bin/php/ezsqlinsertschema.php --type=oracle \
  --user=<user> --password=<password> \
  --insert-types=schema \
  share/db_schema.dba <instance>

3. Import the data:

./bin/php/ezsqlinsertschema.php --type=oracle \
  --user=<user> --password=<password> \
  --insert-types=data \
  --schema-file=db_data.dba \
  share/db_data.dba <instance>

5.2. Migrate from Existing Database

If you want/need to migrate an existing database, you can achieve this with the native eZ Publish tools. Don't forget to add the --siteaccess  parameter if your default siteaccess isn't sufficient information:

1. Navigate into the "extension/ezoracle/scripts" subdirectory.

2. Dump your existing database to the local database.dba file:

./bin/php/ezsqldumpschema.php --output-types=all --format=generic \
  --output-array --type=mysql \
  --host=<host> --user=<user> --password=<password> \
  <database> database.dba

3. Insert the dumped data to your oracle database:

./bin/php/ezsqlinsertschema.php --type=oracle \
  --user=<user> --password=<password> \
  --insert-types=all \
  --schema-file=database.dba database.dba <instance>

6. Continue the installation process

Once the Oracle database is ready, you may continue the installation process by following the remaining steps.