This discussion is archived
6 Replies Latest reply: Apr 30, 2013 12:24 PM by Seenu001 RSS

FTP in PLSQL

Seenu001 Newbie
Currently Being Moderated
Hi All -

I am developing a small website using APEX 4.2 which has backend database that has 10-12 tables. All these tables have to be populated on daily basis based on public data published on some website.
The question is, what are the possible ways to ftp these files into my database. I donot have access to the oracle file system to use the UNIX ftp program to copy the files into Oracle directory and then do a PLSQL API which is explained in http://www.oracle-base.com/articles/misc/ftp-from-plsql.php. Please share your thoughts.

Thanks,
-Seenu
  • 1. Re: FTP in PLSQL
    mobra Journeyer
    Currently Being Moderated
    based on public data published on some website.
    The question is, what are the possible ways to ftp these files into my database.
    If the data is "published on some website", why don't you just use UTL_HTTP to download the file?

    http://www.oracle-base.com/articles/misc/retrieving-html-and-binaries-into-tables-over-http.php

    - Morten

    http://ora-00001.blogspot.com
  • 2. Re: FTP in PLSQL
    Seenu001 Newbie
    Currently Being Moderated
    Thanks Morten for you input.

    The public website which holds this data is in a zip format. Will this work for zip file as well?

    Once I unzip the file, it has one flat file which holds lot of data and from that I need to populate some tables based on soime business rules.

    Thanks,
    -Seenu
  • 3. Re: FTP in PLSQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    If the file is on a web site, you would need to use HTTP to access it. If the file is on a FTP server, you would need to use FTP to access it. When you click the link to download the file, are you sent to an HTTP:// address or a FTP:// URL?

    You may be able to use the UTL_COMPRESS package to unzip the ZIP file once you download it-- see, for example

    http://technology.amis.nl/2010/03/13/utl_compress-gzip-and-zlib/

    Personally, though, I'd find it easier to write a Java stored procedure to do that. There are way more examples and far more complete libraries for working with ZIP files in Java.

    Once you've unzipped the ZIP file and extracted the file, you'll have to write code to parse the file and load the data into your table.

    Of course, all this would be dramatically easier if you had access to the operating system functions on the server. Then you could simply download, unzip, and parse the file as part of an external table definition with a preprocess step doing all the heavy lifting.

    Justin
  • 4. Re: FTP in PLSQL
    AndyH Journeyer
    Currently Being Moderated
    Seenu001 wrote:
    Hi All -

    I am developing a small website using APEX 4.2 which has backend database that has 10-12 tables. All these tables have to be populated on daily basis based on public data published on some website.
    The question is, what are the possible ways to ftp these files into my database. I donot have access to the oracle file system to use the UNIX ftp program to copy the files into Oracle directory and then do a PLSQL API which is explained in http://www.oracle-base.com/articles/misc/ftp-from-plsql.php. Please share your thoughts.
    I use the FTP package described in your link. It works well and doesn't need to use the file system.
  • 5. Re: FTP in PLSQL
    Seenu001 Newbie
    Currently Being Moderated
    Thanks Justin ... I agree .. it would be much easier if I had access to the OS (Linux) and simply could do a FTP korn shell script and then use the EXTERNAL FILE concept.

    The public website publishes this zip file on HTTP site.

    Thanks,
    -Seenu
  • 6. Re: FTP in PLSQL
    Seenu001 Newbie
    Currently Being Moderated
    Hi Andy - Thanks for your response. Did you use that ftp API on a zip file?

    Thanks,
    -Seenu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points