Solved

Infile oracle loop and load files in powershell

Posted on 2015-02-08
8
222 Views
Last Modified: 2015-02-12
Hi,

I would like to load files with powershell as my environment is windows so can't do it in shell....

OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
[b]infile 'd:\test.DH[/b]' 
"str '\n'"
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
(filename)

Open in new window


I have around 27K files and this is everyday operation... I need a powershell to loop through files in put them in INFILE ... after load is done it will move them at archive folder.... or it will load the file name at the end of each record...  or load the files name in another table ....

thanks for help...
0
Comment
Question by:hi4ppl
  • 3
  • 3
  • 2
8 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40596977
I am not a Powershell pro, so I don't think that I can help you with the actual script.

From the SQL*Loader side, what I would recommend is not to put the name of the file to be loaded in the control file.  Instead, specify the name of the file on the command line with the DATA parameter.  That takes the step of  having to try to modify the file out of the equation.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40596996
hi,

can you give example of what you mean? you mean to put it as variable in sql*loader?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40597002
Your sample control file changes to:
OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
(filename)

Open in new window

Then when calling SQL*Loader, your command line looks like:

sqlldr control=control.ctl user=u/p data="d:\test.DH"
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40597119
I would have to look that up, so just asking: what does (filename) mean here?
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 1

Author Comment

by:hi4ppl
ID: 40597623
Hi, file name is static, where I will put fields of tables that can be as it's... the only part is where INFILE to loop through files in directory and put the files one at a time ...
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40597801
hi and also data="d:\test.DH" is not working for me since I have multiple files not only one file
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40598064
I am very confused here.

You say file name is static, then loop through files in directory.  If the file name is static, then what is the loop doing?  You cannot have multiple files with the same name.

Pseudo code:

for file in *
   sqlldr control=control.ctl user=u/p data=$file
done

You call sqlldr once for each file.  Using a script to edit a file is a real pain to do.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40598950
And this part is very unprecise, too:

after load is done it will move them at archive folder.... or it will load the file name at the end of each record...  or load the files name in another table ....
You might think too complicated here. The way I get it, the PowerShell code is:
push-location C:\Import\Data
dir *.dh | % {
  sqlldr control=control.ctl user=usr/pwd data=$_.Name
  move-item $_ C:\Import\Archive\
}
pop-location

Open in new window

0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now