Manual initialization

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 UNIX/Linux and Windows users can perform manual initialization. The following instructions explain how it can be done.

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

You will not be able to run eZ Publish on an Oracle database unless you have an Oracle user with sufficient privileges. If you do not have such a user, connect to the Oracle database as the database administrator (or any other Oracle user that has the "CREATE USER" system privilege) and execute the following SQL query:

SQL> 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;
 GRANT ALTER ANY TABLE     TO scott;
 GRANT ALTER ANY TRIGGER   TO scott;
 GRANT ALTER ANY SEQUENCE  TO scott;
 GRANT ALTER ANY PROCEDURE TO scott;
 GRANT DROP  ANY TABLE     TO scott;
 GRANT DROP  ANY TRIGGER   TO scott;
 GRANT DROP  ANY SEQUENCE  TO scott;
 GRANT DROP  ANY PROCEDURE TO scott;

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

In this case, you should either chose a different username or make sure that the user with the username "scott" has the necessary privileges.

2. 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 an Oracle account that has the "CREATE PROCEDURE" privilege 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.

Navigate into the eZ Publish installation directory.
Create the "md5_digest" function using the following shell command:
```
sqlplus scott/tiger@ORCL @extension/ezoracle/sql/md5_digest.sql
```

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

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

Creating a clean database

In order to create a new eZ Publish site that uses an Oracle database, you will have to initialize the necessary database structure for eZ Publish according to the instructions specified in the "share/db_schema.dba" file and then import pre-defined data from the "share/db_data.dba" file to the database. This can be done by using the "ezsqlinsertschema.php" script located in the "bin/php" directory of your eZ Publish installation. The following example shows how to run this script:

Navigate into the eZ Publish installation directory.

Initialize the necessary database structure using the following shell command:

./bin/php/ezsqlinsertschema.php --type=oracle --user=scott --password=tiger share/db_schema.dba ORCL

The "--clean-existing" option makes it possible to remove eZ Publish data (if it already exists, left-overs from a previous installation):

./bin/php/ezsqlinsertschema.php --type=oracle --user=scott --password=tiger share/db_schema.dba ORCL --clean-existing

Import the pre-defined data to the database using the following shell command:

./bin/php/ezsqlinsertschema.php --type=oracle --user=scott --password=tiger --schema-file=share/db_schema.dba --insert-types=data share/db_data.dba ORCL

Importing data from MySQL

In order to migrate an existing eZ Publish site from MySQL to Oracle, you will have to import data from an existing MySQL database to Oracle. The following example shows how this can be done assuming that user "root" has password "secret" and can connect to a MySQL database called "mydb" on localhost.

Navigate into the "extension/ezoracle/scripts" subdirectory.
Run the "mysql2oracle-schema.php" script like this:
```
./mysql2oracle-schema.php mydb:root/secret@localhost > mydump.sql
```
The script will connect to the MySQL database, retrieve the database schema and save it to the "mydump.sql" file in a specific format that is compatible with Oracle.

If the Oracle database that you are going to use already contains some eZ Publish data, the "mysql2oracle-schema.php" script must be run using the "--drop" option, like this:
```
./mysql2oracle-schema.php mydb:root/secret@localhost > mydump.sql --drop
```
Import the database schema from "mydump.sql" to the Oracle database:
```
sqlplus scott/tiger@ORCL < mydump.sql
```
If you have used the "--drop" option during the previous step, the "mydump.sql" file will include an appropriate "drop" statement before every "create" instruction, so that the existing elements (if any) will be removed before new ones are created.
Run the "mysql2oracle-data.php" script using the following shell command:
```
./mysql2oracle-data.php mydb:root/secret@localhost scott/tiger@ORCL
```
The script will import data from the MySQL database to the Oracle database.
Update the sequences in the Oracle database:
```
./ora-update-seqs.php scott/tiger@ORCL
```

4. Continue the installation process

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