| Author |
Topic |
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
 Posted - 2004-07-21 : 01:14:55
|
Hi all, I've been away for a while to be _insanely_ busy, but good to see you've been getting along just fine without me  Ok, so now my question. Our application runs on a SQL Server database, and one of our partners has asked if it is possible to periodically replicate our data into their database. Now these folks are running Oracle (9i, I think) and I am thinking of sending them a weekly update in a CSV or XML file. I want to be a bit prepared for the discussions with them, so, what's the Oracle equivalent of BULK INSERT, bcp and DTS?The two servers are separated geographically, so we'd also have to look at how the file is going to be transferred. What's the best way to approach this? I suppose FTP should be able to do the job well enough, but if you have any other suggestions they'd be welcome.OS |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-21 : 01:37:06
|
| Oracle equivalent of bcp => SQL*Loader - its pretty powerful.DTS => DTS - no reason not to use the MS offering is there?<g>Transfer: ZIP first maybe? PKWARE.COM have a command line Zip that can be executed unattended (but their DOCs make even Oracle look easy!), and I think WinZip has a command line version too. The new WinZip has strong encryption on offer too (and I think pkWare were planning that too).But other than that FTP sounds OK.Kristen |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-21 : 15:37:19
|
| Owais,Nice to hear from you.How big is the file?Will it be transactional or full replacements? Do you want return trecipts from them.Plus they also prefer pipe (|) delimited files...Should be no big deal.How often do they want the file?Brett8-) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-21 : 19:49:41
|
quote:
Originally posted by X002548
Plus they also prefer pipe (|) delimited files...
Be careful of this. In Oracle the pipe (actually double-pipe ||) is the concatenation character, so if I were them I would not want it in my import file.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-21 : 20:33:03
|
| sql loader is not fun to play with. It almost has too many options. If you can DTS the data, I would suggest using that method.Also, I have to ask the dumb question. Is this being sent across a secure connection, or are you thinking of ftping the file across the internet? -ec |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-07-22 : 04:11:31
|
Hey guys, thanks for the responses. Brett, the data that is going to be sent will be a small subset of the database, and definitely incremental, so the size of the file should be well under 5 MB. And what is that about "return trecipts"? You mean confirmation of file received or something like that? Guess that will be logged on their server along with details of the actual import. We'll probably nominate a person there to check this log file regularly to ensure that they've been receiving the data and it's being imported successfully.Mark, thanks for the pointer on the pipe thingy. Good ol' CSV always wins  ec, you mean sql loader has more options than bcp? DTS wont work since the two DB servers wont have a physical connection between them due to cost constraints, so we'll probably have to do this over http or ftp. But the security issue no dumb question, it is a good observation. If they choose http, I should be able to get them to use https. And if they choose ftp, we could encrypt the file or zip and password protect it. Maybe we should password protect the zip file regardless of how its delivered.Why do I get the feeling that this whole approach is a bit tacky? I wish we could get a point-to-point leased line between the two locations but they are thousands of miles apart and the business requirements don't justify the cost of doing that. If this approach is going to be too complex or insecure, I'm going to suggest they just drop the whole idea.OS |
|
|
|
rharmon
Starting Member
41 Posts |
Posted - 2004-07-22 : 04:24:42
|
| These psudo edi projects can be a lot of fun, I'd be on the side of makin it work just for the experience if you havn't done it before. In my all too numerous projects dealing with edi w/ no budget, the return recipt is your best friend. And it doesn't hurt to know ftp scripting like the back of your hand! I understand, it appears a bit low-tech, but this sort of thing happens more than you think and it looks pretty darned good on a resume.ROh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 05:05:24
|
| mohdowais, couple of observations:You need one of the new ZIP programs to get robust encryption - the old "Pasword protect a zip" is very easy to crack [alegedly!]Could you set up a VPN cost effectively between the two sites? That c/would be secureIf there is the possibility of HTTP to the remote site could you "push" XML from your end straight into the far end's HTTP handler (IIS or whatever)?If that starts to look like a runner then maybe you could do some ground breaking and write a Web Service Kristen |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-07-23 : 14:07:21
|
| roh, I couldn't agree with you more. I have already completed about 4 such projects in the last year, and boy, were they fun! But thankfully, in all those projects our team was involved only in sending the data across and we never had to trouble ourselves with what happened after the data got to the partner. But this one is a really large company, and strangely enough don't have dozens of qualified people in their IT department. That's why we are forced to do their homework for them!Kirsten, I'll have to take a good look at the encryption options, do you have any options in mind that you have tried and aren't easily crackable? You know, my first choice for all these "pseudo-edi" projects (gotta love that title) was webservices, but unfortunately our entire operations are still running on pre-dotnet infrastructure. dotNet migration is currently under way and should be done in about three months time... then i can definitely think about going down that route.OS |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 14:42:30
|
| WinZip 9 has strong encryption, and a command-line version.Good luck,Kristen |
|
|
|
|
Is this the Oracle forum?