How to unload data in a format for SQLLDR to reload later
|
|
|
Sometimes you need a simple way to transfer one or more tables from Oracle to Oracle. If you have a network connection to both databases you can use the INSERT / SELECT statement. However if the databases are located in protected areas you have to unload the data on the source database and then load it again on the target database using SQLLDR. It would be nice, if you can generate the complete controlfile including the data for SQLLDR.
The Unix and Windows script SQLLDR_EXP can be used to accomplish this task, be aware of the following. - There is an absolute limit of 2000 bytes in 7.x and 4000 bytes in 8.x per line/row for unloaded data. The total size of the unloaded data is unlimited.
- Date columns are unloaded in the format "DD.MM.YYYY".
- Beware of data with pipes or tabs in it.
- Beware of data with newlines as well.
- The Windows script need modifications if your command line SQLPLUS is not called SQLPLUS (eg: its plus33 or something similar).
- On Windows, you need to set your SQLPATH environment variable and put these files into that directory OR you need to run SQLLDR_EXP.CMD from those directories so SQLPLUS can find the corresponding SQLLDR_EXP.SQL file.
Generate the controlfile including the data for the SCOTT.EMP table: $ ./sqlldr_exp scott/tiger@RAB1 emp > emp.ctl
$ cat emp.ctl LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17.12.1980|800||20
7499|ALLEN|SALESMAN|7698|20.02.1981|1600|300|30
7521|WARD|SALESMAN|7698|22.02.1981|1250|500|30
7566|JONES|MANAGER|7839|02.04.1981|2975||20
7654|MARTIN|SALESMAN|7698|28.09.1981|1250|1400|30
7698|BLAKE|MANAGER|7839|01.05.1981|2850||30
7782|CLARK|MANAGER|7839|09.06.1981|2450||10
7788|SCOTT|ANALYST|7566|09.12.1982|3000||20
7839|KING|PRESIDENT||17.11.1981|5000||10
7844|TURNER|SALESMAN|7698|08.09.1981|1500|0|30
7876|ADAMS|CLERK|7788|12.01.1983|1100||20
7900|JAMES|CLERK|7698|03.12.1981|950||30
7902|FORD|ANALYST|7566|03.12.1981|3000||20
7934|MILLER|CLERK|7782|23.01.1982|1300||10 C:\Users\Zahn\Work sqlldr_exp.cmd scott/tiger emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17.12.1980|800||20
7499|ALLEN|SALESMAN|7698|20.02.1981|1600|300|30
7521|WARD|SALESMAN|7698|22.02.1981|1250|500|30
7566|JONES|MANAGER|7839|02.04.1981|2975||20
7654|MARTIN|SALESMAN|7698|28.09.1981|1250|1400|30
7698|BLAKE|MANAGER|7839|01.05.1981|2850||30
7782|CLARK|MANAGER|7839|09.06.1981|2450||10
7788|SCOTT|ANALYST|7566|09.12.1982|3000||20
7839|KING|PRESIDENT||17.11.1981|5000||10
7844|TURNER|SALESMAN|7698|08.09.1981|1500|0|30
7876|ADAMS|CLERK|7788|12.01.1983|1100||20
7900|JAMES|CLERK|7698|03.12.1981|950||30
7902|FORD|ANALYST|7566|03.12.1981|3000||20
7934|MILLER|CLERK|7782|23.01.1982|1300||10 Click here to download the ZIP file. |
