SQL Loader Question

[DKL01]

Hello All:
I have unix question. We receive data file in unix. SQL Loader loads this data into oracle. Assume that in this data file 1-11 characters represents first name, 12-18 represents address, 19-23 represents Phone #. All records in this file are 23 characters long. In special situations, for some records, the address will have more characters (look at 4th record). How to handle this type of situation. How to make control file dynamic ? I really appreciate any help.

MyfirstnameAddressPhone
MyfirstnameAddressPhone
MyfirstnameAddressPhone
MyfirstnameAddressinforeigncountryPhone
MyfirstnameAddressPhone
MyfirstnameAddressPhone

 

[vgersh99]

I'd suggest:

1. make your files be 'field-oriented' with your choice of the field separator - say it's a '|'. Your data will look like:
Myfirstname|Address|Phone
Myfirstname|Address|Phone
Myfirstname|Address|Phone
Myfirstname|Addressinforeigncountry|Phone
Myfirstname|Address|Phone
Myfirstname|Address|Phone

2. in your ocntrol file say something like - I don't quite remember the syntax:
FIELDS TERMINATED BY "|"

3. you might change your loading logic: istead of loading by 'bytes' TO loading by fields.

It been awhile for me. A better place to ask might be the Oracle forum.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[Annihilannic]

Failing that, you could use sed to do it.

Something like:

[tt]sed 's/\(.\{11\}\)\(.\{7,\}\)\(.\{5\}\)/\1|\2|\3/' inputfile > outputfile[/tt]

\(.\{11\}\) means exactly 11 occurences of the preceding character, in this case a &quot;.&quot;, which matches any character. The \( \) brackets allow you to re-insert the matching string in the replacement field using \1. This will match the first name.

\(.\{7,\}\) means 7 or more of the preceding character, and will match the address.

\(.\{5\}\) is similar to the first expression, and will match the phone number.

Annihilannic.