ExcelDocTypeUtils Update

At the suggestion of  a user (Paul Jensen … thanks for the sample code as well), I have updated the ExcelDocTypeUtils package making the COL_COUNT attribute of the T_WORKSHEET_DATA type optional.  This will allow users to introduce queries with varying numbers of columns dynamically, or use queries such as SELECT * FROM some_table.  In the previous version, the COL_COUNT attribute was mandatory.  I’ve also added a new function called getColHeaderString that will generate a delimited column name list for use with the COL_HEADER_LIST attribute of the T_WORKSHEET_DATA.  This could be used with a ‘SELECT * FROM …’ type query string.

Example:

v_worksheet_rec.col_header_list       := ExcelDocTypeUtils.getColHeaderString(v_worksheet_rec.query,v_worksheet_rec.worksheet_list_delimiter)

(Download the code here:Code)

PL/SQL Key/Value Pair Utility

Get the code: keyValueUtils.pkg

KeyValueUtils is a simple (but very useful) Key/Value pair utility that allows you to define sets (collections) of KV pairs for use in PL/SQL applications.  The utility also provides the ability to access the KV set in a query using a pipeline function (and a table function).  This gives a developer the ability to create something similar to a NoSQL type of KV pair table (on the fly).

The utility consists of:

  • A  user defined type called T_KEY_VALUE, that contains two VARCHAR2 attributes key and value.
  • A collection of T_KEY_VALUE type called KEY_VALUE_SET.
  • A PL/SQL package called keyValueUtils.

The package contains the following procedure and functions:

  • PROCEDURE addPairToCollection – Adds  a KV pair to a KV pair set (collection).
  • PROCEDURE setValue – Sets the value of KV pair in the given KV set (collection). If KV pair currently exists in the set, it will be updated.  Otherwise, a new KV pair will added to the KV set.
  • FUNCTION getValue – Retrieves the value of KV pair in a given KV Set.
  • FUNCTION pipeLineQueryKVSet – Pipeline Function for retrieving the values in the KV set via a table function in a SQL query.
  • FUNCTION parseStringToKVSet – This function will parse a parametrized string into a KV Set and return that set. Example: test1=hello,test2=world … would be parsed into two pairs ((“test1″,”Hello”),(“test2″,”World”)).

The following code sample displays all of the functionality of the utility:

SET SCAN OFF;
DECLARE
v_param_string VARCHAR2(200) := ‘p_id_number=20101001-0900-00&p_wst_code=W&p_start_date=09/10/2010 0900&p_end_date=09/30/2010 0700&p_emp_code=003509&p_emp_name=JBENNETT';
v_param_set KEY_VALUE_SET := KEY_VALUE_SET();
v_pair T_KEY_VALUE := T_KEY_VALUE();CURSOR crsrKVCursor(kv_set KEY_VALUE_SET) IS
SELECT
key,
value
FROM
TABLE(kv_set)
ORDER BY key;

BEGIN

v_param_set := keyvalueutils.parsestringtokvset(v_param_string,’&’);

DBMS_OUTPUT.PUT_LINE(‘Initial Value of p_id_number=’||keyvalueutils.getValue(v_param_set,’p_id_number’));

keyvalueutils.setValue(v_param_set,’p_id_number’,’20101001-0930-00′);

DBMS_OUTPUT.PUT_LINE(‘Access and Display values “manually”.’);
DBMS_OUTPUT.PUT_LINE(‘p_id_number=’||keyvalueutils.getValue(v_param_set,’p_id_number’));
DBMS_OUTPUT.PUT_LINE(‘p_wst_code=’||keyvalueutils.getValue(v_param_set,’p_wst_code’));
DBMS_OUTPUT.PUT_LINE(‘p_start_date=’||keyvalueutils.getValue(v_param_set,’p_start_date’));
DBMS_OUTPUT.PUT_LINE(‘p_end_date=’||keyvalueutils.getValue(v_param_set,’p_end_date’));
DBMS_OUTPUT.PUT_LINE(‘p_emp_code=’||keyvalueutils.getValue(v_param_set,’p_emp_code’));
DBMS_OUTPUT.PUT_LINE(‘p_emp_name=’||keyvalueutils.getValue(v_param_set,’p_emp_name’));
DBMS_OUTPUT.PUT_LINE(”);

v_pair.key := ‘p_new_param';
v_pair.value := ‘Hello World';

keyvalueutils.addPairtoCollection(v_param_set,v_pair);

DBMS_OUTPUT.PUT_LINE(‘Access and display values via SQL query.’);

FOR data_rec IN crsrKVCursor(v_param_set) LOOP

DBMS_OUTPUT.PUT_LINE(data_rec.key||’=’||data_rec.value);

END LOOP;

END;

The results produced by the code above are:

Initial Value of p_id_number=20101001-0900-00
Access and Display values “manually”.
p_id_number=20101001-0930-00
p_wst_code=W
p_start_date=09/10/2010 0900
p_end_date=09/30/2010 0700
p_emp_code=003509
p_emp_name=JBENNETTAccess and display values via SQL query.
p_emp_code=003509
p_emp_name=JBENNETT
p_end_date=09/30/2010 0700
p_id_number=20101001-0930-00
p_new_param=Hello World
p_start_date=09/10/2010 0900
p_wst_code=W

Trinidad 2.0.0 PPR Bug with JSF 2.0 outputText Tag

I am currently in the process of porting all of my Oracle AS 10G R2 (OC4J) applications over to WebLogic  10.3.5.  After upgrading Oracle JDeveloper to version 11.1.2 and hand converting an old ADF 10.1.3 application to Trinidad, I ran into a curios issue.  None of my components that used PPR (Partial Page Rendering) would respond to events.  Most notably, pop-up dialogs triggered  by Command Buttons, or Command Links.  The dialogs would not pop-up, but I could see the events executing in the backing beans. After a couple of weeks of tracing log files, running code through the debugger, and digging into the source code for both JSF 2.0 and Trinidad 2.0.0, I came up with with nothing.  No error messages are ever thrown.  I even filed a bug report under an existing (similar) bug report (Trinidad-1813) with the Trinidad project group (they did respond and are looking into it).  To make a long story short, I finally found the issue.  All of my screens contained a outputText tag from the JSF component library (<h:outputText value=”This will cause PPR to fail.”/>).  As soon as I replaced this tag with the outputText tag from the Trinidad component library (<tr:outputText value=”This will not cause PPR to fail.”/>), my PPR events started behaving as expected.

I hope this helps someone who may be experiencing a similar issue.  This issue did not occur with the earlier releases of Trinidad and JSF.

Automatically Grouping Query Results into Row Sets of a Specific Size

I came up with a simple formula a couple of days ago that will automatically determine which result set  a row belongs in if the results of the query need to be grouped in specific numeric sets of rows (such as sets of five rows, or sets of 10 rows, etc ..) for retrieval and display in an object such as a table widget on a web page.  These would be the sets of rows that are displayed by the ‘Next’, ‘Last’, and ‘Previous’ buttons (or arrows) on the widget.  The formula can  be applied directly in the query, or separately as a function (depends on how you are handling your query results before passing them to the widget).  Here is the formula:

Rowset# = (trunc((x-1)/y)+1) where x is equal to the selected row number and y is the number of rows per set.

If we apply this formula in a query where want the resulting rows to be grouped into sets of five (5), it would look something like this (sample query uses the standard Oracle demo EMPLOYEES table) :

As you can see, the formula is simple and (best of all)  it works.   As always, feel to shoot me an email if you have questions.

ExcelDocTypeUtils New Features: Worksheet Orientation and Repeating Column Headers

At the request of several folks, I’ve added two new features to the ExcelDocumentType and ExcelDocTypeUtils PL/SQL package:

1. The ability to specify whether or not a worksheet’s orientation is Portrait or Landscape.

2. The ability to specify whether or not column headers for a worksheet will repeat at the top of each printed page.

See the EmployeeReport demo files in the code bundle (or download the ExcelDocTypeUtils Developer Guide) for specifics.

(Download the code here:Code)

ExcelDocTypeUtils API Guide

I finally broke down and documented the ExcelDocTypeUtils PL/SQL API  in a 34 page document. Sorry about the long wait …

Here it is:  (opens in new window):ExcelDocTypeUtils API Guide

ExcelDocumentType: New Feature … Freeze Column Header Row

I  added a new feature to  the ExcelDocumentType object and ExcelDocTypeUtils package that will allow report developers to freeze the column header.  This means that the column header row will stay stationary (not scroll away) as the user scrolls through all of the data rows in the worksheet.  I have added an example of how to use this feature to the EmployeeReportDemo.sql file in the code bundle.

(Download the code here:Code)

New Open Source Offering: OWA Session Cache Utility

I have added another open source code/utility offering to the site. It’s called the OWA Session Cache Utility.

The OWA Session Cache utility is a custom add-on for MOD_PLSQL and DBMS_EPG DADs that provides the ability to maintain a stateful session in a Web PL/SQL Toolkit application. The utility has features similar to those found in Java Servlet based applications such as session cookie (holds session id), ability to set session timeout period, expired session management, the ability to store and retrieve session data using AJAX enabled JavaScript, and a garbage collector to clean up the session cache.

To find out more and/or download the code, click on the OWA Session Cache link in the menu at the top of the page, or on the same link under the “Pages” menu on the right hand side of the page.

Book Review: Oracle SQL Developer 2.1

Recently, I was given the opportunity to review a new  book published by PACKT Publishing entitled “Oracle SQL Developer 2.1“.    The book was written by Sue Harper, who works for Oracle Corporation and is a product manager for the SQL Developer tool.  I was actually excited to review the book.  I’ve been using Oracle’s SQL Developer tool on and off since it’s “Project Raptor” days.  I had yet to come across a definitive and all encompassing guide to using SQL Developer, until the UPS driver delivered the book into my hands a few weeks ago.

The book does a fantastic job of covering everything from basic installation and configuration to data modeling to  creating your own custom extensions to SQL Developer.   I was happy to see that it was written for all audiences from beginners to advanced users. Unlike a lot of “how to” books written about Oracle tools, this book isn’t packed full of fluff.  The book contains  13 chapters in 459 pages and covers all of the key features of the tool and more.  The chapters are concise and contain well thought out examples and illustrations. There are even small sections within topic areas  that provide what I call  “insider tips” containing useful tidbits of information.

I took this book to work with me over a two week period to verify it’s usefulness as a  day to day tool.  In that two week period, I read the book cover to cover and actually learned quite few new things.  I was most impressed with the coverage of new features such as the new data modeling component, and integration with Oracle APEX … new tricks for old dogs!   Chapter13, which covers the data migration feature, is an absolute gem.   The data migration feature is (in my opinion) one of the  most valuable and useful  components of SQL Developer.  I have actually used this feature to migrate SQL Server databases (structure and data) in their entirety to Oracle.   Every step of the migration can be accomplished using SQL Developer, and chapter 13 covers the process very well.

The bottom line … I give this book two thumbs up!  It’s definitely a must have for your tech library if you are a user of Oracle SQL Developer.

ExcelDocumentType: Create Hyperlinks between Worksheets

This post  introduces a new feature in the ExcelDocTypeUtils PL/SQL package that allows a developer to create hyper linked cell data that points from one worksheet to another.   The feature is implemented by a  new function called createWorksheetLink.  The function takes two parameters: the cell data, and the name of the target worksheet.  The function is called from the dynamic SQL statement that is passed as part of a worksheet data object (ExcelDocTypeUtils.T_WORKSHEET_DATA).

The following code sample demonstrates how this feature can be used to generate a summary worksheet containing a hyper linked list of department names. Each hyper linked department name points to a worksheet containing employee data for that particular department.    The department related worksheets each contain a hyper link back to the summary worksheet that contains the department listings. The data for this demo comes from Oracle HR demo schema.

CREATE OR REPLACE PROCEDURE ExcelHyperlinkHR
IS

— This cursor will be used to generate the individual Department worksheets.
CURSOR crsrDepatmentWS IS
SELECT
department_id,
SUBSTR(UPPER(DEPARTMENT_NAME),1,20) sheet_name
FROM
hr.departments dp
WHERE
EXISTS (select ‘x’ from HR.employees where DEPARTMENT_ID = dp.department_id)
ORDER BY
dp.department_name;

— Base SQL statement for loading each departmental worksheet
v_employee_base_sql VARCHAR2(500) := ‘SELECT last_name||”, ”||first_name,email,phone_number,to_char(hire_date,”MM/DD/YYYY”) ‘||
‘FROM hr.employees ‘||
‘WHERE department_id = :id ‘||
‘ORDER BY last_name,first_name';

— SQL statement based on v_employee_base_sql, but with the :id replaced with department_id.
v_employee_dept_sql VARCHAR2(500) := NULL;

— SQL statement used to generate hyperlinked worksheet listing all departments containing employees
v_department_sql    VARCHAR2(500) := ‘SELECT ExcelDocTypeUtils.createWorksheetLink(UPPER(department_name),department_name) ‘||
‘FROM hr.departments dp ‘||
‘WHERE EXISTS (select ”x” from HR.employees where DEPARTMENT_ID = dp.department_id) ‘||
‘ORDER BY DP.DEPARTMENT_NAME ‘;

excelReport         ExcelDocumentType := ExcelDocumentType();
documentArray       ExcelDocumentLine := ExcelDocumentLine();

v_worksheet_rec     ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;
v_worksheet_array   ExcelDocTypeUtils.WORKSHEET_TABLE  := ExcelDocTypeUtils.WORKSHEET_TABLE();

v_sheet_title       ExcelDocTypeUtils.T_SHEET_TITLE := NULL;

— Objects for Defining Document Styles (Optional)

v_style_def         ExcelDocTypeUtils.T_STYLE_DEF := NULL;
v_style_array       ExcelDocTypeUtils.STYLE_LIST  := ExcelDocTypeUtils.STYLE_LIST();

BEGIN

— Add Styles

v_style_def.p_style_id     := ‘HyperlinkStyle';
v_style_def.p_text_color   := ‘Blue';

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyle';
v_style_def.p_align_horizontal  := ‘Center';
v_style_def.p_bold              := ‘Y';
v_style_def.p_text_color        := ‘Black';

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyleLink';
v_style_def.p_align_horizontal  := ‘Center';
v_style_def.p_bold              := ‘Y';
v_style_def.p_text_color        := ‘Blue';

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

— Define Sheet Title
v_sheet_title.title      := ‘Employee Listing By Department';
v_sheet_title.cell_span  := ‘4’;
v_sheet_title.style      := ‘SheetTitleStyle';

v_worksheet_rec.query             := v_department_sql;
v_worksheet_rec.worksheet_name    := ‘Departments';
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 1;
v_worksheet_rec.col_width_list    := ’40′;
v_worksheet_rec.col_header_list   := ‘Department';
v_worksheet_rec.col_datatype_list := ‘String';
v_worksheet_rec.col_style_list    := ‘HyperlinkStyle';

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

— Construct Department worksheets

FOR data_rec IN crsrDepatmentWS LOOP

v_worksheet_rec      := NULL;
v_employee_dept_sql  := REPLACE(v_employee_base_sql,':id’,data_rec.department_id);

v_sheet_title.title      := ExcelDocTypeUtils.createWorksheetLink(‘Departments’,’Back to Department Listing’);
v_sheet_title.cell_span  := ‘3’;
v_sheet_title.style      := ‘SheetTitleStyleLink';

v_worksheet_rec.query             := v_employee_dept_sql;
v_worksheet_rec.worksheet_name    := data_rec.sheet_name;
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 4;
v_worksheet_rec.col_width_list    := ‘50,20,20,15’;
v_worksheet_rec.col_header_list   := ‘Name,Email,Phone Number,Hire Date';
v_worksheet_rec.col_datatype_list := ‘String,String,String,String';

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

END LOOP;

excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array,v_style_array);

excelReport.displayDocument;

END;

This video clip demonstrates the output from the source code.

Download the code for the procedure here:ExcelHyperlinkHR.sql.
(Note … you must have the latest version of the ExcelDocumentType and ExcelDocTypeUtils)

PL/SQL Phases of the Moon

At work this past week, we kicked off a large Predictive Analytics and BI project.  The predictive analytics piece will use various factors and statistical models to try and pre-determine likely areas of criminal activity around our city.  Among those factors … the current and historical phase of the moon (based on date).  The Oracle RDBMS contains a plethora of date and calendar related functions, but determination of the moon’s phase is not among them.   Since I am the resident Oracle “guru”, the gauntlet was thrown down in front of me to come up with such a function.  Not being an astronomer or mathematician, I picked up the gauntlet and ran straight to Google!  I didn’t have any luck finding a PL/SQL based moon phase calculator.  However, I did find a very nice algorithm that I was able to translate into a PL/SQL function at this location: lunar phase calculator.  This particular algorithm uses the Julian date and the number of days in the lunar cycle to break the phases into their eight main states: New Moon (1),  Waxing Crescent (2), First Quarter (3), Waxing Gibbous (4), Full Moon (5), Waning Gibbous (6), Last Quarter (7), and Waning Crescent (8).    Based on this information, I actually created two functions.  One function generates the numeric phase, and the other generates the text version of the phase.  Here is the code for the two functions:

CREATE OR REPLACE FUNCTION MOON_PHASE_NUMERIC(p_date DATE := SYSDATE) RETURN NUMBER
IS

v_age NUMBER(7,5) := 0;

v_phase NUMBER(1) := 0;

BEGIN

v_age := TRUNC(MOD(((TO_NUMBER(to_char(p_date,’J’))-2451550.1)/29.530588853) * 29.53,29.53),5);

CASE

WHEN v_age < 1.84566 THEN v_phase := 1;
WHEN v_age < 5.53699 THEN v_phase := 2;
WHEN v_age < 9.22831 THEN v_phase := 3;
WHEN v_age < 12.91963 THEN v_phase := 4;
WHEN v_age < 16.61096 THEN v_phase := 5;
WHEN v_age < 20.30228 THEN v_phase := 6;
WHEN v_age < 23.99361 THEN v_phase := 7;
WHEN v_age < 27.68493 THEN v_phase := 8;
ELSE v_phase := 1;

END CASE;

RETURN v_phase;

END;

CREATE OR REPLACE FUNCTION MOON_PHASE_TEXT(p_date DATE := SYSDATE) RETURN VARCHAR2
IS

v_phase_ind NUMBER(1) := 0;

v_phase VARCHAR2(30) := 0;

BEGIN

v_phase_ind := MOON_PHASE_NUMERIC(p_date);

CASE

WHEN v_phase_ind = 1 THEN v_phase := ‘New Moon';
WHEN v_phase_ind = 2 THEN v_phase := ‘Waxing Crescent';
WHEN v_phase_ind = 3 THEN v_phase := ‘First Quarter';
WHEN v_phase_ind = 4 THEN v_phase := ‘Waxing Gibbous';
WHEN v_phase_ind = 5 THEN v_phase := ‘Full Moon';
WHEN v_phase_ind = 6 THEN v_phase := ‘Waning Gibbous';
WHEN v_phase_ind = 7 THEN v_phase := ‘Last Quarter';
WHEN v_phase_ind = 8 THEN v_phase := ‘Waning Crescent ‘;
ELSE v_phase := ‘New Moon';

END CASE;

RETURN v_phase;

END;

If we run these functions and supply them with a date, we will get the correct phase:

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Dec 20 09:58:46 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select MOON_PHASE_NUMERIC(’25-DEC-2009′) from dual;

MOON_PHASE_NUMERIC(’25-DEC-2009′)
———————————
3

SQL> select MOON_PHASE_TEXT(’25-DEC-2009′) from dual;

MOON_PHASE_TEXT(’25-DEC-2009′)
———————————————————————-
First Quarter

I verified the results on several moon phase calculator websites using various dates (past, present, and future).  Combine these functions with some graphical representations, and you could create a nice little widget for your website.

FusionCharts PL/SQL API Optimized for Performance

Thanks to Rune Langtind of the U.K. for optimizing the StreamDataSet procedure in the FusionFlashCharts PL/SQL package … the charts render 100% faster now.  Prior to his change, charts with large data sets took a very long time to render.

If you haven’t tried this API and you are a Fusion Charts user, give it a try (see the FusionCharts PL/SQL API link on the top menu or the right side menu).

Convert Oracle Date Format to Excel Date Format

MS Excel uses a numeric format, called Serial Date format, when storing and dealing with dates and times.   Serial date is calculated by taking a given date and determining the number of days that have passed since that date and 01-Jan-1900 (actually 00-Jan1900, but that’s a story for another time).  Why is this important to an Oracle developer?  If you are using a tool such as the ExcelDocumentType to generate an Excel document with PL/SQL and you are trying to apply an Excel style to an Oracle Date data type … it will not work.  The Oracle Date column must be converted to serial format before Excel will recognize it as a date and apply any kind of special date formatting to it.  Luckily, the conversion formula is simple.  The following function will take an Oracle Date data type and convert it to an Excel Date data type (or serial date):

CREATE OR REPLACE FUNCTION convertOracleToSerialDate(p_date DATE) RETURN NUMBER
IS

v_date_differential NUMBER(1) := 0;

BEGIN

IF TRUNC(p_date) >= TO_DATE(’01-MAR-1900′,’DD-MON-YYYY’) THEN

v_date_differential := 1;

END IF;

RETURN ((trunc(p_date) + v_date_differential) – (TO_DATE(’01-JAN-1900′,’DD-MON-YYYY’)-1));

END;
/

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Nov 23 22:31:54 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select convertOracleToSerialDate(’05-MAY-1968′) from dual;

CONVERTORACLETOSERIALDATE(’05-MAY-1968′)
—————————————-
24963

If we pass in the value ’05-MAY-1968′ to the function above, we will get the value 24963.  Now, if we place this value in an Excel Spreadsheet and format the cell as a Date type, it will be converted to ‘5/5/1968′ (or what ever date format you choose for that cell or cells).

The reverse is also possible.  If we have a Serial Date value, and we want to convert it to an Oracle Date type, we would use a function such as:

CREATE OR REPLACE FUNCTION convertSerialToOracleDate(p_serial_date NUMBER) RETURN DATE
IS

v_orcl_date DATE := NULL;

BEGIN

v_orcl_date := (((TO_DATE(’01-JAN-1900′,’DD-MON-YYYY’)) + p_serial_date-1));

IF TRUNC(v_orcl_date) > TO_DATE(’01-MAR-1900′,’DD-MON-YYYY’) THEN

v_orcl_date := v_orcl_date – 1;

END IF;

RETURN v_orcl_date;

END;
/

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Nov 23 22:45:44 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select convertSerialToOracleDate(24963) from dual;

CONVERTSE
———
05-MAY-68

SQL>

If we take the result we obtained from the first function, 24963, and apply the second formula (using SQL*Plus, SQL Developer, TOAD, etc), we get …05-May-68.

Oracle APEX: Easily Integrate Custom Web PL/SQL Procedures and Packages

In this post, we look at a code authorization utility consisting of a schema, a table, and a PL/SQL package that allows developers to easily integrate their own custom web PL/SQL code into an Oracle APEX application.  The utility works in conjunction with APEX’s built-in security function, wwv_flow_epg_include_mod_local, that determines if a given procedure is authorized to be executed by the APEX PL/SQL DAD.  The security function resides in Oracle APEX home schema (in this case, APEX_030200), and must be customized to allow custom (external) procedures.  Most examples of customizing the security function demonstrate the use of an IF statement that grows as new procedures are added to the list of authorized procedures.  Each time a new procedure is added, the function must be edited and then compiled.  The utility presented here replaces that IF statement with a single function and allows the developer to register an entire package or just a single procedure. Compile and forget …

The Utility …

The custom procedure authorization utility consists of the following pieces:

  • A database schema called APEX_ADD_ONS –  schema that holds the components related to the authorization utility (tables, packages, etc …).
  • A table called APEX_ALLOWED_EXECUTE – table that holds the names and types of all authorized procedures and packages.
  • A PL/SQL package called APEX_ADD_ON_UTILS – contains all of the constants, functions, and procedures required to add, removes, and validate a package or procedure.
  • The customized version of APEX_030200.wwv_flow_epg_include_mod_local.

The APEX_ALLOWED_EXECUTE table is defined as follows:

/*
Table used by the APEX_030200.wwv_flow_epg_include_mod_local function to
verify procedures that can be called thru the promary APEX
PL/SQL DAD.

*/

CREATE TABLE APEX_ALLOWED_EXECUTE(
code_name VARCHAR2(200) NOT NULL,
code_type VARCHAR2(10) NOT NULL
)
/

CREATE OR REPLACE TRIGGER UPPER_CASES_CODE
BEFORE INSERT OR UPDATE ON APEX_ALLOWED_EXECUTE
FOR EACH ROW
DECLARE
BEGIN

:NEW.CODE_NAME := UPPER(:NEW.CODE_NAME);
:NEW.CODE_TYPE := UPPER(:NEW.CODE_TYPE);

END;
/

CREATE PUBLIC SYNONYM APEX_ALLOWED_EXECUTE FOR APEX_ALLOWED_EXECUTE;

/* This grant should be to the schema that houses APEX … older versions were called FLOWS_<version>*/
GRANT SELECT ON APEX_ALLOWED_EXECUTE TO APEX_030200;

The APEX_ADD_ON_UTILS PL/SQL package is defined as follows:

/*

APEX_ADD_ONS Utility package.
Package contains utilities that allow for

*/

CREATE OR REPLACE PACKAGE APEX_ADD_ON_UTILS AS

/*————————————————-*/
/* Constants used to Identify Executable Code Type */
/*————————————————-*/

CODE_TYPE_PROCEDURE CONSTANT VARCHAR2(9)  := ‘PROCEDURE';
CODE_TYPE_PACKAGE   CONSTANT VARCHAR2(10) := ‘PACKAGE';

/*———————————————*/
/* Make sure to add name as it will be called. */
/* i.e.  <schema>.<package>.<proc> or          */
/* <schema>.<proc> or just plain old <proc>     */
/*———————————————*/
PROCEDURE addAllowedExecute(p_name VARCHAR2 := NULL,p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE);

PROCEDURE removeAllowedExecutable(p_name VARCHAR2 := NULL,p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE);

/*——————————————————*/
/* This function should be embedded within the          */
/* APEX_030200.wwv_flow_epg_include_mod_local function. */
/*——————————————————*/

FUNCTION  isAllowedExecute(p_name VARCHAR2 := NULL) RETURN BOOLEAN;

END;
/
sho err;

/******************************************************************************************************************************************************/
/************************************************************** PACKAGE BODY **************************************************************************/
/******************************************************************************************************************************************************/

CREATE OR REPLACE PACKAGE BODY APEX_ADD_ON_UTILS AS

PROCEDURE addAllowedExecute(p_name      VARCHAR2 := NULL,
p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE)
IS

v_sql_chk VARCHAR2(200) := ‘SELECT count(”x”) FROM apex_allowed_execute WHERE upper(code_name) = :code_name AND code_type= :code_type';
v_chk_val NUMBER(5)     := 0;

v_sql_ins VARCHAR2(200) := ‘INSERT INTO apex_allowed_execute(code_name,code_type) VALUES (:code_name,:code_type)';

BEGIN

EXECUTE IMMEDIATE v_sql_chk INTO v_chk_val USING UPPER(p_name),UPPER(p_code_type);

IF v_chk_val = 0 THEN

EXECUTE IMMEDIATE v_sql_ins USING UPPER(p_name),UPPER(p_code_type);
COMMIT;

END IF;

END;

/*====================================================================================================================================*/

PROCEDURE removeAllowedExecutable(p_name      VARCHAR2 := NULL,
p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE)
IS

v_sql VARCHAR2(200) := ‘DELETE FROM apex_allowed_execute WHERE code_name = :code_name AND code_type := :code_type';

BEGIN

BEGIN
EXECUTE IMMEDIATE v_sql USING UPPER(p_name),UPPER(p_code_type);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

END;

/*===================================================================================================================================*/

FUNCTION  isAllowedExecute(p_name VARCHAR2 := NULL) RETURN BOOLEAN
IS

CURSOR crsrCheckProc(cv_code_name VARCHAR2,
cv_code_type VARCHAR2) IS
SELECT
‘x’
FROM
apex_allowed_execute
WHERE
code_name = cv_code_name
AND    code_type = cv_code_type;

v_package_name VARCHAR2(200) := NULL;

BEGIN

— Check for matching procedure name

FOR data_rec IN crsrCheckProc(UPPER(p_name),CODE_TYPE_PROCEDURE) LOOP

RETURN TRUE;

END LOOP;

— Try to isolate package name from <schema>.<package>.<code> or <package>.<code> or <package>
IF INSTR(p_name,’.’,1,2) > 0 THEN

v_package_name:= SUBSTR(p_name,1,INSTR(p_name,’.’,1,2)-1);

ELSIF INSTR(p_name,’.’,1,1) > 0 THEN

v_package_name := SUBSTR(p_name,1,INSTR(p_name,’.’,1,1)-1);

ELSE

v_package_name := p_name;

END IF;

FOR data_rec IN crsrCheckProc(UPPER(v_package_name),CODE_TYPE_PACKAGE) LOOP

RETURN TRUE;

END LOOP;

RETURN FALSE;

END;

/*=============*/
/* END PACKAGE */
/*=============*/

END;
/
sho err;

CREATE PUBLIC SYNONYM APEX_ADD_ON_UTILS FOR APEX_ADD_ON_UTILS;
GRANT EXECUTE ON APEX_ADD_ON_UTILS TO APEX_030200;

The modifed APEX_030200.wwv_flow_epg_include_mod_local function looks like:

/*

Created a schema called APEX_ADD_ONS and a packaged function called APEX_ADD_ON_UTILS.isAllowedExecute
that compares the passed procedure name with a table stored list of allowed packages and procedures.

Run this script as SYS or other user that can alter the session and set it to the owning
schema for Oracle APEX.

*/

alter session set current_schema=APEX_030200;

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)return boolean
IS
BEGIN

IF APEX_ADD_ONS.APEX_ADD_ON_UTILS.isAllowedExecute(upper(procedure_name)) THEN

RETURN TRUE;

END IF;

RETURN FALSE;

END;
/

How it works …

We’ve briefly taken a look at all of the pieces of the utility, but how does it work?  The following scenario demonstrates how to add a package (or procedure … same process) as  authorized to be executed in an Oracle APEX application.

First, let’s look at what happens when we add a PL/SQL region to a page containing a reference to a PL/SQL packaged procedure that IS NOT registered with the utility as being authorized.

ApexCustomWebPLSQL1

The following code was added to the PL/SQL region displayed above:

IF NOT APEX_ADD_ON_UTILS.isAllowedExecute(‘HRChartsDemo.dataSet’)OR
NOT APEX_ADD_ON_UTILS.isAllowedExecute(‘FusionFlashCharts’) THEN

htp.p(‘<div style=”color:red”><b>Unauthorized Stored Procedure Call</b></div>’);

END IF;

htp.p(‘<div id=”OPC_Div”>’);

FusionFlashCharts.addChart(p_chart_id     =>’OfficesPerCountry’,
p_chart_type   => FusionFlashCharts.Column3D,
p_chart_width  =>’600′,
p_chart_height =>’400′,
p_data_url     =>’HRChartsDemo.dataSet?p_set_code=OPC’);

htp.p(‘</div>’);

In the code sample above, we execute a packaged procedure called FusionFlashCharts.addChart that will generate a Fusion Flash Charts  3D bar chart.  The procedure produces the following HTML:

<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase=http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" width="800" height="600" id="OfficesPerCountry" >
<param id="movie_OfficesPerCountry" name="movie" value="FusionFlashCharts.swf?p_chart_code=CL3D" />
<param id="FlashVars_OfficesPerCountry" name="FlashVars" value="&dataURL=HRChartsDemo.dataSet%3Fp_set_code=OPC&chartWidth=800&chartHeight=600">
<param id="quality_OfficesPerCountry" name="quality" value="high" />
<embed id="embed_OfficesPerCountry" src="FusionFlashCharts.swf?p_chart_code=CL3D" flashVars="&dataURL=HRChartsDemo.dataSet%3Fp_set_code=OPC&chartWidth=800&chartHeight=600" quality="high" width="800" height="600" name="OfficesPerCountry" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" />
</object>

In the HTML above, there are references to two Web PL/SQL packaged procedures that must be executed through the APEX DAD:

  • FusionFlashCharts.swf – Streams the requested Flash based chart object
  • HRChartsDemo.dataSet – Generates the XML data set required to populate the chart.

Since we have not yet registered these packaged procedures with APEX using the wwv_flow_epg_include_mod_local function, we see the following output when executing the page:

ApexCustomWebPLSQL2

Notice in the image above that no chart was displayed, and our error message indicating that references to unauthorized procedures have been made is being displayed.  The <object> tag was unable to load the .swf file.  It is also unable to execute the code that generates the data set for the chart.  How can we fix this issue?  We have to either authorize the two offending package procedures, or simply authorize the packages themselves.  Here is the code we would execute to accomplish the task:

BEGIN
APEX_ADD_ON_UTILS.addAllowedExecute(‘FusionFlashCharts’,APEX_ADD_ON_UTILS.CODE_TYPE_PACKAGE);
APEX_ADD_ON_UTILS.addAllowedExecute(‘HRChartsDemo’,APEX_ADD_ON_UTILS.CODE_TYPE_PACKAGE);
END;
/

The code above places references to the two packages into the APEX_ALLOWED_EXECUTE table.  If we refresh the APEX page after executing the code, we will see that the chart is rendered:

ApexCustomWebPLSQL3

** Note ** We still have grant execute on any package or procedure to the user (or schema) assigned to the APEX PL/SQL DAD.  In this case, the user is ANONYMOUS.

Wrapping it up …

Using the method described in this post, we can easily add (and remove) stand alone and packaged procedures the “Oracle APEX execution authorization list” without having to edit and compile the APEX_030200.wwv_flow_epg_include_mod_local function.   If you would like the code for creating this utility, you can get it here: ApexAddOns.zip.  If you have any questions, please feel free to contact me.

Exposing Web Services as Database Views

In recent years, Web Services have become the defacto standard in passing  data between applications.   They make communication between disparate computer languages and architectures relatively simple.  For the most part, we tend to think of web services in terms of traditional application development (Java, .NET, Ruby, PHP, etc …) and inter-process communication.  What if we looked at them from another perspective?  What if we could easily integrate web services into the database?  Make them consumable by Business Intelligence tools, and Data Mining engines WITHOUT having to change the traditional manner in which these tools access data … the standard table and view structure.  SQL is much less complicated than XPath (my opinion)! The remainder of this post is dedicated to demonstrating how to expose a web service as a database view using only resources found in the Oracle database. The method we will explore for exposing a web service as a view utilizes several built-in Oracle database features: PL/SQL (built in packages and the basic fare), XMLTable,  XMLType, and XQuery.

Consuming the Web Service

First and foremost … we have to consume or access a web service before we can expose it’s results in view form.  Web services use a protocol called SOAP (Simple Object Access Protocol).  In order to access the web service, we need to be able to construct, send, and receive SOAP messages.    Oracle provides a built-in PL/SQL package called UTL_HTTP that makes a simple matter to construct a SOAP API.  Tim Hall has created a simple and easy to use PL/SQL based SOAP API and I have created a utility/API which sits on top of his API that makes this step very simple.  Please see my previous post, PL/SQL Web Service Utility: Accessing Web Services with PL/SQL is Simple! , for all the details and code.  The examples in this post will utilize the code and methods introduced in that article.

Taking the SOAP Response and Creating a View …

The data that is received in response to a SOAP query is returned as an XML document.  Using a few other built-in Oracle features (XMLTable, XQuery, and XMLType  … note that XQuery is not available with Oracle 10g XE), we can query that XML data with a SQL statement.   At this point, creating the view is simple.  All we need is that SQL statement and we are in business!  The following two examples demonstrate the processes.  One example demonstrates this method on a simple XML structure returned by a web service provider, and the other example uses a more complex XML structure.  Both of the web services used in these examples are public web services from out in “the wild” (found them on Seekda! ).

Example 1 … Yesterday’s MLB Scores

This example creates a view based on a web service that returns all of the MLB (Major League Baseball) scores for a given date.   I could have used stock data … but what’s the fun in that?  The XML returned by this service has a flat structure, making it very uncomplicated to map elements to query columns using the XMLTable function.

Step 1. Create a PL/SQL function (packaged or stand alone) that returns the Web Service results as an XMLType object.

CREATE OR REPLACE FUNCTION getBaseballScoresWS(p_date VARCHAR2 := TO_CHAR(SYSDATE,’DD MON YYYY’))
RETURN XMLType
ISv_service_def WebServiceUtils.SERVICE_DEFINITION_TYPE;
v_param       WebServiceUtils.service_param_type;
v_param_list  WebServiceUtils.service_param_list := WebServiceUtils.service_param_list();

BEGIN

v_service_def.service_name       :=  ‘MLBScoresStr';
v_service_def.service_url        :=  ‘http://www.streamingscores.com/livescoresservice.asmx';
v_service_def.service_action_url :=  ‘http://StreamingScores.com/MLBScoresStr';
v_service_def.service_ns         :=  ‘http://StreamingScores.com';

v_param.name      := ‘date';
v_param.data_type := ‘s:string';
v_param.value     := p_date;

WebServiceUtils.addParamToCollection(v_param_list,v_param);

v_service_def.service_params := v_param_list;

RETURN XMLType(WebServiceUtils.executeWebService(v_service_def));

END;

The function returns the full  SOAP response with the following structure:

<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<soap:Body>
<MLBScoresStrResponse xmlns=”http://StreamingScores.com”>
<MLBScoresStrResult>
<Game>
<SportID>1</SportID>
<GameId>18931</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Twins</AwayTeam>
<AwayTeamAbbrev>MIN</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Indians</HomeTeam>
<HomeTeamAbbrev>CLE</HomeTeamAbbrev>
<AwayWins>68</AwayWins>
<AwayLosses>68</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>60</HomeWins>
<HomeLosses>76</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>1</AwayScore>
<HomeScore>3</HomeScore>
<AwayPitcher>N. Blackburn</AwayPitcher>
<HomePitcher>D. Huff</HomePitcher>
<Inning>Final</Inning>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:05 PM</StartTime>
<StartTimeUTC>2009-09-06T17:05:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18932</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Yankees</AwayTeam>
<AwayTeamAbbrev>NYY</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Blue Jays</HomeTeam>
<HomeTeamAbbrev>TOR</HomeTeamAbbrev>
<AwayWins>87</AwayWins>
<AwayLosses>49</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>60</HomeWins>
<HomeLosses>75</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>8</AwayScore>
<HomeScore>14</HomeScore>
<AwayPitcher>S. Mitre</AwayPitcher>
<HomePitcher>B. Tallet</HomePitcher>
<Inning>Top 8th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>1</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:07 PM</StartTime>
<StartTimeUTC>2009-09-06T17:07:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18924</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Cubs</AwayTeam>
<AwayTeamAbbrev>CHC</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Mets</HomeTeam>
<HomeTeamAbbrev>NYM</HomeTeamAbbrev>
<AwayWins>68</AwayWins>
<AwayLosses>66</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>61</HomeWins>
<HomeLosses>75</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>2</AwayScore>
<HomeScore>4</HomeScore>
<AwayPitcher>R. Wells</AwayPitcher>
<HomePitcher>M. Pelfrey</HomePitcher>
<Inning>Final</Inning>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:10 PM</StartTime>
<StartTimeUTC>2009-09-06T17:10:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18925</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Cardinals</AwayTeam>
<AwayTeamAbbrev>STL</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Pirates</HomeTeam>
<HomeTeamAbbrev>PIT</HomeTeamAbbrev>
<AwayWins>81</AwayWins>
<AwayLosses>56</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>53</HomeWins>
<HomeLosses>81</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>5</AwayScore>
<HomeScore>4</HomeScore>
<AwayPitcher>J. Piñeiro</AwayPitcher>
<HomePitcher>P. Maholm</HomePitcher>
<Inning>Bot 9th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:35 PM</StartTime>
<StartTimeUTC>2009-09-06T17:35:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18926</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Marlins</AwayTeam>
<AwayTeamAbbrev>FLA</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Nationals</HomeTeam>
<HomeTeamAbbrev>WAS</HomeTeamAbbrev>
<AwayWins>72</AwayWins>
<AwayLosses>64</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>46</HomeWins>
<HomeLosses>90</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>4</AwayScore>
<HomeScore>3</HomeScore>
<AwayPitcher>A. Sánchez</AwayPitcher>
<HomePitcher>J. Martin</HomePitcher>
<Inning>Bot 9th</Inning>
<Runners>1</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:35 PM</StartTime>
<StartTimeUTC>2009-09-06T17:35:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18927</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Reds</AwayTeam>
<AwayTeamAbbrev>CIN</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Braves</HomeTeam>
<HomeTeamAbbrev>ATL</HomeTeamAbbrev>
<AwayWins>62</AwayWins>
<AwayLosses>73</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>70</HomeWins>
<HomeLosses>66</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>2</AwayScore>
<HomeScore>2</HomeScore>
<AwayPitcher>J. Cueto</AwayPitcher>
<HomePitcher>T. Hudson</HomePitcher>
<Inning>Top 10th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>2</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:35 PM</StartTime>
<StartTimeUTC>2009-09-06T17:35:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18933</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Rangers</AwayTeam>
<AwayTeamAbbrev>TEX</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Orioles</HomeTeam>
<HomeTeamAbbrev>BAL</HomeTeamAbbrev>
<AwayWins>76</AwayWins>
<AwayLosses>59</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>55</HomeWins>
<HomeLosses>81</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>0</AwayScore>
<HomeScore>7</HomeScore>
<AwayPitcher>D. Holland</AwayPitcher>
<HomePitcher>J. Guthrie</HomePitcher>
<Inning>Top 9th</Inning>
<Runners>2,3</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>2</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:35 PM</StartTime>
<StartTimeUTC>2009-09-06T17:35:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18934</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Tigers</AwayTeam>
<AwayTeamAbbrev>DET</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Rays</HomeTeam>
<HomeTeamAbbrev>TB</HomeTeamAbbrev>
<AwayWins>74</AwayWins>
<AwayLosses>61</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>72</HomeWins>
<HomeLosses>63</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>5</AwayScore>
<HomeScore>3</HomeScore>
<AwayPitcher>E. Jackson</AwayPitcher>
<HomePitcher>W. Davis</HomePitcher>
<Inning>Top 9th</Inning>
<Runners>3</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>2</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 1:38 PM</StartTime>
<StartTimeUTC>2009-09-06T17:38:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18928</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Phillies</AwayTeam>
<AwayTeamAbbrev>PHI</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Astros</HomeTeam>
<HomeTeamAbbrev>HOU</HomeTeamAbbrev>
<AwayWins>77</AwayWins>
<AwayLosses>56</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>65</HomeWins>
<HomeLosses>70</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>3</AwayScore>
<HomeScore>4</HomeScore>
<AwayPitcher>C. Hamels</AwayPitcher>
<HomePitcher>B. Norris</HomePitcher>
<Inning>Top 8th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 2:05 PM</StartTime>
<StartTimeUTC>2009-09-06T18:05:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18929</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Giants</AwayTeam>
<AwayTeamAbbrev>SF</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Brewers</HomeTeam>
<HomeTeamAbbrev>MIL</HomeTeamAbbrev>
<AwayWins>75</AwayWins>
<AwayLosses>61</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>65</HomeWins>
<HomeLosses>70</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>1</AwayScore>
<HomeScore>1</HomeScore>
<AwayPitcher>J. Sánchez</AwayPitcher>
<HomePitcher>B. Looper</HomePitcher>
<Inning>Top 8th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 2:05 PM</StartTime>
<StartTimeUTC>2009-09-06T18:05:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18935</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Red Sox</AwayTeam>
<AwayTeamAbbrev>BOS</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>White Sox</HomeTeam>
<HomeTeamAbbrev>CHW</HomeTeamAbbrev>
<AwayWins>78</AwayWins>
<AwayLosses>57</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>68</HomeWins>
<HomeLosses>69</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>3</AwayScore>
<HomeScore>0</HomeScore>
<AwayPitcher>J. Lester</AwayPitcher>
<HomePitcher>J. Danks</HomePitcher>
<Inning>Mid 7th</Inning>
<Runners/>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>3</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 2:05 PM</StartTime>
<StartTimeUTC>2009-09-06T18:05:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18936</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Angels</AwayTeam>
<AwayTeamAbbrev>LAA</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Royals</HomeTeam>
<HomeTeamAbbrev>KC</HomeTeamAbbrev>
<AwayWins>80</AwayWins>
<AwayLosses>54</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>51</HomeWins>
<HomeLosses>84</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>7</AwayScore>
<HomeScore>2</HomeScore>
<AwayPitcher>J. Saunders</AwayPitcher>
<HomePitcher>L. Hochevar</HomePitcher>
<Inning>Bot 6th</Inning>
<Runners>1,2,3</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>1</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 2:10 PM</StartTime>
<StartTimeUTC>2009-09-06T18:10:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18930</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>D’Backs</AwayTeam>
<AwayTeamAbbrev>ARZ</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Rockies</HomeTeam>
<HomeTeamAbbrev>COL</HomeTeamAbbrev>
<AwayWins>61</AwayWins>
<AwayLosses>76</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>76</HomeWins>
<HomeLosses>60</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>1</AwayScore>
<HomeScore>4</HomeScore>
<AwayPitcher>Y. Petit</AwayPitcher>
<HomePitcher>J. De La Rosa</HomePitcher>
<Inning>Bot 3rd</Inning>
<Runners>1,2</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>2</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 3:10 PM</StartTime>
<StartTimeUTC>2009-09-06T19:10:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>18937</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Mariners</AwayTeam>
<AwayTeamAbbrev>SEA</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Athletics</HomeTeam>
<HomeTeamAbbrev>OAK</HomeTeamAbbrev>
<AwayWins>72</AwayWins>
<AwayLosses>65</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>60</HomeWins>
<HomeLosses>76</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>1</AwayScore>
<HomeScore>0</HomeScore>
<AwayPitcher>D. Fister</AwayPitcher>
<HomePitcher>G. González</HomePitcher>
<Inning>Bot 1st</Inning>
<Runners>1,2</Runners>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>1</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 4:05 PM</StartTime>
<StartTimeUTC>2009-09-06T20:05:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
<Game>
<SportID>1</SportID>
<GameId>19401</GameId>
<AwayTeamID>0</AwayTeamID>
<AwayTeam>Padres</AwayTeam>
<AwayTeamAbbrev>SD</AwayTeamAbbrev>
<HomeTeamID>0</HomeTeamID>
<HomeTeam>Dodgers</HomeTeam>
<HomeTeamAbbrev>LAD</HomeTeamAbbrev>
<AwayWins>60</AwayWins>
<AwayLosses>77</AwayLosses>
<AwayTies>0</AwayTies>
<HomeWins>81</HomeWins>
<HomeLosses>56</HomeLosses>
<HomeTies>0</HomeTies>
<AwayRank>0</AwayRank>
<HomeRank>0</HomeRank>
<AwayScore>0</AwayScore>
<HomeScore>0</HomeScore>
<AwayPitcher>T. Stauffer</AwayPitcher>
<HomePitcher>H. Kuroda</HomePitcher>
<Inning>0</Inning>
<Balls>0</Balls>
<Strikes>0</Strikes>
<Outs>0</Outs>
<TimeRemaining>0</TimeRemaining>
<StoppageTime>0</StoppageTime>
<StartTime>9/06 8:10 PM</StartTime>
<StartTimeUTC>2009-09-07T00:10:00Z</StartTimeUTC>
<TimeZoneOffset>240</TimeZoneOffset>
<TimeTBD>false</TimeTBD>
<HasTip>false</HasTip>
</Game>
</MLBScoresStrResult>
</MLBScoresStrResponse>
</soap:Body>
</soap:Envelope>

Step 2. Create a Query and View from the Resulting XML

Now that we have the XML, we use the XMLTable function and an XQuery expression to create a query and define a view based on this query (Notice the use of the namespace below …  if it’s specified in the XML, it must be used when parsing it.):

CREATE OR REPLACE VIEW MLB_SCORES_VIEW AS
SELECT
HomeTeam,
HomeTeamAbbrev,
AwayTeam,
AwayTeamAbbrev,
HomeScore,
AwayScore,
HomePitcher,
AwayPitcher,
CurrentInning,
Balls,
Strikes,
Outs,
TimeRemaining
FROM
XMLTable(XMLNamespaces(‘http://StreamingScores.com’ AS “x”),
‘for $i in $xml return $i//x:Game’
PASSING getBaseballScoresWS  as “xml”
COLUMNS
HomeTeam          VARCHAR2(100) PATH ‘x:HomeTeam’,
HomeTeamAbbrev    VARCHAR2(4)   PATH ‘x:HomeTeamAbbrev’,
AwayTeam          VARCHAR2(100) PATH ‘x:AwayTeam’,
AwayTeamAbbrev    VARCHAR2(4)   PATH ‘x:AwayTeamAbbrev’,
HomeScore         NUMBER(3)     PATH ‘x:HomeScore’,
AwayScore         NUMBER(3)     PATH ‘x:AwayScore’,
HomePitcher       VARCHAR2(100) PATH ‘x:HomePitcher’,
AwayPitcher       VARCHAR2(100) PATH ‘x:AwayPitcher’,
CurrentInning     VARCHAR2(50)  PATH ‘x:Inning’,
Balls             NUMBER(1)     PATH ‘x:Balls’,
Strikes           NUMBER(1)     PATH ‘x:Strikes’,
Outs              NUMBER(1)     PATH ‘x:Outs’,
TimeRemaining     VARCHAR2(50)  PATH ‘x:TimeRemaining’
) a
/

Example 2.  What’s Showing at the Movies …

This example uses a web service that lists theaters,  movies, and movie times for a given zipcode.  The steps are exactly same as in the previous example.  However, this example is little more complex.  The XML returned by this web service doesn’t lend itself as easily to be queried.  In the previous example the resulting XML had more of a flattened structure (one to one relationships), where as the XML returned in this example has more of relational structure (one to many relationships), which means we have to manipulate the XML into a flattened structure using an XQuery FLWOR (FOR, LET, WHERE, ORDER BY, RETURN) expression.

Step 1. Create the Function to reeturn our XML from the Web Service …

CREATE OR REPLACE FUNCTION getMovieListWS(p_zipcode VARCHAR2 := ‘28216’,
p_radius  VARCHAR2 := ‘5’)  RETURN XMLType
IS

v_service_def WebServiceUtils.SERVICE_DEFINITION_TYPE;
v_param       WebServiceUtils.service_param_type;
v_param_list  WebServiceUtils.service_param_list := WebServiceUtils.service_param_list();

BEGIN

v_service_def.service_name       :=  ‘GetTheatersAndMovies';
v_service_def.service_url        :=  ‘http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx';
v_service_def.service_action_url :=  ‘http://www.ignyte.com/whatsshowing/GetTheatersAndMovies';
v_service_def.service_ns         :=  ‘http://www.ignyte.com/whatsshowing';

v_param.name      := ‘zipCode';
v_param.data_type := ‘s:string';
v_param.value     := p_zipcode;

v_param_list.EXTEND;
v_param_list(v_param_list.COUNT) := v_param;

v_param.name      := ‘radius';
v_param.data_type := ‘s:int';
v_param.value     := p_radius;

v_param_list.EXTEND;
v_param_list(v_param_list.COUNT) := v_param;

v_service_def.service_params := v_param_list;

RETURN XMLType(WebServiceUtils.executeWebService(v_service_def));

END;
/

Here is the resulting XML response.  Notice the one to many relationship between Theater tag and the Movies tag …

soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<soap:Body>
<GetTheatersAndMoviesResponse xmlns=”http://www.ignyte.com/whatsshowing”>
<GetTheatersAndMoviesResult>
<Theater>
<Name>AMC Northlake 14</Name>
<Address>7325 Northlake Mall Drive, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>11:20am | 2:00pm | 4:50pm | 7:30pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Carriers</Name>
<RunningTime>1 hr 24 mins</RunningTime>
<ShowTimes>10:30am | 12:50pm | 3:10pm | 5:30pm | 7:40pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>11:00am | 1:50pm | 4:40pm | 7:30pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>10:00am | 12:20pm | 3:00pm | 5:40pm | 8:10pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>1:40pm | 6:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>10:30am | 1:20pm | 4:10pm | 7:00pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:50am | 2:30pm | 5:10pm | 7:50pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>2:30pm | 5:20pm | 7:50pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:00pm | 3:30pm | 6:50pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>10:20am | 1:10pm | 4:10pm | 7:20pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>11:20am | 4:00pm | 9:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>4:20pm | 7:15pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>10:00am | 12:20pm | 2:40pm | 5:00pm | 7:20pm | 9:40pm | 10:40am | 1:00pm | 3:20pm | 5:40pm | 8:00pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:10pm | 10:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Observer Omnimax Theatre</Name>
<Address>301 North Tryon Street, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>G</Rating>
<Name>Under the Sea 3D</Name>
<RunningTime>0 hrs 35 mins</RunningTime>
<ShowTimes>1:00pm | 3:00pm | 4:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Epicentre Theater 5</Name>
<Address>210 E Trade St., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>12:30pm | 3:40pm | 6:50pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>12:45pm | 3:50pm | 7:00pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:00pm | 3:20pm | 6:25pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:00pm | 4:00pm | 7:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>12:15pm | 3:30pm | 6:35pm | 9:20pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Manor Theatre</Name>
<Address>607 Providence Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>Adam</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>2:00pm | 4:10pm | 7:00pm | 9:10pm</ShowTimes>
</Movie>
<Movie>
<Name>Yoo-Hoo, Mrs. Goldberg</Name>
<ShowTimes>2:10pm | 4:20pm | 7:10pm | 9:20pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Park Terrace Stadium 6</Name>
<Address>4289 Park Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:30pm | 3:50pm | 6:50pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>2:10pm | 4:30pm | 7:30pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Paper Heart</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>4:50pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>G</Rating>
<Name>Ponyo</Name>
<RunningTime>1 hr 40 mins</RunningTime>
<ShowTimes>1:40pm | 4:05pm | 7:00pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>1:50pm | 4:35pm | 7:20pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Cove</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>1:55pm | 7:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hurt Locker</Name>
<RunningTime>2 hrs 07 mins</RunningTime>
<ShowTimes>1:20pm | 4:15pm | 7:10pm | 10:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Movies at Birkdale</Name>
<Address>16950 Birkdale Commons Pkwy, Huntersville, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>12:35pm | 2:35pm | 4:40pm | 7:20pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Carriers</Name>
<RunningTime>1 hr 24 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
</Movie>
<Movie>
<Rating>G</Rating>
<Name>Ponyo</Name>
<RunningTime>1 hr 40 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination 3D</Name>
<RunningTime>1 hr 22 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Starlight Cinema 14</Name>
<Address>11240 US Hwy 29, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>11:45am | 2:10pm | 4:35pm | 7:15pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>11:50am | 2:25pm | 4:55pm | 7:35pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>12:30pm | 2:55pm | 5:25pm | 7:50pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>11:55am | 2:25pm | 4:40pm | 7:10pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:40am | 2:20pm | 5:00pm | 7:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:50am | 2:15pm | 4:45pm | 7:20pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:00pm | 2:35pm | 5:10pm | 7:40pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>11:40am | 2:55pm | 7:05pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>12:05pm | 2:50pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:20pm | 2:40pm | 4:50pm | 7:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:15pm | 2:45pm | 5:20pm | 7:30pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>12:10pm | 2:40pm | 5:10pm | 7:55pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>11:45am | 2:30pm | 5:05pm | 7:45pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>12:25pm | 2:45pm | 5:15pm | 7:30pm | 9:55pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Phillips Place Stadium 10</Name>
<Address>6911 Phillips Place Ct., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>12:30pm | 2:50pm | 5:10pm | 7:30pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>12:15pm | 2:55pm | 5:25pm | 8:00pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:45am | 2:25pm | 5:05pm | 7:50pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:50am | 2:15pm | 4:40pm | 7:00pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:00pm | 2:30pm | 5:00pm | 7:40pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:10pm | 3:30pm | 6:45pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:00pm | 4:00pm | 7:15pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:05pm | 2:20pm | 4:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:20pm | 2:45pm | 5:20pm | 7:25pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>11:55am | 2:35pm | 5:15pm | 7:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>7:10pm | 9:45pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>AMC Concord Mills 24</Name>
<Address>8241 Concord Mills Blvd, Concord, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>10:20am | 12:40pm | 3:00pm | 5:20pm | 7:40pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Aliens in the Attic</Name>
<RunningTime>1 hr 26 mins</RunningTime>
<ShowTimes>10:10am | 12:25pm | 2:35pm | 4:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>10:00am | 12:20pm | 2:45pm | 5:10pm | 7:55pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Carriers</Name>
<RunningTime>1 hr 24 mins</RunningTime>
<ShowTimes>10:15am | 12:25pm | 2:35pm | 4:50pm | 7:05pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>2:30pm | 5:10pm | 7:50pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>10:45am | 1:10pm | 3:35pm | 6:00pm | 8:20pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>10:25am | 12:45pm | 3:05pm | 5:25pm | 7:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:45am | 2:30pm | 5:15pm | 8:00pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>10:05am | 11:00pm | 12:25pm | 1:20pm | 2:50pm | 3:45pm | 5:15pm | 6:10pm | 7:45pm | 8:35pm | 10:10pm | 11:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>10:35am | 12:10pm | 1:05pm | 2:45pm | 3:40pm | 5:20pm | 6:15pm | 7:55pm | 8:50pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:20pm | 3:40pm | 7:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Ice Age: Dawn of the Dinosaurs</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>10:35am | 12:55pm | 3:15pm | 5:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>1:00pm | 4:20pm | 7:35pm | 10:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>11:00am | 1:50pm | 4:40pm | 7:30pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Old Partner</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>10:30am | 12:30pm | 2:40pm | 4:50pm | 6:50pm | 8:50pm | 10:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Orphan</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>8:00pm | 10:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Play the Game</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>10:40am | 3:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Shorts</Name>
<RunningTime>2 hrs 06 mins</RunningTime>
<ShowTimes>10:05am | 12:35pm | 2:50pm | 5:05pm | 7:25pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Star Trek</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>2:00pm | 4:50pm | 7:40pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>10:00am | 12:10pm | 2:25pm | 4:30pm | 6:40pm | 8:55pm | 11:00pm | 12:40pm | 2:55pm | 5:00pm | 7:10pm | 9:25pm | 12:40pm | 2:55pm | 5:00pm | 7:10pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 48 mins</RunningTime>
<ShowTimes>1:25pm | 3:50pm | 6:20pm | 8:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Proposal</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>7:05pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:40am | 2:20pm | 5:00pm | 7:35pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Name>The Ugly Truth</Name>
<ShowTimes>1:15pm | 6:20pm | 8:40pm | 11:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Ayrsley Grand 14</Name>
<Address>9110 Kings Parade Blvd., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:40pm | 4:00pm | 7:20pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Carriers</Name>
<RunningTime>1 hr 24 mins</RunningTime>
<ShowTimes>1:50pm | 3:40pm | 5:30pm | 7:35pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:20pm | 3:50pm | 7:25pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>1:20pm | 3:20pm | 5:20pm | 7:30pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>1:10pm | 3:00pm | 5:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:00pm | 3:40pm | 7:00pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:30pm | 3:40pm | 5:40pm | 7:45pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>1:45pm | 4:15pm | 7:40pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>1:45pm | 5:00pm | 8:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>7:10pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:00pm | 3:00pm | 5:00pm | 7:15pm | 9:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>1:40pm | 4:15pm | 7:20pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination 3D</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>1:15pm | 3:10pm | 5:00pm | 7:10pm | 9:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>4:00pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:00pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:50pm | 7:30pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>AMC Carolina Pavilion 22</Name>
<Address>9541 South Boulevard, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>6:50pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>A Perfect Getaway</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>4:40pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Aliens in the Attic</Name>
<RunningTime>1 hr 26 mins</RunningTime>
<ShowTimes>1:10pm | 3:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>11:15am | 1:55pm | 4:20pm | 7:15pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Bandslam</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>12:45pm | 4:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Carriers</Name>
<RunningTime>1 hr 24 mins</RunningTime>
<ShowTimes>11:10am | 1:25pm | 3:30pm | 5:45pm | 8:15pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>11:00am | 1:45pm | 4:30pm | 7:10pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>10:45am | 12:55pm | 3:10pm | 5:35pm | 8:00pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Funny People</Name>
<RunningTime>2 hrs 26 mins</RunningTime>
<ShowTimes>6:25pm | 9:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>11:20am | 1:40pm | 3:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:25am | 2:25pm | 5:10pm | 7:50pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:30am | 2:05pm | 4:50pm | 7:35pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>11:45am | 2:30pm | 5:00pm | 7:30pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>10:55am | 2:15pm | 5:25pm | 8:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Ice Age: Dawn of the Dinosaurs</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>11:35am | 1:50pm | 4:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>10:45am | 2:00pm | 5:30pm | 7:00pm | 8:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>10:50am | 1:35pm | 4:25pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>1:05pm | 7:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>11:00am | 1:15pm | 3:40pm | 5:50pm | 8:05pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Spread</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>7:20pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>3:20pm | 6:40pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>11:05am | 12:20pm | 1:20pm | 2:30pm | 3:25pm | 4:45pm | 5:40pm | 7:05pm | 8:10pm | 9:15pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>6:35pm | 9:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>6:55pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:00pm | 4:10pm | 6:45pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Transformers: Revenge of the Fallen</Name>
<RunningTime>2 hrs 29 mins</RunningTime>
<ShowTimes>11:40am | 3:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Up</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>11:50am | 2:35pm | 4:55pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Cinemark Movies 10</Name>
<Address>9508 Northeast Ct, Matthews, NC</Address>
<Movies>
<Movie>
<Rating>PG</Rating>
<Name>Bandslam</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:40pm | 4:10pm | 7:15pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Ice Age: Dawn of the Dinosaurs</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>12:45pm | 1:55pm | 3:05pm | 4:15pm | 5:30pm | 6:35pm | 7:45pm | 9:00pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>My Sister’s Keeper</Name>
<RunningTime>1 hr 48 mins</RunningTime>
<ShowTimes>1:45pm | 4:20pm | 7:25pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Night at the Museum: Battle of the Smithsonian</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:55pm | 3:25pm | 5:55pm | 8:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Star Trek</Name>
<RunningTime>2 hrs 06 mins</RunningTime>
<ShowTimes>1:00pm | 7:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Taking of Pelham 123</Name>
<RunningTime>1 hr 46 mins</RunningTime>
<ShowTimes>3:50pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Transformers: Revenge of the Fallen</Name>
<RunningTime>2 hrs 29 mins</RunningTime>
<ShowTimes>12:30pm | 2:05pm | 3:40pm | 5:15pm | 6:50pm | 8:25pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Up</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:05pm | 2:15pm | 3:30pm | 4:35pm | 5:50pm | 6:55pm | 8:10pm | 9:15pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Arboretum Stadium 12</Name>
<Address>8008 Providence Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:20pm | 4:20pm | 7:30pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:10pm | 4:25pm | 7:25pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>1:45pm | 4:45pm | 7:45pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:15pm | 4:15pm | 7:10pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:40pm | 4:40pm | 7:50pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>1:30pm | 4:30pm | 7:40pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>2:00pm | 7:00pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:05pm | 4:00pm | 7:20pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:25pm | 4:10pm | 7:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>1:50pm | 4:50pm | 7:35pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:35pm | 4:35pm | 7:55pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:00pm | 4:05pm | 7:15pm | 9:45pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Movies @ Franklin Square</Name>
<Address>3778 E. Franklin Boulevard, Gastonia, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>All About Steve</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>12:20pm | 2:40pm | 5:05pm | 7:30pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>11:55am | 2:30pm | 5:00pm | 7:45pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Extract</Name>
<RunningTime>1 hr 31 mins</RunningTime>
<ShowTimes>12:35pm | 2:50pm | 5:10pm | 7:20pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>11:50am | 2:15pm | 4:40pm | 7:10pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:00pm | 4:20pm | 7:25pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Gamer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>12:25pm | 2:45pm | 5:20pm | 7:40pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:10pm | 2:35pm | 5:25pm | 8:00pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:00pm | 3:15pm | 6:45pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>11:45am | 3:00pm | 6:30pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:15pm | 4:15pm | 7:05pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:15pm | 9:20pm | 2:25pm | 4:45pm | 7:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:05pm | 12:50pm | 2:10pm | 2:55pm | 4:50pm | 5:30pm | 7:15pm | 7:50pm | 9:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:05pm | 4:25pm | 7:55pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Name>Jobs</Name>
</Movie>
</Movies>
</Theater>
</GetTheatersAndMoviesResult>
</GetTheatersAndMoviesResponse>
</soap:Body>
</soap:Envelope>

Step 2. Create the query and define the view.  For this XML document we have use the XQuery FLWOR expression to create a flattened version of the original document.  We do this by taking the Theater tag and it’s immediate child elements and the Movie tag and it’s child elements and making them all attributes of the Movie tags that are the children of each Theater tag.  Giving us a  flattened structure like:

<Movie theater=”blah” address=”blah” name=”blah”  rating=”blah” runtime=”blah” showtimes=”blah></Movie>

Here is the entire view definition with the XMLTable function and XQuery FLWOR expression:

CREATE OR REPLACE VIEW LOCAL_MOVIES_VW AS
SELECT
theatre,
address,
movie,
rating,
runtime,
showtimes
FROM
XMLTable(XMLNamespaces(‘http://www.ignyte.com/whatsshowing’ AS “x”),
‘for $i in $xml//x:Theater
for $x in $i//x:Movie
return <Movie theater=”{$i/x:Name/text()}”
address=”{$i/x:Address/text()}”
name=”{$x/x:Name/text()}”
rating=”{$x/x:Rating/text()}”
runtime=”{$x/x:RunningTime/text()}”
showtimes=”{$x/x:ShowTimes/text()}”></Movie>’
PASSING getMovieListWS  as “xml”
COLUMNS
theatre          VARCHAR2(100) PATH ‘/Movie/@theater’,
address          VARCHAR2(100) PATH ‘/Movie/@address’,
movie            VARCHAR2(100) PATH ‘/Movie/@name’,
rating           VARCHAR2(100) PATH ‘/Movie/@rating’,
runtime          VARCHAR2(100) PATH ‘/Movie/@runtime’,
showtimes        VARCHAR2(100) PATH ‘/Movie/@showtimes’) a
/

Now, we can access the data provided by the web service by simply executing a SQL statement against our newly defined view.

Wrapping It up …

Exposing web services as a view is a great way to take data from non-traditional sources and include it directly into any existing data model.  As always … if you have any questions, please feel free to contact me.

PL/SQL Web Service Utility: Accessing Web Services with PL/SQL is Simple!

This entry showcases a PL/SQL package called WebServiceUtils that makes accessing a web service from with the Oracle database very simple.  The package makes use of Tim Hall’s SOAP_API PL/SQL package (updated by me to get the SOAP response as a CLOB instead of a VARCHAR2) to do the SOAP encoding and processing.  The WebServiceUtils package puts a user friendly layer over the SOAP_API.    The WebServiceUtils package (here after known as the “utils” package) provides three structures (2 record types and 1 collection).  The two record types are:

  • service_definition_type – Contains attributes related to the service you are trying to call.
  • service_param_type – attributes represent service parameter information.

(The collection) service_param_list is a collection of service_param_type.  The following code block contains these type (and collection) definitions (also notice the constants for SOAP 1.0 and SOAP 1.2 style tags):

– Public Objects/Types

SOAP_TAG_11 CONSTANT VARCHAR2(8) := ‘SOAP-ENV';
SOAP_TAG_12 CONSTANT VARCHAR2(7) := ‘soapenv';

— Type allowing developer to define a web service parameter name,data
TYPE service_param_type IS RECORD(
name VARCHAR2(100),
data_type VARCHAR2(100),
value VARCHAR2(1000)
);

— Collection of SERVICE_PARAM Type allowing developer to bundle service params.
TYPE service_param_list IS TABLE OF service_param_type;

— Type that allows developer to create service end point definition for generic exection of service
TYPE service_definition_type IS RECORD(
service_name VARCHAR2(100),
service_url VARCHAR2(100),
soap_tag VARCHAR2(10),
service_action_url VARCHAR2(200),
service_ns VARCHAR2(200),
service_params service_param_list,
result_ns VARCHAR2(200),
result_target VARCHAR2(200)
);

The util package contains a function called executeWebService that accepts a service_definition_type as it’s only parameter and returns a  CLOB (which can be converted to an XMLType).  The util package also contains a procedure called addParamToCollection that accepts two parameters, service_param_list and service_param_type.  The addParamToCollection procedure simply extends the passed collection object and places the parameter type object into it.

The code (including the SOAP_API package and examples) can be downloaded  here:WebServiceUtils.zip

Lets take a look at an example (included in the download above) …

This example demonstrates how connect to the a public web service (provided by www.ignyte.com) that will return a list of movie theaters given a zipcode and a radius of miles:

/* Movie Locations Service */

DECLARE

v_service_def WebServiceUtils.SERVICE_DEFINITION_TYPE;
v_param WebServiceUtils.service_param_type;
v_param_list WebServiceUtils.service_param_list := WebServiceUtils.service_param_list();

BEGIN

v_service_def.service_name := ‘GetTheatersAndMovies';
v_service_def.service_url := ‘http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx';
v_service_def.service_action_url := ‘http://www.ignyte.com/whatsshowing/GetTheatersAndMovies';
v_service_def.service_ns := ‘http://www.ignyte.com/whatsshowing';

v_param.name := ‘zipCode';
v_param.data_type := ‘s:string';
v_param.value := ‘28216’;

WebServiceUtils.addParamToCollection(v_param_list,v_param);

v_param.name := ‘radius';
v_param.data_type := ‘s:int';
v_param.value := ‘5’;

WebServiceUtils.addParamToCollection(v_param_list,v_param);

v_service_def.service_params := v_param_list;

DBMS_OUTPUT.PUT_LINE(substr(WebServiceUtils.executeWebService(v_service_def),1,20000));

END;
/

The result returned by the code above is ( without the substr):

<soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<soap:Body>
<GetTheatersAndMoviesResponse xmlns=”http://www.ignyte.com/whatsshowing”>
<GetTheatersAndMoviesResult>
<Theater>
<Name>AMC Northlake 14</Name>
<Address>7325 Northlake Mall Drive, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:50pm | 4:40pm | 7:30pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>11:55am | 2:10pm | 4:20pm | 6:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:20pm | 4:10pm | 7:00pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:00pm | 2:30pm | 5:20pm | 7:50pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>11:55am | 2:30pm | 3:10pm | 6:00pm | 6:30pm | 9:15pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>12:10pm | 3:15pm | 6:20pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>9:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:30pm | 3:00pm | 5:20pm | 7:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:00pm | 2:10pm | 4:30pm | 7:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>1:20pm | 4:20pm | 7:15pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:20pm | 2:40pm | 5:00pm | 7:20pm | 9:40pm | 1:00pm | 3:20pm | 5:40pm | 8:00pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:40pm | 3:00pm | 5:30pm | 8:00pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:10pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>10:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Observer Omnimax Theatre</Name>
<Address>301 North Tryon Street, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>G</Rating>
<Name>Grand Canyon Adventure: River at Risk</Name>
<RunningTime>0 hrs 35 mins</RunningTime>
<ShowTimes>11:00am | 2:00pm</ShowTimes>
</Movie>
<Movie>
<Name>Under the Sea 3D</Name>
<ShowTimes>10:00am | 12:00pm | 1:00pm | 3:00pm | 4:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Epicentre Theater 5</Name>
<Address>210 E Trade St., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>12:15pm | 3:00pm | 6:20pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>12:30pm | 3:30pm | 6:40pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>In the Loop</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:10pm | 6:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>12:00pm | 3:15pm | 6:25pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>3:10pm | 9:10pm</ShowTimes>
</Movie>
<Movie>
<Name>The Time Traveler’s Wife</Name>
<ShowTimes>1:00pm | 4:10pm | 6:50pm | 9:55pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Manor Theatre</Name>
<Address>607 Providence Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>Adam</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>2:00pm | 4:15pm | 7:00pm | 9:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>G</Rating>
<Name>Ponyo</Name>
<RunningTime>1 hr 40 mins</RunningTime>
<ShowTimes>2:10pm | 4:30pm | 7:10pm | 9:20pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Park Terrace Stadium 6</Name>
<Address>4289 Park Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:30pm | 3:50pm | 6:50pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Paper Heart</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>2:00pm | 4:50pm | 7:40pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Revanche</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>1:40pm | 4:20pm | 7:00pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>2:05pm | 4:40pm | 7:30pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Cove</Name>
<RunningTime>2 hrs 07 mins</RunningTime>
<ShowTimes>1:50pm | 4:30pm | 7:20pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Name>The Hurt Locker</Name>
<ShowTimes>1:20pm | 4:15pm | 7:10pm | 10:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Movies at Birkdale</Name>
<Address>16950 Birkdale Commons Pkwy, Huntersville, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>12:50pm | 3:00pm | 5:15pm | 7:45pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
</Movie>
<Movie>
<Rating>G</Rating>
<Name>Ponyo</Name>
<RunningTime>1 hr 40 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination 3D</Name>
<RunningTime>1 hr 22 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hurt Locker</Name>
<RunningTime>2 hrs 07 mins</RunningTime>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Starlight Cinema 14</Name>
<Address>11240 US Hwy 29, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>2:25pm | 4:55pm | 7:35pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>2:25pm | 4:40pm | 7:10pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>2:20pm | 5:00pm | 7:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>2:35pm | 5:10pm | 7:40pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>2:55pm | 4:00pm | 7:05pm | 8:00pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>2:50pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>2:30pm | 4:45pm | 7:20pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>2:40pm | 4:50pm | 7:15pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>2:45pm | 5:20pm | 7:30pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>2:35pm | 5:20pm | 7:50pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>2:40pm | 5:10pm | 7:55pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>2:30pm | 5:05pm | 7:45pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>2:45pm | 5:15pm | 7:30pm | 9:55pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Phillips Place Stadium 10</Name>
<Address>6911 Phillips Place Ct., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>12:10pm | 2:50pm | 5:25pm | 8:00pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:45am | 2:25pm | 5:05pm | 7:50pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:00pm | 2:30pm | 5:00pm | 7:30pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:15pm | 3:30pm | 6:45pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:15pm | 4:00pm | 7:00pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>4:55pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:05pm | 2:20pm | 4:30pm | 6:50pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:20pm | 2:45pm | 5:15pm | 8:05pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>2:15pm | 7:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>11:55am | 2:35pm | 5:10pm | 7:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:50am | 2:10pm | 4:40pm | 7:20pm | 9:45pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>AMC Concord Mills 24</Name>
<Address>8241 Concord Mills Blvd, Concord, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>11:55am | 2:30pm | 4:50pm | 7:10pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>A Perfect Getaway</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Aliens in the Attic</Name>
<RunningTime>1 hr 26 mins</RunningTime>
<ShowTimes>12:20pm | 2:40pm | 4:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Bandslam</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>8:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:05pm | 4:05pm | 7:05pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Funny People</Name>
<RunningTime>2 hrs 26 mins</RunningTime>
<ShowTimes>7:35pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>11:50am | 2:05pm | 4:15pm | 6:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:45am | 2:30pm | 5:15pm | 7:10pm | 8:05pm | 9:55pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>11:40am | 12:30pm | 2:10pm | 3:00pm | 4:40pm | 5:30pm | 7:15pm | 8:05pm | 9:50pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:00pm | 3:20pm | 6:40pm | 10:00pm | 9:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Ice Age: Dawn of the Dinosaurs</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>12:45pm | 3:05pm | 5:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:50pm | 2:00pm | 4:10pm | 5:25pm | 7:30pm | 8:40pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>11:25am | 2:15pm | 5:00pm | 7:50pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>12:45pm | 3:35pm | 6:25pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Play the Game</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:15pm | 2:45pm | 5:15pm | 7:45pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:10pm | 2:35pm | 4:55pm | 7:15pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:05pm | 2:20pm | 4:35pm | 6:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>11:35am | 2:25pm | 5:10pm | 8:00pm | 10:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>11:30am | 1:35pm | 3:40pm | 5:45pm | 7:55pm | 10:10pm | 12:25pm | 2:50pm | 5:05pm | 7:10pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:55pm | 5:35pm | 7:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:00pm | 3:25pm | 5:50pm | 8:15pm | 10:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Proposal</Name>
<RunningTime>1 hr 48 mins</RunningTime>
<ShowTimes>7:20pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:15pm | 4:10pm | 6:55pm | 9:30pm | 1:15pm | 4:10pm | 6:55pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>12:55pm | 3:25pm | 5:50pm | 8:10pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Transformers: Revenge of the Fallen</Name>
<RunningTime>2 hrs 29 mins</RunningTime>
<ShowTimes>12:40pm | 3:55pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Ayrsley Grand 14</Name>
<Address>9110 Kings Parade Blvd., Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:20pm | 3:50pm | 7:25pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>1:10pm | 3:00pm | 5:00pm | 7:15pm | 9:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:00pm | 3:40pm | 7:00pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>1:45pm | 4:15pm | 7:40pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>1:45pm | 5:00pm | 8:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:30pm | 4:30pm | 7:10pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:15pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:20pm | 3:20pm | 5:20pm | 7:25pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:00pm | 3:00pm | 5:00pm | 7:15pm | 9:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>1:40pm | 4:00pm | 7:20pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination 3D</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>1:15pm | 3:10pm | 5:00pm | 7:10pm | 9:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:45pm | 4:00pm | 7:30pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:00pm | 9:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>1:50pm | 4:10pm | 7:30pm | 9:40pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>AMC Carolina Pavilion 22</Name>
<Address>9541 South Boulevard, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>(500) Days of Summer</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>4:05pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>A Perfect Getaway</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>7:15pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Aliens in the Attic</Name>
<RunningTime>1 hr 26 mins</RunningTime>
<ShowTimes>1:10pm | 3:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Bandslam</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:00pm | 4:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:45pm | 4:30pm | 7:10pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Funny People</Name>
<RunningTime>2 hrs 26 mins</RunningTime>
<ShowTimes>6:25pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>12:50pm | 3:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>12:45pm | 1:25pm | 3:30pm | 4:15pm | 6:30pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:35pm | 3:55pm | 7:35pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:40pm | 4:25pm | 8:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Ice Age: Dawn of the Dinosaurs</Name>
<RunningTime>1 hr 34 mins</RunningTime>
<ShowTimes>12:00pm | 2:10pm | 4:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:00pm | 1:30pm | 3:20pm | 5:30pm | 7:00pm | 8:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>12:55pm | 4:10pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>1:05pm | 4:40pm | 7:45pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:30pm | 6:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Public Enemies</Name>
<RunningTime>2 hrs 20 mins</RunningTime>
<ShowTimes>7:00pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:15pm | 3:40pm | 5:50pm | 8:05pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Spread</Name>
<RunningTime>1 hr 37 mins</RunningTime>
<ShowTimes>1:30pm | 4:20pm | 7:10pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Taking Woodstock</Name>
<RunningTime>2 hrs 00 mins</RunningTime>
<ShowTimes>12:15pm | 3:25pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Collector</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>7:20pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:20pm | 1:20pm | 2:30pm | 3:30pm | 4:50pm | 5:40pm | 7:05pm | 8:10pm | 9:15pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>6:35pm | 9:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>6:45pm | 9:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>12:25pm | 4:00pm | 6:40pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Ugly Truth</Name>
<RunningTime>1 hr 35 mins</RunningTime>
<ShowTimes>6:55pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Transformers: Revenge of the Fallen</Name>
<RunningTime>2 hrs 29 mins</RunningTime>
<ShowTimes>12:05pm | 3:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Up</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:10pm | 2:35pm | 5:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Charlotte Crownpoint</Name>
<Address>9630 Monroe Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:50pm | 4:35pm | 7:15pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>2:55pm | 7:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:45am | 2:25pm | 5:05pm | 7:45pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:40pm | 2:50pm | 5:10pm | 7:30pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:30pm | 3:50pm | 7:05pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>12:40pm | 3:55pm | 7:10pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:45pm | 4:40pm | 7:30pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Orphan</Name>
<RunningTime>2 hrs 03 mins</RunningTime>
<ShowTimes>1:55pm | 4:30pm | 7:05pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:35pm | 5:15pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:25pm | 2:45pm | 5:00pm | 7:20pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination 3D</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>1:05pm | 3:10pm | 5:20pm | 7:45pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Goods: Live Hard. Sell Hard.</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:00pm | 3:10pm | 5:25pm | 7:40pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>11:50am | 2:20pm | 4:55pm | 7:25pm | 10:00pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Cinemark Movies 10</Name>
<Address>9508 Northeast Ct, Matthews, NC</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>Drag Me to Hell</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>3:55pm | 7:20pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Imagine That</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>4:00pm | 7:15pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>My Sister’s Keeper</Name>
<RunningTime>1 hr 48 mins</RunningTime>
<ShowTimes>4:20pm | 7:25pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Night at the Museum: Battle of the Smithsonian</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>3:25pm | 4:30pm | 5:55pm | 7:00pm | 8:15pm | 9:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Star Trek</Name>
<RunningTime>2 hrs 06 mins</RunningTime>
<ShowTimes>3:50pm | 6:45pm | 9:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Terminator Salvation</Name>
<RunningTime>1 hr 56 mins</RunningTime>
<ShowTimes>4:10pm | 7:05pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Taking of Pelham 123</Name>
<RunningTime>1 hr 46 mins</RunningTime>
<ShowTimes>4:15pm | 7:10pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Up</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>3:30pm | 4:35pm | 5:50pm | 6:55pm | 8:10pm | 9:15pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Arboretum Stadium 12</Name>
<Address>8008 Providence Road, Charlotte, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>1:20pm | 4:25pm | 7:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>1:30pm | 4:15pm | 7:15pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>1:25pm | 4:35pm | 7:05pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>1:45pm | 4:40pm | 7:40pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>2:00pm | 7:00pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:05pm | 4:00pm | 7:20pm | 10:05pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:00pm | 4:20pm | 7:35pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:35pm | 4:10pm | 7:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>1:40pm | 4:50pm | 7:50pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hangover</Name>
<RunningTime>1 hr 39 mins</RunningTime>
<ShowTimes>1:50pm | 4:45pm | 7:45pm | 10:25pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>1:15pm | 4:30pm | 7:30pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Up</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>1:10pm | 4:05pm | 6:55pm | 9:30pm</ShowTimes>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Regal Movies @ Franklin Square</Name>
<Address>3778 E. Franklin Boulevard, Gastonia, NC</Address>
<Movies>
<Movie>
<Rating>R</Rating>
<Name>District 9</Name>
<RunningTime>1 hr 51 mins</RunningTime>
<ShowTimes>11:55am | 2:50pm | 5:30pm | 8:05pm | 10:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>G-Force</Name>
<RunningTime>1 hr 28 mins</RunningTime>
<ShowTimes>12:15pm | 2:25pm | 4:35pm | 7:15pm | 9:35pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>G.I. Joe: The Rise of Cobra</Name>
<RunningTime>1 hr 58 mins</RunningTime>
<ShowTimes>11:50am | 2:30pm | 5:10pm | 7:45pm | 10:30pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Halloween II</Name>
<RunningTime>1 hr 45 mins</RunningTime>
<ShowTimes>12:10pm | 2:35pm | 5:00pm | 7:25pm | 9:50pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Harry Potter and the Half-Blood Prince</Name>
<RunningTime>2 hrs 33 mins</RunningTime>
<ShowTimes>12:30pm | 3:50pm | 7:00pm | 10:15pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Inglourious Basterds</Name>
<RunningTime>2 hrs 32 mins</RunningTime>
<ShowTimes>11:45am | 1:00pm | 3:00pm | 4:30pm | 6:30pm | 8:00pm | 9:45pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Julie &amp; Julia</Name>
<RunningTime>2 hrs 04 mins</RunningTime>
<ShowTimes>1:10pm | 4:10pm | 7:05pm | 10:00pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>Post Grad</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>5:25pm | 10:10pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Shorts</Name>
<RunningTime>1 hr 29 mins</RunningTime>
<ShowTimes>12:25pm | 3:05pm | 5:20pm | 7:55pm | 10:20pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Final Destination</Name>
<RunningTime>1 hr 22 mins</RunningTime>
<ShowTimes>12:00pm | 12:40pm | 2:05pm | 2:45pm | 4:15pm | 4:50pm | 7:10pm | 7:50pm | 9:15pm | 9:55pm</ShowTimes>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>The Hurt Locker</Name>
<RunningTime>2 hrs 07 mins</RunningTime>
<ShowTimes>12:55pm | 3:55pm | 6:50pm | 9:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Proposal</Name>
<RunningTime>1 hr 48 mins</RunningTime>
<ShowTimes>12:20pm | 2:55pm | 7:40pm</ShowTimes>
</Movie>
<Movie>
<Rating>PG-13</Rating>
<Name>The Time Traveler’s Wife</Name>
<RunningTime>1 hr 47 mins</RunningTime>
<ShowTimes>2:40pm | 5:05pm | 10:35pm | 12:05pm | 8:05pm</ShowTimes>
</Movie>
<Movie>
<Name>Jobs</Name>
</Movie>
</Movies>
</Theater>
</GetTheatersAndMoviesResult>
</GetTheatersAndMoviesResponse>
</soap:Body>
</soap:Envelope>

As you can see, it’s very straight forward. If you have any questions, feel free to shoot me an email.

PL/SQL: Create Dynamic PL/SQL Functions with the AnonymousFunction Data Type

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, November 9, 2008)

Spending some time with loosely typed languages like JavaScript and LISP has made me realize how powerful anonymous functions can be.  In my last big project using ADF Faces, I found it necessary to rewrite some the ADF Faces JavaScript functions on the fly (at runtime) in order to force a specific and non-native behavior. Since JavaScript is loosely typed (meaning, in a nutshell, I don’t need to explicitly specify a type for my functions or variables) and supports anonymous functions (functions defined and executed at runtime), altering the functions provided by the Oracle developers was a snap.  I was able to wrap my code around their code and create a new function on the fly.  Cool stuff.  LISP provides a similar ability with the lambda function.  I thought it would be cool to have the same ability when writing PL/SQL code.  However, PL/SQL is strongly typed, and doesn’t provide an easy mechanism for defining generic anonymous functions out of the box.  However, since Oracle 9i, there is a way to implement generics through the ANYDATA, ANYDATASET, ANYTYPE objects.  Using these new tools, I was able to create a user defined type called AnonymousFunction that allows a developer to create dynamic functions (or anonymous functions) at runtime.  The user defined type makes use of both SYS.ANYDATA and the DBMS_TYPES package to allow the developer to create and execute functions of almost every type with relative ease.  Here is a sample of how a user would define a numeric function on the fly:

DECLARE
v_numberFunction AnonymousFunction := AnonymousFunction();
v_function_text VARCHAR2(200) := ‘FUNCTION testNumber RETURN NUMBER IS BEGIN RETURN 12345; END;';
v_result NUMBER(10) := NULL;
BEGIN

v_numberFunction.defineFunctionNumber(p_function_name=>’testNumber’,
p_function_text=>v_function_text);

v_result := v_numberFunction.executeFunctionNumber;

DBMS_OUTPUT.PUT_LINE(‘Number result=’||v_result);

END;

The (trivial) code example above would generate the result 12345 if executed in SQL*Plus or TOAD with SERVER OUT set to ON.  Rather than bore you with more details (you can get them from the code or ask me), here is the code for the user defined type:

(Download this code and a more comprehensive example here: AnonymousFunction.zip )

Here is the code for the curious …

/*====================================================

Global Temporary table used to store and retrieve
the results of the anonymous function execution.

=====================================================*/

CREATE GLOBAL TEMPORARY TABLE anon_func_results(
id NUMBER(12) PRIMARY KEY,
result SYS.ANYDATA,
clob_result CLOB,
blob_result BLOB);

/*=====================================================================
Sequence used to create a unique key for the result storage rows.
======================================================================*/
CREATE SEQUENCE anon_func_seq;

/*====================================================================

The user defined type Anonymous Function. This type allows a user
to execute dynamic functions of multiple types: Char, Varchar2, Number,
Float, Double, Raw, BFile, RAW, CLOB, BLOB, Collection, Object.

There are several member procedures and functions defined as conveniences
for executing functions of type: Varchar2, Number, Char, Float, Double,
Raw, BFILE, Clob, and Blob. Collections and Objects requires a few more
steps since the are not stand Oracle data types.

The UDT takes advantage of Oracle’s ANYDATA type as a generic type target.

=====================================================================*/
CREATE OR REPLACE TYPE AnonymousFunction AS OBJECT(

af_function_text VARCHAR2(32000),
af_function_type NUMBER(12),
af_function_name VARCHAR2(70),

— Constructor
CONSTRUCTOR FUNCTION AnonymousFunction RETURN SELF AS RESULT,

— Define the function to be executed.
— Include the function name, function body, and function type.
— Use the DBMS_TYPES package to specify the function’s data type.
MEMBER PROCEDURE defineFunction(p_function_name VARCHAR2,
p_function_text VARCHAR2,
p_function_type NUMBER),

MEMBER FUNCTION executeFunction RETURN SYS.ANYDATA,

MEMBER PROCEDURE defineFunctionChar(p_function_name VARCHAR2,
p_function_text VARCHAR2),
MEMBER FUNCTION executeFunctionChar RETURN CHAR,

MEMBER PROCEDURE defineFunctionVarchar2(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionVarchar2 RETURN VARCHAR2,

MEMBER PROCEDURE defineFunctionNumber(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionNumber RETURN NUMBER,

MEMBER PROCEDURE defineFunctionCLOB(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionCLOB RETURN CLOB,

MEMBER PROCEDURE defineFunctionBLOB(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionBLOB RETURN BLOB,

MEMBER PROCEDURE defineFunctionDate(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionDate RETURN DATE,

MEMBER PROCEDURE defineFunctionBDouble(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionBDouble RETURN BINARY_DOUBLE,

MEMBER PROCEDURE defineFunctionBFile(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionBFile RETURN BFILE,

MEMBER PROCEDURE defineFunctionBFloat(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionBFloat RETURN BINARY_FLOAT,

MEMBER PROCEDURE defineFunctionRaw(p_function_name VARCHAR2,
p_function_text VARCHAR2),

MEMBER FUNCTION executeFunctionRaw RETURN RAW

)
/
sho err;

CREATE OR REPLACE TYPE BODY AnonymousFunction AS

CONSTRUCTOR FUNCTION AnonymousFunction RETURN SELF AS RESULT
IS
BEGIN

SELF.af_function_text := NULL;
SELF.af_function_type := NULL;
SELF.af_function_name := NULL;

RETURN;

END;

/*============================================================================================================*/

MEMBER PROCEDURE defineFunction(p_function_name VARCHAR2,
p_function_text VARCHAR2,
p_function_type NUMBER)
IS
BEGIN

SELF.af_function_name := p_function_name;
SELF.af_function_text := p_function_text;
SELF.af_function_type := p_function_type;

END;

/*==========================================================================================================*/

— This is the key function … it executes the dynamic functions and provides the correct type encoding.
MEMBER FUNCTION executeFunction RETURN SYS.ANYDATA
IS

v_sql VARCHAR2(32000) := ‘DECLARE ‘||
‘result_key anon_func_results.id%TYPE := ; ‘||
‘v_result SYS.ANYDATA := NULL ; ‘||
‘ ‘||
‘BEGIN ‘||
‘ ‘||
‘ CASE v_result.getTypeName ‘||
‘ WHEN ”SYS.CLOB” THEN ‘||
‘ INSERT INTO anon_func_results(id,clob_result) VALUES (result_key,v_result.accessClob()); ‘||
‘ WHEN ”SYS.BLOB” THEN ‘||
‘INSERT INTO anon_func_results(id,blob_result) VALUES (result_key,v_result.accessBlob()); ‘||
‘ ELSE ‘||
‘INSERT INTO anon_func_results(id,result) VALUES (result_key,v_result); ‘||
‘ END CASE; ‘||
‘END;';

v_convert_text VARCHAR2(100) := NULL;

v_key NUMBER(12) := 0;

v_result_sql VARCHAR2(100) := ‘SELECT result FROM anon_func_results WHERE id=:id';

v_clob_result_sql VARCHAR2(100) := ‘SELECT AnyData.convertClob(clob_result) FROM anon_func_results WHERE id=:id';

v_blob_result_sql VARCHAR2(100) := ‘SELECT AnyData.convertBlob(blob_result) FROM anon_func_results WHERE id=:id';

v_return_val SYS.ANYDATA := NULL;

BEGIN

CASE SELF.af_function_type

WHEN DBMS_TYPES.TYPECODE_BDOUBLE THEN
v_convert_text := ‘v_result := AnyData.convertBDouble(); ‘;
WHEN DBMS_TYPES.TYPECODE_BFILE THEN
v_convert_text := ‘v_result := AnyData.convertBFile(); ‘;
WHEN DBMS_TYPES.TYPECODE_BFLOAT THEN
v_convert_text := ‘v_result := AnyData.convertBFloat(); ‘;
WHEN DBMS_TYPES.TYPECODE_BLOB THEN
v_convert_text := ‘v_result := AnyData.convertBlob(); ‘;
v_result_sql := v_blob_result_sql;
WHEN DBMS_TYPES.TYPECODE_CHAR THEN
v_convert_text := ‘v_result := AnyData.convertChar(); ‘;
WHEN DBMS_TYPES.TYPECODE_CLOB THEN
v_convert_text := ‘v_result := AnyData.convertClob(); ‘;
v_result_sql := v_clob_result_sql;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
v_convert_text := ‘v_result := AnyData.convertDate(); ‘;
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
v_convert_text := ‘v_result := AnyData.convertNumber(); ‘;
WHEN DBMS_TYPES.TYPECODE_RAW THEN
v_convert_text := ‘v_result := AnyData.convertRaw(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
v_convert_text := ‘v_result := AnyData.convertVarchar2(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
v_convert_text := ‘v_result := AnyData.convertVarchar(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARRAY THEN
v_convert_text := ‘v_result := AnyData.convertCollection(); ‘;
WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
v_convert_text := ‘v_result := AnyData.convertObject(); ‘;
END CASE;

IF v_convert_text IS NOT NULL THEN

SELECT anon_func_seq.nextval INTO v_key FROM DUAL;

v_sql := REPLACE(v_sql,”,SELF.af_function_text);
v_sql := REPLACE(v_sql,”,v_convert_text);
v_sql := REPLACE(v_sql,”,v_key);
v_sql := REPLACE(v_sql,”,SELF.af_function_name);

EXECUTE IMMEDIATE v_sql;

EXECUTE IMMEDIATE v_result_sql INTO v_return_val USING v_key;

END IF;

RETURN v_return_val;

END;

/*====================================================================================================*/

MEMBER PROCEDURE defineFunctionChar(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_CHAR);

END;

/*====================================================================================================*/

MEMBER FUNCTION executeFunctionChar RETURN CHAR
IS
v_result SYS.ANYDATA := NULL;
BEGIN

IF DBMS_TYPES.TYPECODE_CHAR = SELF.af_function_type THEN

v_result := SELF.executeFunction;

RETURN v_result.accessChar();

ELSE

RETURN NULL;

END IF;

END;

/*=====================================================================================================*/

MEMBER PROCEDURE defineFunctionVarchar2(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_VARCHAR2);

END;

/*====================================================================================================*/

MEMBER FUNCTION executeFunctionVarchar2 RETURN VARCHAR2
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_VARCHAR2 = SELF.af_function_type THEN

v_result := SELF.executeFunction;

RETURN v_result.accessVarchar2();

ELSE

RETURN NULL;

END IF;

END;

/*============================================================================================*/

MEMBER PROCEDURE defineFunctionNumber(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_NUMBER);

END;

/*===========================================================================================*/

MEMBER FUNCTION executeFunctionNumber RETURN NUMBER
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_NUMBER = SELF.af_function_type THEN

v_result := SELF.executeFunction;

COMMIT;

RETURN v_result.accessNumber();

ELSE

RETURN NULL;

END IF;

END;

/*===========================================================================================*/

MEMBER PROCEDURE defineFunctionClob(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_CLOB);

END;

/*==========================================================================================*/

MEMBER FUNCTION executeFunctionClob RETURN CLOB
IS

v_result SYS.ANYDATA := NULL;
v_clob CLOB := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_CLOB = SELF.af_function_type THEN

v_result := SELF.executeFunction;

v_clob := v_result.accessClob();

RETURN v_clob;

ELSE

RETURN NULL;

END IF;

END;

/*========================================================================================*/

MEMBER PROCEDURE defineFunctionBlob(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_BLOB);

END;

/*=======================================================================================*/

MEMBER FUNCTION executeFunctionBlob RETURN BLOB
IS

v_result SYS.ANYDATA := NULL;
v_blob BLOB := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_BLOB = SELF.af_function_type THEN

v_result := SELF.executeFunction;

v_blob := v_result.accessBlob();

RETURN v_blob;

ELSE

RETURN NULL;

END IF;

END;

/*=======================================================================================*/

MEMBER PROCEDURE defineFunctionDate(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_DATE);

END;

/*======================================================================================*/

MEMBER FUNCTION executeFunctionDate RETURN DATE
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_DATE = SELF.af_function_type THEN

v_result := SELF.executeFunction;

RETURN v_result.accessDate();

ELSE

RETURN NULL;

END IF;

END;

/*=====================================================================================*/

MEMBER PROCEDURE defineFunctionBDouble(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_BDOUBLE);

END;

/*=====================================================================================*/

MEMBER FUNCTION executeFunctionBDouble RETURN BINARY_DOUBLE
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_BDOUBLE = SELF.af_function_type THEN

v_result := SELF.executeFunction;

COMMIT;

RETURN v_result.accessBDouble();

ELSE

RETURN NULL;

END IF;

END;

/*====================================================================================*/

MEMBER PROCEDURE defineFunctionBFloat(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_BFLOAT);

END;

/*===================================================================================*/

MEMBER FUNCTION executeFunctionBFloat RETURN BINARY_FLOAT
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_BFLOAT = SELF.af_function_type THEN

v_result := SELF.executeFunction;

COMMIT;

RETURN v_result.accessBFloat();

ELSE

RETURN NULL;

END IF;

END;

/*===================================================================================*/

MEMBER PROCEDURE defineFunctionBFile(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_BFILE);

END;

/*===================================================================================*/

MEMBER FUNCTION executeFunctionBFile RETURN BFILE
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_BFILE = SELF.af_function_type THEN

v_result := SELF.executeFunction;

RETURN v_result.accessBFile();

ELSE

RETURN NULL;

END IF;

END;

/*===================================================================================*/

MEMBER PROCEDURE defineFunctionRaw(p_function_name VARCHAR2,
p_function_text VARCHAR2)
IS
BEGIN

SELF.defineFunction(p_function_name,
p_function_text,
DBMS_TYPES.TYPECODE_RAW);

END;

/*===================================================================================*/

MEMBER FUNCTION executeFunctionRaw RETURN RAW
IS

v_result SYS.ANYDATA := NULL;

BEGIN

IF DBMS_TYPES.TYPECODE_RAW = SELF.af_function_type THEN

v_result := SELF.executeFunction;

RETURN v_result.accessRaw();

ELSE

RETURN NULL;

END IF;

END;

/* END TYPE BODY */
END;

JavaScript: The Utlimate RegExp Email Address Format Validator

(Originally posted on the “old” Jason Bennett’s Developer Corner, Thursday, September 18, 2008)

I developed the following email address format validator for my current ADF Faces project.  The QA person keep failed my feeble attempt at a validator, so I decided to go big guns and create a validator that conforms to the standards for email addresses as set forth in this wiki entry:http://en.wikipedia.org/wiki/E-mail_address .  I created a single Regular Expression to handle all cases (unless someone can break it).  Here is the function:

(Make sure you put the regexp in one long string if you cut and paste this.  It wrapped in the blog entry.)

function isValidEmail(ctl){

v_pattern = /^((([^.]*)(([a-zA-Z0-9#!%/-=_`~&’$\*?\|^\{}\+][.]{0,1})+)[^.]|(“.*?”))[@](([a-zA-Z0-9-]+)([.]([a-zA-Z]{2,3}))+|([[]{1}(([0-1]?[0-9]{1,2}.)|(2[0-4][0-9].)|(25[0-5].)){3}(([0-1]?[0-9]{1,2})|(2[0-4][0-9])|(25[0-5]))[]]{1})))$/;


if(ctl.length > 0){
if (ctl.match(v_pattern)){
return true;
}else{
return false;
}
}else{
return true;
}
}

ADF Faces: Passing a Java Collection from a Custom ViewObjectImpl Class to a PL/SQL Function

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, August 17, 2008)

I recently had the challenge of creating several complex query search screens using ADF Faces.  These new search screens had to integrate seamlessly into our current application’s (not a J2EE application …) search screens (look the same, act the same, feel the same …).  One of the challenges that presented itself was to determine how to pass multiple search parameters with various operators (=, <, LIKE,SOUNDEX, etc …) and the values associated with them and map them to their associated columns in a dynamic where clause.  Since I was creating multiple screens with similar functionality, I needed a generic method of parameter passing.  The obvious answer was to use some sort of array or combination of arrays as the parameter transport mechanism.  The not so obvious part was how the heck I was going to pass an array of Java Objects to a PL/SQL function that was expecting a PL/SQL Collection object.  Luckily, this turned out to be less complicated than I thought thanks to Oracle and the developers of JDBC.  The Java to PL/SQL user-defined type mapping is accomplished using the java.sql.SQLData interface (http://java.sun.com/j2se/1.5.0/docs/api/java/sql/SQLData.html).  This interface was designed to allow for the mapping between a SQL UDT (user defined type) and a Java class.  The mapping of an array of these objects to an Oracle collection (or array) is accomplished using two Oracle JDBC classes: oracle.sql.ARRAY (http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/oracle.sql.ARRAY.html) and oracle.sql.ArrayDescriptor (http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/oracle.sql.ArrayDescriptor.html). Next problem … how to create a custom (generic) Programmatic ViewObjectImpl class that will accept a Collection object  as the parameter passing mechanism.  This turned out to be another simple task with the help of some Oracle ADF documentation: (http://download.oracle.com/docs/cd/B31017_01/web.1013/b25947/bcadvvo008.htm).  After that, it’s elementary!  All of the View Objects defined for each of my custom search screens were extended from this new custom ViewObjectImpl class.  The rest of this article details the steps and code I used to implement the solution.  For the sake of brevity, I will limit the code to just the generic ViewObjectImpl and PL/SQL collection mapping.  (The PL/SQL package used to implement the searches has some interesting bits of code, and I will publish that in a separate article.)

Step 1. Create SQL User Defined Types

Step one is to create the SQL user defined types.  In this instance I created two types.  The base type SEARCH_PARAM_TYPE and a collection of SEARCH_PARAM_TYPE called SEARCH_PARAM_ARRAY.  These two user defined types are defined by the following commands:

CREATE OR REPLACE TYPE SEARCH_PARAM_TYPE AS OBJECT(

PARAM_NAME      VARCHAR2(100),

PARAM_OPERATOR  VARCHAR2(4),

PARAM_VALUE     VARCHAR2(1000)

)

/

CREATE OR REPLACE TYPE SEARCH_PARAM_ARRAY AS TABLE OF SEARCH_PARAM_TYPE

/

The SEARCH_PARAM_TYPE contains a parameter name (or label or identifier), a parameter query operator code, and the value for the particular parameter.  The SEARCH_PARAM_ARRAY is just a simple array (PL/SQL Table) that may hold 0 or more SEARCH_PARAM_TYPES.  Each of these SQL types gets mapped in the Java code.

Step 2. Create Java Class to Match SQL Type SEARCH_PARAM_TYPE

Step two is to create a Java class to match the SQL user defined type SEARCH_PARAM_TYPE.  This class must implement java.sql.SQLData and java.io.Serializable.  The code below demonstrates how to implement the class that matches the SQL type SEARCH_PARAM_TYPE:

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

/***
* This class maps to an Oracle User Defined Type called
* SEARCH_PARAM_TYPE. It hold search parameter data
* consisting of a parameter name, operator code,
* and parameter value.
*
*/

public class SearchParamBean implements SQLData, Serializable{

/* Oracle User Defined Type Name */

private String sql_type = ”SEARCH_PARAM_TYPE”;

/* These String represent the attributes in the Oracle user defined type. */

Private String param_name = null;

private String param_operator = null;

private String param_value = null;

public SearchParamBean() {

}

public void setParamName(String p_param_name){
param_name = p_param_name;
}

public String getParamName(){
return param_name;
}

public void setParamOperator(String p_param_operator){
param_operator = p_param_operator;
}

public String getParamOperator(){
return param_operator;
}

public void setParamValue(String p_param_value){
param_value = p_param_value;
}

public String getParamValue(){
return param_value;
}

public String getSQLTypeName()
throws SQLException
{
return sql_type;
}

public void readSQL(SQLInput stream, String typeName)
{
/* No need to implement this */
}

public void writeSQL(SQLOutput stream) throws SQLException
{

/*
the order of values matters! The order must match the structure
of the user defined type exactly!
*/

stream.writeString(param_name);
stream.writeString(param_operator);
stream.writeString(param_value);

}

}

As you can see, this is a pretty straight forward class.  It is important to note that in the writeSQL method above, the order attribute assignements defined with the stream.writeString statements must match the order of attributes as defined in the SQL user defined type exactly (under the covers I’m sure it has to do with marshalling and unmarshalling the serialized objects and not knowing how to intuitively identify what value goes where).

Step 3. Create the Custom ViewObjectImpl Class

The third step is to create the (generic) custom ViewObjectImpl class that your actual ViewObjects will extend.  As stated at the start of this article, I made use of the example in the Oracle ADF documentation that details how to create a View Object using alternate data sources.  The key piece of code is the callStoredFunction method:

/**
* Mechanism to execute PL/SQL functions that populate rows and data
* for the ViewObject
* @param sqlReturnType
* @param stmt
* @param params
* @return
*/

protected Object callStoredFunction(int sqlReturnType,
String stmt,
SearchParamBean[] params) {

CallableStatement plsqlStmt = null;

StringBuffer sb_plsqlBlock = new StringBuffer();

Object dataSet = new Object();

try {

sb_plsqlBlock.append(”begin ? := ”).append(stmt).append(”; end;”);

plsqlStmt = getDBTransaction().createCallableStatement(

sb_plsqlBlock.toString(),0);

/* Register the first bind variable for the return value */

plsqlStmt.registerOutParameter(1, sqlReturnType);

/*This is where we map the Java Object Array to a PL/SQL Array */

ArrayDescriptor desc = ArrayDescriptor.createDescriptor(arrayDescriptor,plsqlStmt.getConnection());

ARRAY objectArray = new ARRAY (desc, plsqlStmt.getConnection(), params);

((OraclePreparedStatement) plsqlStmt).setARRAY(2,objectArray);

plsqlStmt.executeUpdate();

dataSet = plsqlStmt.getObject(1);

}

catch (SQLException e) {

throw new JboException(e);

}

finally {

if (plsqlStmt != null) {

try {

plsqlStmt.close();

}

catch (SQLException e) {System.err.println(e.getMessage());}

}

}

return dataSet;

}

The custom ViewObjectImpl also has methods that construct the ArrayList containing SearchParamBeans.  There are getter and setter methods provided in the class for adding and retrieving SearchParamBean objects. The SearchParamBean[] array you see in the code above is generated internally by the method:

/**
* Constructs the SearchParamBean array from the searchParams ArrayList
* @return
*/

protected SearchParamBean[] getParamBeanArray(){

return (SearchParamBean[])searchParams.toArray(new SearchParamBean[searchParams.size()]);

}

Rather than explain each method in detail, I’ll provide the code with appropriate comments.  It’s fairly straight forward code:

import adf.custom.beans.SearchParamBean;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import oracle.jbo.JboException;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class ObjectArrayVOImpl extends ViewObjectImpl{

/**
* Number of columns in result set returned by this VO.
*/

private int numberOfVOColumns = 0;

/**
* Name of PL/SQL function that will execute the query
* for the VO implemented by this code.
*/

private String queryFunction = null;

/**
* Name of PL/SQL function that will return the
* number of rows returned by this VO.
*/

private String countFunction = null;

/**
* Name of SQL User Defined Type/ PLSQL Collection Type
*/

private String arrayDescriptor = null;

/**
* ArrayList that holds SearchParamBean objects.
*/

private ArrayList searchParams = new ArrayList();

public ObjectArrayVOImpl() {

}

/**
* This method adds a SearchParamBean object to the ArrayList that
* is mapped to the PL/SQL Collection
* @param p_param_bean
*/

public void addSearchParamBean(SearchParamBean p_param_bean){

searchParams.add(p_param_bean);

}

public void clearSearchParamArray(){

searchParams.clear();

}

/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will accept the collection of parameters and execute a query returning the
* desired results.
* @param p_value
*/

public void setQueryFunction(String p_value){

queryFunction = p_value;

}

/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will return the number of rows that will be returned to the View Object from
* the PL/SQL package result set.
* @param p_value
*/

public void setCountFunction(String p_value){

countFunction = p_value;

}

/**
* The array descriptor is is used by the method callStoredFunction
* to map the SearchParamBean[] to the correct PL/SQL Array or SQL
* Type in the backing database.
* @param p_value
*/

public void setArrayDescriptor(String p_value){

arrayDescriptor = p_value;

}

public void setNumberOfVOColumns(int p_value){

numberOfVOColumns = p_value;

}

/**
* Returns the most current ArrayList containing search parameter beans.
* @return ArrayList
*/

public ArrayList getSearchParamArrayList(){

return searchParams;

}

/**
* Constructs the SearchParamBean array from the searchParams ArrayList
* @return
*/

protected SearchParamBean[] getParamBeanArray(){

return (SearchParamBean[])searchParams.toArray(new SearchParamBean[searchParams.size()]);

}

/**
* Overridden framework method.
*
* The role of this method is to ”fetch”, populate, and return a single row
* from the datasource by calling createNewRowForCollection() and populating
* its attributes using populateAttributeForRow().
*/

protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs) {

/*
* We ignore the JDBC ResultSet passed by the framework (null anyway) and
* use the resultset that we’ve stored in the query-collection-private
* user data storage
*/

rs = getResultSet(qc);

/*
* Create a new row to populate
*/

ViewRowImpl r = createNewRowForCollection(qc);

try {

/*
* Populate new row by attribute slot number for current row in Result Set
*/

for (int x=0; x

populateAttributeForRow(r,x,rs.getString(x+1));

}

}

catch (SQLException s) {

throw new JboException(s);

}

return r;

}

/**
* Overridden framework method.
*
* Return true if the datasource has at least one more record to fetch.
*/

protected boolean hasNextForCollection(Object qc) {

ResultSet rs = getResultSet(qc);

boolean nextOne = false;

try {

nextOne = rs.next();

/*
* When were at the end of the result set, mark the query collection
* as “FetchComplete”.
*/

if (!nextOne) {

setFetchCompleteForCollection(qc, true);

/*
* Close the result set, we’re done with it
*/

rs.close();

}

}

catch (SQLException s) {

throw new JboException(s);

}

return nextOne;

}

/**
* Overridden framework method.
*
* The framework gives us a chance to clean up any resources related
* to the datasource when a query collection is done being used.
*/

protected void releaseUserDataForCollection(Object qc, Object rs) {
/*
* Ignore the ResultSet passed in since we’ve created our own.
* Fetch the ResultSet from the User-Data context instead
*/

ResultSet userDataRS = getResultSet(qc);

if (userDataRS != null) {

try {

userDataRS.close();

}

catch (SQLException s) {

/* Ignore */

}

}

super.releaseUserDataForCollection(qc, rs);

}

/**
* Mechanism to execute PL/SQL functions that populate rows and data
* for the ViewObject
* @param sqlReturnType
* @param stmt
* @param params
* @return
*/

protected Object callStoredFunction(int sqlReturnType,
String stmt,
SearchParamBean[] params) {

CallableStatement plsqlStmt = null;

StringBuffer sb_plsqlBlock = new StringBuffer();

Object dataSet = new Object();

try {
sb_plsqlBlock.append(”begin ? := ”).append(stmt).append(”; end;”);

plsqlStmt = getDBTransaction().createCallableStatement(
sb_plsqlBlock.toString(),0);

/* Register the first bind variable for the return value */

plsqlStmt.registerOutParameter(1, sqlReturnType);

/*This is where we map the Java Object Array to a PL/SQL Array */

ArrayDescriptor desc = ArrayDescriptor.createDescriptor(arrayDescriptor,plsqlStmt.getConnection());

ARRAY objectArray = new ARRAY (desc, plsqlStmt.getConnection(), params);

((OraclePreparedStatement) plsqlStmt).setARRAY(2,objectArray);

plsqlStmt.executeUpdate();

dataSet = plsqlStmt.getObject(1);

}

catch (SQLException e) {

throw new JboException(e);

}

finally {

if (plsqlStmt != null) {

try {

plsqlStmt.close();

}

catch (SQLException e) {System.err.println(e.getMessage());}

}

}

return dataSet;

}

/**
* Return a JDBC ResultSet representing the REF CURSOR return
* value from our stored package function.
*/

private ResultSet retrieveRefCursor(Object qc, SearchParamBean[] params) {

ResultSet rs = (ResultSet)callStoredFunction(OracleTypes.CURSOR,

queryFunction,

params);

return rs ;

}

/**
* Retrieve the result set wrapper from the query-collection user-data
*/

private ResultSet getResultSet(Object qc) {
return (ResultSet)getUserDataForCollection(qc);
}

/**
* Store a new result set in the query-collection-private user-data context
*/

private void storeNewResultSet(Object qc, ResultSet rs) {

ResultSet existingRs = getResultSet(qc);

/* If this query collection is getting reused, close out any previous rowset */

if (existingRs != null) {

try {existingRs.close();} catch (SQLException s) {}

}

setUserDataForCollection(qc,rs);

hasNextForCollection(qc); // Prime the pump with the first row.

}

/**
* Overridden framework method.
*/

protected void create() {

getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);

}

/**
* Overridden framework method.
*/

public long getQueryHitCount(ViewRowSetImpl viewRowSet) {

Long count = Long.valueOf(“0″);

if (!searchParams.isEmpty()){

count= (Long)callStoredFunction(OracleTypes.BIGINT,
countFunction,
getParamBeanArray());

}

return count.longValue();
}

/**
* Overridden framework method.
*/

protected void executeQueryForCollection(Object qc,Object[] params,
int numUserParams) {

if (!searchParams.isEmpty()){

storeNewResultSet(qc,retrieveRefCursor(qc,getParamBeanArray()));
super.executeQueryForCollection(qc, params, numUserParams);

}

}

}

Step 4. Create a View Object based on the Custom ViewObjectImpl

The last step is to create a new ViewObject based on the custom ViewObjectImpl that was created in step three.  The easiest way to do this is to use the JDeveloper View Object creation wizard.  Select the ”Rows populated programmatically”  option.  Add any attributes (return columns) you need.   Under the Java section of the View Object definition, and set the VO to extend the new ViewObjectImpl class.   Edit the new View Object class and configure it to use the correct PL/SQL (packaged) functions.  Here is an example:

import adf.custom.model.customViewObjectImpl.ObjectArrayVOImpl;

/* ———————————————————————
— File generated by Oracle ADF Business Components Design Time.
— Custom code may be added to this class.
— Warning: Do not modify method signatures of generated methods.
———————————————————————*/

public class EmployeeQueryVOImpl extends ObjectArrayVOImpl {

/**This is the default constructor (do not remove)
*/

public EmployeeQueryVOImpl() {

/*
Set the name of the PL/SQL function that will act as the
query execution agent. This function will also return
a result set.
*/

this.setQueryFunction(”EmployeeSearch.executeQuery”);

/*
Set the name of the PL/SQL function that will returns the
row count of the query result set for this VO.
*/

this.setCountFunction(”EmployeeSearch.getRowReturnCount”);

/*
This sets the name of the SQL Type representing the PL/SQL collection
or PL/SQL table.
*/

this.setArrayDescriptor(”SEARCH_PARAM_ARRAY”);

/*
The VO needs to know how many columns of data will be returned when
constructing the VO’s result set.
*/

this.setNumberOfVOColumns(5);

}

}

Wrapping it Up …

This article illustrated how to create a custom ViewObjectImpl class that passes an array of Java objects to a PL/SQL Collection based upon a user defined type.  The article touched on a lot of peripheral topics: Creating custom ViewObjectImpl’s, mapping Java objects to SQL user defined types, and converting a Java Object Array into an Oracle Collection/Array.  Basically, it covered a lot of ground in a very brief text.  If you have questions (or suggestions) please feel to shoot me an email.

The Oracle Report Bean

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, June 15, 2008)

The Oracle Report Bean is a cool little bit of code I developed this week that will let you to execute an Oracle Report from within your JEE or plain old Java application.  The bean allows you to configure all of the report execution parameters and contains methods to execute the report as a printed report or have the report streamed back to the client if the chosen format (PDF, RTF, HTML, XML, etc).  The code basically constructs the URL you need to access the Oracle Reports servlet (rwservlet). I’m actually using the Oracle Report Bean as the interface to Oracle Reports in my current ADF Faces project.

Hitting the highlights …

The bean provides a set of constants (static variables) that represent rwservlet keywords (commands).   The code uses two Hash Maps (HashMap classes) for storing parameters.  One Hash Map holds the reports servlet keywords and values, and the other holds input parameters and values for the actual report being executed.  The Hash Maps are hidden behind getter and setter methods.  When setting a reports servlet key word, the developer may either use one of the static keyword references provided by the bean, or simply add one of their choosing (it’s up to them to make sure it’s a legitimate keyword/parameter at that point).  Using the Hash Maps makes it easier to construct the URL later using a simple loop.

The bean provides two methods for executing the report. One method, executePrintedReport, passes the fully constructed URL to the reports servlet and returns the response (either HTML or XML) as a String.  A helper method, formatXMLResponse, is provided to format the response returned by the reports server in client friendly manner (NOTE: The developer needs to set the status format to XML to use this helper method). This method is specifically intended for use with reports bound for external destinations (printers, email, etc.).  The other method, executeBinaryReport, passes the fully constructed URL to the reports servlet and then returns a data stream (InputStream class).  This method is intended for use with reports whose content will be returned directly to the client (the destination for these reports should be set to CACHE).  Use the executeBinaryReport method when you need to return report results directly to the client (desformats PDF, RTF, HTML, XML).  The bean also provides a method that simply returns the URL for executing the report: getReportServerURL.

Sample Bean Usages …

The following code snippet demonstrates how to setup the bean to execute a printed report:

{

OracleReportBean testBean = new OracleReportBean(“appserver”,”7778″,null);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_SERVER,”my_repserv”);

testBean.setKeyMap(“db_key”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_ENVID,”orcl”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_DESTYPE,

OracleReportBean.DESTYPE_PRINTER);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_DESNAME,

“myPrinter-01″);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_REPORT,

“MyReport.rdf”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_ORIENTATION,

OracleReportBean.ORIENTATION_PORTRAIT);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_DESFORMAT,

OracleReportBean.DESFORMAT_HTML);

testBean.setReportParameter(“p_id”,”50″);

testBean.setReportParameter(“p_user”,”JASON BENNETT”);

System.out.println(testBean.formatXMLResponse(testBean.executePrintedReport()));

}

This code snippet demonstrates how to setup the bean to execute and retrieve a binary report (PDF, HTML, XML, RTF, …):

{

OracleReportBean testBean = new OracleReportBean(“appserver”,”7778″,null);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_SERVER,”my_repserv”);

testBean.setKeyMap(“db_key”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_ENVID,”orcl”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_DESTYPE,

OracleReportBean.DESTYPE_CACHE);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_REPORT,

“MyReport.rdf”);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_ORIENTATION,

OracleReportBean.ORIENTATION_PORTRAIT);

testBean.setReportServerParam(OracleReportBean.RS_PARAM_DESFORMAT,

OracleReportBean.DESFORMAT_HTML);

testBean.setReportParameter(“p_id”,”50″);

testBean.setReportParameter(“p_user”,”JASON BENNETT”);

try{

BufferedReader br;

br = new BufferedReader(

new    InputStreamReader(testBean.executeBinaryReport()));

String inputString = null;

while((inputString = br.readLine()) != null){

System.out.println(inputString);

};

}catch(Exception e){

e.printStackTrace();

}

}

The Code …

Finally, here is the code for the Oracle Report Bean:

import java.io.BufferedReader;
import java.io.InputStream;

import java.net.URLConnection;
import java.net.URL;
import java.io.InputStreamReader;
import java.io.StringReader;

import java.util.HashMap;
import java.util.Iterator;

import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLConstants;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLElement;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

/***
* This bean performs the following functions:
* Constructing the Report URL with various parameters passed in by client.
* Sending the report request.
* Execute printed report and retrieve return status in the indicated format.
* Execute a binary report (PDF, RTF, XML, with DESTYPE CACHE) and return the
* InputStream for processing.
*/
public class OracleReportBean {

/* Report Servlet Host Settings */
private String http_host = null;
private String http_port = null;

/* Default path as per generic Oracle Appserver install */
private String servlet_path = “/reports/rwservlet”;

/* Report Servlet URL params */
public static final String RS_PARAM_SERVER = “server”;
public static final String RS_PARAM_REPORT = “report”;
public static final String RS_PARAM_ENVID = “envid”;
public static final String RS_PARAM_DESTYPE = “destype”;
public static final String RS_PARAM_DESFORMAT = “desformat”;
public static final String RS_PARAM_STATUSFORMAT = “statusformat”;
public static final String RS_PARAM_DESNAME = “desname”;
public static final String RS_PARAM_PAGESTREAM = “pagestream”;
public static final String RS_PARAM_DELIMITER = “delimiter”;
public static final String RS_PARAM_ORIENTATION = “orientation”;
public static final String RS_PARAM_DISTRIBUTE = “distribute”;

private String value_keyMap = null;

/* Static values for destination formats */
public static final String DESFORMAT_PDF = “PDF”;
public static final String DESFORMAT_HTML = “HTML”;
public static final String DESFORMAT_POSTSCRIPT = “POSTSCRIPT”;
public static final String DESFORMAT_DELIMITED = “DELIMITED”;
public static final String DESFORMAT_XML = XML;
public static final String DESFORMAT_RTF = “RTF”;

/* Static values for destination types*/
public static final String DESTYPE_MAIL = “mail”;
public static final String DESTYPE_PRINTER = “printer”;
public static final String DESTYPE_FILE = “file”;
public static final String DESTYPE_LOCAL_FILE = “localFile”;
public static final String DESTYPE_CACHE = “cache”;

/* Static values for distribute */
public static final String DISTRIBUTE_YES = “YES”;
public static final String DISTRIBUTE_NO = “NO”;

/*Static values for status format */
public static final String STATUSFORMAT_XML = XML;
public static final String STATUSFORMAT_HTML = “HTML”;

/* Static values for report orientation */
public static final String ORIENTATION_PORTRAIT = “PORTRAIT”;
public static final String ORIENTATION_LANDSCAPE = “LANDSCAPE”;
public static final String ORIENTATION_DEFAULT = DEFAULT;

/* HashMap to hold individual report parameters*/
private HashMap reportParams = new HashMap();

/* HashMap to hold report server params */
private HashMap reportServerParams = new HashMap();

/* Report Servlet */
private StringBuffer reportURL = new StringBuffer();
private String XMLReturnStatus = null;

/***
* Constructor
*/
public OracleReportBean(String p_http_host,
String p_http_port,
String p_servlet_path)
{
http_host = p_http_host;
http_port = p_http_port;

/* If the servlet path is null, we assign the default path. */
if (p_servlet_path != null){
servlet_path = p_servlet_path;
}

/* Default the status format to XML */
setReportServerParam(RS_PARAM_STATUSFORMAT,STATUSFORMAT_XML);

}

/*****
* Private utility methods …
*
*/
private String buildKeyValueString(HashMap p_map){

String map_key = null;
String param_sep = “&”;
String param_equal = “=”;
StringBuffer keyValueBuffer = new StringBuffer();

if (!p_map.isEmpty()){

Iterator mapKeys = p_map.keySet().iterator();

while (mapKeys.hasNext()){
map_key = (String)mapKeys.next();
keyValueBuffer.append(map_key).append(param_equal).append(p_map.get(map_key));

if(mapKeys.hasNext()){
keyValueBuffer.append(param_sep);
}
}
}

return keyValueBuffer.toString();

}

/* Construct the URL for accessing the Oracle Reports Servlet */
private void constructURL(){

String param_sep = “&”;

/* Clearout current URL */
reportURL = new StringBuffer();

/* HOST Section */

reportURL.append(“http://”);

reportURL.append(http_host);

if (http_port != null){
reportURL.append(“:”).append(http_port);
}

/* Add “/” separator if necessary. */
if (servlet_path.indexOf(“/”) > 0){
reportURL.append(“/”);
}

reportURL.append(servlet_path);
reportURL.append(“?”);

if(value_keyMap != null){
reportURL.append(value_keyMap).append(param_sep);
}

/*Construct Report Server Parameter URL component*/
reportURL.append(buildKeyValueString(reportServerParams));

if(!reportServerParams.isEmpty()){
reportURL.append(param_sep);
}

/*Construct Report Parameters URL Component*/
reportURL.append(buildKeyValueString(reportParams));
}

/***
* Getters and Setters for the Reports Servlet
* URL parameter values.
*/

public void setReportServerParam(String p_param,
String p_value){
reportServerParams.put(p_param,p_value);
}

public String getReportServerParam(String p_param){
if(reportServerParams.containsKey(p_param)){
return (String)reportServerParams.get(p_param);
} else {
return null;
}
}

/* Set/Get the value of a Reports KeyMap file */
public void setKeyMap(String p_keyMap){
value_keyMap = p_keyMap;
}

public String getKeyMap(){
return value_keyMap;
}

/* Add/Update and retrieve individual report parameters */
public void setReportParameter(String paramName,
String paramValue){

reportParams.put(paramName,paramValue);
}

public String getReportParameter(String paramName){

if (reportParams.containsKey(paramName)){
return (String)reportParams.get(paramName);
} else {
return null;
}

}

/****
* Construct and return a URL that can be used to execute the report.
*/
public String getReportServerURL(){
constructURL();
return reportURL.toString();
}

/***
* Execute a report whose destination is a printer or other
* non-client destination. (i.e. the report is not coming back
* to the calling client in binary format …)
*/
public String executePrintedReport(){

String v_return_status = null;
StringBuffer serverResponse = new StringBuffer();

try{

BufferedReader br;

br = new BufferedReader(new InputStreamReader(executeBinaryReport()));

String inputString = null;
while((inputString = br.readLine()) != null){
serverResponse.append(inputString);
};

v_return_status = serverResponse.toString();

}catch(Exception e){
e.printStackTrace();
v_return_status = “Error printing report: “+e.getMessage();
}
return v_return_status;
}

/***
* This method is used to execute a binary report
* that is intended to be returned to the
* A binary report is a report that is returned as
* a physical file such as PDF, RTF, etc
* DESTYPE needs to be CACHE in order to get a return
* stream (file …).
*/
public InputStream executeBinaryReport() throws Exception{

URL url = new URL(getReportServerURL());
URLConnection urlc= url.openConnection();

return urlc.getInputStream();
}

/****
* This method takes the XML response generated by the Oracle Reports Server
* servlet and generates a more user friendly response message.
* NOTE: This only works for the XML statusformat type.
*/
public String formatXMLResponse(String p_response){

StringBuffer formattedResponse = new StringBuffer();

try{

DOMParser parser = new DOMParser();
parser.showWarnings(false);
parser.setValidationMode(XMLConstants.NONVALIDATING);
parser.parse(new InputSource(new StringReader(p_response)));

XMLDocument doc = parser.getDocument();

XMLElement elements = (XMLElement)doc.getDocumentElement();

NodeList nl = elements.getElementsByTagName(“error”);

if (nl.getLength() > 0){
String err_component = doc.selectSingleNode(“//error[1]/@component”).getNodeValue();
String err_code = doc.selectSingleNode(“//error[1]/@code”).getNodeValue();
String err_message = doc.selectSingleNode(“//error[1]/@message”).getNodeValue();

formattedResponse.append(“Oracle Reports job submit error; “).append(err_component);
formattedResponse.append(“-“).append(err_code).append(“: “).append(err_message);
}else{

String job_id = doc.selectSingleNode(“//job[1]/@id”).getNodeValue();
String job_status = doc.selectSingleNode(“//status[1]/text()”).getNodeValue();
String job_status_code = doc.selectSingleNode(“//status[1]/@code”).getNodeValue();

if ((job_id == null)||(job_status==null)||(job_status_code==null)){

formattedResponse.append(“Oracle Reports job submit problem; “).append(“Job Id=”).append(job_id);
formattedResponse.append(“, Code=”).append(job_status_code).append(“, Status=”).append(job_status);

}else{

formattedResponse.append(“Report submitted successfully!”);

}

}

}catch(Exception e){
e.printStackTrace();
String error = “Error processing Oracle Report Server response: “+e.getMessage();
System.err.println(error);
formattedResponse.append(error);
}

return formattedResponse.toString();

}
}