TRANSLATED_DESCRIPTION CHAR(4000) )

Practices for Lesson 16: Overview

Practices for Lesson 16: Moving Data

TRANSLATED_NAME,

TRANSLATED_DESCRIPTION CHAR(4000) )

End of generated control file for possible reuse.

The following index(es) on table PRODUCT_DESCRIPTIONS were processed:

index OE.PRD_DESC_PK loaded successfully with 8 keys index OE.PROD_NAME_IX loaded successfully with 8 keys

Table PRODUCT_DESCRIPTIONS: 8 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

Bind array size not used in direct path. Column array rows : 5000

Stream buffer bytes: 256000 Read buffer bytes: 1048576

Total logical records skipped: 0 Total logical records read: 8 Total logical records rejected: 0 Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 1 Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Thu Oct 16 11:07:22 2014 Run ended on Thu Oct 16 11:07:26 2014

Elapsed time was: 00:00:04.17 CPU time was: 00:00:00.03 $

f. Select the rows inserted in the OE.PRODUCT_DESCRIPTIONS table. $ sqlplus oe/oracle_4U

SQL> SELECT * FROM PRODUCT_DESCRIPTIONS WHERE product_id > 4000;

PRODUCT_ID LAN TRANSLATED_NAME TRANSLATED_DESCRIPTION

--- --- --- --- 4001 ENG Door Outdoor

4002 FRE Porte Porte exterieure

4003 SPA Puerta Puerta exterior

4004 GER Tur Auberliche Tur

5001 ENG Shutter Outdoor shutter

5002 FRE Volet Volet exterieur

5003 SPA Obturador Obturador exterior

5004 GER Fenster Fensterladen

8 rows selected.

SQL> exit $

2. As the OE user, load data into the INVENTORIES table by using SQL*Loader command line. The lab_16_02_02.dat data file contains rows of data for the PRODUCT_ON_HAND table. The lab_16_02_02.ctl file is the control file for this load.

Optionally, view the lab_16_02_02.dat and lab_16_02_02.ctl files to learn more about their structure before going further.

a. Open a terminal window and navigate to the $LABS/P16 directory.

b. Ensure that your environment is configured for the orcl database by running oraenv. $ . oraenv

ORACLE_SID = [orcl] ? orcl The Oracle base for

ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle

c. Enter the following SQL*Loader command (in continuation, without pressing Enter before reaching the end of the command.

$ sqlldr userid=oe/oracle_4U control=lab_16_02_02.ctl log=lab_16_02_02.log data=lab_16_02_02.dat

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 16 11:14:25 2014

Path used: Conventional

Commit point reached - logical record count 64

Table OE.INVENTORIES:

0 Rows successfully loaded.

Check the log file: lab_16_02_02.log

for more information about the load. $

You note that no rows were loaded. Read the log file. $ cat lab_16_02_02.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 16 11:14:25 2014

Control File: lab_16_02_02.ctl Data File: lab_16_02_02.dat Bad File: lab_16_02_02.bad Discard File: none specified

(Allow all discards)

Number to load: ALL Number to skip: 0 Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified

Path used: Conventional

Table OE.INVENTORIES, loaded from every logical record. Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype --- --- --- ---- ---- ---- WAREHOUSE_ID FIRST * , CHARACTER PRODUCT_ID NEXT * , CHARACTER

QUANTITY_ON_HAND NEXT * , CHARACTER

Record 1: Rejected - Error on table OE.INVENTORIES.