Bulk Upload Tool in Oracle Apex

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 :)

XML .. XML and XML .... How do I utilize it !

We all know that today's world demands XML data everywhere, it can be seen in our blogs as RSS feeds, can be seen in Web Services, can be heard of in SOA, also in Web 2.0. So now how do we work about it.

What can be worked upon this huge available xml data ? Lets find out through this post. We will see how to utilize the available XML data through PL/SQL.

If you want to start with knowing XML, please give a look to http://www.w3schools.com/xml/

In this post, we will be utilizing a sample XML file available at http://www.w3schools.com/xml/note.xml. Here is what it looks like

                <?xml version="1.0" encoding="ISO-8859-1"?>
                  <note> 
                       <to>Tove</to> 
                       <from>Jani</from> 
                       <heading>Reminder</heading> 
                       <body>Don't forget me this weekend!</body> 
                  </note>

To understand above XML code, by looking at the piece of code, we can understand, code is providing us the following data :

• message heading,
• message body,
• the sender, and
• the receiver.

Lets utilize the above XML and try to fetch the information.

Before proceeding, I would like to quote the blogs which helped me to understand the usage of XML data.

• http://oraexplorer.com/2007/10/read-xml-data-from-url-and-insert-into-oracle/
• http://www.oracle.com/technetwork/developer-tools/apex/howto-yahoo-pipe-integration-096735.html

Here is the SQL query which i have tried after referring the OraExplorer's post. Please have peek through of above links. I have combined the statements provided in the post and created the final SQL query to give out the data :

 select extractvalue(mydata,'/note/to') as "To",
 extractvalue(mydata,'note/from') as "From",
 extractvalue(mydata,'note/heading') as "Title",
 extractvalue(mydata,'note/body') as "Message" from (
 select sys.xmltype.createXML(sys.UriFactory.getUri('http://www.w3schools.com/'||
 'xml/note.xml').getClob()) as mydata from dual );

Screen Print for above query :

 

This was an easy and very interesting XML, let's try one more available at http://www.w3schools.com/xml/cd_catalog.xml. This XML gives a catalog of CD displaying the details of CD as following ( pasting only one section from XML )

                   <?xml version="1.0" encoding="ISO-8859-1"?>
                   <CATALOG> 
                         <CD> 
                              <TITLE>Empire Burlesque</TITLE> 
                              <ARTIST>Bob Dylan</ARTIST> 
                              <COUNTRY>USA</COUNTRY> 
                              <COMPANY>Columbia</COMPANY> 
                              <PRICE>10.90</PRICE> 
                              <YEAR>1985</YEAR> 
                         </CD>
                         <CD> 
                              <TITLE>Hide your heart</TITLE> 
                    .
                    .
                    .
                   </CATALOG>

So this XML has a list of records unlike note.xml which had only one record. For cd_catalog.xml, i have taken help from the second post and following SQL query provide us the data :

               SELECT EXTRACTVALUE(VALUE(ITEM), '/CD/TITLE')  AS TITLE,
               EXTRACTVALUE(VALUE(ITEM), '/CD/ARTIST')  AS ARTIST,
               EXTRACTVALUE(VALUE(ITEM), '/CD/PRICE')  AS PRICE
               FROM TABLE
                    ( XMLSEQUENCE
                    ( EXTRACT
                    ( HTTPURITYPE
                        ( 'http://www.w3schools.com/xml/cd_catalog.xml'
                             ).getXML()
                           , '/CATALOG/CD') ) ) ITEM
               where rownum <= 5;

 Screenshot for above query :

 

This was all about writing SQL query through SQL Developer or Toad. I also created an Apex page where i utilized the same XML to create an Interactive control to display a marquee section with the gathered XML data.

As the network ACL configuration @ apex.oracle.com doesn't allow us to request a URL, I am unable to place an example at apex.oracle.com.

One can retrieve the RSS Atom feed from a blog and display the blog posts in similar manner.
Eg. I can retrive RSS from KPIT's blog and display it as a content on any web page or any application as well.

Written By : Aashish Singh Negi ( http://apex.aashishnegi.com )
Contributor : Vijeta Digarse

A small step from my side .....

Hi All,
Welcome to the blog where we would be doing exactly same what title says "Apex n Fun" .... So we will be learning and sharing the knowledge of Oracle Apex a.k.a Application Express and also see the lot of Fun it brings to work on such a koool technology.
This is my first posting on this blog and to start with, I would share my experience with Apex from initial till the moment I m publishing this post. Hoping this would help my readers to understand how easy it can be to work and learn with fun !
The First Meet : It is the year of 2009, and we are into start of final year of engineering at Sinhgad Institue Of Technology, Lonavala. As being the final year, we are required to complete an industrial project. So my project group member Sameer works out his social links and brings a cool project which need to be developed in Oracle Apex.
As being a total Microsoft lover at college, I never stepped beyond knowing the name "Oracle Apex". I visited the site http://apex.oracle.com but didn't understand a bit out of the things which appeared on the page.
This is the only short meet I had with Apex and I never looked back to it after this.
The Second Encounter : Year 2011, I am now working at an IT Company at Pune.
It is about two years since I learned, rather heard the existence of something called Oracle Apex. I get an internal email from a senior colleague to join up and learn Oracle Apex through a training session given by few SMEs.
Something struck my mind this day, and I decide to give it a try. I subscribed for the training.
The training went great with each and everything explained so well by Mahesh and Nachiket sir. They introduced us with the basics, the gui, the needed skills, the benefits, the architecture, and everything. I gave a little time and a more little effort to the knowledge we learnt and to tell you the truth, I was now fan of Apex. I never knew that I can work something great and all I need to know is PL/SQL. Damm thing, we are seeing and working PL/SQL from past 5 years, but never leveraged it.
So now I started to work on Apex, the features I found interesting, the effort goes in very easy, the work goes quick, and the best part - you have something to learn everyday ....
Time by time from this moment, me and few colleagues started working Apex, exploring the possibilities, understanding the limits, utilizing the features, and it is all fun working with the latest and simple tool !