Oracle's SQL Loader (sqlldr) can be used to load rows in text file into database tables. But sqlldr need a control file to specify how to interpret the text file format, including the column (field) separator, the record separator, and the data format. If you are using ociuldr to unload the rows, this can be done by specifying the target table name with "table=" option.
It will generate a file with ".ctl" extension.
<target table name>_sqlldr.ctl
Lets take a quick example by view ALL_OBJECTS.
ociuldr user=test/test@test1 query="select * from all_objects" table=ora_objects file=ora_objects.txt
According to the name convention, let's check the content of file "ora_objects_sqlldr.ctl".
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'ora_objects.txt' "STR X'0a'"
INTO TABLE ora_objects
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
OWNER CHAR(30),
OBJECT_NAME CHAR(30),
SUBOBJECT_NAME CHAR(30),
OBJECT_ID CHAR(40),
DATA_OBJECT_ID CHAR(40),
OBJECT_TYPE CHAR(19),
CREATED DATE "YYYY-MM-DD HH24:MI:SS",
LAST_DDL_TIME DATE "YYYY-MM-DD HH24:MI:SS",
TIMESTAMP CHAR(19),
STATUS CHAR(7),
TEMPORARY CHAR(1),
GENERATED CHAR(1),
SECONDARY CHAR(1)
)
Now you can use sqlldr utility to load these rows into table "ora_objects".
sqlldr test/test@test2 control=ora_objects_sqlldr.ctl
With this feature, we can make the data exchange by text format easier. You can also use this tool to generate sqlldr control file for you by a query returning no data.