Monday, February 27, 2017

Oracle External Table PREPROCESSOR :: Loading first n lines from datafile.

This is based on one of the thread in OTN where user wants to load first 100 lines from the data file. Of course this does not make much sense, because you can load the whole file as an external table without preporcessor complexity and then just use a WHERE clause with rownum <=100.
But, for some reason if you must have to do this, then there is a workaround.
 My source file emp_csv.txt has 14 lines, emp_ext.sh is my shell file used in the table definition.

[oracle@localhost saubhik]$ pwd
/home/oracle/saubhik
[oracle@localhost saubhik]$ cat emp_csv.txt
7369,"SMITH","CLERK",7902,17-DEC-80 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82 00:00:00,1300,,10
[oracle@localhost saubhik]$ cat emp_csv.txt|wc -l
14
[oracle@localhost saubhik]$ cat emp_ext.sh
#!/bin/bash  
  
# Listing the first 10 lines  
/usr//bin/head -10 $1 
exit 
[oracle@localhost saubhik]$ 


Now the table creation and testing:

SCOTT@orclSB 27-FEB-17>  --My database version
SCOTT@orclSB 27-FEB-17> select * from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 27-FEB-17> -- External table definition which will read first 10 lines.
SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create table emp_ext
  2         (       empno varchar2(30),
  3            ename varchar2(30),
  4            job varchar2(30),
  5            mgr varchar2(30),
  6            hiredate varchar2(30),
  7            sal varchar2(30),
  8            comm varchar2(30),
  9            deptno varchar2(30)
 10        )
 11        ORGANIZATION EXTERNAL
 12         ( TYPE ORACLE_LOADER
 13           DEFAULT DIRECTORY SAUBHIK
 14           ACCESS PARAMETERS
 15           ( records delimited by newline
 16         preprocessor saubhik: 'emp_ext.sh'
 17         badfile saubhik:'emp_ext%a_%p.bad'
 18         logfile saubhik:'emp_ext%a_%p.log'
 19         fields terminated by ","
 20          optionally enclosed by '"'
 21          missing field values are null
 22          (empno,ename,job,mgr,hiredate ,sal,comm,deptno
 23          )
 24             )
 25           location
 26            ( SAUBHIK:'emp_csv.txt'
 27            )
 28         )
 29        reject limit unlimited
 30*     PARALLEL 2
SCOTT@orclSB 27-FEB-17> /

Table created.

SCOTT@orclSB 27-FEB-17> select * from emp_ext;

EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7369                           SMITH                          CLERK                          7902                           17-DEC-80 00:00:00             800
                               20

7499                           ALLEN                          SALESMAN                       7698                           20-FEB-81 00:00:00             1600
300                            30

7521                           WARD                           SALESMAN                       7698                           22-FEB-81 00:00:00             1250
500                            30


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7566                           JONES                          MANAGER                        7839                           02-APR-81 00:00:00             2975
                               20

7654                           MARTIN                         SALESMAN                       7698                           28-SEP-81 00:00:00             1250
1400                           30

7698                           BLAKE                          MANAGER                        7839                           01-MAY-81 00:00:00             2850
                               30


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7782                           CLARK                          MANAGER                        7839                           09-JUN-81 00:00:00             2450
                               10

7788                           SCOTT                          ANALYST                        7566                           19-APR-87 00:00:00             3000
                               20

7839                           KING                           PRESIDENT                                                     17-NOV-81 00:00:00             5000
                               10


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7844                           TURNER                         SALESMAN                       7698                           08-SEP-81 00:00:00             1500
0                              30


10 rows selected.


No comments :

Post a Comment