Scripts for Oracle application or Oracle archive creating

An application which stores its data into Oracle database does not use standard D2000 systems processes working with ODBC interface (kernel.exe, archiv.exe, arcsynchro.exe, ...) but uses modified versions of them (kernel_ora.exe, archiv_ora.exe, arcsynchro_ora.exe, ...). These modified processes directly use OCI interface (Oracle Client Interface) also available on platforms other than Windows (Linux, VMS).

For creating applications on Oracle platform, the PL/SQL script D2OraApp.txt is available that allows to create tablespace and tables necessary for the operation of the configuration and log databases.

For creating archive on Oracle platform, the PL/SQL script D2OraArc.txt is available that allows you to create tablespace and tables necessary for the operation of the archive database.
Both of these scripts are placed in subdirectory Templates\Oracle of installation directory.

Configuration, log and archive databases can be either in one Oracle database as three tablespaces, or in two different databases (configuration and log tablespaces in one, archive tablespace in another database). For each of the tablespaces, D2000 system uses just one user also created by the script D2OraApp.txt or D2OraArc.txt.
Configuration and log databases cannot be in different databases.

For the possibility of the use of the Oracle XE (Express Edition) database - a freely usable database based on Oracle 10g Release 2 with limitation to 4 GB of user tablespaces - there is also supported the compact configuration where the tables of configuration and log databases, or all the three ones are in one tablespace (configuration one). That configuration does not need to define the size of individual tablespaces. You must only set the size of the configuration tablespace to the maximum (4GB) and this available 4GB will be used by the database that needs it. The 'compact' configuration can enable by the parameters LogFileInSyscfg and ArchivInSyscfg.

How to create Oracle application or Oracle archive

1. Values of parameters are loaded from subdirectory Templates\Oracle of installation directory from template file:
   a/ D2OraApp.txt - in case of new Oracle application creating
   b/ D2OraArc.txt - in case of new Oracle archive creating
   
2. The files with the name starting with [Prefix] = [AppName] will be created in subdirectory [AppName]\SysCfg of application directory when the new Oracle application is being created.
   The files with the name starting with [Prefix] = [AppName]_[ArcName] will be created in subdirectory [AppName]\Archiv of application directory when the new Oracle archive is being created.
   
3. Creating the Oracle application or Oracle archive causes a generating of new script which contains the changed values of parameters and they are saved into file [Prefix].sql.
4. Then these scripts are activated in sequence:
   
   

   [Prefix]_Run1.sql	[Prefix]_Run2.sql	[Prefix]_Run3.sql
   SPOOL .\[Prefix]_Log1.log; connect @[Prefix].sql; SPOOL OFF EXIT;	connect SET SERVEROUTPUT ON SET LINE 260 SPOOL .\[Prefix]_Log2.sql; EXEC [ProcName]_[Prefix]; SPOOL OFF EXIT;	connect SPOOL .\[Prefix]_Log3.log; @[Prefix]_Log2.sql; SPOOL OFF EXIT;
   - it creates SQL procedure - a result is in the first log	- it starts up SQL procedure - a result is in the second log	- it starts up the create statement - a result is in the third log

   
   connect means the command connect [user]/[password]@[TNS] as sysdba
   [ProcName] is:
   a/ D2OraApplication - in case of new Oracle application creating
   b/ D2OraArchive - in case of new Oracle archive creating
   
5. It is recommended to check logs [Prefix]_Log1.log, [Prefix]_Log2.sql and [Prefix]_Log3.log.

Example:

The following files will be created in subdirectory AppN\SysCfg of application directory after the Oracle application AppN has been created:
AppN.sql, AppN_Run1.sql, AppN_Run2.sql, AppN_Run3.sql, AppN_Log1.log, AppN_Log2.sql and AppN_Log3.log.

The following files will be created in subdirectory AppN\Archiv of application directory after the Oracle archive ArcN has been created:
AppN_ArcN.sql, AppN_ArcN_Run1.sql, AppN_ArcN_Run2.sql, AppN_ArcN_Run3.sql, AppN_ArcN_Log1.log, AppN_ArcN_Log2.sql a AppN_ArcN_Log3.log.

When the Oracle application or Oracle archive is edited, the parameters will already be loaded from new scripts, so according to above mentioned example, from AppN.sql and AppN_ArcN.sql. These parameters will be protected against overwriting.