Oracle Database : External Tables Overview and Usage


What are External Tables?

External tables are like regular SQL tables with some exceptions:
          – The metadata of external tables is created using the SQL "CREATE TABLE … ORGANIZATION EXTERNAL" statement. 
          – The data resides outside the database in OS files, thus the EXTERNAL organization.
          – The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located. The owner of the files is Oracle. Users granted access via the Database can access the files through the external table. 
          – The data is read only. 
          – You cannot perform any DML operations, nor create indexes. 
          – The external table can be queried and joined directly, in parallel using the SQL statement SELECT.

Example
——— 
A. Create the external table to extract the data without loading them inside the database

1. Create flat files emp1.dat

2 Create a directory that defines the location of the directory where the flat files reside on the OS
3 Create the external table (metadata)
4 Select data from the external table to verify that data are visible

  • Create flat files emp1.dat

$ vi /u01/ora9i/data/emp1.dat
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20

Check OS level permissions and ensure the user has read/write privileges to this directory.

  • Create a logical directory to map the OS directory where the external files reside and grant permissions to SCOTT
  • SQL> create directory emp_dir as ‘/u01/ora9i/data’ ;
    Directory created.
    SQL> GRANT READ ON DIRECTORY emp_dir TO scott;
    Grant succeeded.
    SQL> GRANT WRITE ON DIRECTORY emp_dir TO scott;
    Grant succeeded.

  • Create the external table :
  • SQL> create table scott.emp_ext
    (emp_id number(4), ename varchar2(12),
    job varchar2(12) , mgr_id number(4) ,
    hiredate date, salary number(8), comm number(8),
    dept_id number(2))
    organization external
    (type oracle_loader
    default directory emp_dir
    access parameters (records delimited by newline
    fields terminated by ‘,’)
    location (’emp1.dat’);
    Table created.

  • Check the data retrieval:

SQL> select * from scott.emp_ext;

EMP_ID ENAME    JOB        MGR_ID  HIREDATE  SALARY  COMM  DEPT_ID
—— ——-  ——–   ——- ——— ——  —– ——-
7369   SMITH    CLERK      7902    17-DEC-80 100     0     20
7499   ALLEN    SALESMAN   7698    20-FEB-81 250     0     30
7521   WARD     SALESMAN   7698    22-FEB-81 450     0     30
7566   JONES    MANAGER    7839    02-APR-81 1150    0     20
7654   MARTIN   SALESMAN   7698    28-SEP-81 1250    0     30
7698   BLAKE    MANAGER    7839    01-MAY-81 1550    0     30
7934   MILLER   CLERK      7782    23-JAN-82 3500    0     10
7 rows selected.

Useful Unix Utilities to Data File Management


The Unix operating system has a number of utilities that can be very useful for pre-processing data files to be loaded with SQL*Loader. Even when the same functionality can be achieved through SQL*Loader, the utilities described

here will be much faster. Data warehousing applications, in particular, can benefit greatly from these utilities.

EXAMPLE 1
Let us assume a load with the following control file:
LOAD DATA
INFILE ‘example1.dat’
INTO TABLE emp
(empno        POSITION(01:04)    INTEGER EXTERNAL,
ename        POSITION(06:14)    CHAR,
job        POSITION(16:24) CHAR,
mgr        POSITION(26:29) INTEGER EXTERNAL,
sal        POSITION(31:37) DECIMAL EXTERNAL,
comm        POSITION(39:42) DECIMAL EXTERNAL,
deptno        POSITION(44:45)    INTEGER EXTERNAL)

Here are the contents of data file example1.dat:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7839 KING PRESIDENT 5850.00 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30

EXAMPLE 2
Let us assume another load with the following control file:
LOAD DATA
INFILE ‘example2.dat’
INTO TABLE dept
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(deptno, dname, loc)

Below are the contents of data file example2.dat:
12,RESEARCH,”SARATOGA”
10,”ACCOUNTING”,CLEVELAND
13,”FINANCE”,”BOSTON”

PRESORTING THE DATA FILE
The performance of direct path loads can be significantly improved by presorting the input data on indexed columns. Pre-sorting minimizes the demand for temporary segments during the load.

The Unix command to be used for presorting is “sort”.
In Example 1, suppose you have added the SORTED INDEXES (empno) clause to the control file to indicate that fields in the data file are presorted on the EMPNO column. To do that presorting, you would enter at the Unix prompt:
% sort +0 -1 example1.dat > example1.srt

This will sort file example1.dat by its first field (by default fields are delimited by spaces and tabs) and send the output to file example1.srt:
7654 MARTIN SALESMAN 7698 1894.00 0.15 30
7782 CLARK MANAGER 7839 2572.50 0.20 10
7839 KING PRESIDENT 5850.00 10

In Example 2, if you wanted to sort file example2.dat by column DNAME, you would enter:
% sort -t, -d +1 -2 example2.dat > example2.srt
where “-t,” indicates that commas are delimiters, “-d” causes sort to consider only letters and digits in comparisons, and example2.srt is the output file:
10,”ACCOUNTING”,CLEVELAND
13,”FINANCE”,”BOSTON”
12,RESEARCH,”SARATOGA”

ELIMINATING FIELDS FROM THE DATA FILE
Often, it is necessary to remove one or more fields from all the records in the data file. The Unix command that does that is “cut”.
In Example 1, if you want to eliminate the COMM field altogether from the data file, enter at the Unix prompt:
% cut -c1-38,44- example1.dat > example1.cut
where the “-c” option specifies the character ranges that you want to extract from each record. The output file example1.cut contains:
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5850.00 10
7654 MARTIN SALESMAN 7698 1894.00 30

In Example 2, to eliminate the LOC field from the data file, you would enter:
% cut -f1-2 -d, example2.dat > example2.cut
where “-f1-2” indicates you want to extract the first two fields of each record and “-d,” tells cut to treat comma as a delimiter. The output file example2.cut would contain:
12,RESEARCH
10,”ACCOUNTING”
13,”FINANCE”

REPLACING CHARACTERS IN THE DATA FILE
Two Unix commands can be used here: “tr” or “sed”.
For instance, if you want to replace all double quotes in the data file in Example 2 by single quotes, you may enter:
% cat example2.dat | tr \” \’ > example2.sqt
The piped “cat” is necessary because tr’s input source is the standard input. Single and double quotes are preceded by backslashes because they are special characters. The output file will be:
12,RESEARCH,’SARATOGA’
10,’ACCOUNTING’,CLEVELAND
13,’FINANCE’,’BOSTON’

Similarly, to substitute colons for commas as delimiters in Example 2, you may enter:
% sed ‘s/,/:/g’ example2.dat > example2.cln
The output would be:
12:RESEARCH:”SARATOGA”
10:”ACCOUNTING”:CLEVELAND
13:”FINANCE”:”BOSTON”

ELIMINATING CHARACTERS FROM THE DATA FILE
Just as for replacing characters, “tr” and “sed” can be used for eliminating them from the data file.
If you want to remove all double quotes from the data file in Example 2, you may type:
% cat example2.dat | tr -d \” > example2.noq
The contents of file example2.dat are piped to the tr process, in which the “-d” option stands for “delete”. The output file example2.noq would look like:
12,RESEARCH,SARATOGA
10,ACCOUNTING,CLEVELAND
13,FINANCE,BOSTON

An identical result would be obtained by using sed:
% sed ‘s/\”//g’ example2.dat > example2.noq
The string in single quotes indicates that double quotes should be replaced by an empty string globally in the input file. Another interesting usage of tr would be to squeeze multiple blanks between fields down to a single space character. That can be achieved by doing:

% cat example1.dat | tr -s ‘ ‘ ‘ ‘ > example1.sqz
The output file would look like:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7839 KING PRESIDENT 5850.00 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30

INSERTING CHARACTERS INTO THE DATA FILE
A typical situation in which you may need to insert characters into the datafile would be to convert a fixed position data file into a delimited one. The data file in Example 1 is a fixed position one. To convert it into a file delimited by commas, you would enter
% cat example1.dat | tr -s ‘ ‘ ‘,’ > example1.dlm
and obtain
7782,CLARK,MANAGER,7839,2572.50,0.20,10
7839,KING,PRESIDENT,5850.00,10
7654,MARTIN,SALESMAN,7698,1894.00,0.15,30

MERGING DIFFERENT FILES INTO A SINGLE DATA FILE
Merging can be done by using “paste”. This command allows you to specify a list of files to be merged and the character(s) to be used as delimiter(s). For instance, to merge the data files in Examples 1 and 2, you may enter:
% paste -d’ ‘ example1.dat example2.dat > example.mrg
where “-d’ ‘” specifies a blank character as the delimiter between records being merged and example.mrg is the merged output file:
7782 CLARK MANAGER 7839 2572.50 0.20 10 12,RESEARCH,”SARATOGA”
7839 KING PRESIDENT 5850.00 10 10,”ACCOUNTING”,CLEVELAND
7654 MARTIN SALESMAN 7698 1894.00 0.15 30 13,”FINANCE”,”BOSTON”

ELIMINATING DUPLICATE RECORDS/FIELDS FROM THE DATA FILE
The Unix command for this is “uniq”. It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq.
In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
% uniq +43 example1.dat > example1.unq
The “+43” indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30

COUNTING THE NUMBER OF LINES IN THE DATA FILE
Use “wc”. For example:
% wc -l example1.dat
3 example1.dat
which indicates that the file contains three lines.

CONVERTING BETWEEN ASCII AND EBCDIC
The “dd” Unix command can be used to convert EBCDIC data files to ASCII and vice-versa. For example:
% dd if=example1.ebc conv=ascii > example1.asc
takes example1.ebc as EBCDIC input file, converts it into ASCII, and writes the converted output to file example1.asc.

CASE CONVERSIONS
The “dd” and “tr” commands can also be used for converting between uppercase and lowercase characters. Because Oracle is currently case-sensitive, this can be useful in many situations.
For example, to convert all characters in file example1.dat from uppercase to lowercase, simply enter
% dd if=example1.dat conv=lcase > example1.low
or
% cat example1.dat | tr “[A-Z]” “[a-z]” > example1.low
The contents of output file example1.low will be:
7782 clark manager 7839 2572.50 0.20 10
7839 king president 5850.00 10
7654 martin salesman 7698 1894.00 0.15 30

To convert this file back to uppercase characters, type
% dd if=example1.low conv=ucase > example1.dat
or
% cat example1.low | tr “[a-z]” “[A-Z]” > example1.dat

Using Images in Oracle HRMS


Hello Friends, Lets learn something in Oracle HRMS. Oracle Human resources has a table called PER_IMAGES. This table stores employee pictures in RAW format. If you want to mass upload the pictures of all employees in this table, you can have a hard time. we were facing a similar issue, and here is the approach i used to bulk upload (or mass upload) employee pictures to PER_IMAGES table in Oracle Human resources system using SQL Loader.

First, let’s see how the table looks:

SQL> desc per_images

Name                            Null?    Type
——————————- ——– —-
IMAGE_ID                        NOT NULL NUMBER(15)
IMAGE                           NOT NULL LONG RAW
PARENT_ID                       NOT NULL NUMBER(15)
TABLE_NAME                      NOT NULL VARCHAR2(30)
IMAGE_ID   – Next value in sequence
IMAGE      – Binary Image
PARENT_ID  – PERSON_ID from PER_PEOPLE_F
TABLE_NAME – PER_PEOPLE_F

To load a binary image in this table, we have to first create a control file, which will look something like this:

options (bindsize 400000)
load data
infile photo.bmp “fix 60999”
replace
concatenate 3
into table PER_IMAGES
(image_id constant 1, image raw(33532), parent_id constant 1253,
table_name constant “PER_PEOPLE_F”)
 
Some important facts now: Size of picture in this example is 33532 bytes. Bindsize must always be larger than the filesize. And, most importantly, only 64k can be loaded at one time, so we have divided the file size into equal portions < 64k. Here are the shell scripts, and ctl files i am using:

#mainscript.sh

 #!/bin/sh
   echo enter file name
   read fname
     exec<$fname
      x=1
      value=“
 while read line
     do
  value=`head -n $x $fname | tail -n 1`
           echo $x $value;
                sh newshscript.sh $x $value
                x=`expr $x + 1`
            done
   echo “****$value”;
 
What the above script called mainscript does is that it read the file that contain employee id and picture file name.For each employee, it than calls another shell script called newshscript.sh, and increments the id. This id we will be using in the ctl file. 

#newshscript.sh

filesize=`ls -l $3 | awk ‘{print $5}’`
fixsize=`expr $filesize`
concno=1

if [ $filesize -gt 65536 ]; then
   echo “Filesize more than 6 k. breaking up…”
   concno=2
    if [ $filesize -gt 131072 ]; then
      concno=3      
    fi
    if [ $filesize -gt 196608 ]; then
      conco=4
    fi
    if [ $filesize -gt 262144 ]; then
      conco=5
    fi
   echo “Breaking into parts :”$concno
   fixsize=`expr $filesize / $concno`  
fi

echo $1 $2 $3 $4 “fixsize” $fixsize “filesize” $filesize “conc” $concno >> fileload.log

echo “Creating ctl file for dataload now”
echo “options (bindsize=400000)” >> loadpics.ctl
echo “load data” >> loadpics.ctl
echo “infile $3″ \”fix $fixsize\”>> loadpics.ctl
echo “append” >> loadpics.ctl
echo “concatenate “$concno >> loadpics.ctl
echo “into table PER_IMAGES” >> loadpics.ctl
echo “(image_id constant $1,” >> loadpics.ctl
echo “image raw($filesize),” >> loadpics.ctl
echo “parent_id constant $2,” >> loadpics.ctl
echo “table_name constant \”PER_PEOPLE_F\”)” >> loadpics.ctl
echo “” >> loadpics.ctl

sqlldr apps/<appspwd> control=loadpics.ctl log=picsload.log

mv loadpics.ctl loadpicslast.ctl

filesize=0
fixsize=0
concno=1

Above script is pretty simple, isn’t it? Well what we are doing is reading the file size, and creating
a ctl file with the information gathered. Then we are calling SQLLDR to upload the picture using this CTL file.

Hope this helps you! Thanks you …. Shivmohan Purohit

Article Courtesy: logicwala.com