Many of us want to ZIP the files (CSV, DAT, TXT, etc.) at the FTP location. We can achieve it via multiple ways. Most common is via Shell Script.
But sometimes we do not have access to UNIX Environment due to various reasons. So here is the way by which we can achieve the above objective through PL/SQL API only.
The only thing we need is the privilege to create APIs in backend.
Below is the step by step example is given to export data into CSV file in Oracle using PL SQL procedure.
In this example, data is being exported from fields using the custom program to a CSV file using UTL_FILE Oracle package.
Follow the below steps to export data from Oracle Database table to a CSV file.
1. Create a Directory Object in which you will write CSV file.
2. Create a Program which will create a CSV file at the FTP location.
In our example we will use 2 CSV files to zip together. So we have created two different procedures to create 2 different CSVs at FTP.
As you can see that the name of the CSV files are:
- NEELTEST1.csv
- NEELTEST2.csv
3. Create a function to convert a file to a BLOB.
This function will be used to create a Zip file using PL/SQL zip_util_pkg package. Also note, that before creating this function, make sure that zip_util_pkg is compiled in your schema.
4. Create a block to zip the created CSV files.
Now run the below shown block which will actually zip the CSV files present at FTP location
As you can see that I have given the name of the zip files as ‘NEEL.zip’.
After successful compilation of the block, a zip file will be created at the same directory containing the CSV files.
This is how we can zip ‘n’ number of files present at FTP location via PL/SQL API.
Neelimesh is working as Senior Technical Consultant and has exposure on Oracle Technologies such as Oracle Applications, Database and Cloud.
