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.
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