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.
- Fetch the .zip file from the URL with
apex_web_service
. - Unzip the .zip file into a BLOB with
apex_zip
. - Convert the BLOB into a CLOB with
dbms_lob.converttoclob
. Make sure you pick appropriate character sets. - 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/