Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
loading oracle tables 1
- Thread starter jhigelin
- Start date
-
1
- #3
To elaborate on SQLLoader:
Assume the following file is called inventory.ctl and it resides in a folder at E:\Migration\SQLloader. Further assume that your flat file is pipe-delimited (could be comma dlimited, or whatever), is called E:\DataFiles\inventory_part.txt, and consists of the fields "site", "part_no" and "description". Finally, you'll need to create a "container" table with the appropriate fields. Edit the first line to correct user, password, and sid), then copy the first line to your clipboard (it should have no breaks. That is, should all be on one line). Open DOS window, right click on title bar, and select "paste". Hit enter.
You now have a data-filled container table against which you can run a script to cursor through the records and validate them against any relevent PL/SQL logic or packages. Of course, you could also use these instructions to directly populate your tables.
-- file starts here
-- sqlldr73 user/password@sid control=E:\Migration\SQLloader\inventory.ctl log=E:\Migration\inventory_part.log
--
--
--
load data
infile 'E:\DataFiles\inventory_part.txt'
replace into table Inventory_Part_CON
fields terminated by '|' optionally enclosed by ' '
(
SITE,
PART_NO,
DESCRIPTION
)
John Hoarty
jhoarty@quickestore.com
Assume the following file is called inventory.ctl and it resides in a folder at E:\Migration\SQLloader. Further assume that your flat file is pipe-delimited (could be comma dlimited, or whatever), is called E:\DataFiles\inventory_part.txt, and consists of the fields "site", "part_no" and "description". Finally, you'll need to create a "container" table with the appropriate fields. Edit the first line to correct user, password, and sid), then copy the first line to your clipboard (it should have no breaks. That is, should all be on one line). Open DOS window, right click on title bar, and select "paste". Hit enter.
You now have a data-filled container table against which you can run a script to cursor through the records and validate them against any relevent PL/SQL logic or packages. Of course, you could also use these instructions to directly populate your tables.
-- file starts here
-- sqlldr73 user/password@sid control=E:\Migration\SQLloader\inventory.ctl log=E:\Migration\inventory_part.log
--
--
--
load data
infile 'E:\DataFiles\inventory_part.txt'
replace into table Inventory_Part_CON
fields terminated by '|' optionally enclosed by ' '
(
SITE,
PART_NO,
DESCRIPTION
)
John Hoarty
jhoarty@quickestore.com
- Status
Similar threads
- Locked
- Question