SQL Loader - Oracle 12C - Express Mode
One such feature is the express mode that is available in 12C and in this post let us experiment this new approach with a basic example.To get this example working, you would require a working version of Oracle 12C available at your end. Some theory to start with, that explains what all this express mode is about;
Express Mode - Basic Definition
Express mode as the name suggests simplifies the SQL loader operation by offering a mode of loading, that requires no control files. All it warrants is a data file with the name which is same as the name of the table that you are loading. For this basic example, it would be enough to know this much (we will see some advanced usage in subsequent posts). Let us see how this mode works now.
Working Example - Express Mode
We will create a test table in HR schema, which we will use for this post. The DDL of the table is shown below:
SQL> create table test
2 ( ename varchar2(10),
3 empno number)
4 /
Table created.
Next, you will have to create a simple dat file the contents of which we will load into this table via SQL Loader. As we have two columns you have to specify the column values in the same order in the file. The separator between two columns in the file should be a comma. A sample file is shown below:
test.dat
ename empno
sekhar,1
pasha,2
Praveen,3
Santhosh,4
That is all you need. You are ready to use SQL Loader to load this data into the table - through the express mode. Run the command as shown below;
Syntax
sqlldr hr table=test
Note that we are specifying the schema where the load has to happen, followed by the table name. You have to keep the test.dat file in the same directory where you are triggering the load. When you enter this command, Oracle will prompt you for the password for hr schema. Enter the password you have completed the load
Assumptions
Because the control file parameter was not specified, Sql*Loader will launch in express load mode, and look for a file called test.dat
In this mode, Sql*Loader will look up the target table, structure, and write out a loader control file possible for future use in its log file, assuming:
1. The input file columns are comma-delimited.
2. Each record is new-line-terminated.
3. Input file column order is the same as employees table.
4. The user intended to append the existing table.
