Databases
StackOverflow2013
Postsdbo
POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
    primarykey
    data
    text
    <p>I have to download a 250mb~ ZIP File password encrypted through FTP. After downloaded I have to unzip it with a common password, the Zip file would contain an 1.5GB MS Access DB that I have to read and make some joins with some tables in my DB Oracle and transform and load that Data into that Oracle DB.</p> <p>I'm looking for the best way to do this process. I'm a c# developer, so my first thought was to use c#, download the file via <a href="http://netftp.codeplex.com/" rel="nofollow noreferrer">FtpClient</a> or <a href="http://msdn.microsoft.com/en-us/library/system.net.ftpwebrequest.aspx" rel="nofollow noreferrer">FtpWebRequest</a>, then use a zip library like <a href="http://dotnetzip.codeplex.com/" rel="nofollow noreferrer">DotNetZip</a> and open the MS Access dataBase via ODBC and load the records into Oracle with ODP.NEt, I think that's is my "easy way", cuz I know how to do it.</p> <p>But since this a big file and I know this could take a long time, I'm concerned about time and efficiency and how to reduce the time of this process.</p> <p>So I'm thinking that processing all the request directly into oracle(download the FTP from there, unzip it there, and process the information directly in there would reduce times like passing record by record from c# to oracle) should reduce the time of this process, but I'm not sure if this is the correct way of doing this.</p> <p>So I started to look into librarys from oracle that could do what I'm trying to archieve and I found the <a href="https://code.google.com/p/plsql-utils/" rel="nofollow noreferrer">PLSQL-utils</a> and seems like they can do everything that I need except reading the MS Access DataBase and I started looking about that and found the <a href="http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67784/hs_ch6.htm" rel="nofollow noreferrer">Heterogeneous Services</a> but I have never used them so I'm little lost about that.</p> <p>Also I heard once that I could use Java directly from Oracle, and I know java can connect to MS Access via JDBC. So I searched about that and found something about <a href="http://docs.oracle.com/cd/B28359_01/java.111/b31225/chthree.htm" rel="nofollow noreferrer">Calling Java Methods in Oracle Database</a></p> <p>That's what I have so far, but I don't know which method should I use, I mean, RDBMS as far as I know, are meant for processing data but not for programming things like downloading files or something like that, that's why we have OOP's languages.</p> <p>As an additional information, this process is going to execute once or twice for month so I have to schedule it, if it is in oracle, can easily be done with an schedule job, or in c# with a Scheduled Task or Windows Service (those are the tools that I know)</p> <p>Some restrictions that I have</p> <ul> <li>My client don't have a MS SQL Server and neither can buy a license for it (So I cannot use DTSX for this process)</li> <li>In the Oracle production server <em>maybe</em> I won't have enough permissions to do all the things, but I can comply for those if they are the best for the process</li> <li>If a backend server (Java, c# hosted on IIS or WebLogic or JBoss or anykind) is going to be required, this Server and the Oracle Server would be differents</li> <li>Oracle database hosted on Unix Server</li> </ul> <p>Being said all of this, how can I efficiently do all this process, should I use .net and load record by record in my Oracle DataBase? Should I do everything in oracle?Or none of this? Is there a better way to do this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMr.
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
      1. USMr.
      1. VTBountyStart
    3. VO
      singulars
      1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you searched about oracle database links? There is a way to connect oracle to another database, look at this post http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206 there is a link pointing to the documentation but is broken, so you woll gave to find where it is.
      singulars
      1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
      1. USJorge Campos
    2. COyes I did i think is on my comment about the Heterogeneous Services , refering to dblink
      singulars
      1. POBest way to download and process a Very Large Compressed MS Access File to be loaded in Oracle
      1. USMr.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload