Home > Mobile >  ORACLE read file from url
ORACLE read file from url

Time:10-07

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

CodePudding user response:

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.

CodePudding user response:

You can use a special httpuritype object: https://docs.oracle.com/database/121/ARPLS/t_dburi.htm

Funny example: http://orasql.org/2021/04/25/vblog-funny-friday/

  • Related