2005_604_wagner_slides

USING ORACLE PL/SQL AND JDBC TO MANAGE FILE UPLOADS WITHIN THE DATABASE - A HANDS-ON EXAMPLE

Mary WagnerDelaware Department of

TransportationSession #604

MY GOAL

• Provide a useful solution• Give you all the code so you can

try it yourself

My Background• Currently employed as a Database

Administrator / Developer• Certified in Oracle 8i and 9i

10 years of IT Experience including…• Oracle and SQL Server Database Administration• Database design and development• Java and PL/SQL development• Internet / Intranet web site development • LAN Administration

Session Objectives

• Storing BLOBs– Using the Oracle Data Type

• Uploading Binary Files– Using PL/SQL and Java Streaming

• Viewing Binary Files– Using PL/SQL and Java Streaming

What is a BLOB?

• An Oracle Data Type• A Type of Large Object (LOB)• A Binary File stored inside an

Oracle database

What is a BLOB (con’t) ?

• Spreadsheets• Word Processing Documents• Adobe Acrobat Documents• Image Files• Any other type of binary file stored

in the database ….

Why Use Them?

• File System Dependence Removed• Files are Included in Database

Backups• Files are More Secure• Higher Recoverability

An Example - myDMS

• Small Document Management System (50mb or less)

• Uses JDBC and PL/SQL• Documents are Categorized by

Topic• Documents can be Uploaded,

Viewed and Deleted

An Example – myDMS (con’t)

• Database Server - Oracle 9i• Java Server - Weblogic 8.1• Application Architecture - Java MVC• Java IDE – Intellij IDEA

myDMS – Logging In• Accounts – Manager and User

myDMS – View Document List

• After logging in a list of uploaded documents is displayed….

myDMS – View a Document

• After clicking the “view” link the BLOB is displayed…

myDMS – Upload a Document

• After clicking the “upload new document” link, the upload form is displayed…

myDMS – Delete a Document

• After clicking the “delete” link, the document is deleted…

myDMS – View Topic List• After clicking the “topic list” link, the

list of topics is displayed…

myDMS – Add a Topic• After clicking the “add new topic”

link, a new topic can be added…

myDMS – Delete a Topic• After clicking the “delete” link, the

topic is deleted…

myDMS – Edit a Topic• After clicking the “edit” link, the topic

can be modified…

myDMS – Development Phases

• Phase 1 – Create Tablespaces, Users, System Objects and Tables

• Phase 2 – Create PL/SQL API• Phase 3 – Create Java Objects• Phase 4 – Putting it Together

myDMS – Phase 1.

• Create Tablespaces • Create Users• Create System Objects• Create Application Tables

myDMS – Phase 1. - Tablespaces

• MYDMS_DATA

CREATE TABLESPACE MYDMS_DATA DATAFILE'C:\ORACLE\ORADATA\MW92\MYDMS_DATA01.DBF' SIZE 10304K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

myDMS – Phase 1. – Tablespaces (con’t)

• MYDMS_BLOB

CREATE TABLESPACE MYDMS_BLOB DATAFILE'C:\ORACLE\ORADATA\MW92\MYDMS_BLOB01.DBF' SIZE 51264K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

myDMS – Phase 1. – Users• MYDMS – create schema ownerCREATE USER MYDMS IDENTIFIED BY MYDMSDEFAULT TABLESPACE MYDMS_DATA TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON MYDMS_BLOB QUOTA UNLIMITED ON MYDMS_DATA;GRANT CONNECT TO MYDMS;GRANT CREATE PROCEDURE TO MYDMS;GRANT CREATE TRIGGER TO MYDMS;

myDMS – Phase 1. – Users (con’t)• MYDMS_USER – create

application userCREATE USER MYDMS_USER IDENTIFIED BY MYDMS_USER DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;GRANT CONNECT TO MYDMS_USER;

myDMS – Phase 1. – Users (con’t)• MYDMS_USER – create logon triggerCREATE OR REPLACE TRIGGER

MYDMS_USER.TRG_LOGON_SET_SCHEMA AFTER LOGON ON MYDMS_USER.SCHEMABEGINEXECUTE IMMEDIATE 'ALTER SESSION SET

CURRENT_SCHEMA=MYDMS';END TRG_LOGON_SET_SCHEMA; /

myDMS – Phase 1. – System ObjectsTABLES………@create_tables_action_and_user.sql• ACTIONS• USERTYPE• USERS• USERACCESS

myDMS – Phase 1. – System Objects (con’t)PL/SQL PACKAGE………@create_package_mvc.sql • PKG_MVC

– User access and verification (verify_user)

– Application navigation meta information (get_action_info)

myDMS – Phase 1. – Application Tables

There are 3 application tables…• DOCUMENTS (contains BLOB)• TOPICS • DOCUMENTDETAILS

myDMS – Phase 1. – Application Tables (con’t)DOCUMENTS – the BLOB table@create_table_documents.sql• LOB Initialization -• Enable Storage in Row - • LOB Tablespace – • Chunk Size – • Lob Cache -

myDMS – Phase 1. – Application Tables (con’t)• DOCUMENTS – the BLOB table--create tableCREATE TABLE DOCUMENTS (DOCUMENTID NUMBER(10) NOT NULL, BINARYFILE BLOB DEFAULT EMPTY_BLOB() NOT NULL,DATESTAMP DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT PK_DOCUMENTS_DOCUMENTID PRIMARY

KEY(DOCUMENTID)) TABLESPACE MYDMS_BLOB; --create sequence to populate primary keyCREATE SEQUENCE SEQ_DOCUMENTS_DOCUMENTID;

myDMS – Phase 1. – Application Tables (con’t)• TOPICS - @create_table_topics.sql

myDMS – Phase 1. – Application Tables (con’t)• DOCUMENTDETAILS -

@create_table_document_details.sql

myDMS – Phase 2 – PL/SQL API• PKG_DMS - @create_package_dms.sql

– Includes stored procedures that handle all database transaction processing

– Includes global variables and functions used by stored procedures

– Called by related Java DAO methods

myDMS – Phase 2 – PL/SQL API (con’t)• PKG_DMS – stored procedures

– VIEW_DOCUMENT_LIST– UPLOAD_DOCUMENT– VIEW_DOCUMENT– DELETE_DOCUMENT

myDMS – Phase 3 – Java Objects• Model – View – Controller (MVC)

– MODEL (value object or VO)– VIEW (java server page of jsp)– CONTROLLER – ACTIONS– DATA ACCESS OBJECTS (dao)

myDMS – Phase 3 – Value Objects• DocumentVO

– Place holder for data retrieved from database

– Setter methods called by DAOs– Getter methods called by JSPs

myDMS – Phase 3 – Value Objects (con’t)• DocumentVO

myDMS – Phase 3 – Views

• viewDocumentList.jsp• viewDocumentServlet• formUploadDocument.jsp

myDMS – Phase 3 – Servlets

• ControllerServlet • ProcessRequestServlet

myDMS – Phase 3 – Action Classes• ViewDocumentListAction • ViewDocumentAction• FormUploadDocumentAction• SaveUploadDocumentAction• SaveDeleteDocumentAction

myDMS – Phase 3 – Data Access Objects

• BaseDAO • DocumentDAO

myDMS – Phase 4 – Putting it all Together

• Viewing a Document Listing • Viewing an Uploaded File• Displaying the Upload Form• Saving the Upload Form• Deleting an Uploaded File

myDMS – Phase 4 – Viewing a Document Listing• ACTION ViewDocumentListAction• DAO viewDocumentList• PKG_DMS VIEW_DOCUMENT_LIST• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Viewing a Document Listing (con’t)• ACTION ViewDocumentListAction

myDMS – Phase 4 – Viewing a Document Listing (con’t)• DAO viewDocumentList

myDMS – Phase 4 – Viewing a Document Listing (con’t)• PKG_DMS VIEW_DOCUMENT_LIST

myDMS – Phase 4 – Viewing a Document Listing (con’t)• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Viewing an Uploaded File• ACTION ViewDocumentAction• DAO viewDocument• PKG_DMS VIEW_DOCUMENT• VIEW ViewDocumentServlet

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• ACTION ViewDocumentAction

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• VIEW ViewDocumentServlet

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• DAO viewDocument

– Step 1.) Retrieve BLOB from Database

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• PKG_DMS VIEW_DOCUMENT

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• DAO viewDocument (con’t)

– Step 2.) Set output and input Streams

myDMS – Phase 4 – Viewing an Uploaded File (con’t)• DAO viewDocument (con’t)

– Step 3.) Set buffer and write file to view Servlet

myDMS – Phase 4 – Displaying Upload Form

• ACTION FormUploadDocumentAction• DAO viewTopicList• PKG_DMS VIEW_TOPIC_LIST• VIEW formUploadDocument.jsp

myDMS – Phase 4 – Displaying Upload Form (con’t)• ACTION FormUploadDocumentAction

myDMS – Phase 4 – Displaying Upload Form (con’t)• DAO viewTopicList

myDMS – Phase 4 – Displaying Upload Form (con’t)• PKG_DMS VIEW_TOPIC_LIST

myDMS – Phase 4 – Displaying Upload Form (con’t)• VIEW formUploadDocument.jsp

myDMS – Phase 4 – Saving Upload Form

• ACTION SaveUploadDocumentAction• DAO uploadDocument• PKG_DMS UPLOAD_DOCUMENT• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Saving Upload Form (con’t)• ACTION SaveUploadDocumentAction

– Step 1.) Declare max allowable file size and MultiPart Content boolean


(con’t)– Step 2.) If form field is a file, build

DiskFileUpload item and declare iterator


(con’t)– Step 3.) If form field is not a file, get values


(con’t)– Step 4.) Get file, set input stream and variables

and validate form


(con’t)– Step 5.) If validation succeeds, call DAO to begin

upload process

myDMS – Phase 4 – Saving Upload Form (con’t)• DAO uploadDocument

– Step 1.) Send form info to the database

myDMS – Phase 4 – Saving Upload Form (con’t)• DAO uploadDocument (con’t)

– Step 2.) Get returned blob locator, set chunk, buffer, length and output stream


– Step 3.) Stream the uploaded file to the database


– Step 4.) Set data for updated document list view and commit transaction

myDMS – Phase 4 – Saving Upload Form (con’t)• PKG_DMS UPLOAD_DOCUMENT

– Step 1.) Insert Initial Record into BLOB Table and return BLOB locator

myDMS – Phase 4 – Saving Upload Form (con’t)• PKG_DMS UPLOAD_DOCUMENT (con’t)

– Step 2.) Insert record into DOCUMENTDETAILS

myDMS – Phase 4 – Saving Upload Form (con’t)• PKG_DMS UPLOAD_DOCUMENT (con’t)

– Step 3.) Return updated data for document list, which is displayed if upload succeeds

myDMS – Phase 4 – Saving Upload Form (con’t)• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Deleting an Uploaded File• ACTION SaveDeleteDocumentAction• DAO deleteDocument• PKG_DMS DELETE_DOCUMENT• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Deleting an Uploaded File (con’t)• ACTION SaveDeleteDocumentAction

myDMS – Phase 4 – Deleting an Uploaded File (con’t)• DAO deleteDocument

myDMS – Phase 4 – Deleting an Uploaded File (con’t)• PKG_DMS DELETE_DOCUMENT

myDMS – Phase 4 – Deleting an Uploaded File (con’t)• VIEW viewDocumentList.jsp

myDMS – Phase 4 – Deploying the Application• Create JDBC Connection Pool • Create JNDI Data Source• Reference JNDI Data Source in

web.xml File• Deploy Application Archive or

Exploded Folder on your Java Server

What We’ve Covered…

• Storing BLOBs– Using the Oracle Data Type

• Uploading Binary Files– Using PL/SQL and Java Streaming

• Viewing Binary Files– Using PL/SQL and Java Streaming

What to do Next…

• Download the Java code and database object creation scripts

• Create the necessary database objects using the database scripts

• Create the necessary java objects using the Java files

• Deploy the application in your test environment

General Suggestions…

• Be Consistent with Object Naming• Close Java Objects in Finally

Clauses• Take Your Environment into

Account when Using Examples• Test Everything as Much as

Possible

Scripts / Contact InfoAll of the code and scripts for this

presentation can be downloaded at —• http://www.dbatasktracker.com

Please let me know if there are any errors in the scripts or if you need help with the scripts —

• [email protected]

Additional Resources (Oracle Docs)• Oracle 9i Application Developer's Guide

- Large Objects (LOBs)• Oracle 9i JDBC Developer's Guide and

Reference - Working with LOBs and BFILEs (Chapter 8)

• Oracle 9i Supplied PL/SQL Packages and Types Reference - DBMS_LOB (Chapter 23)

Additional Resources (Books)• Expert Oracle One on One - Tom Kyte• Effective Oracle By Design - Tom Kyte • Mastering Oracle PL/SQL - Connor

MacDonald• Sun Certified Web Component

Developer for J2EE - Sam Dalton, Dan Jepp

Additional Resources (Web Sites)• http://asktom.oracle.com• http://java.sun.com• http://forums.java.sun.com• http://www.jguru.com• http://jakarta.apache.org• http://struts.apache.org• http://e-docs.bea.com/wls/docs81/jdbc/

Any Questions?

• ??????????????????????????????

Thank You

• Thanks for coming! • Please complete the evaluation

form– Speaker: Mary Wagner– Session#: 604– Session Name: Using Oracle 9i Pl/SQL

and JDBC to Manage File Uploads Within The Database

Disclaimer– The content provided as papers and

presentations from the IOUG conferences is copyrighted by the authors of information, and has been licensed to the IOUG. It is only authorized for the personal use of IOUG members and IOUG conference attendees directly through the IOUG web site. Downloading the files, placing them on other web sites or sharing them with other individuals is prohibited.