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