SQLLoader.docx

SQLLoader.docx

《SQLLoader.docx》由会员分享，可在线阅读，更多相关《SQLLoader.docx（72页珍藏版）》请在冰豆网上搜索。

SQLLoader

10SQL*LoaderFieldListReference

Thischapterdescribesthefield-listportionoftheSQL*Loadercontrolfile.Thefollowingtopicsarediscussed:

∙FieldListContents

∙SpecifyingthePositionofaDataField

∙SpecifyingColumnsandFields

∙SQL*LoaderDatatypes

∙SpecifyingFieldConditions

∙UsingtheWHEN,NULLIF,andDEFAULTIFClauses

∙LoadingDataAcrossDifferentPlatforms

∙ByteOrdering

∙LoadingAll-BlankFields

∙TrimmingWhitespace

∙HowthePRESERVEBLANKSOptionAffectsWhitespaceTrimming

∙ApplyingSQLOperatorstoFields

∙UsingSQL*LoadertoGenerateDataforInput

FieldListContents

Thefield-listportionofaSQL*Loadercontrolfileprovidesinformationaboutfieldsbeingloaded,suchasposition,datatype,conditions,anddelimiters.

Example10-1showsthefieldlistsectionofthesamplecontrolfilethatwasintroducedinChapter9.

Example10-1FieldListSectionofSampleControlFile

.

.

.

1（hiredateSYSDATE,

2deptnoPOSITION（1:

2）INTEGEREXTERNAL

（2）

NULLIFdeptno=BLANKS,

3jobPOSITION（7:

14）CHARTERMINATEDBYWHITESPACE

NULLIFjob=BLANKS"UPPER（:

job）",

mgrPOSITION（28:

31）INTEGEREXTERNAL

TERMINATEDBYWHITESPACE,NULLIFmgr=BLANKS,

enamePOSITION（34:

41）CHAR

TERMINATEDBYWHITESPACE"UPPER（:

ename）",

empnoPOSITION（45）INTEGEREXTERNAL

TERMINATEDBYWHITESPACE,

salPOSITION（51）CHARTERMINATEDBYWHITESPACE

"TO_NUMBER（:

sal,'$99,999.99'）",

4commINTEGEREXTERNALENCLOSEDBY'（'AND'%'

":

comm*100"

）

Inthissamplecontrolfile,thenumbersthatappeartotheleftwouldnotappearinarealcontrolfile.Theyarekeyedinthissampletotheexplanatorynotesinthefollowinglist:

1.SYSDATEsetsthecolumntothecurrentsystemdate.See"SettingaColumntotheCurrentDate".

2.POSITIONspecifiesthepositionofadatafield.See"SpecifyingthePositionofaDataField".

INTEGEREXTERNAListhedatatypeforthefield.See"SpecifyingtheDatatypeofaDataField"and"NumericEXTERNAL".

TheNULLIFclauseisoneoftheclausesthatcanbeusedtospecifyfieldconditions.See"UsingtheWHEN,NULLIF,andDEFAULTIFClauses".

Inthissample,thefieldisbeingcomparedtoblanks,usingtheBLANKSparameter.See"ComparingFieldstoBLANKS".

3.TheTERMINATEDBYWHITESPACEclauseisoneofthedelimitersitispossibletospecifyforafield.See"SpecifyingDelimiters".

4.TheENCLOSEDBYclauseisanotherpossiblefielddelimiter.See"SpecifyingDelimiters".

SpecifyingthePositionofaDataField

Toloaddatafromthedatafile,SQL*Loadermustknowthelengthandlocationofthefield.Tospecifythepositionofafieldinthelogicalrecord,usethePOSITIONclauseinthecolumnspecification.Thepositionmayeitherbestatedexplicitlyorrelativetotheprecedingfield.ArgumentstoPOSITIONmustbeenclosedinparentheses.Thestart,end,andintegervaluesarealwaysinbytes,evenifcharacter-lengthsemanticsareusedforadatafile.

Thesyntaxforthepositionspecification（pos_spec）clauseisasfollows:

Descriptionoftheillustrationpos_spec.gif

Table10-1describestheparametersforthepositionspecificationclause.

Table10-1ParametersforthePositionSpecificationClause

Parameter

Description

start

Thestartingcolumnofthedatafieldinthelogicalrecord.Thefirstbytepositioninalogicalrecordis1.

end

Theendingpositionofthedatafieldinthelogicalrecord.Eitherstart-endorstart:

endisacceptable.Ifyouomitend,thenthelengthofthefieldisderivedfromthedatatypeinthedatafile.NotethatCHARdataspecifiedwithoutstartorend,andwithoutalengthspecification（CHAR（n））,isassumedtohavealengthof1.Ifitisimpossibletoderivealengthfromthedatatype,thenanerrormessageisissued.

*

Specifiesthatthedatafieldfollowsimmediatelyafterthepreviousfield.Ifyouuse*forthefirstdatafieldinthecontrolfile,thenthatfieldisassumedtobeatthebeginningofthelogicalrecord.Whenyouuse*tospecifyposition,thelengthofthefieldisderivedfromthedatatype.

+integer

Youcanuseanoffset,specifiedas+integer,tooffsetthecurrentfieldfromthenextpositionaftertheendofthepreviousfield.Anumberofbytes,asspecifiedby+integer,areskippedbeforereadingthevalueforthecurrentfield.

YoumayomitPOSITIONentirely.Ifyoudo,thenthepositionspecificationforthedatafieldisthesameasifPOSITION（*）hadbeenused.

UsingPOSITIONwithDataContainingTabs

Whenyouaredeterminingfieldpositions,bealertfortabsinthedatafile.SupposeyouusetheSQL*LoaderadvancedSQLstringcapabilitiestoloaddatafromaformattedreport.Youwouldprobablyfirstlookataprintedcopyofthereport,carefullymeasureallcharacterpositions,andthencreateyourcontrolfile.Insuchasituation,itishighlylikelythatwhenyouattempttoloadthedata,theloadwillfailwithmultiple"invalidnumber"and"missingfield"errors.

Thesekindsoferrorsoccurwhenthedatacontainstabs.Whenprinted,eachtabexpandstoconsumeseveralcolumnsonthepaper.Inthedatafile,however,eachtabisstillonlyonecharacter.Asaresult,whenSQL*Loaderreadsthedatafile,thePOSITIONspecificationsarewrong.

Tofixtheproblem,inspectthedatafilefortabsandadjustthePOSITIONspecifications,orelseusedelimitedfields.

SeeAlso:

"SpecifyingDelimiters"

UsingPOSITIONwithMultipleTableLoads

Inamultipletableload,youspecifymultipleINTOTABLEclauses.WhenyouspecifyPOSITION（*）forthefirstcolumnofthefirsttable,thepositioniscalculatedrelativetothebeginningofthelogicalrecord.WhenyouspecifyPOSITION（*）forthefirstcolumnofsubsequenttables,thepositioniscalculatedrelativetothelastcolumnofthelasttableloaded.

Thus,whenasubsequentINTOTABLEclausebegins,thepositionisnotsettothebeginningofthelogicalrecordautomatically.ThisallowsmultipleINTOTABLEclausestoprocessdifferentpartsofthesamephysicalrecord.Foranexample,see"ExtractingMultipleLogicalRecords".

Alogicalrecordmightcontaindataforoneoftwotables,butnotboth.Inthiscase,youwouldresetPOSITION.InsteadofomittingthepositionspecificationorusingPOSITION（*+n）forthefirstfieldintheINTOTABLEclause,usePOSITION

（1）orPOSITION（n）.

ExamplesofUsingPOSITION

siteidPOSITION（*）SMALLINT

sitelocPOSITION（*）INTEGER

Ifthesewerethefirsttwocolumnspecifications,thensiteidwouldbeginincolumn1,andsitelocwouldbegininthecolumnimmediatelyfollowing.

enamePOSITION（1:

20）CHAR

empnoPOSITION（22-26）INTEGEREXTERNAL

allowPOSITION（*+2）INTEGEREXTERNALTERMINATEDBY"/"

Columnenameischaracterdatainpositions1through20,followedbycolumnempno,whichispresumablynumericdataincolumns22through26.Columnallowisoffsetfromthenextposition（27）aftertheendofempnoby+2,soitstartsincolumn29andcontinuesuntilaslashisencountered.

SpecifyingColumnsandFields

Youmayloadanynumberofatable'scolumns.Columnsdefinedinthedatabase,butnotspecifiedinthecontrolfile,areassignednullvalues.

Acolumnspecificationisthenameofthecolumn,followedbyaspecificationforthevaluetobeputinthatcolumn.Thelistofcolumnsisenclosedbyparenthesesandseparatedwithcommasasfollows:

（columnspec,columnspec,...）

Eachcolumnname（unlessitismarkedFILLER）mustcorrespondtoacolumnofthetablenamedintheINTOTABLEclause.AcolumnnamemustbeenclosedinquotationmarksifitisaSQLorSQL*Loaderreservedword,containsspecialcharacters,oriscasesensitive.

IfthevalueistobegeneratedbySQL*Loader,thenthespecificationincludestheRECNUM,SEQUENCE,orCONSTANTparameter.See"UsingSQL*LoadertoGenerateDataforInput".

Ifthecolumn'svalueisreadfromthedatafile,thenthedatafieldthatcontainsthecolumn'svalueisspecified.Inthiscase,thecolumnspecificationincludesacolumnnamethatidentifiesacolumninthedatabasetable,andafieldspecificationthatdescribesafieldinadatarecord.Thefieldspecificationincludesposition,datatype,nullrestrictions,anddefaults.

Itisnotnecessarytospecifyallattributeswhenloadingcolumnobjects.AnymissingattributeswillbesettoNULL.

SpecifyingFillerFields

Afillerfield,specifiedbyBOUNDFILLERorFILLERisadatafilemappedfieldthatdoesnotcorrespondtoadatabasecolumn.Fillerfieldsareassignedvaluesfromthedatafieldstowhichtheyaremapped.

Keepthefollowinginmindregardingfillerfields:

∙Thesyntaxforafillerfieldissameasthatforacolumn-basedfield,exceptthatafillerfield'snameisfollowedbyFILLER.

∙Fillerfieldshavenamesbuttheyarenotloadedintothetable.

∙Fillerfieldscanbeusedasargumentstoinit_specs（forexample,NULLIFandDEFAULTIF）.

∙Fillerfieldscanbeusedasargumentstodirectives（forexample,SID,OID,REF,andBFILE）.

Toavoidambiguity,ifaFillerfieldisreferencedinadirective,suchasBFILE,andthatfieldisdeclaredinthecontrolfileinsideofacolumnobject,thenthefieldnamemustbequalifiedwiththenameofthecolumnobject.Thisisillustratedinthefollowingexample:

LOADDATA

INFILE*

INTOTABLEBFILE1O_TBLREPLACE

FIELDSTERMINATEDBY','

（

emp_numberchar,

emp_info_bcolumnobject

（

bfile_nameFILLERchar（12）,

emp_bBFILE（constant"SQLOP_DIR",emp_info_b.bfile_name）NULLIF

emp_info_b.bfile_name='NULL'

）

）

BEGINDATA

00001,bfile1.dat,

00002,bfile2.dat,

00003,bfile3.dat,

∙FillerfieldscanbeusedinfieldconditionspecificationsinNULLIF,DEFAULTIF,andWHENclauses.However,theycannotbeusedinSQLstrings.

∙FillerfieldspecificationscannotcontainaNULLIForDEFAULTIF