How-To Document

After completing this How To, you should be able to:

• Build a form and report in Oracle HTML DB which can upload CSV data into an Oracle table

Introduction

Many applications need the ability to bulk upload data from a spreadsheet into database tables. This can be accomplished using a TEXTAREA item and some PL/SQL code. In this example, we will upload data about different cars from a spreadsheet into the database.

Software Requirements

• Oracle HTML DB 1.6
• Web browser such Netscape 7+ or Interner Explorer 5.5+

Create the Required Schema Objects

As a first step, you must create the required schema objects.

1. Download the DDL Script for the required schema objects and un-zip it to your local machine.
2. Navigate to the SQL Workshop.
3. Click on Scripts, which is the first option under the SQL Scripts heading.
4. Click the Upload button.
5. Enter a title for your script and use the Browse button to locate the file upload_car_ddl.sql on your local machine.
6. Click the Add Script button.
7. Your script will now be listed under the scripts report. Please click on the Run link for your script.
8. Click on the Run Script Button.
9. A screen showing the commands and the status will appear. The first time you run the script you will receive five "Object doesn't exist" errors. This will be normal as there are drop sequence and drop table commands in the script. You can ignore these errors. Any other errors indicate a problem has occurred.

Create an HTML DB Application

Next, you will need to create an HTML DB Application where you can build a form to upload the spreadsheet data. If you would like to implement this functionality into an existing HTML DB Application, then you can skip to the "Create a Form" section.

1. Navigate to the Workspace home page by clicking on the Menu Item Workspace <YOUR WORKSPACE NAME>.
2. Click on Create Application, which can be found at the top of the Application region.
3. Select From Scratch and click Next.
4. Enter a Name for your Application, specify 1 Page, and click Next.
5. Select One Level of Tabs and click Next.
6. Enter " Spreadsheet Upload" as the Page Name and click Next.
7. Take the defaults for the Tab Name and click Next.
8. Select a Theme and click Next.
9. Verify your settings and then click Create Application.

Create a Form and Report

Now that you have an Application, you will need to create a form and report in order to facilitate the upload.

1. Run your Application and navigate to an empty page.
2. Edit that page by clicking on Edit Page X at the bottom of the screen.
3. Click on the " +" icon in the Regions region, under Page Rendering.
4. Select HTML as the Region Type and click Next.
5. Select HTML again and click Next.
6. Enter " Upload Spreadsheet Data" as the Title and click Next.
7. Leave the HTML Text Region Source blank and click Create Region.
8. Click on the " +" icon in the Items region, under Page Rendering.
9. Select Text Area and click Next.
10. Select Textarea and click Next.
11. Enter PX_UPLOAD as the Item Name, where X is the current page number and click Next.
12. Keep defaults and click Create Item .
13. Click on the " +" icon in the Buttons region, under Page Rendering.
14. Select the Upload Spreadsheet region and click Next.
15. Select Create a button in a region position and click Next.
16. Enter " Submit" as the Button Name. The button Label will automatically be named the same. Click on Next.
17. Take the defaults for the Display Properties and click Next.
18. Enter " X" for Branch to Page, where X is the page which you are on, and then click Create Button.

At this point, run your application by clicking the stop light in the upper right-hand corner of the page, just below the main tabs. You should see a text area called "Upload", and a "Submit" button just below it to the right. If you enter some text, and click on the Submit button, the page should reload sucessfully. At this point, the data is not being uploaded into a table.

In the next steps, we will create a process to upload the data, which references a PL/SQL package which was created when the scripts were run at the beginning of this How To.

1. Edit that page by clicking on Edit Page X at the bottom of the screen.
2. Click on the " +" icon in the Processes region, under Page Processing (Note - this is the middle column of the page)
3. Select PL/SQL and click Next.
4. Enter " Upload Spreadsheet Data" for the Name, and click Next.
5. In the PL/SQL Page Process, enter the following and click Next:
6. process_upload.process_cars(:PX_UPLOAD, :PX_UPLOAD);

Be sure to replace PX_UPLOAD with the item name you specified in Step 11. For example: P1_UPLOAD

7. For the Success Message, enter " Data Processed" and click Next.
8. Select SUBMIT for When Button Pressed, and then click Create Process.

Now that we have the process created, we need to create a Report so that we can immediately see the results.

1. Click on the " +" icon in the Regions region, under Page Rendering.
2. Select Report and click Next.
3. Select SQL Report and click Next.
4. Enter " Car Upload Results" as the Title and click Next.
5. For the SQL Query, enter the following and then click Create Region :

select * from stage_cars

Our application is complete. All we have to do is test it with some sample data.

1. Open the spreadsheet " upload_cars_data.xls", which was a part of the ZIP file downloaded at the beginning of this How To.
2. Select all of the rows and columns of data - excluding the titles (Row 1), and then select Copy from the Edit menu.
3. Switch back to your HTML DB Application, and Paste the data into the Upload textarea, then click Submit.
4. You should now see your data in the Car Upload Results report.