?
Solved

Batch file to load data in windows oracle

Posted on 2012-08-13
8
Medium Priority
?
969 Views
Last Modified: 2012-11-08
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
Comment
Question by:hi4ppl
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38287347
SQL*Loader will load the data inside the file.

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

by:ThomasMcA2
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

)

Open in new window

0
 
LVL 20

Expert Comment

by:gatorvip
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\

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:hi4ppl
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

Open in new window


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

Expert Comment

by:slightwv (䄆 Netminder)
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

by:hi4ppl
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

by:slightwv (䄆 Netminder)
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.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN12896
0
 
LVL 20

Accepted Solution

by:
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
                                            

Open in new window


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,
  ...
)

Open in new window


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')"
)

Open in new window


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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question