To export data from Oracle table into a Flat File using Toad, follow these steps.
Export Data into Flat File in Toad
- Open Toad and connect to the database.
- Then click on the menu Database > Export > Table as Flat File.
- A window will appear as shown in below image.
- Then click on the Options tab and specify the Schema name, Table name and the flat file location as shown in below image.
- Then again click on the Spec File tab and click on the Generate Columns button. It will generate the specification file as shown in below example.
TABLEOWNER=SCOTT TABLENAME=EMP DATAFILE=F:\Temp\emp.dat LINESPERRECORD=1 COL1=EMPNO,1,1,5 COL2=ENAME,1,7,10 COL3=JOB,1,18,9 COL4=MGR,1,28,5 COL5=HIREDATE,1,34,19 COL6=SAL,1,54,9 COL7=COMM,1,64,9 COL8=DEPTNO,1,74,3
- You can click on the Save Spec File button to save this configuration in a file (optional).
- Then click on the SQL Loader tab to view the SQL loader script for this file. Below is an example of generated SQL loader file.
RECOVERABLE LOAD DATA INFILE "F:\Temp\emp.dat" INSERT INTO TABLE EMP ( EMPNO POSITION (1:5) DECIMAL EXTERNAL, ENAME POSITION (7:16) CHAR, JOB POSITION (18:26) CHAR, MGR POSITION (28:32) DECIMAL EXTERNAL, HIREDATE POSITION (34:52) DATE (19) "MM/DD/YYYY HH24:MI:SS", SAL POSITION (54:62) DECIMAL EXTERNAL, COMM POSITION (64:72) DECIMAL EXTERNAL, DEPTNO POSITION (74:76) DECIMAL EXTERNAL )
- You can use this SQL Loader control file script to load data into the similar table in any other schema.
- Then finally click on the Execute button to export the Flat file into the specified location.
