Hello All, recently I worked on a short project and one of requirement which
came in the project is for Bulk uploading of files. As the project is built upon
Oracle database, I had to fit to Oracle technology to accomplish this task.
Here I will share my learning experience while developing this tool, how i
met with hidden gems of Oracle, and I hope this goes useful to you in one way or
other.
Technology : Oracle Application Express 4.1
Database : Oracle 11g Express Edition
Additional Requirement : Please have the database DBA privilege with
you or find a person who has one :D
Tool Requirement : The tool should allow me to upload bulk files to
the database. As per the existing upload tool in Oracle Apex, user need to
upload each and every file individually, I believe this adds to the effort and
temper :(
* Why not Javascript or jQuery ?
One example of Bulk file upload is available @ http://apex.oracle.com/pls/otn/f?p=47121:1 which involves
Javascript to have background upload for each file we select. But in our
scenario, user does need to select each and every file (s)he selects.
Hence I regret using this option, and went to develop a complete new tool as
per my own understanding of requirements.
Working of our Bulk Upload tool :
1. You need to place the files to a folder ( either a shared network path
or a folder on server machine which can be mapped as directory in Oracle
database )
2. Our tool would identify the files in the selected folder and upload them
to the required table.
How things work ?
1. We would be here using a hidden gem of Oracle,
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES. To tell you about this gem, it is a
procedure used for restoring of database backup files.
2. As this is a built in package, it is only accessible to the DBA privileged
database user. Hence if you are planning to use functionality through another
user, please make sure you grant the correct rights.
3. Once the procedure is executed, the list of files is available on
X$KRBMSFT table. I created a view on this table and provided granted
access on the view to all users of my database.
4. We would be using this procedure to read the folder and get the content
inside a folder. This is actually the easiest + best + fastest way i found to
read contents from a folder and the biggest benefit is that it can read
ANY FOLDER from your machine or any accesible path ( no
restriction on what folder you provide as parameter ).
5. Once we have the list of filenames, we can upload the files to any table
with blob columns.
How it worked out ! :))
I am uploading the screen print and short description on how my tool works.
Once can surely have alterations to the usage and have it as per the
requirement.
Screen 1 : A text file placed to a folder on the server. Filename :
MyDemo.txt
Screen 2 : This is functionality I added on the screen, which allows user to
specify the path. It then display the number of files found on the path and also
the name of files which it find. Additionally, I also provided an option to
select the type of file viz .txt, .pdf, .csv
Screen 3 : Finally we have our file uploaded to database, please refer
below screen print showing the file available into the table. I optionally added
an alert to send an email to user once the uploading of files is finished
successfully.
Please share your views upon this tool and also let me know if you face any
issues while trying similar development.
Have a good reading :)
No comments:
Post a Comment