Search code examples
sqloracle-databasecsvzip

ORACLE read file from url

I have a url that download a zip file, inside this one there is a csv file that I want to read into select statement.

Example I have this url: http://www.xyxy.com/test.zip Immediately you run this url your browser downloads a zip file, inside this one there is a csv file with 5 columns: name, lastname, age, direction, phone

I want to read this csv file called: directions_clients.csv with a select statement just sending the url as parameter.

something like this:

Select * from getcsv(getzip('url'))

and display each column from the csv file

any idea how to read zip files from url and read also the csv inside of them?

Regards

Solution

  • You're in Oracle, so I assume you have Application Express available. If not, download and install it, it's awesome.

    1. Fetch the .zip file from the URL with apex_web_service.
    2. Unzip the .zip file into a BLOB with apex_zip.
    3. Convert the BLOB into a CLOB with dbms_lob.converttoclob. Make sure you pick appropriate character sets.
    4. Convert the CLOB into rows and columns of data with apex_data_parser.