Oracle Database Read Contents of Control File
Summary: in this tutorial, you will learn how to utilise the Oracle SQL*Loader tool to load from a flat-file into a table in the database.
Introduction to SQL*Loader tool
SQL*Loader allows you lot to load information from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and piping-delimited.
SQL*Loader provides the following methods to load information:
- Conventional path loads – construct
INSERTstatements from the contents of the input datafile based on the predefined specification and execute the inserts. - Direct path loads – creates data blocks in Oracle database block format from the datafile and direct write the data block to the database. This way is much faster than the conventional path simply subject to some restrictions.
- External table loads – create an external table for the data stored in the datafile and execute
INSERTstatements to insert the data from the datafile into the target table. The external table loads support parallel loading if datafile is big enough.
To execute the SQL*Load tool, you lot need at least three files:
- The input data file stores delimited or raw data
- The parameter file stores the location of the input/output files
- The command file contains the specification on how data is loaded.
After that, you execute the command sqlldr from the command line on Windows or Terminal on GNU/Linux:
Code language: SQL (Structured Query Language) ( sql )
>sqlldr parfile=parameter_file.par
The post-obit picture illustrates the SQL*Loader process:
Let's accept the example of using the SQL*Load tool.
SQL*Loader example
Nosotros will load email data CSV file format into the emails table in the database.
Prepare the input files
The post-obit is the content of theemail.dat file:
1,john .doe @case.com 2,jane.doe@instance.com 3,peter.doe@example.com
Code language: CSS ( css ) The contents of the command file (email.ctl) is as follows:
load data into table emails insert fields terminated by "," ( email_id, email )
Lawmaking language: JavaScript ( javascript ) In the control file:
- The
load data into table emails insertinstruct the SQL*Loader to load data into the emails table using the INSERT statement. - The
fields terminated by "," (email_id,electronic mail)specifies that each row in the file has two columnsemail_idandemailseparated by a comma (,).
Here is the content of the parameter file (email.par):
userid=ot@pdborc/Abcd1234 control=email.ctl log=email.log bad=email.bad data=email.dat direct=truthful
Code language: JavaScript ( javascript ) In this parameter file, we specify the user (userid) that will connect to the Oracle database, the command file (email.ctl), log file (email.log), and information file (electronic mail.dat).
The email.bad file stores invalid information. And the concluding line direct=truthful instructs the SQL*Loader to use the straight path load method.
Annotation that there is no space between the parameter and value, for case:
Code language: SQL (Structured Query Language) ( sql )
control=email.ctrl
Afterwards having 3 files, you can identify it in a directory e.k., C:\loader.
Hither is the link to download the three files in a zip file format:
Download the data, control and parameter files
Load data from a apartment file into the table
Starting time, create a new table to shop the email information from the input datafile:
CREATE Tabular array emails( email_id NUMBER PRIMARY Primal, email VARCHAR2(150) NOT NULL );
Code language: SQL (Structured Query Linguistic communication) ( sql ) Second, launch the SQL*Loader program from the directory that you store the files using the sqlldr command:
Code language: SQL (Structured Query Linguistic communication) ( sql )
C:\loader> sqlldr parfile=dept_loader.par
Hither is the output:
Path used: Straight Load completed - logical record count 3. Table EMAILS: 3 Rows successfully loaded. Check the log file: email .log for more than data about the load.
Lawmaking language: CSS ( css ) 3rd, log in to the Oracle and verify the contents of the emails table:
SELECT * FROM emails;
Code language: SQL (Structured Query Linguistic communication) ( sql ) Here is the result ready:
Finally, review the log file:
SQL*Loader: Release 12.1 .0 .2 .0 - Production on Tue Jul 23 08:04:46 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Control File: email.ctl Data File: email.dat Bad File: email.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors immune: fifty Continuation: none specified Path used: Directly Tabular array EMAILS, loaded from every logical record. Insert option in issue for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMAIL_ID FIRST * , CHARACTER Electronic mail NEXT * , CHARACTER The following index(es) on table EMAILS were candy: index OT.SYS_C0010446 loaded successfully with 3 keys Table EMAILS: 3 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded considering all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Bind array size non used in direct path. Cavalcade array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: iii Total logical records rejected: 0 Full logical records discarded: 0 Full stream buffers loaded by SQL*Loader main thread: 2 Total stream buffers loaded by SQL*Loader load thread: 0 Run began on Tue Jul 23 08:04:46 2019 Run ended on Tue Jul 23 08:04:48 2019 Elapsed fourth dimension was: 00:00:01.47 CPU fourth dimension was: 00:00:00.07
Code language: JavaScript ( javascript ) In this tutorial, yous have learned how to use the SQL*Loader to load data from a flat-file into a tabular array in the database.
Was this tutorial helpful?
galliherthantly62.blogspot.com
Source: https://www.oracletutorial.com/oracle-administration/oracle-sqlloader/
0 Response to "Oracle Database Read Contents of Control File"
Post a Comment