Re: NEWBIE: file structure for ORACLE

Here a snippet of a sql loader file. PLI was used to create the .ctl file with the ascii data inclosed with a hex 1f (editor shows it as ^_ ) . I use the 1f character because it does not print and has a very good chance of not being in an ascii string.
Two commas indicate null data for that column. Field names tell the loader what order the data is presented in the data lines.

• start of loadpgm.ctl on next line --------------------- load data infile * insert into table srlexp_pgm fields terminated by ',' optionally enclosed by X'1F' (PROD_COMPNT_NUM, ITEM_NAME_NUM, CREATED DATE "YYMMDD", CREATED_BY, UPDATED DATE "YYMMDD", UPDATED_BY, ITEM_NAME, LAST_EXTENT, CREATEDBY, UPDATEDBY) begindata 1,4249,901015,,901015,,^_AATRM^_,0,^_JDSMITH^_,^_JDSMITH^_ 1,1,841108,,841108,,^_AC2PRG^_,0,^_PEKARSKE^_,^_PEKARSKE^_ 1,2,901015,,901015,,^_AC3PRG^_,0,^_MHORBOVE^_,^_MHORBOVE^_ 1,4250,910213,,910213,,^_ACDPRG^_,0,^_RMICKLEY^_,^_RMICKLEY^_
• end of loadpgm.ctl on the previous line -------------

Ari Kaplan (akaplan_at_interaccess.com) wrote:
: Alan Macaluso <anm_at_interport.net> writes:
 

: >Howdy all,
 

: >I'm relatively new to SQL/Oracle. I understand a bit of SQL. I've
: >recently installed Oracle (Solaris) but have not configured it
: >completely.
 

: >Although it's not completely configured, I am going to have to start
: >capturing user data (name, address, phone, etc) -- most likely some kind
: >of delimited file (tab, semi-colon....).
 

: >My question is this: is there some standard/prefered way to structure
: >the file for import into Oracle??
 

: >Any help, tips, suggestions would be GREATLY appreciated.
 

: >Alan Macaluso
 

: Alan,
 

: There is no standard way to structure the file for loading into Oracle. The
: SQL*Loader program is flexible enough to be used with any delimeter, be it
: spaces, tabs, commas, etc.
 

: The best delimiter is a character that is not contained in any field. For
: instance, if one field is a name, such as "Alan Macaluso" or an address, such
: as "23 Broadway Ave", then a space would not be ideal as a delimiter.
 

: You can specify when loading the delimiter by commands such as
: TERMINATED BY ","
: TERMINATED BY WHITESPACE (spaces and tabs)
: ENCLOSED BY '"'
 
: See the Oracle Utilities User's Guide for more specifics on SQL*Loader.
 

: My personal preference is comma-delimited fields, with a line feed after each
: record.
 

: Good luck!
 

: -Ari Kaplan
: Independent Oracle DBA Consultant
 

: <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
: <-> Visit my Web Page: http://homepage.interaccess.com/~akaplan <->
: <-> email: akaplan_at_interaccess.com <->
: <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.