Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
In this chapter, we will discuss data loading - in other words, how to get data into an Oracle database. The main focus of the chapter will be the SQL*LOADER tool (or SQLLDR, pronounced 'sequel loader'), as it is still the predominant method for loading data. However, we will look at a few other options along the way and will also briefly explore how to get data out of the database.
SQLLDR has been around for as long as I can remember, with various minor enhancements over time, but is still the subject of some confusion. My remit in this chapter is not to be comprehensive, but rather to tackle the issues that I see people encounter every day when using this tool. The chapter has a 'question and answer' format. I will present a frequently encountered requirement or issue, and explore possible solutions. In the process we'll cover many of the practicalities of using the SQLLDR tool and of data loading in general:
Loading delimited and fixed format data.
Loading dates.
Loading data using sequences, in which we take a look at the addition of the CASE statement in SQL, added in Oracle 8.1.6.
How to do an upsert (update if data exists, insert otherwise).
Loading data with embedded newlines, where we rely on the use of some new features and options, such as the FIX, VAR, and STR attributes, added in Oracle 8.1.6.
Loading LOBs using the new data types, BLOB and CLOB, introduced in Oracle8.0, which support a much deeper functionality than the legacy LONG and LONG RAW types.
We will not cover the direct path loader mode in any detail and nor will we cover topics such as using SQLLDR for data warehousing, parallel loads, and so on. These topics could fill an entire book by themselves.
SQL*LOADER (SQLLDR) is Oracle's high speed, bulk data loader. It is an extremely useful tool, used to get data into an Oracle database from a variety of flat file formats. SQLLDR can be used to load enormous amounts of data in an amazingly short period of time. It has two modes of operation:
Conventional path - SQLLDR will employ SQL inserts on our behalf to load data.
Direct path - Does not use SQL. Formats database blocks directly.
The direct path load allows you to read data from a flat file, and write it directly to formatted database blocks, bypassing the entire SQL engine (and rollback and redo at the same time). When used in parallel, direct path load is the fastest way from no data to a fully loaded database, and can't be beaten.
We will not cover every single aspect of SQLLDR. For all of the details, the Oracle Server Utilities Guide dedicates six chapters to SQLLDR. The fact that it is six chapters is notable, since every other utility gets one chapter or less. For complete syntax and all of the options, I will point you to this reference guide - this chapter is intended to answer the 'How do I?' questions that a reference guide does not address.
It should be noted that the OCI (Oracle Call Interface for C) allows you to write your own direct path loader using C, with Oracle8.1.6 release 1 and onwards as well. This is useful when the operation you want to perform is not feasible in SQLLDR, or when seamless integration with your application is desired. SQLLDR is a command line tool - a separate program. It is not an API or anything that can be 'called from PL/SQL', for example.
If you execute SQLLDR from the command line with no inputs, it gives you the following help:
$ sqlldr SQLLDR: Release 8.1.6.1.0 - Production on Sun Sep 17 12:02:59 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Usage: SQLLOAD keyword=value [,keyword=value,...] Valid Keywords: userid -- ORACLE username/password control -- Control file name log -- Log file name bad -- Bad file name data -- Data file name discard -- Discard file name discardmax -- Number of discards to allow (Default all) skip -- Number of logical records to skip (Default 0) load -- Number of logical records to load (Default all) errors -- Number of errors to allow (Default 50) rows -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- Size of conventional path bind array in bytes (Default 65536) silent -- Suppress messages during run (header, feedback, errors, discards, partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- File to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- Size of Read buffer (Default 1048576)
We will quickly go over the meaning of these parameters in the following table:
| Parameter | Meaning |
|---|---|
| BAD | The name of a file that will contain rejected records at the end of the load. If you do not specify a name for this, the BAD file will be named after the CONTROL file (see later in the chapter for more details on control files), we use to load with. For example, if you use a CONTROL file named foo.ctl, the BAD file will default to foo.bad, which SQLLDR will write to (or overwrite if it already exists). |
| BINDSIZE | The size in bytes of the buffer used by SQLLDR to insert data in the conventional path loader. It is not used in a direct path load. It is used to size the array with which SQLLDR will insert data. |
| CONTROL | The name of a CONTROL file, which describes to SQLLDR how the input data is formatted, and how to load it into a table. You need a CONTROL file for every SQLLDR execution. |
| DATA | The name of the input file from which to read the data. |
| DIRECT | Valid values are True and False, with the default being False. By default, SQLLDR will use the conventional path load method. |
| DISCARD | The name of a file to write records that are not to be loaded. SQLLDR can be used to filter input records, allowing you to specify that only records meeting a specified criteria are loaded. |
| DISCARDMAX | Specifies the maximum number of discarded records permitted in a load. If you exceed this value, the load will terminate. By default, all records may be discarded without terminating a load. |
| ERRORS | The maximum number of errors encountered by SQLLDR that are permitted before the load terminates. These errors can be caused by many things, such as conversion errors (by trying to load ABC into a number field for example), duplicate records in a unique index, and so on. By default, 50 errors are permitted, and then the load will terminate. In order to allow all valid records to be loaded in that particular session (with the rejected records going to the BAD file), specify a large number such as 999999999. |
| FILE | When using the direct path load option in parallel, you may use this to tell SQLLDR exactly which database data file to load into. You would use this to reduce contention for the database data files during a parallel load, to ensure each loader session is writing to a separate device. |
| LOAD | The maximum number of records to load. Typically used to load a sample of a large data file, or used in conjunction with SKIP to load a specific range of records from an input file. |
| LOG | Used to name the LOG file. By default, SQLLDR will create a LOG file named after the CONTROL file in the same fashion as the BAD file. |
| PARALLEL | Will be TRUE or FALSE. When TRUE, it signifies you are doing a parallel direct path load. This is not necessary when using a conventional path load - they may be done in parallel without setting this parameter. |
| PARFILE | Can be used to specify the name of a file that contains all of these KEYWORD=VALUE pairs. This is used instead of specifying them all on the command line. |
| READSIZE | Specifies the size of the buffer used to read input data. |
| ROWS | The number of rows SQLLDR should insert between commits in a conventional path load. In a direct path load, this is the number of rows to be loaded before performing a data save (similar to a commit). In a conventional path load, the default is 64 rows. In a direct path load, the default is to not perform a data save until the load is complete. |
| SILENT | Suppresses various informational messages at run-time. |
| SKIP | Used to tell SQLLDR to skip x number of records in the input file. Most commonly used to resume an aborted load (skipping the records that have been already loaded), or to only load one part of an input file. |
| USERID | The USERNAME/PASSWORD@DATABASE connect string. Used to authenticate to the database. |
| SKIP_INDEX_MAINTENANCE | Does not apply to conventional path loads - all indexes are always maintained in this mode. In a direct path load, this tells Oracle not to maintain indexes by marking them as unusable. These indexes must be rebuilt after the load. |
| SKIP_UNUSABLE_INDEXES | Tells SQLLDR to allow rows to be loaded into a table that has unusable indexes, as long as the indexes are not unique indexes. |
In order to use SQLLDR, you will need a control file. A control file simply contains information describing the input data - its layout, datatypes, and so on, as well as information about what table(s) it should be loaded into. The control file can even contain the data to load. In the following example, we build up a simple control file in a step-by-step fashion, with an explanation of the commands at each stage:
LOAD DATA LOAD DATA - This tells SQLLDR what to do, in this case load data. The other thing SQLLDR can do is CONTINUE_LOAD, to resume a load. We would use this option only when continuing a multi-table direct path load.
INFILE * INFILE * - This tells SQLLDR the data to be loaded is actually contained within the control file itself (see below). Alternatively you could specify the name of another file that contains the data. We can override this INFILE statement using a command line parameter if we wish. Be aware that command line options override control file settings, as we shall see in the Caveats section.
INTO TABLE DEPT INTO TABLE DEPT - This tells SQLLDR to which table we are loading data, in this case the DEPT table.
FIELDS TERMINATED BY ',' FIELDS TERMINATED BY ',' - This tells SQLLDR that the data will be in the form of comma-separated values. There are dozens of ways to describe the input data to SQLLDR, this is just one of the more common methods.
(DEPTNO, DNAME, LOC )
(DEPTNO, DNAME, LOC) - This tells SQLLDR what columns we are loading, their order in the input data, and their data types. The data types are of the data in the input stream, not the data types in the database. In this case, they are defaulting to CHAR(255), which is sufficient.
BEGINDATA BEGINDATA - This tells SQLLDR we have finished describing the input data, and that the very next line will be the actual data to be loaded into the DEPT table:
10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia
So, this is a control file in one of its most simple and common formats - to load delimited data into a table. We will take a look at some much complex examples in this chapter, but this a good one to get our feet wet with. To use this control file, all we need to do is create an empty DEPT table:
tkyte@TKYTE816> create table dept 2 ( deptno number(2) constraint emp_pk primary key, 3 dname varchar2(14), 4 loc varchar2(13) 5 ) 6 / Table created.
and run the following command:
C:\sqlldr>sqlldr userid=tkyte/tkyte control=demo1.ctl SQLLDR: Release 8.1.6.0.0 - Production on Sat Apr 14 10:54:56 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Commit point reached - logical record count 4
If the table is not empty, you will receive an error message to the following effect:
SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT This is because we allowed almost everything in the control file to default, and the default load option is INSERT (as opposed to APPEND, TRUNCATE, or REPLACE). In order to INSERT, SQLLDR assumes the table is empty. If we wanted to add records to the DEPT table, we could have specified APPEND, or to replace the data in the DEPT table, we could have used REPLACE or TRUNCATE.
Every load will generate a log file. The log file from our simple load looks like this:
SQLLDR: Release 8.1.6.0.0 - Production on Sat Apr 14 10:58:02 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: demo1.ctl Data File: demo1.ctl Bad File: demo1.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 65536 bytes Continuation: none specified Path used: Conventional Table DEPT, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------- ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER Table DEPT: 4 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. Space allocated for bind array: 49536 bytes(64 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 4 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Sat Apr 14 10:58:02 2001 Run ended on Sat Apr 14 10:58:02 2001 Elapsed time was: 00:00:00.11 CPU time was: 00:00:00.04
These log files tell us about many of the aspects of our load. We can see the options we used (defaulted or otherwise). We can see how many records were read, how many loaded, and so on. It specifies the locations of all BAD and DISCARD files. It even tells us how long it took. These log files are crucial for verifying that the load was successful, as well as for diagnosing errors. If the loaded data resulted in SQL errors (the input data was 'bad', and created records in the BAD file), these errors would be recorded here. The information in the log file is largely self-explanatory, so we will not spend any more time on it.
We will now cover what I have found to be the most frequently asked questions with regards to loading and unloading data in an Oracle database, using SQLLDR.
Delimited data, data that is separated by some special character, and perhaps enclosed in quotes, is the most popular data format for flat files today. On a mainframe, a fixed length, fixed format file would probably be the most recognized file format, but on UNIX and NT, delimited files are the norm. In this section, we will investigate the popular options used to load delimited data.
The most popular format for delimited data is the CSV format where CSV stands for comma-separated values. In this file format, where each field of data is separated from the next by a comma, text strings can be enclosed within quotes, thus allowing for the string itself to contain a comma. If the string must contain a quotation mark as well, the convention is to double up the quotation mark (in the following code we use "" in place of just a ").
A typical control file to load delimited data will look much like this:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,"""USA""" 20,Accounting,"Virginia,USA" 30,Consulting,Virginia 40,Finance,Virginia 50,"Finance","",Virginia 60,"Finance",,Virginia
The following line performs the bulk of the work:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
It specifies that a comma separates the data fields, and that each field might be enclosed in double quotes. When we run SQLLDR using this control file, the results will be:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales "USA" 20 Accounting Virginia,USA 30 Consulting Virginia 40 Finance Virginia 50 Finance 60 Finance 6 rows selected.
Notice the following in particular:
"USA" C This resulted from input data that was """USA""". SQLLDR counted the double occurrence of " as a single occurrence within the enclosed string. In order to load a string that contains the optional enclosure character, you must ensure they are doubled up.
Virginia,USA in department 20 C This results from input data that was "Virginia,USA". This input data field had to be enclosed in quotes to retain the comma as part of the data. Otherwise, the comma would have been treated as the end of field marker, and Virginia would have been loaded without the USA text.
Departments 50 and 60 were loaded with Null location fields. When data is missing, you can choose to enclose it or not, the effect is the same.
Another popular format is tab-delimited data: data that is separated by tabs rather than commas. There are two ways to load this data using the TERMINATED BY clause:
TERMINATED BY X'09', which is the tab character using hexadecimal format (ASCII 9 is a tab character), or you might use
TERMINATED BY WHITESPACE
The two are very different in implementation however, as the following shows. Using the DEPT table from above we'll load using this control file:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE (DEPTNO, DNAME, LOC) BEGINDATA 10 Sales Virginia
It is not readily visible on the page, but there are two tabs between each piece of data in the above. The data line is really:
10\t\tSales\t\tVirginia Where the \t is the universally recognized tab escape sequence. When you use this control file with the TERMINATED BY WHITESPACE clause as above, the resulting data in the table DEPT is:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales Virginia
TERMINATED BY WHITESPACE parses the string by looking for the first occurrence of whitespace (tab, blank or newline) and then continues until it finds the next non-whitespace character. Hence, when it parsed the data, DEPTNO had 10 assigned to it, the two subsequent tabs were considered as whitespace, and then Sales was assigned to DNAME, and so on.
On the other hand, if you were to use FIELDS TERMINATED BY X'09', as the following control file does:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY X'09' (DEPTNO, DNAME, LOC ) BEGINDATA 10 Sales Virginia You would find DEPT loaded with the following data:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales
Here, once SQLLDR encountered a tab, it output a value. Hence, 10 is assigned to DEPTNO, and DNAME gets Null since there is no data between the first tab, and the next occurrence of a tab. Sales gets assigned to LOC.
This is the intended behavior of TERMINATED BY WHITESPACE, and TERMINATED BY <character>. Which is more appropriate to use will be dictated by the input data, and how you need it to be interpreted.
Lastly, when loading delimited data such as this, it is very common to want to skip over various columns in the input record. For example, you might want to load columns 1, 3, and 5, skipping columns 2 and 4. In order to do this, SQLLDR provides the FILLER keyword. This allows us to map a column in an input record, but not put it into the database. For example, given the DEPT table from above, the following control file contains 4 delimited fields but will not load the second field into the database:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( DEPTNO, FILLER_1 FILLER, DNAME, LOC ) BEGINDATA 20,Something Not To Be Loaded,Accounting,"Virginia,USA"
The resulting DEPT table is:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 20 Accounting Virginia,USA
Often, you have a flat file generated from some external system, and this file is a fixed length file with positional data. For example, the NAME field is in bytes 1 to 10, the ADDRESS field is in bytes 11 to 35, and so on. We will look at how SQLLDR can import this kind of data for us.
This fixed width, positional data is the optimal data for SQLLDR to load. It will be the fastest for it to process as the input data stream is somewhat trivial to parse. SQLLDR will have stored fixed byte offsets and lengths into data records, and extracting a given field is very simple. If you have an extremely large volume of data to load, converting it to a fixed position format is generally the best approach. The downside to a fixed width file is, of course, that it can be much larger than a simple, delimited file format.
To load fixed position data, you will use the POSITION keyword in the control file. For example:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME position(3:16), LOC position(17:29) ) BEGINDATA 10Accounting Virginia,USA
This control file does not employ the FIELDS TERMINATED BY clause, rather it uses POSITION to tell SQLLDR where fields begin and end. Of interest to note with the POSITION clause is that we could use overlapping positions, and go back and forth in the record. For example if we were to alter the DEPT table as follows:
tkyte@TKYTE816> alter table dept add entire_line varchar(29); Table altered.
And then use the following control file:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME position(3:16), LOC position(17:29), ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting Virginia,USA
The field ENTIRE_LINE is defined as position(1:29) C it extracts its data from all 29 bytes of input data, whereas the other fields are substrings of the input data. The outcome of the above control file will be:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- ------------- ----------------------------- 10 Accounting Virginia,USA 10Accounting Virginia,USA
When using POSITION, we can use relative or absolute offsets. In the above, I used absolute offsets. I specifically denoted where fields begin, and where they end. I could have written the above control file as:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME position(*:16), LOC position(*:29), ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting Virginia,USA
The * instructs the control file to pick up where the last field left off. Therefore (*:16) is just the same as (3:16) in this case. Notice that you can mix relative, and absolute positions in the control file. Additionally, when using the * notation, you can add to the offset. For example, if DNAME started 2 bytes after the end of DEPTNO, I could have used (*+2:16). In this example the effect would be identical to using (5:16).
The ending position in the POSITION clause must be the absolute column position where the data ends. At times, it can be easier to specify just the length of each field, especially if they are contiguous, as in the above example. In this fashion we would just have to tell SQLLDR the record starts at byte 1, and then specify the length of each field. This will save us from having to compute start and stop byte offsets into the record, which can be hard at times. In order to do this, we'll leave off the ending position, and instead, specify the length of each field in the fixed length record as follows:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1) char(2), DNAME position(*) char(14), LOC position(*) char(13), ENTIRE_LINE position(1) char(29) ) BEGINDATA 10Accounting Virginia,USA
Here we only had to tell SQLLDR where the first field begins, and its length. Each subsequent field starts where the last one left off, and continues for a specified length. It is not until the last field that we have to specify a position again, since this field goes back to the beginning of the record.
Loading dates using SQLLDR is fairly straightforward, but seems to be a common point of confusion. You simply need to use the DATE data type in the control file, and specify the date mask to be used. This date mask is the same mask you use with TO_CHAR and TO_DATE in the database. SQLLDR will apply this date mask to your data and load it for you.
For example, if we alter our DEPT table again:
tkyte@TKYTE816> alter table dept add last_updated date; Table altered.
We can load it with the following control file:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC, LAST_UPDATED date 'dd/mm/yyyy' ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001
The resulting DEPT table will look like this:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC LAST_UPDA ---------- -------------- ------------- --------- 10 Sales Virginia 01-MAY-00 20 Accounting Virginia 21-JUN-99 30 Consulting Virginia 05-JAN-00 40 Finance Virginia 15-MAR-01
It is that easy. Just supply the format in the control file and SQLLDR will convert the date for us. In some cases, it might be appropriate to us a more powerful SQL function. For example, if your input file contains dates in many different formats: sometimes with the time component, sometimes without, sometimes in DD-MON-YYYY format, sometime in DD/MM/YYYY format, and so on. We'll see in the next section how we can use functions in SQLLDR to overcome these challenges.
In this section we will see how to refer to sequences and other functions whilst loading data. Bear in mind however, that the use of database sequences and other functions require the SQL engine, and hence will not work in a direct path load.
Using functions in SQLLDR is very easy once you understand how SQLLDR builds its INSERT statement. To have a function applied to a field in a SQLLDR script, we simply add it to the control file in double quotes. For example, say you have the DEPT table from above, and would like to make sure the data being loaded is in uppercase. You could use the following control file to load it:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy' ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001
The resulting data in the database will be:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA ------ -------------- ------------- ----------------------------- --------- 10 SALES VIRGINIA 01-MAY-00 20 ACCOUNTING VIRGINIA 21-JUN-99 30 CONSULTING VIRGINIA 05-JAN-00 40 FINANCE VIRGINIA 15-MAR-01
Notice how we are able to easily uppercase the data just by applying the UPPER function to a bind variable. It should be noted that the SQL functions could refer to any of the columns, regardless of the column the function is actually applied to. This means that a column can be the result of a function on two or more of the other columns. For example, if we wanted to load the column ENTIRE_LINE, we could use the SQL concatenation operator. It is a little more involved than that though in this case. Right now, the input data set has four data elements in it. If we were to simply add ENTIRE_LINE to the control file like this:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy', ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001
We would find this error in our LOG file, for each input record:
Record 1: Rejected - Error on table DEPT, column ENTIRE_LINE. Column not found before end of logical record (use TRAILING NULLCOLS)
Here, SQLLDR is telling us that it ran out of data in the record before it ran out of columns. The solution is easy in this case, and in fact SQLLDR even tells us what to do C USE TRAILING NULLCOLS. This will have SQLLDR bind a Null value in for that column if no data exists in the input record. In this case, adding TRAILING NULLCOLS will cause the bind variable :ENTIRE_LINE to be Null. So, we retry with this control file:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy', ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001 Now the data in the table is as follows:
tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA ------ -------------- ------------- ----------------------------- --------- 10 SALES VIRGINIA 10SalesVirginia1/5/2000 01-MAY-00 20 ACCOUNTING VIRGINIA 20AccountingVirginia21/6/1999 21-JUN-99 30 CONSULTING VIRGINIA 30ConsultingVirginia5/1/2000 05-JAN-00 40 FINANCE VIRGINIA 40FinanceVirginia15/3/2001 15-MAR-01
What makes this feat possible is the way SQLLDR builds its INSERT statement. SQLLDR will look at the above, and see the DEPTNO, DNAME, LOC, LAST_UPDATED, and ENTIRE_LINE columns in the control file. It will set up five bind variables named after these columns. Normally, in the absence of any functions, the INSERT statement it builds is simply:
INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE ) VALUES ( :DEPTNO, :DNAME, :LOC, :LAST_UPDATED, :ENTIRE_LINE );
It would then parse the input stream, assigning the values to its bind variables, and then execute the statement. When we begin to use functions, SQLLDR incorporates them into its INSERT statement. In our example above, the INSERT statement SQLLDR builds will look like this:
INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE) VALUES ( :DEPTNO, upper(:dname), upper(:loc), :last_updated, :deptno||:dname||:loc||:last_updated );
It then prepares, and binds, the inputs to this statement, and executes it. So, pretty much anything you can think of doing in SQL, you can incorporate into you SQLLDR scripts. With the addition of the CASE statement in SQL (added in Oracle), this can be extremely powerful and easy. For example, let's say sometimes our dates contained time components, and sometimes they did not. We could use a control file like this:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "case when length(:last_updated) <= 10 then to_date(:last_updated,'dd/mm/yyyy') else to_date(:last_updated,'dd/mm/yyyy hh24:mi:ss') end" ) BEGINDATA 10,Sales,Virginia,1/5/2000 12:03:03 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 01:23:00 40,Finance,Virginia,15/3/2001
which results in:
tkyte@TKYTE816> alter session 2 set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered. tkyte@TKYTE816> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ------ -------------- ------------- ----------------- -------------------- 10 SALES VIRGINIA 01-may-2000 12:03:03 20 ACCOUNTING VIRGINIA 21-jun-1999 00:00:00 30 CONSULTING VIRGINIA 05-jan-2000 01:23:00 40 FINANCE VIRGINIA 15-mar-2001 00:00:00
Now, one of two date formats will be applied to the input character string (notice that we are not loading a DATE anymore, we are just loading a string). The CASE function will look at the length of the string to determine which of the two masks it should use.
It is interesting to note that we can write our own functions to be called from SQLLDR. This is a straightforward application of the fact that PL/SQL can be called from SQL. For example, suppose your dates are supplied in the input file in one of the following formats (I am surprised how frequently this comes up, apparently it is common to mix and match your date formats in input files for some reason):
dd-mon-yyyy dd-month-yyyy dd/mm/yyyy dd/mm/yyyy hh24:mi:ss number of seconds since January 1st 1970 GMT (aka "UNIX time")
Now, using the CASE statement would be very hard, since the length of the string does not imply the format to use. What we can do instead, is to create a function that tries date formats until it finds the one that works. The following routine loops over an array of formats trying them one by one until one of them succeeds. If, after looping, we still haven't converted the date, we assume it is a UNIX date, and perform the appropriate conversion. If this fails, the failure is simply allowed to propagate back to SQLLDR, which will place the record in the BAD file. The routine looks like this:
tkyte@TKYTE816> create or replace 2 function my_to_date( p_string in varchar2 ) return date 3 as 4 type fmtArray is table of varchar2(25); 5 6 l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 7 'dd/mm/yyyy', 8 'dd/mm/yyyy hh24:mi:ss' ); 9 l_return date; 10 begin 11 for i in 1 .. l_fmts.count 12 loop 13 begin 14 l_return := to_date( p_string, l_fmts(i) ); 15 exception 16 when others then null; 17 end; 18 EXIT when l_return is not null; 19 end loop; 20 21 if ( l_return is null ) 22 then 23 l_return := 24 new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * 25 p_string, 'GMT', 'EST' ); 26 end if; 27 28 return l_return; 29 end; 30 / Function created.
Then, we could use a control file such as:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )" ) BEGINDATA 10,Sales,Virginia,01-april-2001 20,Accounting,Virginia,13/04/2001 30,Consulting,Virginia,14/04/2001 12:02:02 40,Finance,Virginia,987268297 50,Finance,Virginia,02-apr-2001 60,Finance,Virginia,Not a date
After loading, we would find the error message:
Record 6: Rejected - Error on table DEPT, column LAST_UPDATED. ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "TKYTE.MY_TO_DATE", line 30 ORA-06512: at line 1
indicating that the last record failed, but all the others were in fact loaded. This failed record will be in our BAD file. We can fix it and reload it later. Inspecting the data that did get loaded we see:
tkyte@TKYTE816> alter session 2 set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered. tkyte@TKYTE816> select deptno, dname, loc, last_updated from dept; DEPTNO DNAME LOC LAST_UPDATED ------ -------------- ------------- -------------------- 10 SALES VIRGINIA 01-apr-2001 00:00:00 20 ACCOUNTING VIRGINIA 13-apr-2001 00:00:00 30 CONSULTING VIRGINIA 14-apr-2001 12:02:02 40 FINANCE VIRGINIA 14-apr-2001 12:11:37 50 FINANCE VIRGINIA 02-apr-2001 00:00:00
Frequently, you will receive a file of records for an existing table. You would like to use this data to update rows that already exist, by some primary key, or insert rows that do not yet exist. This is not really possible in one step, but can be accomplished in three easy steps. I'll first outline what we need to do, and then show you step-by-step what the code might look like. You would:
Load all of the data with the APPEND option and specifying ERRORS=99999999. Using a large number for errors lets all of the 'good' records get loaded. The records that would be used for UPDATES will get rejected based on a primary key or unique constraint violation. They will be written to a BAD file. We will get all of the new records loaded this way.
Load the BAD file into a working table with the TRUNCATE option. This table is structurally the same as the 'real' table C it should have the same set of constraints and such. This will make it so that only the duplicate records are loaded. Records that were rejected for other data validation reasons will be rejected again from this table.
Update the join of the real table and the working table.
Using the DEPT table from earlier, with the last set of data (departments 10, 20, 30, 40 and 50), we'll load the following data:
10, Sales,New York,14-april-2001 60,Finance,Virginia,14-april-2001
This should UPDATE one record and INSERT one record. Assume this data is in the file new.dat and you have the control file load.ctl as follows:
LOAD DATA INTO TABLE DEPT APPEND FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )" )
This is very similar to our last control file, with the exception that the INFILE * and BEGINDATA statements have been removed, and REPLACE was changed to APPEND. We will specify the data file to load on the command line, hence the INFILE is not needed and since the data is in an external file, the BEGINDATA is no longer needed. Since we want to INSERT new records and UPDATE existing ones, we will use the APPEND mode, not REPLACE as before. So, now we can load our data using:
C:\>sqlldr userid=tkyte/tkyte control=load.ctl data=new.dat errors=9999999 When we execute this, a BAD file will be generated with one record in it. The record for department 10 will be found in the new.bad file since it violated the primary key. We can verify that by reviewing the log file load.log:
Record 1: Rejected - Error on table DEPT. ORA-00001: unique constraint (TKYTE.EMP_PK) violated
We will now take that BAD file and using virtually the same control file, load it. The only changes we'll make to the control file will be the name of the table being loaded into, DEPT_WORKING instead of DEPT, and to use the REPLACE option for the working table instead of APPEND. The table we load into is created like this:
tkyte@TKYTE816> create table dept_working 2 as 3 select * from dept 4 where 1=0 5 / Table created. tkyte@TKYTE816> alter table dept_working 2 add constraint dept_working_pk 3 primary key(deptno) 4 / Table altered.
When you load the data, make sure to use the BAD=<SOME FILENAME> option on the command line to avoid reading and writing the same file in during the load!
C:\sqlldr>sqlldr userid=tkyte/tkyte control=load_working.ctl bad=working.bad data=new.bad
After we load this, we would find the one row loaded into the DEPT_WORKING table. If there are any records in WORKING.BAD, they would be really bad records, records that violated some other constraint, and must be reviewed. Now, once this is loaded, we can update the existing rows in DEPT via this single update statement:
tkyte@TKYTE816> set autotrace on explain tkyte@TKYTE816> update ( select /*+ ORDERED USE_NL(dept) */ 2 dept.dname dept_dname, 3 dept.loc dept_loc, 4 dept.last_updated dept_last_updated, 5 w.dname w_dname, 6 w.loc w_loc, 7 w.last_updated w_last_updated 8 from dept_working W, dept 9 where dept.deptno = w.deptno ) 10 set dept_dname = w_dname, 11 dept_loc = w_loc, 12 dept_last_updated = w_last_updated 13 / 1 row updated. Execution Plan ---------------------------------------------------------- 0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=67 Bytes=5226) 1 0 UPDATE OF 'DEPT' 2 1 NESTED LOOPS (Cost=83 Card=67 Bytes=5226) 3 2 TABLE ACCESS (FULL) OF 'DEPT_WORKING' (Cost=1 Card=82 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=8 5 4 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE) tkyte@TKYTE816> select deptno, dname, loc, last_updated from dept; DEPTNO DNAME LOC LAST_UPDA ------ -------------- ------------- --------- 10 SALES NEW YORK 14-APR-01 20 ACCOUNTING VIRGINIA 13-APR-01 30 CONSULTING VIRGINIA 14-APR-01 40 FINANCE VIRGINIA 14-APR-01 50 FINANCE VIRGINIA 02-APR-01 60 FINANCE VIRGINIA 14-APR-01 6 rows selected.
Since DEPT_WORKING typically won't be analyzed, we'll use hints to tell the optimizer to use DEPT_WORKING as the driving table. We want it to full scan the DEPT_WORKING table. We will be updating a row for every row in there, and then do an indexed read into DEPT to update that row (this is the NESTED LOOPS). In most cases, this should be the most efficient approach.
Frequently, you have a TEXT report that contains data you would like to load. This data is in a formatted style, but the data you need comes from all over the report. For example, I was given the task of taking a report that looked like this:
3205679761 - Detailed Report July 01, 2000 21:24 Location : location data 1 Status : status 1 July 01, 2000 22:18 Location : location data 2 Status : status 2 ... 3205679783 - Detailed Report July 01, 2000 21:24 Location : location data 3 Status : status data 3 ...
which needed to be loaded into a table that looked like this:
tkyte@TKYTE816> create table t 2 ( serial_no varchar2(20), 3 date_time varchar2(50), 4 location varchar2(100), 5 status varchar2(100) 6 ) 7 / Table created.
Now, it is possible that by using some obtuse triggers, a PL/SQL package to maintain state, and some SQLLDR tricks, we could load this report directly. In fact, the Oracle Server Utilities Guide has an example of doing just such a thing. However, I find this approach, to be incredibly complex and obscure, not to mention that you would need to put triggers on a table in support of a load. What if the tables were used for other things? The triggers should not fire for them, but there is no way to create a trigger that only fires for SQLLDR. So, I set about to find an easier way.
Frequently, when the data is as complex as this, it is faster, cheaper, and easier to load the data into a 'scratch' table, and then use a small stored procedure to manipulate it. This is exactly what I did with the above report. I used a control file:
LOAD DATA INTO TABLE TEMP REPLACE ( seqno RECNUM, text Position(1:1024))
to load the data into a table that was created via:
tkyte@TKYTE816> create table temp 2 ( seqno int primary key, 3 text varchar2(4000) ) 4 organization index 5 overflow tablespace data; Table created.
The RECNUM clause in the control file directs SQLLDR to supply the current record number for that column as it loads the data. This will assign the number 1 to the first record, 100 to the 100th record, and so on. Then, I used a very small stored procedure to reformat the data as I wanted it. The logic of this routine is to read each line of input from the table in order. It then looks at the line, and if the line contains:
Detailed Report, it extracts the number out of the line, placing it into the L_SERIAL_NO variable.
Location, it extracts the location data into the L_LOCATION variable.
Status, it extracts the status data into the L_STATUS variable and inserts the record. We are at the point in the report where we have collected all of the necessary fields by now. We have the serial number, the date and time (see next bullet point), and the location already.
With anything else, we see if the field can be converted into a date. If it cannot, we skip this line of input totally. The exception block below does this for us.
Here is the routine:
tkyte@TKYTE816> create or replace procedure reformat 2 as 3 l_serial_no t.serial_no%type; 4 l_date_time t.date_time%type; 5 l_location t.location%type; 6 l_status t.status%type; 7 l_temp_date date; 8 begin 9 for x in ( select * from temp order by seqno ) 10 loop 11 if ( x.text like '%Detailed Report%' ) then 12 l_serial_no := substr( x.text, 1, instr(x.text,'-')-1 ); 13 elsif ( x.text like '%Location : %' ) then 14 l_location := substr( x.text, instr(x.text,':')+2 ); 15 elsif ( x.text like '%Status %:%' ) then 16 l_status := substr( x.text, instr(x.text,':')+2 ); 17 insert into t ( serial_no, date_time, location, status ) 18 values ( l_serial_no, l_date_time, l_location, l_status ); 19 else 20 begin 21 l_temp_date := to_date( ltrim(rtrim(x.text)), 22 'Month dd, yyyy hh24:mi'); 23 l_date_time := x.text; 24 exception 25 when others then null; 26 end; 27 end if; 28 end loop; 29 end; 30 / Procedure created.
When you compare this amount of work, to the amount of effort it would have taken to develop the extremely complex triggers to maintain a state between inserts, and to coordinate those triggers with other applications that access the table, there is no comparison. Loading directly into the table itself might have saved a couple of I/Os on the system, but the code to do so would have been unreliable, due to its complexity and level of trickery.
The moral of this example is that if you have to solve a complex task, try to minimize the complexity wherever possible. One way to do it is by using the appropriate tools. In this case, we used PL/SQL to write some procedural logic to help us re-format the data once it was loaded. It was the easiest way to do this. Trying to make SQLLDR do everything is not always the right approach. In other cases, we find that SQLLDR is a better tool than PL/SQL. Use the appropriate tool for the job.
Even though the LONG RAW type is a deprecated type in Oracle8i, we will still find it from time to time in legacy applications, and still have to deal with it. Occasionally you might need to load a file, or files, into a long RAW and rather than write a custom program to do it, you would like to use SQLLDR. The good news is you can, but the bad news is that it is not very easy, and is not very adept at loading large amounts of files.
In order to load a long RAW using SQLLDR, we will in general need to have a control file per file (per row) to be loaded (unless the files are all the same size). There is a trick involved in getting SQLLDR to do this for us. We have to work with a series of 64KB or less buffers, and find some number of fixed sized records to concatenate together, to load with. For example, let's say you wanted to load a file that was 1075200 bytes long. The control file could look like this:
| Note | The numbers in parentheses and bold on the right, are not actually part of the file. They are for our reference: options(bindsize=1075700, rows=1) (1) Load Data (2) Infile mydata.dat "fix 53760" (3) concatenate 20 (4) Preserve Blanks (5) Into Table foo (6) Append (7) (id constant 1,bigdata raw(1075200)) (8) |
The trick here is that 53760 * 20 = 1075200 and 53760 is the largest number that is a factor of 1075200, less than 64k. We needed to find the largest integer less than 64KB to use as a 'fix' size, and then concatenate 20 of them together to reconstruct our physical record.
So, on line (3) above, we used the 53760 number to specify a fixed size input record. This disables the normal SQLLDR interpretation of a line feed as an end of line. SQLLDR now considers 53760 bytes to be a line C regardless of the data in it. Line (4) tells SQLLDR that a logical record (what gets loaded) will be 20 of these physical records concatenated together. We used bindsize=1075700 on line (1) in order to set up a bind buffer that is big enough to accommodate our input file, plus some extra (for the other column). Lastly, on line (8) we specify how big to make the raw column buffer for that column (default would be 255 bytes).
This control file will load the file MYDATA.DAT into the table FOO assigning the column ID the constant value of 1, and the BIGDATA column the contents of the file itself. Since this is such a tricky thing to do (find the largest integer less than 64KB and so on), I've set up a small portable C program, which I use to do this for me. I find I frequently need to load a LONG or LONG RAW column with the contents of a file, and at the same time, need to fill in one other column, some primary key column. So, I use this C program to write a control file just like the above. Its usage is the following:
genctl filename tablename lr_column_name pk_column_name pk_value RAW|CHAR and I ran it like this to generate the above control file:
genctl mydata.dat foo bigdata id 1 RAW > test.ctl The program looked at MYDATA.DAT, got its size, did the math, and generated the control file for me. You can find the source code for GENCTL on the Wrox web site at http://www.apress.com/
This is something that has been problematic for SQLLDR historically C how to load free form data that may include a newline in it. The newline character is the default 'end of line' character to SQLLDR, and the ways around this did not offer much flexibility in the past. Fortunately, in Oracle 81.6 and later versions we have some new options.
The options for loading data with embedded newlines are now as follows:
Load the data with some other character in the data that represents a newline (for example, put the string \n in the text where a newline should appear) and use a SQL function to replace that text with a CHR(10) during load time.
Use the FIX attribute on the INFILE directive, and load a fixed length flat file.
Use the VAR attribute on the INFILE directive, and load a varying width file that uses a format such that the first few bytes of each line is the length of the line to follow.
Use the STR attribute on the INFILE directive to load a varying width file with some sequence of characters that represent the end of line, as opposed to just the newline character representing this.
We will demonstrate each in turn.
This is an easy method if you have control over how the input data is produced. If it is easy enough to convert the data when creating the data file, this will work fine. The idea is to apply a SQL function to the data on the way into the database, replacing some string of characters with a newline. Lets add another column to our DEPT table:
tkyte@TKYTE816> alter table dept add comments varchar2(4000); Table altered.
We'll use this column to load text into. An example control file with inline data could be:
LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS "replace(:comments,'\n',chr(10))" ) BEGINDATA 10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia 30,Consulting,Virginia,14/04/2001,This is the Consulting\nOffice in Virginia 40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia
Now, there is something important to point out here. The above control file will only work on DOS-based platforms, such as Windows NT. On a UNIX platform, we must use:
COMMENTS "replace(:comments,'\\n',chr(10))" Notice how in the call to replace we had to use \\n not just \n. This is because \n is recognized by SQLLDR as a newline, and it would have converted it into a newline, not a two character string. We used \\n to get the character string constant \n into the control file in SQLLDR on UNIX. When we execute SQLLDR with the above control file (adjusted for UNIX if you are on that platform), the table DEPT is loaded with:
tkyte@TKYTE816> select deptno, dname, comments from dept; DEPTNO DNAME COMMENTS ------ -------------- ---------------------------------------- 10 SALES This is the Sales Office in Virginia 20 ACCOUNTING This is the Accounting Office in Virginia 30 CONSULTING This is the Consulting Office in Virginia 40 FINANCE This is the Finance Office in Virginia
The FIX attribute is another method available to us. If you use this, the input data must appear in fixed length records. Each record will be exactly the same number of bytes as any other record in the input data set. When using positional data, this is especially valid. These files are typically fixed length input files to begin with. When using 'free form' delimited data, it is less likely that you will have a fixed length file as these files are generally of varying length (this is the entire point of delimited files C to make each line only as big as it needs to be).
When using the FIX attribute, we must use an INFILE clause, as this is an option to INFILE. Additionally, the data must be stored externally, not in the control file itself using this option. So, assuming we have fixed length input records, we can use a control file such as this:
LOAD DATA INFILE demo17.dat "fix 101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date(:last_updated)", COMMENTS )
This specifies an input data file that will have records that are 101 bytes each. This includes the trailing newline that may or may not be there. In this case, the newline is nothing special in the input data file. It is just another character to be loaded or not. This is the thing to understand C the newline at the end of the record (if present) will become part of the record. In order to fully understand this, we need a utility to dump the contents of a file on screen so we can see what is really in there. To do this in a totally portable fashion so that we have something that works on all platforms, we'll use the database. We can write a routine that uses a BFILE to read a file from the operating system, and dump it character by character on screen, showing us where carriage returns (ASCII 13), line feeds (ASCII 10), tabs (ASCII 9), and other special characters are in the file. Using PL/SQL it might look like this:
tkyte@TKYTE816> create or replace 2 procedure file_dump( p_directory in varchar2, 3 p_filename in varchar2 ) 4 as 5 type array is table of varchar2(5) index by binary_integer; 6 7 l_chars array; 8 l_bfile bfile; 9 l_buffsize number default 15; 10 l_data varchar2(30); 11 l_len number; 12 l_offset number default 1; 13 l_char char(1); 14 begin 15 -- special cases, print out "escapes" for readability 16 l_chars(0) := '\0'; 17 l_chars(13) := '\r'; 18 l_chars(10) := '\n'; 19 l_chars(9) := '\t'; 20 21 l_bfile := bfilename( p_directory, p_filename ); 22 dbms_lob.fileopen( l_bfile ); 23 24 l_len := dbms_lob.getlength( l_bfile ); 25 while( l_offset < l_len ) 26 loop 27 -- first show the BYTE offsets into the file 28 dbms_output.put( to_char(l_offset,'fm000000') || '-'|| 29 to_char(l_offset+l_buffsize-1,'fm000000') ); 30 31 -- now get BUFFSIZE bytes from the file to dump 32 l_data := utl_raw.cast_to_varchar2 33 (dbms_lob.substr( l_bfile, l_buffsize, l_offset )); 34 35 -- for each character 36 for i in 1 .. length(l_data) 37 loop 38 l_char := substr(l_data,i,1); 39 40 -- if the character is printable, just print it 41 if ascii( l_char ) between 32 and 126 42 then 43 dbms_output.put( lpad(l_char,3) ); 44 -- if it is one of the SPECIAL characters above, print 45 -- it in using the text provided 46 elsif ( l_chars.exists( ascii(l_char) ) ) 47 then 48 dbms_output.put( lpad( l_chars(ascii(l_char)), 3 ) ); 49 -- else it is just binary data, display it in HEX 50 else 51 dbms_output.put( to_char(ascii(l_char),'0X') ); 52 end if; 53 end loop; 54 dbms_output.new_line; 55 56 l_offset := l_offset + l_buffsize; 57 end loop; 58 dbms_lob.close( l_bfile ); 59 end; 60 / Procedure created.
| Note | For more information on DBMS_LOB and BFILES see the Appendix A at the back of this book. |
So, if you take a data file such as:
tkyte@TKYTE816> host type demo17.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 40,Finance,Virginia,987268297,This is the Finance Office in Virginia tkyte@TKYTE816> exec file_dump( 'MY_FILES', 'demo17.dat' ); 000001-000015 1 0 , S a l e s , V i r g i n 000016-000030 i a , 0 1 - a p r i l - 2 0 0 000031-000045 1 , T h i s i s t h e S 000046-000060 a l e s \r \n O f f i c e i n 000061-000075 V i r g i n i a 000076-000090 000091-000105 \r \n 2 0 , A 000106-000120 c c o u n t i n g , V i r g i 000121-000135 n i a , 1 3 / 0 4 / 2 0 0 1 , 000136-000150 T h i s i s t h e A c c 000151-000165 o u n t i n g \r \n O f f i c e 000166-000180 i n V i r g i n i a 000181-000195 000196-000210 \r \n 3 0 , C o n s u 000211-000225 l t i n g , V i r g i n i a , 000226-000240 1 4 / 0 4 / 2 0 0 1 1 2 : 0 000241-000255 2 : 0 2 , T h i s i s t h 000256-000270 e C o n s u l t i n g \r \n O 000271-000285 f f i c e i n V i r g i n 000286-000300 i a 000301-000315 \r \n 4 0 , F i n a n c e , V 000316-000330 i r g i n i a , 9 8 7 2 6 8 2 000331-000345 9 7 , T h i s i s t h e 000346-000360 F i n a n c e \r \n O f f i c e 000361-000375 i n V i r g i n i a 000376-000390 000391-000405 \r \n PL/SQL procedure successfully completed.
So, using this utility we can see that every record is 101 bytes long. If you look at the line of data that starts with 000091-000105 we can see the end of line (\r\n). Since we know the last character on this display line is at byte 105 in the file, we can count backwards to see that the \n is at byte 101. Looking forwards to the line that begins with 000196-000210, we see at offset 202 in the file, another end of line representing the end of that record.
Now that we know each and every record is 101 bytes long, we are ready to load it using the control file we listed above with the FIX 101 clause. When we do so, we can see:
tkyte@TKYTE816> select '"' || comments || '"' comments from dept; COMMENTS ------------------------------ "This is the Sales Office in Virginia " "This is the Accounting Office in Virginia " "This is the Consulting Office in Virginia " "This is the Finance Office in Virginia "
Notice how each row loaded ends with a newline, as evidenced by that the fact the ending quote we added is at the beginning of a line (the last character of the COMMENTS field is a newline, and causes the quote to be on a new line). This is because my input data always had a newline in the 101st byte, and SQLLDR did not count this as a record separator of any sort. If this is not desired, you should enclose your data in quotes, and use the OPTIONALLY ENCLOSED BY clause to have SQLLDR load just the enclosed data, minus the newline (and don't forget to account for that additional space on each line in the FIX attribute). In this fashion, the trailing newline will not be loaded as part of the input data. So, if you change the control file to:
LOAD DATA INFILE demo18.dat "fix 101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS )
and change the data file to:
C:\sqlldr>TYPE demo18.dat 10,Sales,Virginia,01-april-2001,"This is the Sales Office in Virginia" 20,Accounting,Virginia,13/04/2001,"This is the Accounting Office in Virginia" 30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting Office in Virginia" 40,Finance,Virginia,987268297,"This is the Finance Office in Virginia"
with quotes around the text, it will get loaded like this instead:
tkyte@TKYTE816> select '"' || comments || '"' comments from dept; COMMENTS ------------------------------ "This is the Sales Office in Virginia" "This is the Accounting Office in Virginia" "This is the Consulting Office in Virginia" "This is the Finance Office in Virginia"
A word of caution for those of you lucky enough to work on both Windows NT and UNIX. The end of line marker is different on these platforms. On UNIX it is simply \n. On Windows NT is it \r\n. Let's say we took the above example and simply ftp'ed (file transferred) the files to a UNIX machine. Doing the same FILE_DUMP as above will show us the problem you will encounter:
ops$tkyte@ORA8I.WORLD> EXEC file_dump( 'MY_FILES', 'demo17.dat' ); 000001-000015 1 0 , S a l e s , V i r g i n 000016-000030 i a , 0 1 - a p r i l - 2 0 0 000031-000045 1 , T h i s i s t h e S 000046-000060 a l e s \n O f f i c e i n 000061-000075 V i r g i n i a 000076-000090 000091-000105 \n 2 0 , A c c 000106-000120 o u n t i n g , V i r g i n i 000121-000135 a , 1 3 / 0 4 / 2 0 0 1 , T h 000136-000150 i s i s t h e A c c o u 000151-000165 n t i n g \n O f f i c e i n 000166-000180 V i r g i n i a 000181-000195 000196-000210 \n 3 0 , C o n s u l t i n 000211-000225 g , V i r g i n i a , 1 4 / 0 000226-000240 4 / 2 0 0 1 1 2 : 0 2 : 0 2 000241-000255 , T h i s i s t h e C o 000256-000270 n s u l t i n g \n O f f i c e 000271-000285 i n V i r g i n i a 000286-000300 \n 4 0 , 000301-000315 F i n a n c e , V i r g i n i 000316-000330 a , 9 8 7 2 6 8 2 9 7 , T h i 000331-000345 s i s t h e F i n a n c 000346-000360 e \n O f f i c e i n V i r 000361-000375 g i n i a 000376-000390 000391-000405 \n PL/SQL procedure successfully completed.
This file is a totally different size, as are each of the records. Each \r\n pair is now just a \n sequence. In this particular example, we can simply adjust the FIX from 101 to 99, but this is only because I had the same exact number of newlines in each record! Each record's length was reduced by 2 bytes. If some of the records had three newlines, their length would have been reduced by three bytes not two. This would change the file in such a way that the records were no longer fixed length. In general, if you use the FIX approach, make sure to create and load the file on a homogenous platform (UNIX and UNIX, or Windows and Windows). Transferring the files from one system to the other will almost certainly render them unloadable.
Another method of loading data with embedded newline characters is to use the VAR attribute. When using this format, each record will begin with some fixed number of bytes that represent the total length of the incoming record. Using this format, I can load varying length records that contain embedded newlines, but only if I have a record length field at the beginning of each and every record. So, if I use a control file such as:
LOAD DATA INFILE demo19.dat "var 3" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date(:last_updated)", COMMENTS )
then the vear 3 says that the first three bytes of each input record will be the length of that input record. If I take a data file such as:
C:\sqlldr>type demo19.dat 07110,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 07820,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 07140,Finance,Virginia,987268297,This is the Finance Office in Virginia
I can load it using that control file. In my input data file, I have four rows of data. The first row starts with 071, meaning that the next 71 bytes represent the first input record. This 71 bytes includes the terminated newline after the word Virginia. The next row starts with 078. It has 78 bytes of text, and so on. Using this format data file, we can easily load our data with embedded newlines.
Again, if you are using UNIX and NT (the above example was NT where a newline is two characters long), you would have to adjust the length field for each record. On UNIX the above .DAT file would have to have 69, 76, 85, and 69 as the length fields in this particular example.
This is perhaps the most flexible method of loading data with embedded newlines. Using the STR attribute, I can specify a new end of line character (or sequence of characters). This allows you to create an input data file that has some special character at the end of each line C the newline is no longer 'special'.
I prefer to use a sequence of characters, typically some special 'marker', and then a newline. This makes it easy to see the end of line character when viewing the input data in a text editor or some utility, as each record still has a newline at the end of it. The STR attribute is specified in hexadecimal, and perhaps the easiest way to get the exact hexadecimal string you need is to use SQL and UTL_RAW (see the Necessary Supplied Packages appendix at the end of this book for details on UTL_RAW) to produce the hexadecimal string for us. For example, assuming you are on Windows NT where the end of line marker is CHR(13) || CHR(10) (carriage return/line feed) and your special marker character is a pipe |, we can write this:
tkyte@TKYTE816> select utl_raw.cast_to_raw( '|'||chr(13)||chr(10) ) from dual; UTL_RAW.CAST_TO_RAW('|'||CHR(13)||CHR(10)) -------------------------------------------------------- 7C0D0A
which shows us that the STR we need to use is X'7C0D0A'. To use this, we might have a control file like this:
LOAD DATA INFILE demo20.dat "str X'7C0D0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS )
So, if your input data looks like this:
C:\sqlldr>type demo20.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia| 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia| 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia| 40,Finance,Virginia,987268297,This is the Finance Office in Virginia|
where each record in the data file ended with a |\r\n, the above control file will load it correctly.
So, we have explored at least four ways to load data with embedded newlines. In the very next section, we will use one of these, the STR attribute, in a generic unload utility to avoid issues with regards to newlines in text.
Additionally, one thing to be very aware of, and I've mentioned it above a couple of times, is that on Windows (all flavors), text files may end in \r\n (ASCII 13 + ASCII 10, carriage return/line feed). Your control file will have to accommodate this C that \r is part of the record. The byte counts in the FIX and VAR, and the string used with STR must accommodate this. For example, if you took any of the above .DAT files that currently contain just \n in them and ftp'ed them to Windows NT 4.0 using an ASCII transfer (the default), every \n would turn into \r\n. The same control file that just worked in UNIX would not be able to load the data anymore. This is something you must be aware of, and take into consideration when setting up the control file.
One thing SQLLDR does not do, and that Oracle supplies no tools for, is the unloading of data in a format understandable by SQLLDR. This would be useful for moving data from system to system without using EXP/IMP. Using EXP/IMP to move data from system to system works fine for moderate amounts of data. Since IMP does not support a direct path import, and would not build indexes in parallel, moving the data via SQLLDR, and then creating the indexes using a parallel, unrecoverable, index build may be many orders of magnitude faster.
We will develop a small PL/SQL utility that may be used to unload data on a server in a SQLLDR-friendly format. Also, equivalent tools for doing so in Pro*C and SQL*PLUS are provided on the Wrox web site. The PL/SQL utility will work fine in most cases, but better performance will be had using Pro*C, and if you need the files to be generated on the client (not the server which is where PL/SQL will generate and so on The specification of the package we will create is:
tkyte@TKYTE816> create or replace package unloader 2 as 3 function run( p_query in varchar2, 4 p_tname in varchar2, 5 p_mode in varchar2 default 'REPLACE', 6 p_dir in varchar2, 7 p_filename in varchar2, 8 p_separator in varchar2 default ',', 9 p_enclosure in varchar2 default '"', 10 p_terminator in varchar2 default '|' ) 11 return number; 12 end; 13 / Package created.
with the following meanings:
/* Function run - Unloads data from any query into a file, and creates a control file to reload this data into another table p_query = SQL query to 'unload'. May be virtually any query. p_tname = Table to load into. Will be put into control file. p_mode = REPLACE|APPEND|TRUNCATE - how to reload the data p_dir = Directory we will write the .ctl and .dat file to. p_filename = Name of file to write to. I will add .ctl and .dat to this name p_separator = Field delimiter. I default this to a comma. p_enclosure = What each field will be wrapped in. p_terminator = End of line character. We use this so we can unload and reload data with newlines in it. I default to '|\n' (a pipe and a newline together), '|\r\n' on NT. You need only to override this if you believe your data will have this sequence in it. I ALWAYS add the OS 'end of line' marker to this sequence, you should not. */
The package body follows. We use UTL_FILE to write a control file and a data file. Make sure to refer to UTL_FILE in the Appendix A on Necessary Supplied Packages at the back of this book for details on setting up UTL_FILE. Without the proper init.ora parameter settings, UTL_FILE will not work. DBMS_SQL is used to dynamically process any query (see the section in the appendix on DBMS_SQL for details on this package). We utilize one data type in our queries C a VARCHAR2(4000). This implies we cannot use this method to unload LOBS, and that is true if the LOB is greater than 4000 bytes. We can however, use this to unload up to 4000 bytes of any LOB using DBMS_LOB.SUBSTR. Additionally, since we are using a VARCHAR2 as the only output data type, we can handle RAWS up to 2000 bytes in length (4000 hexadecimal characters), which is sufficient for everything except LONG RAWS, and LOBS. The solution below is a 90 percent solution C it solves the problem 90 percent of the time. With a little more work, and by using other tools described in this book, for example, the LOB_IO package described in Chapter 18 on C-Based External Procedures, we could expand this easily to handle all data types, including LOBS.
tkyte@TKYTE816> create or replace package body unloader 2 as 3 4 5 g_theCursor integer default dbms_sql.open_cursor; 6 g_descTbl dbms_sql.desc_tab; 7 g_nl varchar2(2) default chr(10); 8
These are some global variables we use in this package. The global cursor is opened once, the first time we reference this package, and will stay open until we log out. This avoids the overhead of getting a new cursor every time you call this package. The G_DESCTBL is a PL/SQL table that will hold the output of a DBMS_SQL.DESCRIBE call. G_NL is a newline character. We use this in strings that need to have newlines embedded in them. We do not need to adjust this for Windows C UTL_FILE will see the CHR(10) in the string of characters, and automatically turn that into a carriage return/line feed for us.
Next we have a small convenience function used to convert a character to hexadecimal. It uses the built-in functions to do this:
9 10 function to_hex( p_str in varchar2 ) return varchar2 11 is 12 begin 13 return to_char( ascii(p_str), 'fm0x' ); 14 end; 15
The following is a procedure to create a control file to reload the unloaded data. It uses the DESCRIBE table generated by dbms_sql.describe_columns to do this. It takes care of the OS specifics for us, such as whether the OS uses a carriage return/line feed (used for the STR attribute), and whether the directory separator is \ or /. It does this by looking at the directory name passed into it. If the directory contains a \, we are on Windows, else we are on UNIX:
16 /* 17 */ 18 19 procedure dump_ctl( p_dir in varchar2, 20 p_filename in varchar2, 21 p_tname in varchar2, 22 p_mode in varchar2, 23 p_separator in varchar2, 24 p_enclosure in varchar2, 25 p_terminator in varchar2 ) 26 is 27 l_output utl_file.file_type; 28 l_sep varchar2(5); 29 l_str varchar2(5); 30 l_path varchar2(5); 31 begin 32 if ( p_dir like '%\%' ) 33 then 34 -- Windows platforms -- 35 l_str := chr(13) || chr(10); 36 if ( p_dir not like '%\' AND p_filename not like '\%' ) 37 then 38 l_path := '\'; 39 end if; 40 else 41 l_str := chr(10); 42 if ( p_dir not like '%/' AND p_filename not like '/%' ) 43 then 44 l_path := '/'; 45 end if; 46 end if; 47 48 l_output := utl_file.fopen( p_dir, p_filename || '.ctl', 'w' ); 49 50 utl_file.put_line( l_output, 'load data' ); 51 utl_file.put_line( l_output, 'infile ''' || p_dir || l_path || 52 p_filename || '.dat'' "str x''' || 53 utl_raw.cast_to_raw( p_terminator || 54 l_str ) || '''"' ); 55 utl_file.put_line( l_output, 'into table ' || p_tname ); 56 utl_file.put_line( l_output, p_mode ); 57 utl_file.put_line( l_output, 'fields terminated by X''' || 58 to_hex(p_separator) || 59 ''' enclosed by X''' || 60 to_hex(p_enclosure) || ''' ' ); 61 utl_file.put_line( l_output, '(' ); 62 63 for i in 1 .. g_descTbl.count 64 loop 65 if ( g_descTbl(i).col_type = 12 ) 66 then 67 utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 68 ' date ''ddmmyyyyhh24miss'' '); 69 else 70 utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 71 ' char(' || 72 to_char(g_descTbl(i).col_max_len*2) ||' )' ); 73 end if; 74 l_sep := ','||g_nl ; 75 end loop; 76 utl_file.put_line( l_output, g_nl || ')' ); 77 utl_file.fclose( l_output ); 78 end;
Here is a simple function to return a quoted string using the chosen enclosure character. Notice how it not only encloses the character, but also doubles them up if they exist in the string as well, so that they are preserved:
79 80 function quote(p_str in varchar2, p_enclosure in varchar2) 81 return varchar2 82 is 83 begin 84 return p_enclosure || 85 replace( p_str, p_enclosure, p_enclosure||p_enclosure ) || 86 p_enclosure; 87 end;
Next we have the main function, RUN. As it is fairly large, we'll comment on it as we go along:
88 89 function run( p_query in varchar2, 90 p_tname in varchar2, 91 p_mode in varchar2 default 'REPLACE', 92 p_dir in varchar2, 93 p_filename in varchar2, 94 p_separator in varchar2 default ',', 95 p_enclosure in varchar2 default '"', 96 p_terminator in varchar2 default '|' ) return number 97 is 98 l_output utl_file.file_type; 99 l_columnValue varchar2(4000); 100 l_colCnt number default 0; 101 l_separator varchar2(10) default ''; 102 l_cnt number default 0; 103 l_line long; 104 l_datefmt varchar2(255); 105 l_descTbl dbms_sql.desc_tab; 106 begin
We will save the NLS DATE format into a variable so we can change it to a format that preserves the date and time when dumping the data to disk. In this fashion, we will preserve the time component of a date. We then set up an exception block so that we can reset the NLS_DATE_FORMAT upon any error:
107 select value 108 into l_datefmt 109 from nls_session_parameters 110 where parameter = 'NLS_DATE_FORMAT'; 111 112 /* 113 Set the date format to a big numeric string. Avoids 114 all NLS issues and saves both the time and date. 115 */ 116 execute immediate 117 'alter session set nls_date_format=''ddmmyyyyhh24miss'' '; 118 119 /* 120 Set up an exception block so that in the event of any 121 error, we can at least reset the date format. 122 */ 123 begin
Next we will parse and describe the query. The setting of G_DESCTBL to L_DESCTBL is done to 'reset' the global table, otherwise it might contain data from a previous DESCRIBE, in addition to data for the current query. Once we have done that, we call DUMP_CTL to actually create the control file:
124 /* 125 Parse and describe the query. We reset the 126 descTbl to an empty table so .count on it 127 will be reliable. 128 */ 129 dbms_sql.parse( g_theCursor, p_query, dbms_sql.native ); 130 g_descTbl := l_descTbl; 131 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl ); 132 133 /* 134 Create a control file to reload this data 135 into the desired table. 136 */ 137 dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator, 138 p_enclosure, p_terminator ); 139 140 /* 141 Bind every single column to a varchar2(4000). We don't care 142 if we are fetching a number or a date or whatever. 143 Everything can be a string. 144 */
Now we are ready to dump the actual data out to disk. We begin by defining every column to be a VARCHAR2(4000) for fetching into. All NUMBERs, DATEs, RAWs C every type will be converted into VARCHAR2, immediately after this we execute the query to prepare for the fetching phase:
145 for i in 1 .. l_colCnt loop 146 dbms_sql.define_column( g_theCursor, i, l_columnValue, 4000; 147 end loop; 148 149 /* 150 Run the query - ignore the output of execute. It is only 151 valid when the DML is an insert/update or delete. 152 */
Now we open the data file for writing, fetch all of the rows from the query, and print it out to the data file:
153 l_cnt := dbms_sql.execute(g_theCursor); 154 155 /* 156 Open the file to write output to and then write the 157 delimited data to it. 158 */ 159 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w', 160 32760 ); 161 loop 162 exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 ); 163 l_separator := ''; 164 l_line := null; 165 for i in 1 .. l_colCnt loop 166 dbms_sql.column_value( g_theCursor, i, 167 l_columnValue ); 168 l_line := l_line || l_separator || 169 quote( l_columnValue, p_enclosure ); 170 l_separator := p_separator; 171 end loop; 172 l_line := l_line || p_terminator; 173 utl_file.put_line( l_output, l_line ); 174 l_cnt := l_cnt+1; 175 end loop; 176 utl_file.fclose( l_output );
Lastly, we set the date format back (and the exception block will do the same if any of the above code fails for any reason) to what it was and return:
177 178 /* 179 Now reset the date format and return the number of rows 180 written to the output file. 181 */ 182 execute immediate 183 'alter session set nls_date_format=''' || l_datefmt || ''''; 184 return l_cnt; 185 -- exception 186 /* 187 In the event of ANY error, reset the data format and 188 re-raise the error. 189 */ 190 -- when others then 191 -- execute immediate 192 -- 'alter session set nls_date_format=''' || l_datefmt || ''''; 193 -- RAISE; 194 end; 195 end run; 196 197 198 end unloader; 199 / Package body created.
Now, to run this, we can simply use:
| Note | The following will of course, require that you have SELECT on SCOTT.EMP granted to your role. |
tkyte@TKYTE816> drop table emp; Table dropped. tkyte@TKYTE816> create table emp as select * from scott.emp; Table created. tkyte@TKYTE816> alter table emp add resume raw(2000); Table altered. tkyte@TKYTE816> update emp 2 set resume = rpad( '02', 4000, '02' ); 14 rows updated. tkyte@TKYTE816> update emp 2 set ename = substr( ename, 1, 2 ) || '"' || 3 chr(10) || '"' || substr(ename,3); 14 rows updated. tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> declare 2 l_rows number; 3 begin 4 l_rows := unloader.run 5 ( p_query => 'select * from emp order by empno', 6 p_tname => 'emp', 7 p_mode => 'replace', 8 p_dir => 'c:\temp', 9 p_filename => 'emp', 10 p_separator => ',', 11 p_enclosure => '"', 12 p_terminator => '~' ); 13 14 dbms_output.put_line( to_char(l_rows) || 15 ' rows extracted to ascii file' ); 16 end; 17 / 14 rows extracted to ascii file PL/SQL procedure successfully completed.
The control file that was generated by this shows:
| Note | The numbers in parentheses and bold on the right are not actually in the file. They are for our reference below: |
load data (1) infile 'c:\temp\emp.dat' "str x'7E0D0A'" (2) into table emp (3) replace (4) fields terminated by X'2c' enclosed by X'22' (5) ( (6) EMPNO char(44 ), (7) ENAME char(20 ), (8) JOB char(18 ), (9) MGR char(44 ), (10) HIREDATE date 'ddmmyyyyhh24miss' , (11) SAL char(44 ), (12) COMM char(44 ), (13) DEPTNO char(44 ), (14) RESUME char(4000 ) (15) ) (16)
The things to note about this control file are:
Line (2) C We use the new feature in Oracle 8.1.6, of STR. We can specify what character or string is used to terminate a record. This allows us to load data with embedded newlines easily. The above string of x'7E0D0A' is simply a tilde followed by a newline.
Line (5) C We use our separator character and enclosure character. I do not use OPTIONALLY ENCLOSED BY, since I will be enclosing every single field, after doubling any occurrence of the enclosure character in the raw data.
Line (11) C We use a large 'numeric' date format. This does two things. Firstly, it avoids any NLS issues with regards to the data, and secondly, it preserves the time component of the date field.
Line (15) C We use char(4000). By default, SQLLDR will use a char(255) for fields. I doubled up the length of each and every field in the control file. This ensures SQLLDR will be able to read the data without truncating any fields. I chose to double the length in order to support RAW types, we will extract in VARCHAR2 format (in hexadecimal). That is, twice as long as the original source data.
The RAW data (the .dat) file generated from the above code will look like this:
"7369","SM"" ""ITH","CLERK","7902","17121980000000","800","","20","02020202020202020202020202020 2020202020202020 <many occurrences removed> ...0202020202020202"~ "7499","AL"" ""LEN","SALESMAN","7698","20021981000000","1600","300","30","0202020202020202020202 0202020202020202 <many occurrences removed> ...0202020202020202"~
Things to note in the .dat file are:
Each field is enclosed in our enclosure character.
The ENAME field, into which we placed quotes and a \n character via an update, has a newline in it. Also, the quotation marks are doubled up to preserve them in the raw data.
The DATES are unloaded as large numbers.
The RAW field we added is preserved, and is dumped in hexadecimal format.
Each line of data in this file ends with a~as requested.
We can now reload this data easily using SQLLDR. You may add options to the SQLLDR command line as you see fit.
You can use this functionality to do things that are otherwise hard, or impossible to do, via other methods. For example, if you wanted to rename the ENAME column of the EMP table to EMP_NAME, you could do the following. What I do here is unload the data for SQLLDR to reload. Notice how we 'rename' the column on the way out using the EMP_NAME alias in the SELECT list after ENAME. This will cause the control file to be created with a column name of EMP_NAME instead of ENAME. I truncate the table, drop the inappropriately named column, and add the new name. We then reload the table. I might choose this approach over a more simple 'add column, update new column to old column, and drop old column' if I wanted to reorganize the table anyway, or was concerned about the amount of rollback and redo I might generate by doing such an operation. I could use SQLLDR with the direct path mode to avoid all rollback and redo generation on the subsequent reload as well. Here are the steps:
tkyte@TKYTE816> declare 2 l_rows number; 3 begin 4 l_rows := unloader.run 5 ( p_query => 'select EMPNO, ENAME EMP_NAME, 6 JOB , MGR, HIREDATE, 7 SAL, COMM, DEPTNO 8 from emp 9 order by empno', 10 p_tname => 'emp', 11 p_mode => 'TRUNCATE', 12 p_dir => 'c:\temp', 13 p_filename => 'emp', 14 p_separator => ',', 15 p_enclosure => '"', 16 p_terminator => '~' ); 17 18 dbms_output.put_line( to_char(l_rows) || 19 ' rows extracted to ascii file' ); 20 end; 21 / PL/SQL procedure successfully completed. tkyte@TKYTE816> truncate table emp; Table truncated. tkyte@TKYTE816> alter table emp drop column ename; Table altered. tkyte@TKYTE816> alter table emp add emp_name varchar2(10); Table altered. tkyte@TKYTE816> host sqlldr userid=tkyte/tkyte control=c:\temp\emp.ctl SQLLDR: Release 8.1.6.0.0 - Production on Sat Apr 14 20:40:01 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Commit point reached - logical record count 14 tkyte@TKYTE816> desc emp Name Null? Type ----------------------------------- -------- ------------------------ EMPNO NOT NULL NUMBER(4) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) RESUME RAW(2000) EMP_NAME VARCHAR2(10) tkyte@TKYTE816> select emp_name from emp; EMP_NAME ---------- SM" "ITH ... MI" "LLER 14 rows selected.
You can use this method to perform other operations as well, such as modifying a data type, de-normalize data (unload a JOIN for example), and so on.
As stated previously, the above logic of the unload package may be implemented in a variety of languages and tools. On the Apress web site, you will find this implemented not only in PL/SQL as above, but also in Pro*C and SQL*PLUS scripts. Pro*C will be the fastest implementation, and will always write to the client workstation file system. PL/SQL will be a good all around implementation (no need to compile and install on client workstations), but always writes to the server file system. SQL*PLUS will be a good middle ground, offering fair performance, and the ability to write to the client file system.
We will now consider some methods for loading into LOBS. This is not a LONG, or LONG RAW field, but rather the preferred data types of BLOB and CLOB. These data types were introduced in Oracle 8.0 and upward, and support a much richer interface/set of functionality than the legacy LONG and LONG RAW types.
We will investigate two methods for loading these fields C SQLLDR and PL/SQL. Others exist, such as Java streams, Pro*C, and OCI. In fact, if you look at Chapter 18 on C-Based External Procedures, we review how to unload a LOB using Pro*C. Loading a LOB would be very similar except that instead of using EXEC SQL READ, we would use EXEC SQL WRITE.
We will begin working with the PL/SQL method of loading LOBs, and then look at using SQLLDR to load them as well.
The DBMS_LOB package has an entry point called LOADFROMFILE. This procedure allows us to utilize a BFILE (which can read OS files) to populate a BLOB or CLOB in the database. In order to use this procedure, we will need to create a DIRECTORY object in the database. This object will allow us to create BFILES (and open them) that point to a file existing on the file system, which the database server has access to. This last point '... the database server has access to' is a key point when using PL/SQL to load LOBS. The DBMS_LOB package executes entirely in the server. It can only see file systems the server can see. It cannot, in particular, see your local file system if you are accessing Oracle over the network.
Using PL/SQL to load a LOB is not appropriate, as the data to be loaded is not on the server machine.
So we need to begin by creating a DIRECTORY object in the database. This is straightforward to do. I will create two directories for this example (examples executed in a UNIX environment this time):
ops$tkyte@DEV816> create or replace directory dir1 as '/tmp/'; Directory created. ops$tkyte@DEV816> create or replace directory "dir2" as '/tmp/'; Directory created.
The user that performs this operation needs to have the CREATE ANY DIRECTORY privilege. The reason I created two directories is to demonstrate a common case-related issue with regards to directory objects. When Oracle created the first directory DIR1 above it stored the object name in uppercase as it is the default. In the second example with DIR2, it will have created the directory object preserving the case I used in the name. Why this is important will be demonstrated below when we use the BFILE object.
Now, we want to load some data into either a BLOB or a CLOB. The method for doing so is rather easy, for example:
ops$tkyte@DEV816> create table demo 2 ( id int primary key, 3 theClob clob 4 ) 5 / Table created. ops$tkyte@DEV816> host echo 'Hello World\!' > /tmp/test.txt ops$tkyte@DEV816> declare 2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 1, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'DIR1', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile, 12 dbms_lob.getlength( l_bfile ) ); 13 14 dbms_lob.fileclose( l_bfile ); 15 end; 16 / PL/SQL procedure successfully completed. ops$tkyte@DEV816> select dbms_lob.getlength(theClob), theClob from demo 2 / DBMS_LOB.GETLENGTH(THECLOB) THECLOB --------------------------- --------------- 13 Hello World!
It might be interesting to note that if you run the example as is on Windows (changing /tmp/ to an appropriate directory for that OS of course) the output will be:
tkyte@TKYTE816> select dbms_lob.getlength(theClob), theClob from demo 2 / DBMS_LOB.GETLENGTH(THECLOB) THECLOB --------------------------- -------------------- 18 'Hello World\!'
The length is larger due to the fact that the Windows shell does not treat quotes and escapes (\) in the same fashion as UNIX, and the end of line character is longer.
Walking through the code above we see:
On lines 5, 6, 7, and 8, we created a row in our table, set the CLOB to an EMPTY_CLOB(), and retrieved its value in one call. With the exception of temporary LOBs, LOBs 'live' in the database C we cannot write to a LOB variable without having a point to either a temporary LOB, or a LOB that is already in the database. An EMPTY_CLOB() is not a Null CLOB C it is a valid non-Null pointer to an empty structure. The other thing this did for us was to get a LOB locator, which points to data in a row that is locked. If we were to have selected this value out without locking the underlying row, our attempts to write to it would fail because LOBs must be locked prior to writing (unlike other structured data). By inserting the row, we have of course locked the row. If this were coming from an existing row, we would have selected FOR UPDATE to lock it.
On line 10, we create a BFILE object. Note how we use DIR1 in uppercase C this is key, as we will see in a moment. This is because we are passing to BFILENAME the name of an object, not the object itself. Therefore, we must ensure the name matches the case Oracle has stored for this object.
On line 11, we open the LOB. This will allow us to read it.
On line 12, we load the entire contents of the operating system file /tmp/test.txt into the LOB locator we just inserted. We use DBMS_LOB.GETLENGTH to tell the LOADFROMFILE routine how many bytes of the BFILE to load (all of them).
Lastly, on line 13, we close the BFILE we opened, and the CLOB is loaded.
If we had attempted to use dir1 instead of DIR1 in the above example, we would have encountered the following error:
ops$tkyte@DEV816> declare 2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 1, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'DIR1', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile, 12 dbms_lob.getlength( l_bfile ) ); 13 14 dbms_lob.fileclose( l_bfile ); 15 end; 16 / declare * ERROR at line 1: ORA-22285: non-existent directory or file for FILEOPEN operation ORA-06512: at "SYS.DBMS_LOB", line 475 ORA-06512: at line 9
This is because the directory dir1 does not exist C DIR1 does. If you prefer to use directory names in mixed case, you would use quoted identifiers when creating them as I did above for dir2. This will allow you to write code as shown here:
ops$tkyte@DEV816> declare 2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 2, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'dir2', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile, 12 dbms_lob.getlength( l_bfile ) ); 13 14 dbms_lob.fileclose( l_bfile ); 15 end; 16 / PL/SQL procedure successfully completed.
There are methods other than LOADFROMFILE by which you can populate a LOB using PL/SQL. LOADFROMFILE is by far the easiest if you are going to load the entire file. If you need to process the contents of the file while loading it, you may also use DBMS_LOB.READ on the BFILE to read the data. The use of UTL_RAW.CAST_TO_VARCHAR2 is handy here if the data you are reading is in fact text, not RAW. See the appendix on Necessary Supplied Packages for more info on UTL_RAW. You may then use DBMS_LOB.WRITE or WRITEAPPEND to place the data into a CLOB or BLOB.
We will now investigate how to load data into a LOB via SQLLDR. There is more than one method for doing this, but we will investigate the two most common:
When the data is 'inline' with the rest of the data.
When the data is stored out of line, and the input data contains a filename to be loaded with the row. This is also known as secondary data files (SDFs) in SQLLDR terminology.
We will start with data that is inline.
These LOBS will typically have newlines and other special characters embedded in them. Therefore, you will almost always be using one of the four methods detailed above in the Load Data with Embedded Newlines section to load this data. Lets begin by modifying the DEPT table to have a CLOB instead of a big VARCHAR2 field for the COMMENTS column:
tkyte@TKYTE816> truncate table dept; Table truncated. tkyte@TKYTE816> alter table dept drop column comments; Table altered. tkyte@TKYTE816> alter table dept add comments clob; Table altered.
For example, I have a data file (demo21.dat) that has the following contents:
10, Sales,Virginia,01-april-2001,This is the Sales Office in Virginia| 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia| 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia| 40,Finance,Virginia,987268297,"This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: ""You will need to double up those quotes!"" to preserve them in the string. This field keeps going for up to 1000000 bytes or until we hit the magic end of record marker, the | followed by a end of line - it is right here ->"|
Each record ends with a pipe (|), followed by the end of line marker. If read, the text for department 40 is much longer than the rest, with many newlines, embedded quotes, and commas. Given this data file, I can create a control file such as this:
LOAD DATA INFILE demo21.dat "str X'7C0D0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS char(1000000) )
| Note | This example is from Windows where the end of line marker is two bytes, hence the STR setting in the above control file. On UNIX it would just be '7C0A'. |
To load the datafile I specified CHAR(1000000) on column COMMENTS since SQLLDR defaults to CHAR(255) for any input field. The CHAR(1000000) will allow SQLLDR to handle up to 1000000 bytes of input text. You must set this to a value which is larger than any expected chunk of text in the input file. Reviewing the loaded data we see:
tkyte@TKYTE816> select comments from dept; COMMENTS ----------------------------------------------------------------------- This is the Accounting Office in Virginia This is the Consulting Office in Virginia This is the Finance Office in Virginia, it has embedded commas and is much longer then the other comments field. If you feel the need to add double quoted text in here like this: "You will need to double up those quotes!" to preserve them in the string. This field keeps going for upto 1,000,000 bytes or until we hit the magic end of record marker, the | followed by a end of line -- it is right here -> This is the Sales Office in Virginia
The one thing to observe here is that the doubled up quotes are no longer doubled up. SQLLDR removed the extra quotes we placed there.
A common scenario is to have a data file that contains the names of files to load into the LOBs, instead of having the LOB data mixed in with the structured data. This offers a greater degree of flexibility, as the data file given to SQLLDR does not have to use one of the four methods to get around having embedded newlines in the input data, as would frequently happen with large amounts of text or binary data. SQLLDR calls this type of additional data file a LOBFILE.
SQLLDR can also support the loading of a structured data file that points to another, single data file. We can tell SQLLDR how to parse LOB data from this other file, so that each row in the structured data gets loaded with a piece of it. I find this mode to be of limited use (I've never found a use for it myself to date) and will not be discussing it here. SQLLDR refers to these externally referenced files as complex secondary data files.
LOBFILES are relatively simple data files aimed at facilitating LOB loading. The attribute that distinguishes LOBFILEs from the main data files, is that in LOBFILEs, there is no concept of a record, hence newlines never get in the way. In LOBFILEs, the data is in any of the following formats:
Fixed length fields (for example, load bytes 100 through 1000 from the LOBFILE).
Delimited fields (terminated by something or enclosed by something).
Length-value pairs, a varying length field.
The most common of these types will be the delimited fields, ones that are terminated by EOF (end of file) in fact. Typically, you have a directory full of files you would like to load into LOB columns C each file in its entirety will go into a BLOB. The LOBFILE statement with TERMINATED BY EOF is what you will use.
So, lets say you have a directory full of files you would like to load into the database. You would like to load the OWNER of the file, the TIMESTAMP of the file, the NAME of the file, and the file itself. Our table we would load into would be:
tkyte@TKYTE816> create table lob_demo 2 ( owner varchar2(255), 3 timestamp date, 4 filename varchar2(255), 5 text clob 6 ) 7 / Table created.
Using a simple ls Cl on Unix, and dir /q /n on Windows, we can generate our input file, and load it using a control file such as this on Unix:
LOAD DATA INFILE * REPLACE INTO TABLE LOB_DEMO ( owner position(16:24), timestamp position(34:45) date "Mon DD HH24:MI", filename position(47:100), text LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rw-r--r-- 1 tkyte 1785 Sep 27 12:56 demo10.log -rw-r--r-- 1 tkyte 1674 Sep 19 15:57 demo2.log -rw-r--r-- 1 tkyte 1637 Sep 17 14:43 demo3.log -rw-r--r-- 1 tkyte 2385 Sep 17 15:05 demo4.log -rw-r--r-- 1 tkyte 2048 Sep 17 15:32 demo5.log -rw-r--r-- 1 tkyte 1801 Sep 17 15:56 demo6.log -rw-r--r-- 1 tkyte 1753 Sep 17 16:03 demo7.log -rw-r--r-- 1 tkyte 1759 Sep 17 16:41 demo8.log -rw-r--r-- 1 tkyte 1694 Sep 17 16:27 demo8a.log -rw-r--r-- 1 tkyte 1640 Sep 24 16:17 demo9.log
or on Windows, it would be:
LOAD DATA INFILE * REPLACE INTO TABLE LOB_DEMO ( owner position(40:61), timestamp position(1:18) "to_date(:timestamp||'m','mm/dd/yyyy hh:miam')", filename position(63:80), text LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA 04/14/2001 12:36p 1,697 BUILTIN\Administrators demo10.log 04/14/2001 12:42p 1,785 BUILTIN\Administrators demo11.log 04/14/2001 12:47p 2,470 BUILTIN\Administrators demo12.log 04/14/2001 12:56p 2,062 BUILTIN\Administrators demo13.log 04/14/2001 12:58p 2,022 BUILTIN\Administrators demo14.log 04/14/2001 01:38p 2,091 BUILTIN\Administrators demo15.log 04/14/2001 04:29p 2,024 BUILTIN\Administrators demo16.log 04/14/2001 05:31p 2,005 BUILTIN\Administrators demo17.log 04/14/2001 05:40p 2,005 BUILTIN\Administrators demo18.log 04/14/2001 07:19p 2,003 BUILTIN\Administrators demo19.log 04/14/2001 07:29p 2,011 BUILTIN\Administrators demo20.log 04/15/2001 11:26a 2,047 BUILTIN\Administrators demo21.log 04/14/2001 11:17a 1,612 BUILTIN\Administrators demo4.log
Notice we did not load a DATE into the timestamp column, we needed to use a SQL function to massage the Windows date format into one that the database could use. Now, if we inspect the contents of the LOB_DEMO table after running SQLLDR, well discover:
tkyte@TKYTE816> select owner, timestamp, filename, dbms_lob.getlength(text) 2 from lob_demo; OWNER TIMESTAMP FILENAME DBMS_LOB.GETLENGTH(TEXT) ---------------------- --------- ---------- ------------------------ BUILTIN\Administrators 14-APR-01 demo10.log 1697 BUILTIN\Administrators 14-APR-01 demo11.log 1785 BUILTIN\Administrators 14-APR-01 demo12.log 2470 BUILTIN\Administrators 14-APR-01 demo4.log 1612 BUILTIN\Administrators 14-APR-01 demo13.log 2062 BUILTIN\Administrators 14-APR-01 demo14.log 2022 BUILTIN\Administrators 14-APR-01 demo15.log 2091 BUILTIN\Administrators 14-APR-01 demo16.log 2024 BUILTIN\Administrators 14-APR-01 demo17.log 2005 BUILTIN\Administrators 14-APR-01 demo18.log 2005 BUILTIN\Administrators 14-APR-01 demo19.log 2003 BUILTIN\Administrators 14-APR-01 demo20.log 2011 BUILTIN\Administrators 15-APR-01 demo21.log 2047 13 rows selected.
This works with BLOBs as well as LOBs. Loading a directory of images using SQLLDR in this fashion is easy.
Now that we know how to load into a simple table we have created ourselves, we might also find the need to load into a table that has a complex object type with a LOB in it. This happens most frequently when using the interMedia image capabilities or the Virage Image Cartridge (VIR) with the database. Both use a complex object type ORDSYS.ORDIMAGE as a database column. We need to be able to tell SQLLDR how to load into this. In order to load a LOB into an ORDIMAGE type column, we must understand a little more of the structure of the ORDIMAGE type. Using a table we want to load into, and a DESCRIBE on that table in SQL*PLUS, we can discover that we have a column called IMAGE of type ORDSYS.ORDIMAGE, which we want to ultimately load into IMAGE.SOURCE.LOCALDATA. The following examples will only work if you have interMedia, or the Virage Image Cartridge installed and configured, otherwise the data type ORDSYS.ORDIMAGE will be an unknown type:
ops$tkyte@ORA8I.WORLD> create table image_load( 2 id number, 3 name varchar2(255), 4 image ordsys.ordimage 5 ) 6 / Table created. ops$tkyte@ORA8I.WORLD> desc image_load Name Null? Type ------------------------------------ -------- ------------------------- ID NUMBER NAME VARCHAR2(255) IMAGE ORDSYS.ORDIMAGE ops$tkyte@ORA8I.WORLD> desc ordsys.ordimage Name Null? Type ------------------------------------ -------- ------------------------- SOURCE ORDSOURCE HEIGHT NUMBER(38) WIDTH NUMBER(38) CONTENTLENGTH NUMBER(38) ... ops$tkyte@ORA8I.WORLD> desc ordsys.ordsource Name Null? Type ------------------------------------ -------- ------------------------- LOCALDATA BLOB SRCTYPE VARCHAR2(4000) SRCLOCATION VARCHAR2(4000) ...
so, a control file to load this might look like this:
LOAD DATA INFILE * INTO TABLE image_load REPLACE FIELDS TERMINATED BY ',' ( ID, NAME, file_name FILLER, IMAGE column object ( SOURCE column object ( LOCALDATA LOBFILE (file_name) TERMINATED BY EOF NULLIF file_name = 'NONE' ) ) ) BEGINDATA 1,icons,icons.gif
In the above, I have introduced two new constructs:
COLUMN OBJECT C This tells SQLLDR that this is not a column name, but rather part of a column name. It is not mapped to a field in the input file, but is rather used to build the correct object column reference to be used during the load. In the above, we have two column object tag's one nested in the other. Therefore, the column name that will be used its IMAGE.SOURCE.LOCALDATA, as we need it to be. Note that we are not loading any of the other attributes of these two object types. For example, IMAGE.HEIGHT, IMAGE.CONTENTLENGTH, IMAGE.SOURCE.SRCTYPE. Below, we'll see how to get those populated.
NULLIF FILE_NAME = 'NONE' C This tells SQLLDR to load a Null into the object column in the event that the field FILE_NAME contains the word NONE in it.
Once you have loaded an interMedia type, you will typically need to post-process the loaded data using PL/SQL to have interMedia operate on it. For example, with the above, you would probably want to run the following to have the properties for the image set up correctly:
begin for c in ( select * from image_load ) loop c.image.setproperties; end loop; end; /
SETPROPERTIES is an object method provided by the ORDSYS.ORDIMAGE type, which processes the image itself, and updates the remaining attributes of the object with appropriate values. See Chapter 17 on interMedia for more information regarding image manipulation.
We will now look at how to load VARRAYS and nested table types with SQLLDR. Basically, VARRAYS and nested table types (just called arrays from now on) will typically be encoded into the input file as follows:
A field will be included in the data file. This field will contain the number of array elements to expect in the data file. This field will not be loaded, but will be used by SQLLDR to figure out how many array elements are coming in the file.
Followed by a (set of) field(s) representing the elements of the array.
So, we expect arrays of a varying length to be encoded in the input data file with a field that tells us how many elements are coming, and then the elements themselves. We can also load array data when there are a fixed number of array elements in each record (for example, each input record will have five array elements). We will look at both methods using the types below:
tkyte@TKYTE816> create type myArrayType 2 as varray(10) of number(12,2) 3 / Type created. tkyte@TKYTE816> create table t 2 ( x int primary key, y myArrayType ) 3 / Table created.
This is the schema that we want to load our data into. Now, here is a sample control/data file we can use to load it. This demonstrates the varying number of array elements. Each input record will be in the format:
Value of x.
Number of elements in y.
Repeating field representing the individual elements of y.
LOAD DATA INFILE * INTO TABLE t replace fields terminated by "," ( x, y_cnt FILLER, y varray count (y_cnt) ( y ) ) BEGINDATA 1,2,3,4 2,10,1,2,3,4,5,6,7,8,9,10 3,5,5,4,3,2,1
Note that in the above, we used the keyword FILLER to allow us to map a variable Y_CNT in the input file but not load this field. We also used the keywords VARRAY COUNT (Y_CNT) to tell SQLLDR that y is a VARRAY. If Y was a nested table, we would have used NESTED TABLE COUNT(Y_CNT) instead. Also note that these are SQLLDR keywords, not SQL functions, so we do not use quotes or colons for bind variables as we would with a SQL function.
The key thing to note in the input data is the inclusion of the COUNT. It tells SQLLDR how to size the array we are loading. For example, given the input line:
1,2,3,4 we will get:
1 C mapped to x, the primary key.
2 C mapped to y_cnt, the count of array elements.
3,4 C mapped to y, the array elements themselves.
After running SQLLDR, we find:
tkyte@TKYTE816> select * from t; X Y ---------- ------------------------------------------- 1 MYARRAYTYPE(3, 4) 2 MYARRAYTYPE(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 3 MYARRAYTYPE(5, 4, 3, 2, 1)
This is exactly what we wanted. Now, lets say you wanted to load an input file where there are a fixed number of array elements. For example, you have an ID, and five observations to go along with this ID. We can use a type/table combination such as:
tkyte@TKYTE816> create or replace type myTableType 2 as table of number(12,2) 3 / Type created. tkyte@TKYTE816> create table t 2 ( x int primary key, y myTableType ) 3 nested table y store as y_tab 4 / Table created.
The control file will look like this. Note the use of CONSTANT 5 in the nested table count construct. This is what tells SQLLDR how many elements to expect for each record now:
LOAD DATA INFILE * INTO TABLE t replace fields terminated by "," ( x, y nested table count (CONSTANT 5) ( y ) ) BEGINDATA 1,100,200,300,400,500 2,123,243,542,123,432 3,432,232,542,765,543
and after executing SQLLDR, we find:
tkyte@TKYTE816> select * from t; X Y ---------- ------------------------------------------- 1 MYTABLETYPE(100, 200, 300, 400, 500) 2 MYTABLETYPE(123, 243, 542, 123, 432) 3 MYTABLETYPE(432, 232, 542, 765, 543)
This shows the data loaded up into our nested table. As a side note (getting away from SQLLDR for a moment), we can observe one of the properties of a nested table. I discovered by accident, that if we reload the table using SQLLDR and this sample data, we get a slightly different end result:
tkyte@TKYTE816> host sqlldr userid=tkyte/tkyte control=demo24.ctl SQLLDR: Release 8.1.6.0.0 - Production on Sun Apr 15 12:06:56 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Commit point reached - logical record count 3 tkyte@TKYTE816> select * from t; X Y ---------- ------------------------------------------- 1 MYTABLETYPE(200, 300, 400, 500, 100) 2 MYTABLETYPE(123, 243, 542, 123, 432) 3 MYTABLETYPE(432, 232, 542, 765, 543)
Notice how the number 100 is last in the first nested table. This is just a side effect of the way space happened to be reused in the table during the second load. It may (or may not) reproduce on all systems with all block sizes, it just happened that for me, it did. Nested tables do not preserve order, so do not be surprised when the nested table data comes out in an order different from the way you loaded it!
The short answer is that you cannot do this. SQLLDR is not an API, it is not something that is callable. SQLLDR is a command line program. You can definitely write an external procedure in Java or C that runs SQLLDR (see Chapter 19 on Java Stored Procedures for running a command at the OS level), but that won't be the same as 'calling' SQLLDR. The load will happen in another session, and will not be subject to your transaction control. Additionally, you will have to parse the resulting log file to determine if the load was successful or not, and how successful (how many rows got loaded before an error terminated the load) it may have been. Invoking SQLLDR from a stored procedure is not something I would recommend.
So, lets say you have a requirement to load data via a stored procedure. What are your options? The ones that come to mind are:
Write a mini-SQLLDR in PL/SQL. It can either use BFILES to read binary data, or UTL_FILE to read text data to parse and load. We will demonstrate this here.
Write a mini-SQLLDR in Java. This can be a little more sophisticated than a PL/SQL-based loader, and can make use of the many available Java routines.
Write a SQLLDR in C, and call it as an external procedure.
I've put these in order of complexity and performance. As the complexity increases, so will the performance in many cases. It will be the case that PL/SQL and Java will be comparable in performance, and C will be faster (but less portable and harder to construct and install). I am a fan of simplicity and portability so I will demonstrate the idea using PL/SQL. It will be surprising how easy it is to write our own mini-SQLLDR. For example:
ops$tkyte@DEV816> create table badlog( errm varchar2(4000), 2 data varchar2(4000) ); Table created.
is a table we will use to place the records we could not load. Next, we have the function we will build:
ops$tkyte@DEV816> create or replace 2 function load_data( p_table in varchar2, 3 p_cnames in varchar2, 4 p_dir in varchar2, 5 p_filename in varchar2, 6 p_delimiter in varchar2 default '|' ) 7 return number
It takes as input the name of the table to load into, the list of column names in the order they appear in the input file, the directory, the name of the file to load, and the delimiter, in other words, what separates the data files in the input file. The return value from this routine is the number of successfully loaded records. Next we have the local variables for this small routine:
8 is 9 l_input utl_file.file_type; 10 l_theCursor integer default dbms_sql.open_cursor; 11 l_buffer varchar2(4000); 12 l_lastLine varchar2(4000); 13 l_status integer; 14 l_colCnt number default 0; 15 l_cnt number default 0; 16 l_sep char(1) default NULL; 17 l_errmsg varchar2(4000); 18 begin
Next, we open the input file. We are expecting simple delimited data, and lines that are no larger than 4000 bytes. This limit could be raised to 32 KB (the maximum supported by UTL_FILE). To go larger, you would need to use a BFILE, and the DBMS_LOB package:
19 /* 20 * This will be the file we read the data from. 21 * We are expecting simple delimited data. 22 */ 23 l_input := utl_file.fopen( p_dir, p_filename, 'r', 4000 );
Now we build an INSERT TABLE that looks like INSERT INTO TABLE (COLUMNS) VALUES (BINDS). We determine the number of columns we are inserting by counting commas. This is done by taking the current length of the list of column names, subtracting the length of the same string with commas removed, and adding 1 (a generic way to count the number of a specific character in a string):
24 25 l_buffer := 'insert into ' || p_table || 26 '(' || p_cnames || ') values ( '; 27 /* 28 * This counts commas by taking the current length 29 * of the list of column names, subtracting the 30 * length of the same string with commas removed, and 31 * adding 1. 32 */ 33 l_colCnt := length(p_cnames)- 34 length(replace(p_cnames,',',''))+1; 35 36 for i in 1 .. l_colCnt 37 loop 38 l_buffer := l_buffer || l_sep || ':b'||i; 39 l_sep := ','; 40 end loop; 41 l_buffer := l_buffer || ')'; 42 43 /* 44 * We now have a string that looks like: 45 * insert into T ( c1,c2,... ) values ( :b1, :b2, ... ) 46 */
Now that we have the string that is our INSERT statement, we parse it:
47 dbms_sql.parse( l_theCursor, l_buffer, dbms_sql.native ); and then read each line of the input file, and break it into the individual columns:
48 49 loop 50 /* 51 * Read data and exit there is when no more. 52 */ 53 begin 54 utl_file.get_line( l_input, l_lastLine ); 55 exception 56 when NO_DATA_FOUND then 57 exit; 58 end; 59 /* 60 * It makes it easy to parse when the line ends 61 * with a delimiter. 62 */ 63 l_buffer := l_lastLine || p_delimiter; 64 65 66 for i in 1 .. l_colCnt 67 loop 68 dbms_sql.bind_variable( l_theCursor, ':b'||i, 69 substr( l_buffer, 1, 70 instr(l_buffer,p_delimiter)-1 ) ); 71 l_buffer := substr( l_buffer, 72 instr(l_buffer,p_delimiter)+1 ); 73 end loop; 74 75 /* 76 * Execute the insert statement. In the event of an error 77 * put it into the "bad" file. 78 */ 79 begin 80 l_status := dbms_sql.execute(l_theCursor); 81 l_cnt := l_cnt + 1; 82 exception 83 when others then 84 l_errmsg := sqlerrm; 85 insert into badlog ( errm, data ) 86 values ( l_errmsg, l_lastLine ); 87 end; 88 end loop;
Now that we have loaded every record possible, and placing any we could not back into the BAD table, we clean up and return:
89 90 /* 91 * close up and commit 92 */ 93 dbms_sql.close_cursor(l_theCursor); 94 utl_file.fclose( l_input ); 95 commit; 96 97 return l_cnt; 98 exception 99 when others then 100 dbms_sql.close_cursor(l_theCursor); 101 if ( utl_file.is_open( l_input ) ) then 102 utl_file.fclose(l_input); 103 end if; 104 RAISE; 105 end load_data; 106 / Function created.
We can use the above as follows:
ops$tkyte@DEV816> create table t1 ( x int, y int, z int ); Table created. ops$tkyte@DEV816> host echo 1,2,3 > /tmp/t1.dat ops$tkyte@DEV816> host echo 4,5,6 >> /tmp/t1.dat ops$tkyte@DEV816> host echo 7,8,9 >> /tmp/t1.dat ops$tkyte@DEV816> host echo 7,NotANumber,9 >> /tmp/t1.dat ops$tkyte@DEV816> begin 2 dbms_output.put_line( 3 load_data( 'T1', 4 'x,y,z', 5 'c:\temp', 6 't1.dat', 7 ',' ) || ' rows loaded' ); 8 end; 9 / 3 rows loaded PL/SQL procedure successfully completed. ops$tkyte@DEV816> SELECT * 2 FROM BADLOG; ERRM DATA ------------------------------ -------------------- ORA-01722: invalid number 7,NotANumber,9 ops$tkyte@DEV816> select * from badlog; X Y Z ---------- ---------- ---------- 1 2 3 4 5 6 7 8 9
Now, this is not as powerful as SQLLDR, since we didn't have a way to specify the maximum number of errors, or where to put the bad records, or what might be a field enclosure, and so on, but you can see how easy it would be to add such features. For example, to add a field enclosure would be as easy as adding a parameter P_ENCLOSED_BY and change the DBMS_SQL.BIND call to be:
loop dbms_sql.bind_variable( l_theCursor, ':b'||i, trim( nvl(p_enclosed_by,chr(0)) FROM substr( l_buffer, 1, instr(l_buffer,p_delimiter)-1 ) ); l_buffer := substr( l_buffer, instr(l_buffer,p_delimiter)+1 ); end loop;
You now have the OPTIONALLY ENCLOSED BY option of SQLLDR. The above routine is adequate for loading small amounts of data, as noted in Chapter 16 on Dynamic SQL, though we should employ array processing in this routine if we need to scale it up. Refer to this chapter for examples of using array processing for inserts.
At the Apress web site you will find an additional mini-SQLLDR written in PL/SQL. This one was developed to facilitate the loading of dBASE format files. It employs BFILES as the input source (dBASE files contain 1, 2, and 4 byte binary integers that UTL_FILE cannot handle) and can either describe what is in a dBASE file, or load the contents of the dBASE file into the database.
Here we will look at some things that you have to watch out for when using SQLLDR.
Often, you will use SQLLDR with the option REPLACE, when loading. What this does is issue a DELETE, prior loading the data. The data may generate an enormous amount of rollback. You might want to use a specific rollback segment to use in order to perform this operation. SQLLDR has no facility to permit that. You must ensure any rollback segment is large enough to accommodate the DELETE, or use the TRUNCATE option instead. Since an INSERT does not generate much rollback, and SQLLDR commits frequently, this issue really only applies to the REPLACE option.
The TRUNCATE option of SQLLDR might appear to work differently than TRUNCATE does in SQL*PLUS, or any other tool. SQLLDR, working on the assumption you will be reloading the table with a similar amount of data, uses the extended form of TRUNCATE. Specifically, it issues:
truncate table t reuse storage The REUSE STORAGE option does not release allocated extents - it just marks them as 'free space'. If this were not the desired outcome, you would truncate the table prior to executing SQLLDR.
The default length of input fields is 255 characters. If your field is longer than this, you will receive an error message:
Record N: Rejected - Error on table T, column C. Field in data file exceeds maximum length
This does not mean the data will not fit into the database column, but rather SQLLDR was expecting 255 bytes or less of input data, and received somewhat more than that. The solution is to simply use CHAR(N) in the control file, where N is big enough to accommodate the largest field length in the input file.
Many of the SQLLDR options may be placed in either the control file, or used on the command line. For example, I can use INFILE filename as well as SQLLDR ... DATA=FILENAME. The command line overrides any options in the control file. You cannot count on the options in a control file actually being used, as the person executing SQLLDR can override them.
In this chapter, we explored many areas of loading data. We covered the typical day to day things we will encounter C the loading of delimited files, loading fixed length files, the loading of a directory full of image files, using functions on input data to transform them, unloading data, and so on. We did not cover massive data loads using the direct path loader in any detail. Rather, we touched lightly on that subject. Our goal was to answer the questions that arise frequently with the use of SQLLDR and affect the broadest audience.