Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: Database 11gR2

Upgrade APEX 3.2.1 to 4.1.1 on Oracle Database 11gR2

My previous post listed the steps I had followed while configuring APEX 3.2.1 in Oracle Database 11gR2. Later, I checked the Oracle APEX website and found that the current version was 4.1.1 and it had quite a few interesting new features. The features seemed exting enough to warrant an upgrade. So, here are the steps for upgrading APEX 3.2.1 to 4.1.1 on Oracle Database 11gR2. The installation guide provides the steps in details.

1. Download APEX and unzip it in your server, preserving the directory names. Here, I have kept the zip file under the base directory /home/oracle.


[oracle@db11gr2 ~]$ pwd
/home/oracle
[oracle@db11gr2 ~]$ ls
apex_4.1.1_en.zip bin data
[oracle@db11gr2 ~]$ unzip apex_4.1.1_en.zip

When you unzip it, a directory called apex will be created under the base directory.


[oracle@db11gr2 ~]$ ls
apex apex_4.1.1_en.zip bin data
[oracle@db11gr2 ~]$

2. Change your working directory to apex. For installing a full development environment, connect as sysdba and run apexins.sql passing the following four arguments:

@apexins tablespace_apex tablespace_files tablespace_temp images

tablespace_apex is the name of the tablespace for the Oracle Application Express application user
tablespace_files is the name of the tablespace for the Oracle Application Express files user
tablespace_temp is the name of the temporary tablespace
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/

I ran the script using the following parameters:


@apexins SYSAUX SYSAUX TEMP /i/

It took around 20 minutes for the script to complete execution in my environment and it ended with the following messages.


...Begin key object existence check 01:13:31
...Completed key object existence check 01:13:31
...Setting DBMS Registry 01:13:31
...Setting DBMS Registry Complete 01:13:31
...Exiting validate 01:13:31
timing for: Validate Installation
Elapsed: 00:02:04.78
timing for: Development Installation
Elapsed: 00:14:06.07
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db11gr2 apex]$

3. Connect as sysdba and run the apxldimg.sql script to update the images directory. Include, as a parameter, the path to the base directory where the APEX installation file was unzipped in step#1.


SQL> connect sys as sysdba
Enter password:
Connected.
SQL> @apxldimg.sql /home/oracle

PL/SQL procedure successfully completed.

old 1: create directory APEX_IMAGES as '&1/apex/images'
new 1: create directory APEX_IMAGES as '/home/oracle/apex/images'

Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.

timing for: Load Images
Elapsed: 00:01:21.55

Directory dropped.

SQL>

4. Enable network services for APEX. This is not a mandatory step but is required in case you want to access network services from APEX. Please check the installation document for additional details. To allow access to network services, grant connect privileges to any host for the APEX_040100 database user. This can be done by using the following script. Please check the installation guide on how to allow more fine grained access.


DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_040100
 -- the "connect" privilege if APEX_040100 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040100'
 -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040100',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_040100', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_040100', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

This completes the upgrade leaving you with a new, shiny and feature-filled Oracle APEX 4.1.1 installation.

Configure APEX in Oracle Database 11gR2

Oracle Application Express(APEX) comes pre-installed with the Oracle database from version 11g onwards. Oracle 11gR2, comes with APEX version 3.2.1. However, before you actually start using the pre-installed Oracle APEX, you need to configure it. The installation guide provides the complete details.

I configured APEX to run with the embedded PL/SQL gateway. The steps I followed for this are:

1. Configure the embedded PL/SQL gateway by running the configuration script apxconf.sql under $ORACLE_HOME/apex. When prompted, enter a password for the APEX admin account and either provide a port number for the XML DB HTTP server or choose the default(8080) one.


[oracle@db11gr2 apex]$ cd $ORACLE_HOME
[oracle@db11gr2 db_1]$ cd apex/
[oracle@db11gr2 apex]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 11 00:48:32 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @apxconf

PORT
----------
 8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.
Enter a password for the ADMIN user []
Enter a port for the XDB HTTP listener [ 8080]
...changing HTTP Port

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.
Commit complete.

SQL>

2. Unlock the ANONYMOUS account


SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

3. Verify whether the XML DB HTTP server is enabled by checking the port number


SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
 8080

SQL>

In case the port number returns 0, enable the server by issuing the following commands in SQL Plus

EXEC DBMS_XDB.SETHTTPPORT(port_number);
COMMIT;

4. Enable network services for APEX. This is not a mandatory step but is required in case you want to access network services from APEX. Please check the installation document for additional details. One of the results of not implementing this step is that clicking on the Find link in the APEX help page will result in the following error:

To allow access to network services, grant connect privileges to any host for the APEX_030200 database user. This can be done by using the following script. Please check the installation guide on how to allow more fine grained access.


DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_030200
 -- the "connect" privilege if APEX_030200 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
 -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_030200', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_030200', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

5. Enable indexing of online help. This step also is not mandatory, but not implementing this may result in the following error while clicking the Find link in the APEX help pages

Enabling the indexing of online help, requires the permission to use an Oracle Text URL datastore to be granted to the APEX_030200 database user.  To implement this step, first find the database role which has been granted this privilege. If no such role exists, then create a role, assign it to the APEX_030200 database user and grant the required permission to the role. If the role exists, then just grant this role to the APEX_030200 user. In my case, the role did not exist initially.


SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
--------------------------------------------------------------------------------

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_030200;

Grant succeeded.

SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

6. The configuration steps are complete and all that remain are creating the workspace and users. Create an APEX workspace by logging in as the administrator using the all URL, http://hostname:port/apex/apex_admin. Here ‘hostname’ will be the name of the system where the database is installed and ‘port’ is the port number specified in step#1. The username will be ‘admin’ while the password is the one specified in step#1.

To create a new workspace, go to Manage Workspaces> Create Workspace. In Identify Workspace, enter a workspace name and description and click Next.

In Identify Schema, enter the details as required. I opted for the following settings:

Re-use existing Schema? No
Schema Name: xxcustom
Schema Password: xxcustom
Space Quota: 10 MB
This will create a new schema named ‘xxcustom’ and 10MB of space will be assigned to it.

In Identify Administrator, enter the details as required. I entered the following options:

Administrator Username:xxuser
Adminsitrator Password:xxuser
First Name:
Last Name:
Email:test@example.com
This will create the APEX user, ‘xxuser’ since it does not exist. It will be created both as a developer and an workspace administrator.

Confirm the selections and click on Create.

7.  Additional users can be created by logging in as the administrator and navigating to Manage Workspace> Manage Developers and Users> Create.

8. Once an user has been created, they can log in using the URL, http://hostname:port/apex

Oracle external tables – A few examples

External tables are an useful feature which allows us to read flat-files directly using SQL just like normal Oracle tables. They offer an uncomplicated way of moving data in and out of a database making ETL processes much easier. Once an external table is created, loading data from the flat-file to the database is as simple as writing a INSERT INTO…SELECT…FROM external_table.

Example 1: A simple data load:

1. Create the directory, MY_DATA which will contain the data file.


SQL> create or replace directory MY_DATA as '/home/oracle/data';

Directory created.

SQL>

2. The data file, emp.csv contains the employee number, employee name, job, manager number, salary, commission and department number in the following format:


7369,SMITH,CLERK,7902,800,,20
7499,ALLEN,SALESMAN,7698,1600,300,30
7521,WARD,SALESMAN,7698,1250,500,30
7566,JONES,MANAGER,7839,2975,,20
7654,MARTIN,SALESMAN,7698,1250,1400,30
7698,BLAKE,MANAGER,7839,2850,,30
7782,CLARK,MANAGER,7839,2450,,10
7788,SCOTT,ANALYST,7566,3000,,20
7839,KING,PRESIDENT,,5000,,10
7844,TURNER,SALESMAN,7698,1500,0,30
7876,ADAMS,CLERK,7788,1100,,20
7900,JAMES,CLERK,7698,950,,30
7902,FORD,ANALYST,7566,3000,,20
7934,MILLER,CLERK,7782,1300,,10

3. Create the external table, emp_external.


CREATE TABLE emp_external
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY my_data
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
 LOGFILE my_data:'data.log'
 BADFILE my_data:'data.bad'
 DISCARDFILE my_data:'data.disc'
 fields terminated by ','
 MISSING FIELD VALUES ARE NULL
)
 LOCATION (my_data:'emp.csv')
);

The DEFAULT DIRECTORY points to the directory which contains the data file. LOGFILE, BADFILE and DISCARDFILE are the files which will be created when the data is loaded into the database. LOCATION indicates the name of the data file in the directory. For a complete description of the clauses refer to the Oracle documentation. One important point to remember is that comments must be placed before any access parameters. If you include comments in the access parameter sections, Oracle will throw an error when you query the external table but not when you are creating it.

4. You can now access the data in the file by querying the external table.


SQL> select * from emp_external;

EMPNO ENAME JOB MGR SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
 7369 SMITH CLERK 7902 800 20
 7499 ALLEN SALESMAN 7698 1600 300 30
 7521 WARD SALESMAN 7698 1250 500 30
 7566 JONES MANAGER 7839 2975 20
 7654 MARTIN SALESMAN 7698 1250 1400 30
 7698 BLAKE MANAGER 7839 2850 30
 7782 CLARK MANAGER 7839 2450 10
 7788 SCOTT ANALYST 7566 3000 20
 7839 KING PRESIDENT 5000 10
 7844 TURNER SALESMAN 7698 1500 0 30
 7876 ADAMS CLERK 7788 1100 20
 7900 JAMES CLERK 7698 950 30
 7902 FORD ANALYST 7566 3000 20
 7934 MILLER CLERK 7782 1300 10

14 rows selected.

SQL>

Example 2: Skip specific fields/columns, load dates

Consider the following data set which contains an additional hire date field in addition to those in the previous example. Also observe the name of the first employee which contains a comma and is surrounded by double-quotes:


7369,"S,SMITH",CLERK,7902,DEC-17-1980,800,,20
7499,ALLEN,SALESMAN,7698,FEB-20-1981,1600,300,30
7521,WARD,SALESMAN,7698,FEB-22-1981,1250,500,30
7566,JONES,MANAGER,7839,APR-02-1981,2975,,20
7654,MARTIN,SALESMAN,7698,SEP-28-1981,1250,1400,30
7698,BLAKE,MANAGER,7839,MAY-01-1981,2850,,30
7782,CLARK,MANAGER,7839,JUN-09-1981,2450,,10
7788,SCOTT,ANALYST,7566,APR-19-1987,3000,,20
7839,KING,PRESIDENT,,NOV-17-1981,5000,,10
7844,TURNER,SALESMAN,7698,SEP-08-1981,1500,0,30
7876,ADAMS,CLERK,7788,MAY-23-1987,1100,,20
7900,JAMES,CLERK,7698,DEC-03-1981,950,,30
7902,FORD,ANALYST,7566,DEC-03-1981,3000,,20
7934,MILLER,CLERK,7782,JAN-23-1982,1300,,10

While loading the data, we want to skip the JOB field.


SQL> drop table emp_external;

Table dropped

SQL> CREATE TABLE emp_external
 2 (
 3 EMPNO NUMBER(4),
 4 ENAME VARCHAR2(10),
 5 MGR NUMBER(4),
 6 HIREDATE DATE,
 7 SAL NUMBER(7,2),
 8 COMM NUMBER(7,2),
 9 DEPTNO NUMBER(2)
 10 )
 11 ORGANIZATION EXTERNAL
 12 (TYPE ORACLE_LOADER
 13 DEFAULT DIRECTORY my_data
 14 ACCESS PARAMETERS
 15 (RECORDS DELIMITED BY NEWLINE
 16 LOGFILE my_data:'data.log'
 17 BADFILE my_data:'data.bad'
 18 DISCARDFILE my_data:'data.disc'
 19 fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
 20 MISSING FIELD VALUES ARE NULL
 21 ( EMPNO,
 22 ENAME,
 23 JOB,
 24 MGR,
 25 HIREDATE DATE "MON-DD-YYYY",
 26 SAL,
 27 COMM,
 28 DEPTNO
 29 )
 30 )
 31 LOCATION (my_data:'emp.csv')
 32 );

Table created

SQL> select * from emp_external;

EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
----- ---------- ----- ----------- --------- --------- ------
 7369 S,SMITH 7902 12/17/1980 800.00 20
 7499 ALLEN 7698 2/20/1981 1600.00 300.00 30
 7521 WARD 7698 2/22/1981 1250.00 500.00 30
 7566 JONES 7839 4/2/1981 2975.00 20
 7654 MARTIN 7698 9/28/1981 1250.00 1400.00 30
 7698 BLAKE 7839 5/1/1981 2850.00 30
 7782 CLARK 7839 6/9/1981 2450.00 10
 7788 SCOTT 7566 4/19/1987 3000.00 20
 7839 KING 11/17/1981 5000.00 10
 7844 TURNER 7698 9/8/1981 1500.00 0.00 30
 7876 ADAMS 7788 5/23/1987 1100.00 20
 7900 JAMES 7698 12/3/1981 950.00 30
 7902 FORD 7566 12/3/1981 3000.00 20
 7934 MILLER 7782 1/23/1982 1300.00 10

14 rows selected

SQL>

Notice that in this case, while creating the external table we had to specify the fields in the data file. This is required for two reasons:

1. The ORACLE_LOADER access driver by default considers all fields to be of CHAR(255) type. In order to process the date field, we need to specify the data type and the date format.

2. If the field names are not specified in the data file, the fields are inserted into the table in the order in which they appear in the file. In case they are specified, the field names in the data file are mapped to the column names in the external table. To skip a specific field, all we need to do is to exclude it from the external table definition

The employee name field in the first record is taken care of by including the parameter OPTIONALLY ENCLOSED BY.

Example 3: Skip records/rows

The first n records in a file can be skipped by specifying  SKIP n. Records can also be skipped conditionally by using the parameter LOAD WHEN. For this example, we will use the data file from Example#2. We want to skip the first record and the record with employee number 7499.


SQL> drop table emp_external;

Table dropped

SQL> CREATE TABLE emp_external
 2 (
 3 EMPNO NUMBER(4),
 4 ENAME VARCHAR2(10),
 5 MGR NUMBER(4),
 6 HIREDATE DATE,
 7 SAL NUMBER(7,2),
 8 COMM NUMBER(7,2),
 9 DEPTNO NUMBER(2)
 10 )
 11 ORGANIZATION EXTERNAL
 12 (TYPE ORACLE_LOADER
 13 DEFAULT DIRECTORY my_data
 14 ACCESS PARAMETERS
 15 (RECORDS DELIMITED BY NEWLINE
 16 SKIP 1
 17 LOAD WHEN EMPNO!='7499'
 18 LOGFILE my_data:'data.log'
 19 BADFILE my_data:'data.bad'
 20 DISCARDFILE my_data:'data.disc'
 21 fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
 22 MISSING FIELD VALUES ARE NULL
 23 ( EMPNO,
 24 ENAME,
 25 JOB,
 26 MGR,
 27 HIREDATE DATE "MON-DD-YYYY",
 28 SAL,
 29 COMM,
 30 DEPTNO
 31 )
 32 )
 33 LOCATION (my_data:'emp.csv')
 34 );

Table created

SQL> select * from emp_external;

EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
----- ---------- ----- ----------- --------- --------- ------
 7521 WARD 7698 2/22/1981 1250.00 500.00 30
 7566 JONES 7839 4/2/1981 2975.00 20
 7654 MARTIN 7698 9/28/1981 1250.00 1400.00 30
 7698 BLAKE 7839 5/1/1981 2850.00 30
 7782 CLARK 7839 6/9/1981 2450.00 10
 7788 SCOTT 7566 4/19/1987 3000.00 20
 7839 KING 11/17/1981 5000.00 10
 7844 TURNER 7698 9/8/1981 1500.00 0.00 30
 7876 ADAMS 7788 5/23/1987 1100.00 20
 7900 JAMES 7698 12/3/1981 950.00 30
 7902 FORD 7566 12/3/1981 3000.00 20
 7934 MILLER 7782 1/23/1982 1300.00 10

12 rows selected

SQL>

SKIP 1 skips the first record while LOAD WHEN EMPNO!=’7499′ skips the second record in the data file.

Example 4: Using the preprocessor feature in 11gR2

The preprocessor feature allows users to preprocess the input data before it is sent to the access driver. The preprocessor feature can be used to execute system commands, user-generated binaries or user-supplied scripts before the data is processed by the access driver. This means that users can modify the data file and its contents before it is loaded into the database in a single step. However, the preprocessor programs must write to the standard output for the feature to work since it reads the input from the standard output.

For this example, we will consider the compressed file, emp.csv.gz as the data file. The contents of this file is same as in Example#2. In real-life scenarios, the input data file is generally provided in a zipped format which needs to be unzipped first before the data is loaded. This example will unzip the file and load it in a single step by using the preprocessor feature.


SQL> create or replace directory MY_BIN as '/home/oracle/bin';

Directory created.

SQL> drop table emp_external;

Table dropped

SQL>
SQL> CREATE TABLE emp_external
 2 (
 3 EMPNO NUMBER(4),
 4 ENAME VARCHAR2(10),
 5 MGR NUMBER(4),
 6 HIREDATE DATE,
 7 SAL NUMBER(7,2),
 8 COMM NUMBER(7,2),
 9 DEPTNO NUMBER(2)
 10 )
 11 ORGANIZATION EXTERNAL
 12 (TYPE ORACLE_LOADER
 13 DEFAULT DIRECTORY my_data
 14 ACCESS PARAMETERS
 15 (RECORDS DELIMITED BY NEWLINE
 16 LOAD WHEN EMPNO!='7369'
 17 LOGFILE my_data:'data.log'
 18 BADFILE my_data:'data.bad'
 19 DISCARDFILE my_data:'data.disc'
 20 PREPROCESSOR my_bin: 'zcat'
 21 fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
 22 MISSING FIELD VALUES ARE NULL
 23 ( EMPNO,
 24 ENAME,
 25 JOB,
 26 MGR,
 27 HIREDATE DATE "MON-DD-YYYY",
 28 SAL,
 29 COMM,
 30 DEPTNO
 31 )
 32 )
 33 LOCATION (my_data:'emp.csv.gz')
 34 );

Table created

SQL> select * from emp_external;

EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
----- ---------- ----- ----------- --------- --------- ------
 7499 ALLEN 7698 2/20/1981 1600.00 300.00 30
 7521 WARD 7698 2/22/1981 1250.00 500.00 30
 7566 JONES 7839 4/2/1981 2975.00 20
 7654 MARTIN 7698 9/28/1981 1250.00 1400.00 30
 7698 BLAKE 7839 5/1/1981 2850.00 30
 7782 CLARK 7839 6/9/1981 2450.00 10
 7788 SCOTT 7566 4/19/1987 3000.00 20
 7839 KING 11/17/1981 5000.00 10
 7844 TURNER 7698 9/8/1981 1500.00 0.00 30
 7876 ADAMS 7788 5/23/1987 1100.00 20
 7900 JAMES 7698 12/3/1981 950.00 30
 7902 FORD 7566 12/3/1981 3000.00 20
 7934 MILLER 7782 1/23/1982 1300.00 10

13 rows selected

SQL>

A new directory, MY_BIN has been created which contains the executable called by the preprocessor.  The PREPROCESSOR parameter points to the executable, zcat that we want to invoke before the loading process starts. Creating separate directories for preprocessor executables is not mandatory but is considered a best practice. The preprocessor feature does not support passing of arguments to the executable. For example, specifying gunzip -c instead of zcat(both of which are functionally similar) for the PREPROCESSOR parameter would have resulted in an error while accessing the external table. The solution lies in using a shell script, etst_script.sh and using it as the preprocessor executable.


[oracle@db11gr2 bin]$ cat test_script.sh
#!/bin/sh
/home/oracle/bin/gunzip -c $1
[oracle@db11gr2 bin]$

SQL> drop table emp_external;

Table dropped

SQL>
SQL> CREATE TABLE emp_external
 2 (
 3 EMPNO NUMBER(4),
 4 ENAME VARCHAR2(10),
 5 MGR NUMBER(4),
 6 HIREDATE DATE,
 7 SAL NUMBER(7,2),
 8 COMM NUMBER(7,2),
 9 DEPTNO NUMBER(2)
 10 )
 11 ORGANIZATION EXTERNAL
 12 (TYPE ORACLE_LOADER
 13 DEFAULT DIRECTORY my_data
 14 ACCESS PARAMETERS
 15 (RECORDS DELIMITED BY NEWLINE
 16 LOAD WHEN EMPNO!='7369'
 17 LOGFILE my_data:'data.log'
 18 BADFILE my_data:'data.bad'
 19 DISCARDFILE my_data:'data.disc'
 20 PREPROCESSOR my_bin: 'test_script.sh'
 21 fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
 22 MISSING FIELD VALUES ARE NULL
 23 ( EMPNO,
 24 ENAME,
 25 JOB,
 26 MGR,
 27 HIREDATE DATE "MON-DD-YYYY",
 28 SAL,
 29 COMM,
 30 DEPTNO
 31 )
 32 )
 33 LOCATION (my_data:'emp.csv.gz')
 34 );

Table created

SQL> select * from emp_external;

EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO
----- ---------- ----- ----------- --------- --------- ------
 7499 ALLEN 7698 2/20/1981 1600.00 300.00 30
 7521 WARD 7698 2/22/1981 1250.00 500.00 30
 7566 JONES 7839 4/2/1981 2975.00 20
 7654 MARTIN 7698 9/28/1981 1250.00 1400.00 30
 7698 BLAKE 7839 5/1/1981 2850.00 30
 7782 CLARK 7839 6/9/1981 2450.00 10
 7788 SCOTT 7566 4/19/1987 3000.00 20
 7839 KING 11/17/1981 5000.00 10
 7844 TURNER 7698 9/8/1981 1500.00 0.00 30
 7876 ADAMS 7788 5/23/1987 1100.00 20
 7900 JAMES 7698 12/3/1981 950.00 30
 7902 FORD 7566 12/3/1981 3000.00 20
 7934 MILLER 7782 1/23/1982 1300.00 10

13 rows selected

SQL>

Please note the following points regarding preprocessor shell scripts:

1. The full path name must be specified for system commands (for example, gunzip)
2. The data file listed in the external table LOCATION clause should be referred to by $1.

External tables and their features can also be put to other uses such as unloading data to files, reading the alert log and listing OS files using SQL.