I have the task of automating a previously manual process, one step of which is to load the contents of a .dat file into the Oracle DB using sqlldr. (This .dat file is coming from an SAP system that is behind the firewall, and there is no way that I can change that process)
The sqlldr statement looks like this:
infile filename_LOG_mmddyy.dat (this filename changes daily)
append into table tablename
( field1 position (01:12) ,
field2 position (13:112),
field3 position (113:113) ,
field4 position (114:123),
field5 position (124:133),
field6 position (134:197) ,
field7 position (198:198),
field8 position (199:201),
field9 position (202:301) ,
field10 position (302:351)
My first question is - can I just write a SQL statement to update the database by reading the .dat file dynamically? If so, how?
My second question is - If I can't programatically update the Oracle DB by using CF to read the .dat file, can I dynamically create a batch file on the fly to update the database, since the .dat file that the control file is reading will change on a daily basis, so I want to make the filename a variable that can be manipulated. If that is possible, can someone send an example of how to dynamically generate a batch file with CF?
If I am going about this all wrong, please let me know if I am missing a much easier way to do this.