Solved

# Batch file to load data in windows oracle

Posted on 2012-08-13
Medium Priority
969 Views
Hi guys,

I have files that are generated from server and their extension is .ul but inside the file the data is separated by "|" so I need a batch script to do the following for me:

- if the .ul is not recognized then the scrip just need to rename it to .csv or something
- pick the files and load them into the table,
- load the name of files to a separate table I have in  with their timestamp
- move the files to another folder so to avoid duplicate loads.

and yeah the database is in oracle, and the system is windows so I need a windows script a batch, already have that in linux but having issue creating the same in DOS

thanks for help
0
Question by:hi4ppl
• 3
• 2
• 2
• +1

LVL 78

Expert Comment

ID: 38287347

You will need some way to add the filename.  Possible a simple echo to add the name to the end of the file.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_concepts.htm#i1005437

The rest is simple copy commands.
0

LVL 14

Expert Comment

ID: 38287794
Use a "for" loop:

for %%file in (c:\path\to\files\*.ul) do (

REM run your sqlldr command, and put %%file in the file name parm

REM save the file name and date:
echo %%file %date% %time% >> your_log

REM rename your file so it doesn't get processed again
ren %%file %%file.bak

)

0

LVL 20

Expert Comment

ID: 38287989
ThomasMcA2 already covered it, but here is part of a script I use:

for /f %%a in ('dir /b *.dat') do sqlldr user/pwd control=t.ctl data=%%a
move *.dat archive\

0

LVL 1

Author Comment

ID: 38288209
Hi guys thanks but I need something like this that i have created on linux:

#!/bin/bash
PGBINDIR=/usr/bin
cd .
if [ ! -d ../archive ]; then
mkdir ../archive
fi
for f in *.csv
do
echo "Processing File - $f"$PGBINDIR/psql -c "\copy table_x from '$f' with csv" test1$PGBINDIR/psql -c "insert into table_y values ( '$f', now() )" test1 mv$f ../archive
done


But I need similar for windows how to do the something in windows for oracle
regards
0

LVL 78

Expert Comment

ID: 38288237
What is psql?  From a quick Google it is Postgres.  This was posted in an Oracle zone.  Is this Oracle or Postgres?
0

LVL 1

Author Comment

ID: 38290697
Hi yeah that script is postures I and I need same for windows platform for oracle, I posted that so people can read and get what I want,

I want a clone of that for oracle in windows platform

regards
0

LVL 78

Expert Comment

ID: 38291678
There are several ways to load a delimited file into Oracle.

I would suggest the original method I posted:  SQL Loader.

Others have posted ways to rename the file when done.

I'm not sure what level of error checking psql has so you might need some additional logic to make sure all the records were loaded and everything is actually complete.

You might also look into using External Tables.  These basically follow sql loader syntax but can be queried directly from inside the database.  This can give you more control when processing.

0

LVL 20

Accepted Solution

gatorvip earned 2000 total points
ID: 38292021
The script you posted is not that difficult to convert  as long as your files are consistent. Something like this should work (I adapted my code and ThomasMcA2's), but there are other possibilities too:

cd .
IF NOT EXIST "../archive" md "../archive"

for /f %%a in ('dir /b *.csv') do
(
sqlldr user/pwd control=t1.ctl data=%%a

echo %%a "," %date% %time% >> your_log

move *.csv ../archive
)

sqlldr user/pwd control=t2.ctl data=your_log

del your_log /y



where t1.ctl and t2.ctl have the following contents

t1.ctl
LOAD DATA
APPEND
INTO TABLE table_x
FIELDS TERMINATED BY "|"
(
your_field1,
your_field2,
...
)


t2.ctl
LOAD DATA
APPEND
INTO TABLE table_y
FIELDS TERMINATED BY ","
(
filename,
ts "to_timestamp(:ts,'DY MM/DD/YYYY, HH:MI:SS.FF')"
)


In the last line, you should modify the timestamp format according to your environment. Another way you can insert the timestamp is by using a trigger inside the table itself.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month16 days, 6 hours left to enroll