[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Infile oracle loop and load files in powershell

Posted on 2015-02-08
8
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

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 35

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

 
LVL 71

Expert Comment

by:Qlemo
ID: 40597119
I would have to look that up, so just asking: what does (filename) mean here?
0
 
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 35

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 71

Accepted Solution

by:
Qlemo earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

650 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