Use sqlldr control file to automatically create several tables in Oracle

importoraclesql-loader

I have a client who has a requirement that I load data from csv files into an Oracle database. Part of the requirement is that I use sqlldr and provide a control file to facilitate loading this data. However there are a lot of text files with a lot of fields, and it will be a lot of labor to create the tables in the database and then create the control file for each table.

I know there are tools which will read a bunch of text files and generate the SQL that is required to create all the tables and load them with data. But that does not involve sqlldr with control file, which is the requirement. Is there a way to do this easily?

Best Answer

The Utilities manual that describes SQL*Loader syntax (which no sane person can recall anyway!) has a number of case studies (doc link here). Case study 5 covers loading into multiple tables. The actual case studies are installed into $ORACLE_HOME/rdbms/demo if you choose to install the companion CD. I seem to recall that in earlier versions they were also described in full in the Utilities manual.

Related Solutions

Load Numerical Column with Variable Comma

Here you go:

SQL> desc loadertst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> !cat loadertst.ctl
load data
infile *
into table loadertst
fields terminated by ',' enclosed by '"'
(
col1 "to_number(replace(:col1,',',''))"
)
begindata
"123456"
"1,2,3,4,5,6"

SQL> !sqlldr phil/phil control=loadertst.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Nov 29 23:33:17 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

SQL> select count(*) from loadertst;

  COUNT(*)
----------
         2

SQL>

Obviously you may need to fiddle with the fields terminated by ',' enclosed by '"' part to suit your data.

Loading XML documents to Oracle 11g DB with control file

I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.

Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.

However, you could also define the column as an "xsd:dateTime". See http://www.adp-gmbh.ch/xml/schema.html and http://www.w3.org/TR/xmlschema-2/#dateTime

Best Answer

Related Solutions

Load Numerical Column with Variable Comma

Loading XML documents to Oracle 11g DB with control file

Related Question