Jason Bennett's Developer Corner

 

Home





Click to see the XML version of this web page.




View David Jason Bennett's profile on LinkedIn

 

 

A Little About Jason Bennett ...

I've had an interest in publishing technical articles and HELPFUL code for a few years.  I am (by trade and hobby) a developer who specializes in Oracle technologies and web based architectures.  I have been an employee of both TUSC and Oracle Corporation.  My intent here is to share my ideas and coding experiences with the developer community as a whole.  As with all developers some of my ideas are great and some of them are ....  well you know.  Anyway, I hope you find something here that will aid in your endeavor, or spark a new idea. 

I am more than happy to assist with technical issues and will even write a little code if need be. If you find something on the site that is really useful and you'd like to make a contribution (absolutely up to you and absolutely not required), just click the "Make a Donation" button on the left!

Good luck and good coding !




  Sunday, August 23, 2009


Jason Bennett's Developer Corner Has Moved!

Radio Userland is shutting down it's Radio Weblogs in December 2009.  That being the case, I decided to move this block to another location (featuring a great new look and feel!).  Thanks to Radio Userland for six years of great hosting!

My new location is: http://www.jasonsdevelopercorner.com

The majority of content has been transfered from this site to the new site.


11:47:01 AM    

  Friday, May 01, 2009


Generate FusionCharts Free Flash Charts with Oracle PL/SQL

Since Flash charts have become the norm for delivering visual data to our endusers in a browser based environment, I thought it would be cool to create a method for delivering both the physical flash object and the dataset for that chart straight from the database using PL/SQL. I chose a product called FusionCharts Free (http://www.fusioncharts.com/free/) as my Flash chart (binary object) provider. FusionCharts Free comes with 22 fully functional (free) charts of varying types from the basic charts to combination charts in both 2D and 3D formats. Each chart type requires a specifically formatted XML data set. The PL/SQL API I have developed makes it easy for a developer to store and deploy these flash charts directly from the Oracle database and to create dynamic data sets for each chart type directly from the database. Even though the API allows for "one stop shopping" with regard to chart object loading and data set generation, developers can also use this API solely for data set generation. This makes it easy to integrate data from Oracle databases into existing applications that use FusionCharts Free.

For complete PL/SQL API documentation including sample code, see the following document (opens in new window):Generate Flash Charts with PLSQL

The PL/SQL API can be downloaded here:FusionChartsPLSQL.zip

FusionCharts Free can be downloaded here:FusionCharts Free

The following chart was generated using the PL/SQL API.  Everything from rendering the HTML to streaming the Flash Object to generating the XML data set was accomplished using the API :

The same chart embedded in an Oracle APEX page Report Region (Dynamic PL/SQL Region):
 
 
 
As with all of my code, please feel free to contact me with questions.

2:39:50 PM    

  Monday, March 02, 2009


Creating an XML Cursor using XMLTable and XQuery

 

Oracle has added some really cool XML features over the years.  Not the least of which include the XMLType, XQuery, and the XMLTable function.  Using these three features, we can take any XML document (or fragment) and query it like a table.  This type functionality makes it really easy import and exchange data between systems in an XML message format.  This entry assumes some familiarity with the afore mentioned Oracle features (XMLType, XQuery, and XMLTable), and XPath. (Note: This technique will not work with Oracle 10g Express Edition.  XQuery is not implemented in that version of the Oracle RDBMS.)

The example in this entry will take a simple XML document containing a repeating record set with an identifying attribute for each record set and turn it into a cursor.  Here is the example code:

 

DECLARE

 

CURSOR crsrSampleData(cv_sample_data XMLType) IS

SELECT

       a.record_id,

       a.name,

       a.id_no

FROM

       XMLTable('for $i in $xml return $i//SampleRecord'

                 PASSING cv_sample_data as XML

                 COLUMNS

                    record_id   VARCHAR2(16) PATH '/SampleRecord/@id',

                    name        VARCHAR2(30) PATH '/SampleRecord/SampleName',

                    id_no       NUMBER(10)   PATH '/SampleRecord/SampleIdNo') a;

 

/* Create an XMLType to pass as a cursor variable. */

v_xml_doc XMLType := XMLType('<?xml version="1.0" standalone="yes"?>

                              <SampleData>

                                <SampleRecord id="12345678">

                                  <SampleIdNo>1284793</SampleIdNo>

                                  <SampleName>ALLEN</SampleName>

                                </SampleRecord>

                                <SampleRecord id="56789345">

                                  <SampleIdNo>345678</SampleIdNo>

                                  <SampleName>JOE</SampleName>

                                </SampleRecord>

                                <SampleRecord id="786543456">

                                  <SampleIdNo>4564321</SampleIdNo>

                                  <SampleName>TOM</SampleName>

                                </SampleRecord>

                              </SampleData>');

  

  

BEGIN

 

   FOR data_rec IN crsrSampleData(v_xml_doc) LOOP

  

      DBMS_OUTPUT.PUT_LINE('Record Id='||data_rec.record_id);

      DBMS_OUTPUT.PUT_LINE('Name='||data_rec.name); 

      DBMS_OUTPUT.PUT_LINE('Id#='||data_rec.id_no);

  

   END LOOP;

 

END;

/

 

Execution of the code sample above will produce the following result:

 

Record Id=12345678

Name=ALLEN

Id#=1284793

Record Id=56789345

Name=JOE

Id#=345678

Record Id=786543456

Name=TOM

Id#=4564321  

 

The XMLTable function is the key piece of code in the example above.  The XMLTable function allows us to shred an XML document into a table like structure of our choosing based upon the data contained in the XML document.  In the example above, we map our XMLType cursor variable to the $xml XQuery variable in the XQuery statement 'for $i in $xml return $i//SampleRecord' using the PASSING statement.  The XQuery statement will return the sequence of SampleRecord tag nodes. Next, in the COLUMNS statement of the XMLTable function, we use XPath statements to map the desired data elements and attributes to column names that we define.  We have taken an XML document and put it in a format that is both familiar and easy to handle for the average PL/SQL developer.

 


10:42:36 PM    

  Friday, January 02, 2009


A Simple Method for Generating Excel Reports from Oracle using the ExcelDocumentType

A couple of years ago, I created a user defined type called the ExcelDocumentType that allows a developer to create a custom Excel (XML) document using PL/SQL. The user
defined type has been well received, but a few folks have commented on how tedious the coding can become when creating multiple reports (with a similar tabular layout). I came to the same conclusion recently, and have created a method that greatly simplifies the process. The method makes use of a couple of new user defined types and a PL/SQL package (all provided for your use). It makes creating a multi worksheet document (with a different query for each worksheet) a breeze and with very little coding involved.

(DOWNLOAD THE CODE FROM :The Code )

The Basics

As mentioned above, this new method for generating ExcelDocumentType reports uses a few Oracle user defined types and a small utility package called ExcelDocUtils. Before I get into the details of the PL/SQL package and the supporting user defined types, let's look at a code sample that will generate an Excel document containing three worksheets.

For this example I used the EMPLOYEES table from the Oracle HR demo schema:

CREATE OR REPLACE PROCEDURE employeeReport AS

v_sql_salary VARCHAR2(200) := 'SELECT last_name,first_name,salary FROM hr.employees
ORDER BY last_name,first_name';

v_sql_contact VARCHAR2(200) := 'SELECT last_name,first_name,phone_number,email FROM
hr.employees ORDER BY last_name,first_name';

v_sql_hiredate VARCHAR2(200) := 'SELECT
last_name,first_name,to_char(hire_date,''MM/DD/YYYY'') hire_date FROM hr.employees ORDER BY
last_name,first_name';

excelReport ExcelDocumentType := ExcelDocumentType();

v_worksheet_rec ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;

v_worksheet_array ExcelDocTypeUtils.WORKSHEET_TABLE :=
ExcelDocTypeUtils.WORKSHEET_TABLE();


BEGIN

-- Salary
v_worksheet_rec.query := v_sql_salary;
v_worksheet_rec.worksheet_name := 'Salaries';
v_worksheet_rec.col_count := 3;
v_worksheet_rec.col_width_list := '25,20,15';
v_worksheet_rec.col_header_list := 'Lastname,Firstname,Salary';

v_worksheet_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

-- Contact
v_worksheet_rec.query := v_sql_contact;
v_worksheet_rec.worksheet_name := 'Contact_Info';
v_worksheet_rec.col_count := 4;
v_worksheet_rec.col_width_list := '25,20,20,25';
v_worksheet_rec.col_header_list := 'Lastname,Firstname,Phone,Email';

v_worksheet_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

-- Contact
v_worksheet_rec.query := v_sql_hiredate;
v_worksheet_rec.worksheet_name := 'Hiredate';
v_worksheet_rec.col_count := 3;
v_worksheet_rec.col_width_list := '25,20,20';
v_worksheet_rec.col_header_list := 'Lastname,Firstname,Hiredate';

v_worksheet_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array);

excelReport.displayDocument;

END;
/

The code above generates the following Excel document:



So, what are we looking at here? First, a drastic reduction in the amount of code it takes to generate a basic Excel report. Second, we see some interesting data structures that allow us to accomplish that feat. The code above centers around three structures and a new PL/SQL package called ExcelDocTypeUtils:

(1) A record type called T_WORKSHEET_DATA that holds: the query string that generates the worksheet data; the worksheet name; the number of columns
displayed in the sheet; a list containing the column width for each displayed column; a list of column headers. This record is defined in the ExcelDocTypeUtils package.

(2) A collection object of T_WORKSHEET_DATA records called WORKSHEET_TABLE. Each item in the collection represents one worksheet in the Excel document.

(3) The ExcelDocumentType is that third structure. The WORKSHEET_TABLE collection is passed to a function in the ExcelDocTypeUtils package that returns a
fully populated ExcelDocumentType object.

(4) The ExcelDocTypeUtils package contains a single public utility function called createExcelDocument. This function creates and returns an ExcelDocumentType
object based upon the WORKSHEET_TABLE input parameter. The package makes use of another user defined type called AnonymousFunction. The
AnonymousFunction object (user defined type) allows for the dynamic generation and execution of PL/SQL functions at runtime (sort of like Lambda in Lisp).
Since the user can pass any query (with varying numbers of return values in the result set or select clause), we need to be able to generate a function that can
execute any refcursor (OPEN-FOR FETCH-INTO) and have the ability to create variable numbers "FETCH INTO" recipients.


The Code

I have provided a link in this blog entry that will allow you to download the code and all of the required objects. Download it and give it a try. I have been using at it at my place
of employment to generate scheduled reports for various departments and individuals. So far so good!

For those who just want to take a look at the code, here is the code for the ExcelDocTypeUtils package:

CREATE OR REPLACE TYPE T_ROW AS TABLE OF VARCHAR2(500);
/
CREATE OR REPLACE TYPE T_ROW_NUMBER AS TABLE OF NUMBER(3);
/
CREATE OR REPLACE TYPE RESULT_TABLE AS TABLE OF T_ROW;
/

CREATE OR REPLACE PACKAGE ExcelDocTypeUtils AS

TYPE t_refcursor IS REF CURSOR;

/* This record contains all of the components
required to create an Excel Report worksheet. */
TYPE T_WORKSHEET_DATA IS RECORD(
query VARCHAR2(4000),
worksheet_name VARCHAR2(20),
col_count NUMBER(3),
col_width_list VARCHAR2(500),
col_header_list VARCHAR2(2000)
);


/* An Array of T_WORKSHEET_DATA allows us to create an excel document
with multiple worksheets based on
different queries. */
TYPE WORKSHEET_TABLE IS TABLE OF T_WORKSHEET_DATA;

FUNCTION createExcelDocument(p_worksheet_data WORKSHEET_TABLE) RETURN ExcelDocumentType;


END;
/
sho err;

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

CREATE OR REPLACE PACKAGE BODY ExcelDocTypeUtils AS


/*

Function that returns the element at the
requested position in a delimited string.

*/
FUNCTION getStringElement(p_string VARCHAR2,
p_element NUMBER,
p_delimiter VARCHAR2 := ',',
p_level NUMBER := 0) RETURN VARCHAR2
IS

v_string VARCHAR2(2000) := NULL;
v_element VARCHAR2(2000) := NULL;
v_next VARCHAR2(2000) := NULL;

v_level NUMBER(4) := 0;


BEGIN

v_level := p_level + 1;

v_element := substr(p_string||p_delimiter,1,
instr(p_string||p_delimiter,p_delimiter)-1);

-- need to look ahead to make sure we handle the null elements.
v_next := substr(p_string||p_delimiter,
instr(p_string||p_delimiter,p_delimiter),length(p_delimiter));

IF ((v_level &gt;= p_element) OR
(v_element IS NULL AND v_next != p_delimiter)) THEN

RETURN v_element;

ELSE

v_string := substr(p_string||p_delimiter,
instr(p_string||p_delimiter,p_delimiter)+1,length(p_string));

RETURN getStringElement(v_string,p_element,p_delimiter,v_level);

END IF;


END;

/*==============================================================================*/
/*
This function executes the given query and returns the data
in a RESULT_TABLE Collection object.

*/

FUNCTION buildDataSet(p_query_string VARCHAR2 := NULL,
p_col_count NUMBER := 0) RETURN RESULT_TABLE
IS


r_table RESULT_TABLE := RESULT_TABLE();

v_row_symbol VARCHAR2(20) := 'v_row';
v_row_fetch VARCHAR2(1000) := NULL;
v_row_extend NUMBER(3) := p_col_count;

v_anydata SYS.ANYDATA := NULL;
v_func_result PLS_INTEGER := 0;

v_query VARCHAR2(16000) := p_query_string;

funcResultTable AnonymousFunction := AnonymousFunction();

v_result_func VARCHAR2(32000) := 'FUNCTION getRowSet RETURN RESULT_TABLE '||
'IS '||
' TYPE t_refcursor IS REF CURSOR; '||
' v_table RESULT_TABLE := result_table(); '||
' v_row T_ROW := T_ROW(); '||
' v_query VARCHAR2(4000) := ''''; '||
' v_refcur t_refcursor; '||
'BEGIN '||
' OPEN v_refcur FOR v_query; '||
' LOOP '||
' v_row.extend(); '||
' FETCH v_refcur INTO ;'||
' EXIT WHEN v_refcur%NOTFOUND; '||
' v_table.EXTEND; '||
' v_table(v_table.COUNT) := v_row; '||
' v_row.DELETE; '||
' END LOOP; '||
' RETURN v_table; '||
'END; ';

BEGIN

FOR x IN 1 .. v_row_extend LOOP

v_row_fetch := v_row_fetch||v_row_symbol||'('||x||'),';

END LOOP;
v_row_fetch := RTRIM(v_row_fetch,',');

v_result_func := REPLACE(v_result_func,'',REPLACE(v_query,'''',''''''));

v_result_func := REPLACE(v_result_func,'',to_char(v_row_extend));

v_result_func := REPLACE(v_result_func,'',v_row_fetch);

funcResultTable.defineFunction(p_function_name=&gt;'getRowSet',
p_function_text=&gt;v_result_func,
p_function_type=&gt;DBMS_TYPES.TYPECODE_TABLE);




v_anydata := funcResultTable.executeFunction;
v_func_result := v_anydata.getCollection(r_table);


RETURN r_table;

END;

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

This functiom constructs and returns an ExcelDocumentType
based upon the parameters passed in
by the WORKSHEET_TABLE type parameter.
*/

FUNCTION createExcelDocument(p_worksheet_data WORKSHEET_TABLE) RETURN ExcelDocumentType
IS

resultDocument ExcelDocumentType;
v_row T_ROW := T_ROW();
v_results RESULT_TABLE := RESULT_TABLE();

v_default_col_width NUMBER(3) := 30;
v_col_width NUMBER(3) := 0;

BEGIN

BEGIN

COMMIT;

EXCEPTION
WHEN OTHERS THEN NULL;
END;



resultDocument := ExcelDocumentType();

-- Open Document
resultDocument.documentOpen;

-- Define Customs Styles
resultDocument.stylesOpen;

resultDocument.defaultStyle;

/* Style for Column Header Row */
resultDocument.createStyle(p_style_id =&gt;'ColumnHeader',
p_font =&gt;'Times New Roman',
p_ffamily =&gt;'Roman',
p_fsize =&gt;'10',
p_bold =&gt;'Y',
p_underline =&gt;'Single',
p_align_horizontal=&gt;'Center',
p_align_vertical=&gt;'Bottom');

resultDocument.stylesClose;


FOR ws_index IN 1 .. p_worksheet_data.COUNT LOOP
-- Open Worksheets

resultDocument.worksheetOpen(p_worksheet_data(ws_index).worksheet_name);

FOR colnum IN 1 .. p_worksheet_data(ws_index).col_count LOOP

v_col_width := NVL(TO_NUMBER(getStringElement(p_worksheet_data(ws_index).col_width_list,colnum))
,v_default_col_width);

resultDocument.defineColumn(p_index=&gt;TO_CHAR(colnum),
p_width=&gt;v_col_width);
END LOOP;


-- Heading Row
resultDocument.rowOpen;

FOR colnum IN 1 .. p_worksheet_data(ws_index).col_count LOOP
resultDocument.addCell(p_style=&gt;'ColumnHeader',
p_data=&gt;getStringElement(p_worksheet_data(ws_index).col_header_list,colnum));
END LOOP;

resultDocument.rowClose;

v_results := buildDataSet(p_worksheet_data(ws_index).query,
p_worksheet_data(ws_index).col_count);

FOR r_index IN 1 .. v_results.COUNT LOOP


resultDocument.rowOpen;

v_row := v_results(r_index);

FOR c_index IN 1 .. v_row.COUNT LOOP

resultDocument.addCell(p_data=&gt;v_row(c_index));

END LOOP;

v_row.DELETE;

resultDocument.rowClose;

END LOOP;

v_results.DELETE;

resultDocument.worksheetClose;

END LOOP;
resultDocument.documentClose;

RETURN resultDocument;

END;


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

Wrapping It Up
As with all of the code I post in my blog entries, please feel to use it and modify it as you see fit. Please feel free to contact me with any questions! I hope this makes generating Excel reports a little more easy and lot less tedious.

11:15:04 AM    

  Sunday, November 09, 2008


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

   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 )

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

  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 := <KEY>; '||
                               'v_result SYS.ANYDATA := NULL ; '||
                               '<FUNC> '||
                           'BEGIN '||
                                '<CONVERT> '||
                                ' 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(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BFILE THEN
       v_convert_text := 'v_result := AnyData.convertBFile(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BFLOAT THEN
       v_convert_text := 'v_result := AnyData.convertBFloat(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BLOB THEN
       v_convert_text := 'v_result := AnyData.convertBlob(<NAME>); ';
       v_result_sql   := v_blob_result_sql;
     WHEN DBMS_TYPES.TYPECODE_CHAR THEN
       v_convert_text := 'v_result := AnyData.convertChar(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_CLOB THEN
       v_convert_text := 'v_result := AnyData.convertClob(<NAME>); ';
       v_result_sql   := v_clob_result_sql;
     WHEN DBMS_TYPES.TYPECODE_DATE THEN
       v_convert_text := 'v_result := AnyData.convertDate(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
       v_convert_text := 'v_result := AnyData.convertNumber(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_RAW THEN
       v_convert_text := 'v_result := AnyData.convertRaw(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
       v_convert_text := 'v_result := AnyData.convertVarchar2(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
       v_convert_text := 'v_result := AnyData.convertVarchar(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARRAY THEN
       v_convert_text := 'v_result := AnyData.convertCollection(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
       v_convert_text := 'v_result := AnyData.convertObject(<NAME>); ';
   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,'<FUNC>',SELF.af_function_text);
     v_sql := REPLACE(v_sql,'<CONVERT>',v_convert_text);
     v_sql := REPLACE(v_sql,'<KEY>',v_key);
     v_sql := REPLACE(v_sql,'<NAME>',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;

As with all of my entries and code, feel free to use this or modify it as you see fit.  If you have questions or comments, please feel free to contact me via email.                           


6:53:59 PM    

  Thursday, September 18, 2008


Oracle OpenWorld 2008

   I'll be attending Oracle OpenWorld 2008 in San Francisco next week.  It looks like a pretty large event this year.  I always come away from this event inspired and with a lot of new perspectives and ideas.  This year I plan to focus on application development (big surprise) tracks (mainly ADF Faces and Apex).  If you see my name tag (Jason Bennett CMPD) feel free to say hello. 


9:17:50 PM    

JavaScript: The Utlimate RegExp Email Address Format Validator

     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;
  }
}

 

 


9:04:24 PM    

  Sunday, August 17, 2008


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

 

   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<numberOfVOColumns; 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.

 

 


4:34:06 PM    

  Sunday, June 22, 2008


ExcelDocumentType Code Update ...

I've made an update to the ExcelDocumentType code making it easier to create custom worksheet headers and footers.  The changes and some new examples can be found in the word document "Create Excel Spreadsheets with PL/SQL" that is bundled with code.  The link to the code bundle is:

ExcelDocumentType Code

As always, if you have questions or comments, shoot me an email.


10:27:53 AM    

  Sunday, June 15, 2008


The Oracle Report Bean

   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();
       
    }    
}


10:36:48 PM    

  Thursday, June 05, 2008


ADF Faces: How To Use a Single JSP for both INSERTING and UPDATING

   In this entry, I'll go over my technique for using a single JSP for both inserting and updating data.  This technique utilizes custom methods in the target ViewObjectImpl class, and Page Definition action methods in the Page Definition associated with our ADF/JSF JSP.  There are probably other ways to do this, but this one is fairly straight forward. (Note: I believe Steve Muench recommended a similar technique using methods in the ApplicationModuleImpl class)

Preparing the View Object

Most of the View Objects in my applications (that are used for data entry) contain a bind variable in the WHERE clause that is associated with the target table's primary key (I use dumb keys versus natural keys).   In order to put the page in update mode, we need to pass a PK value to the View Object and pre-populate it prior to rendering the page.  Otherwise, the page is rendered in insert mode.  This is pretty much a universal concept.  How do we go about passing the PK value (or parameter) to the View Object?  First, we need to create a couple of custom public methods in the View Object's ViewObjectImpl class.   One method will take the PK param value and pre-populate the View Object instance, and the other method will clear the state of (empty the data from) the current instance of the View Object.   The first method looks like this:

    /***
     * This method will be used to pre-populate the view object using a passed value.
     */
    
     public void queryViewById(String p_id) {
    
      /*setp_emp_id is ADF generated method for setting the bind variable
        value defined in my View Object (p_emp_id). */


       setp_emp_id(p_id);
       executeQuery();
      
     }

 

The second method (used to clear the data from the view object) looks like this:

 

    /***

     * This method will clear any data out of the existing VO.

     */

    public void clearView() {

      if (getWhereClause() != null) {

        setWhereClause(null);

      }

     

      executeQuery();

    }

Add these two methods (modified to fit your View Object) to the ViewObjectImpl class associated with your View Object.  Some might say that you could combine the two methods (since both will be end up being called to put the page in update mode).  I chose to keep them separate in case I wanted (or needed) to call one without the other at some point.

Preparing the Page Definition File

ADF Faces JSPs use an XML based Page Definition file to bind data to the user interface and to perform some actions prior to rendering the page (overly simplified definition ...).  Each View Object referenced by an ADF Faces JSP component is represented in that page's Page Definition file.  ADF Faces comes with some canned page actions (such as Create and Delete) that can be "dropped" into the page definition file and then applied to event (or actions) that occur later in JSP page.  We can also create custom actions (known as method actions) that are mapped to methods we have created.  The two methods we created in the last section (queryViewById and clearView) will be included as custom method actions the Page Definition file for our ADF/JSF JSP page.  The method action entries go in the "bindings" section of the page, and look like this (I have my own View Object referenced in the example):

    <methodAction id="clearView"
                  MethodName="clearView"
                  RequiresUpdateModel="true" Action="999"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.MyemployeeView1"/>

    <methodAction id="queryViewById"
                  MethodName="queryViewById" RequiresUpdateModel="true"
                  Action="999" IsViewObjectMethod="true"
                  DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.MyemployeeView1">
      <NamedData NDName="p_id" NDValue="#{param.p_emp_id}"
                 NDType="java.lang.String" />
    </methodAction>

Notice the "#{param.p_emp_id}" in NDValue attribute above.  Any request parameter passed to the your page can be accessed using "#{param.<parameter name>}".  The only caveat is if you use the <f:param> tag in conjunction with a command link or command button and the navigation rule behind your navigation action specifies a "redirect" ... the parameters will not be passed.  Here is a quick break down of the attributes for the "methodAction" tag above:

  • The "id" attribute represents the identifier of this methodAction tag as it relates to other components (tags) in the current Page Definition file.

  • The "MethodName" attribute defines the binding name for this method action when called from the "invokeAction" tag ( tag that is responsible for executing the code).
  • The "RequiresUpdateModel" attribute specifies whether or not the model needs to be updated prior to executing the method.
  • The "Action" attribute identifies the internal class for which the data control is created.  Always seems to be 999 for custom class.
  • The "IsViewObjectMethod" attribute indicates whether the method being invoked is defined within a View Object.
  • The "InstanceName" attribute points to the View Object instance as defined by the application data control.
  • The "NamedData" tag is used to map any parameters that the method might take. It contains attributes "NDName" (method parameter name), "NDValue" (value being passed), and "NDType" (data type of the parameter).

 

In order for the page to be rendered in INPUT mode, we have to include a "Create" action in the page definition.  Place the following tag in the "bindings" section (before or after the "methodAction" tags):

 

    <action id="Create" IterBinding="MyemployeeView1Iterator"
            InstanceName="AppModuleDataControl.MyemployeeView1"
            DataControl="AppModuleDataControl" RequiresUpdateModel="true"
            Action="41"/>

 

(Note:  The instance name references my View Object ... you would replace the reference with yours.)

The next step in preparing the Page Definition is to add "invokeAction" tags to the "executables" section.  The "invokeAction" tags define what actions will be executed and under what conditions they will be executed.  The invoke actions for our methods look like:


    <invokeAction id="clearViewObject"
                  Binds="clearView"
                  RefreshCondition="#{adfFacesContext.postback == false and not empty param.p_emp_id}"
                  Refresh="prepareModel"/>

    <invokeAction id="queryViewObject"
                  Binds="queryViewById"
                  RefreshCondition="#{adfFacesContext.postback == false and not empty param.p_emp_id}"
                  Refresh="prepareModel"/>

    <invokeAction Binds="Create"
                            id="invokeCreate" Refresh="renderModel"
                           RefreshCondition="${adfFacesContext.postback == false and empty param.p_emp_id and empty bindings.exceptionsList}"/>

The "RefreshCondition" condition attribute determines whether or not the action will be triggered. The three action invocations below determine how the page will be rendered to the user. If  the call to the page is not a post back from the current page, and the "p_emp_id" param is not null, then associated View Object(s) will be cleared of existing data, passed the p_emp_id param and will execute and populate. This will put the screen in UPDATE mode.  Otherwise, if the "p_emp_id" param is null and the call to the page is not a post back, the screen will be presented in INSERT mode.

Passing a Parameter to the Page

   Passing a parameter is a fairly simple matter.  You could pass it via a command link or command button with a param tag (NOTE: The navigation rule definition for the "action" can not contain a redirect reference.):

<af:commandButton text="Some Text" action="SomeAction">
   <f:param name="p_emp_id" value="123"/>
</af:commandButton>

or

 <af:commandLink text="Some Text" immediate="true"
                               action="SomeAction">
     <f:param name="p_emp_id" value="#{somebinding.value}"/>
  </af:commandLink>


You can also pass a parameter via a standard URL:

http://someserver.some.domain:7778/myapp/faces/somepage.jsp?p_emp_id=123

Passing no parameters will result in the page rendering in INPUT mode.

Wrapping It Up ...

As always, if you have questions, or input, please shoot me an email.


 





9:50:12 PM    

  Saturday, April 12, 2008


Recursive PL/SQL String Parser

In an earlier blog entry, I posted a PL/SQL package called StringTools.  This package contained a routine called getListElement that would let a user retrieve the nth element in a delimited string.  The routine uses iteration (iterates over the passed string until the desired element at position 'n' is located) to get the desired list element.  I was bored today, so I decided to write a variation of that function that uses recursion instead of iteration.  The recursive version has only eleven lines of code (not counting variable declarations and block declaration tokens).  It's fast and useful! 

Here is the code ... give it a try!

/*

   Recursive PL/SQL function that returns the nth element in a delimited String.
   The default delimiter is a ',' (comma) and the level indicator defaults to 0.


*/

CREATE OR  REPLACE FUNCTION getStringElement(p_string    VARCHAR2,
                                                                                      p_element   NUMBER,
                                                                                      p_delimiter VARCHAR2 := ',',
                                                                                      p_level     NUMBER   := 0)   RETURN VARCHAR2
IS

   v_string   VARCHAR2(2000) := NULL;
   v_element  VARCHAR2(2000) := NULL;

   v_level    NUMBER(4)      := 0;


BEGIN

   v_level := p_level + 1;

   v_element  := substr(p_string||p_delimiter,1,instr(p_string||p_delimiter,p_delimiter)-1);

   IF ((v_level >= p_element) OR (v_element IS NULL AND v_next != p_delimiter)) THEN

      RETURN v_element;

   ELSE

      v_string := substr(p_string||p_delimiter,instr(p_string||p_delimiter,p_delimiter)+1,length(p_string));

      RETURN getStringElement(v_string,p_element,p_delimiter,v_level);

   END IF;


END;
/

Example:

The statement:

SELECT getStringElement('This is an interesting test of recursion in PL/SQL',7,' ') from dual;

will return the value "recursion".

 


5:59:55 PM    

  Saturday, March 15, 2008


ADF Faces JavaScript Hack: How to Move a Generated Tag Event Handler

 

You may have noticed that ADF Faces generates some very specific JavaScript code for handling page level (tag level) events (onclick, onChange, onBlur, etc ...).  These event handlers "handle" all of those cool features such as "Partial Submit" and "Auto Submit".   Most of the time, we as developers (and users of ADF) have no direct control over where these framework generated event handlers are placed, or specific control over when they will fire.  The focus of this entry is on how to move a generated event handler from one event to another on the same tag.  For example:  Let's say that you want to implement a feature that will allow a user to type in an employee Id and then have the rest of the employee information (name, etc ..) auto populate. No problem, you just bind a method from a backing bean to a ValueChangeListener and turn on Auto Submit and you are done!  However, what if you also want to use a command link to implement an LOV dialog on the same data entry field and use the data (i.e. the employee id) to limit the results of the LOV?  Now you have a problem.  If you enter data in the employee id field, and then try to navigate to your LOV link, the value change event (onChange) fires before the LOV triggers and nullifies the LOV event.  The solution to the problem is obvious ... just change the triggering event from onChange to onKeyPress (for the auto population ... you would also need to add code to check for the triggering "Hot Key").  But wait ... that code was generated by ADF Faces.  We (the developers) have no direct control over where they place their code.  Now, the problem becomes slightly more challenging (or interesting).  The solution (one solution anyway ...) is to write a JavaScript function that will move it for you and leave the original code intact.  My version of the function looks like this:

 

function moveEventFunction(p_id,p_curr_event,p_new_event,conditionalFunc){

 

   /* Get instance of the tag we are modifying. */

   var formItem = document.getElementById(p_id);

 

   var eventFunc = null;

  

   /* Get text of target event handler code */

   var existingEventCode = formItem.attributes[p_curr_event].value;

 

   /* Only execute the move if the targeted event handler is populated */

   if (existingEventCode.length > 0){

  

      /* Convert text of existing event handler back into a working function */

      var existingFunction = new Function(existingEventCode);

  

      /* Create a function that will assign a new event handler to the desired item event.

       * This needs to be dynamic since we can only make a direct assignment under "normal"

       * circumstances.  Ex. node.onclick=  or node.onchange

       */

 

      var addNewEvent = new Function("node","eventfunc","node."+p_new_event+"=eventfunc;");

     

      /* We assign "nothing" to the existing event handler. This must be dynamic

       * for the same reason as the function above.

       */

 

      var removeExistingEvent = new Function("node","node."+p_curr_event+"='';");

  

      /* If a conditinal function (boolean function that can halt the event ...)

       * is provided, then wrap it around the existing code.  Otherwise, execute

       * the original handler under the new event.

       */

 

       if (typeof(conditionalFunc) != "undefined"){

          eventFunc = function(){ if (conditionalFunc()){ existingFunction()}};

       }else{

          eventFunc = existingFunction;

       }

     

      /* Add the new event handler */

      addNewEvent(formItem,eventFunc);

     

      /* Remove the old event handler */

      removeExistingEvent(formItem);

   }

}

 

The function, moveEventFunction, will move or swap the code assigned to one event handler and move it to a new event handler on the same node (or tag).  The function also gives the developer the option of wrapping a new conditional (Boolean) function around the original event handler code.

 

Now that we have a solution, how do we apply it?  You have a couple of options.  First, place the call to moveEventFunction at the bottom of the page (between script tags).  It will fire after all of the ADF/JSF  HTML has been rendered. 

 

<script>

     moveEventFunction(<id of target tag>,<existing event name>,<new event    name>,<optional conditional function>);

</script>

 

 

However, if you plan on making the target tag a partial target (dynamic refresh), you will lose the swap.  The second option handles this situation.  Place the call on the onFocus event of the target tag.  Doing so guarantees it the swap.

 

onFocus="moveEventFunction(this.id,<existing event name>,<new event name>,<optional conditional function>);"

 

The conditional function (the last parameter) can perform multiple tasks prior to executing the main function (original function).  In the case of the scenario above, the conditional function would check for the "Hot Key" that triggers our auto population. However, it must return true or false at the end of its execution.  The conditional function needs to be defined in the manner:

 

   var fnc_MyConditionalFunction = function MyConditionalFunction(){ ...}

 

This will allows allows you to pass the actual function as a parameter without it actually being executed (or evaluated) . Instead of passing in MyConditionalFunction, you pass in fnc_MyConditionalFunction.

 

(Another option that isn't JavaScript related might be to get an instance of the UIComponent prior to page rendering and make the swap ... haven't tried that yet.)

 


10:22:48 AM    

  Thursday, March 06, 2008


ADF Faces - How To Get a DBTransaction Object Anytime You Need One

    If you decide to execute a SQL statement or some PL/SQL code from with your ADF Faces application outside of the ADF BC framework (i.e. without using Entity Objects and View Object), you will need access to a JDBC connection.  Do you need to instantiate your own connections, or maintain a separate connection pool?  The answer is no!  ADF BC provides us with (abstracted) access to the JDBC connection object that is associated with our current application session.  Access is provided through the oracle.jbo.server.DBTransaction class.  You can get an instance of the DBTransaction object from several places within the ADF BC Framework: ViewObjectImpl, TransactionEventImpl, EntityImpl, and ApplicationModuleImpl.  All of these classes have a method called getDBTransaction() that return an instance of the DBTransaction object.  Armed with this knowledge, how do we go about getting an instance of DBTransaction anytime we want one?  By "anytime time we want one", I mean externally from a View Object or Entity Object instance.  The answer is pretty simple.  We just need to access to an instance of the current ApplicationModule.  Using the following code, you can get a DBTransaction object anytime you want:

    /***
     * This method returns the current instance of the session DBTransaction object.
     * The method below is implemented from a Singleton Object for utilitarian purposes.
     */
    public static synchronized DBTransaction getDBTransaction(){
   
       FacesContext ctx = FacesContext.getCurrentInstance();
      
       ValueBinding vb            = ctx.getCurrentInstance().getApplication().createValueBinding("#{data}");
       BindingContext bc          = (BindingContext)vb.getValue(ctx.getCurrentInstance());

       //Use your own data control name when creating the DataControl object
       //Look in the DataBindings.cpx file ... the id attribute of the BC4JDataControl tag
       DataControl dc             = bc.findDataControl("MyApplicationModuleControl");
       ApplicationModuleImpl am   = ((ApplicationModuleImpl)(ApplicationModule)dc.getDataProvider());
      
       return am.getDBTransaction(); 
      
   }

   Place the code listed above in a utility class.  I prefer to use a static class or Singleton. 


8:56:17 PM    

  Thursday, January 31, 2008


ADF Faces: Retrieve and/or Set values in a SelectOneChoice Component backed by a RowSetIterator

This entry covers how to retrieve values from a SelectOneChoice component that is backed (populated) by a ViewObject as defined in a ADF Faces page definition file.

   I've been working on a fairly large and complex ADF Faces project for my current employer.  I often find myself having to both retrieve and set values in SelectOneChoice components in my backing beans.  I was initially confounded by the fact that the HTML generated by the component did not include my own code values behind the select options. Rather, each option (option tag) uses a numeric value instead of the actual code you would normally see if you were creating the HTML yourself (such as "NC" as the code for description "North Carolina").  After some digging, I found that the numbers correspond to the Iterator index values of the Iterator that is bound to SelectOneChoice component.   By the way, setting the valuePassThru attribute on the component to true did not work for some reason.

   Armed with this new knowledge, I was able to create two utility methods that reside in my base (utility) managed bean class. 

  The first method, getSelectOneChoiceValues, retrieves both the code (the actual code from your database or view object) and the description value from the Iterator that is bound to the SelectOneChoice component given the index value.  Here is the code for first method:

    /***
     *  This method returns the code and desc values from a SelectOneChoice.
     *  Parameters
     *  (1) p_iteratorIndex - represents the index value of the iterator row we want. You get
     *                        this value from bound ADF component.
     *                        Ex. (Integer)boundSelectItem.getValue()
     *
     *  (2) p_iterator      - Name of the Iterator that populates the SelectOneChoice component
     *  (3) p_code_colname  - Column attribute name of the column containing the code value (get this
     *                        from the PageDefinition file for the screen, or the ViewObject that
     *                        is bound to the Iterator.)
     *  (4) p_codedesc_colname - Column attribute name of the column containing the code desc (get this
     *                           from the PageDefinition file for the screen, or the ViewObject that
     *                           is bound to the Iterator.)
     *  (5) p_noselection_val  - Set this value to "true" if you chose to add a "No Selection" row
     *                           or null value row to your selection object. This row will become the
     *                           zero index row.  This "null" value row is not represented in the
     *                           Iterator and throws the index values off by 1.
     *
     *  The return value is a HashMap, but you could create a simple class for this as well.      
     */
    protected HashMap getSelectOneChoiceValues(int p_iteratorIndex,
                                               String p_iterator,
                                               String p_code_colname,
                                               String p_codedesc_colname,
                                               boolean p_noselection_val)
    {
        HashMap hm_lovVals = new HashMap();
        String  lovTypeCode = null;
        String  lovTypeDesc = null;
        int     noSelectIncrementor = 0;
       
        if (p_noselection_val){
            noSelectIncrementor = 1;
        }
       
                                      
        try{

           // The code for "getRowSetIterator" can be found in the blog entry I published directly
           // before this entry or Google "Jason getRowSetIterator"

           RowSetIterator lovIter       = getRowSetIterator(p_iterator);
       
           Row iterRow = null;
       
           iterRow = lovIter.getRowAtRangeIndex(p_iteratorIndex-noSelectIncrementor);
       
           lovTypeCode = iterRow.getAttribute(p_code_colname).toString();
           lovTypeDesc = iterRow.getAttribute(p_codedesc_colname).toString();
          
        }catch(Exception e){
            System.err.println("Error looking up values for LOV iterator "+p_iterator);
        }
       
        //the keys "code" and "desc" should not be hard coded values in your production code ...
        hm_lovVals.put("code",lovTypeCode);
        hm_lovVals.put("desc",lovTypeDesc);
       
        return hm_lovVals;
    }

    The second method, getSelectOneChoiceIndex, performs the opposite function.  Given code value and code column, it returns the actual index value.  This allows you set the value of SelectOneChoice component on the screen ( Ex. selectionComponent.setValue(indexVal); ).  Here is the code for the second method:

    /***
     *  This method returns the index value for an item in a SelectOneChoice.
     *  Parameters
     *
     *  (1) p_iterator      - Name of the Iterator that populates the SelectOneChoice component
     *  (2) p_code_value    - Code value whose index we are looking for.
     *                        Ex. We pass in "NC" and get the index for the row in the Iterator
     *                        that contains the code "NC".
     *
     *  (3) p_code_column   - Column attribute name of the column containing the code value (get this
     *                        from the PageDefinition file for the screen, or the ViewObject that
     *                        is bound to the Iterator.)
     *
     *  (4) p_noselection_val  - Set this value to "true" if you chose to add a "No Selection" row
     *                           or null value row to your selection object. This row will become the
     *                           zero index row.  This "null" value row is not represented in the
     *                           Iterator and throws the index values off by 1.
     *
     *  The return value is an Integer.      
     */
   
    protected Integer getSelectOneChoiceIndex(String p_iterator,
                                              String p_code_value,
                                              String p_code_column,
                                              boolean p_noselection_val){
        int  v_code_index = 0;                                 
        int  noSelectIncrementor = 0;
       
        if (p_noselection_val){
            noSelectIncrementor = 1;
        } 
       
        try{
       
           // The code for "getRowSetIterator" can be found in the blog entry I published directly
           // before this entry or Google "Jason getRowSetIterator"

           RowSetIterator lovIter       = getRowSetIterator(p_iterator);
           Row            lovRow        = null;
          
           for (int x=0;x<lovIter.getRowCount();x++){
              
               lovRow = lovIter.getRowAtRangeIndex(x);
              
               if(lovRow.getAttribute(p_code_column).equals(p_code_value)){
                   v_code_index = x+noSelectIncrementor;
                   break;
               }
              
           }
          
          
        }catch(Exception e){
            System.err.println("Error looking up index value for LOV iterator "+p_iterator);
        }                                         
       
        return new Integer(v_code_index);
       
    }

 


7:58:45 AM    

  Friday, November 23, 2007


An ADF Faces Base Class for a Backing/Managed Bean

   Over the last few months, I've been working on an ADF based project for my current employer.  The project is my first venture into JSF and ADF.  In this time I've developed a little base class that I extend when creating a backing bean (or managed bean) for my pages.  The base class contains several helpful utility methods that I thought I would share.  Some of these I discovered for myself, and a few others were inspired by samples provided by folks like: Steve Muench, Frank Nimphius, and Jonas Jacobi.  I have put the code below.  Each method has a comment at the top to explain what the method does:

The Code ...

import javax.faces.application.Application;
import javax.faces.context.FacesContext;

import oracle.binding.OperationBinding;
import oracle.adf.model.binding.DCBindingContainer;
import oracle.adf.model.binding.DCDataControl;
import oracle.adf.model.binding.DCIteratorBinding;

import oracle.adf.view.faces.component.core.data.CoreTable;

import oracle.adf.view.faces.context.AdfFacesContext;


import oracle.binding.BindingContainer;

import oracle.jbo.ApplicationModule;

import oracle.jbo.Row;
import oracle.jbo.RowSet;
import oracle.jbo.server.EntityImpl;
import oracle.jbo.RowSetIterator;
import oracle.jbo.ViewObject;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.uicli.binding.JUCtrlValueBindingRef;

public class ManagedBeanBase {
   
   
    /**
    * Contains page binding references
    * This can be populated manually or
    * automatically based on config setting
    * in faces-config.xml
    *
    * This is a sample of an entry you would put in
    * the faces-config.xml file.  Notice the 'managed-property'
    * tag with property name 'bindings'.  This property will be
    * set by the faces servlet using the setBindings method below.
    *
    * <managed-bean>
    * <managed-bean-name>MyPageBackingBean</managed-bean-name>
    * <managed-bean-class>MyPageMB</managed-bean-class>
    * <managed-bean-scope>request</managed-bean-scope>
    *   <managed-property>
    *    <property-name>bindings</property-name>
    *    <value>#{bindings}</value>
    *   </managed-property>
    * </managed-bean>
    */
    */
    protected  BindingContainer  bindings;
   
    public ManagedBeanBase() {
    }

    public BindingContainer getBindings() {

        return this.bindings;
    }
   
    public void setBindings(BindingContainer bindings) {
        this.bindings = bindings;
    }
   
    /**
    *  Use this to manually set page bindingings.
    */
    public void setBindings(){
        FacesContext context = FacesContext.getCurrentInstance();
        Application app = context.getApplication();
        bindings = (DCBindingContainer) app.getVariableResolver().resolveVariable(context, "bindings");
    }
   

    /**
    * Call this method to refresh the binding container
    * with any changes.
    */   
    protected void refreshBindingContainer(){
   
        DCBindingContainer dcBind = (DCBindingContainer)bindings;
        dcBind.refresh();
       
    }
   
    /**
    * This method is used the removed the currently selected
    * row from and ADF (JSF) table.  The method requires
    * that the table object be passed in as a parameter.
    */
    protected void deleteSelectedRow(CoreTable pageTable){
 
        JUCtrlValueBindingRef rwData = (JUCtrlValueBindingRef)pageTable.getSelectedRowData();
       
        Row rw = rwData.getRow();
       
        rw.remove();
       
    }
   
    /**
    * This method takes the name of a given iterator (as defined in the page def file)
    * and returns the current rowset.
    */
    protected RowSetIterator getRowSetIterator(String p_iterator){
        
        DCBindingContainer dcBind = (DCBindingContainer)bindings;
        DCIteratorBinding iterBind= (DCIteratorBinding)dcBind.get(p_iterator);
       
        return iterBind.getLovRowSetIterator();
       
       
    }
   
    /**
    * This method will return the ViewObject (object) associated
    * with a given iterator.
    */
    protected ViewObject getViewObjectFromIterator(String p_iterator){
       
        DCBindingContainer dcBind = (DCBindingContainer)bindings;
        DCIteratorBinding iterBind= (DCIteratorBinding)dcBind.get(p_iterator);
       
        return iterBind.getViewObject();
       
       
    }
   
    /**
    * This method will execute the query associated the view object
    * with which the iterator is associated.
    */
    protected void executeIterQuery(String p_iterator){
       
        DCBindingContainer dcBind = (DCBindingContainer)bindings;
    
        DCIteratorBinding iterBind= (DCIteratorBinding)dcBind.get(p_iterator);
       
        iterBind.executeQuery();
       
    }
   
    /**
     *   This method issues a rollback again the current transaction.
     */
    protected void rollbackCurrentChanges(){
        
        DCBindingContainer dcBind = (DCBindingContainer)bindings;
        dcBind.getDataControl().getApplicationModule().getTransaction().rollback();
       
    }


    /**
    * Manually add a page component to the partial target list
    * for partial page refresh.
    */
    protected void addPartialTarget(UIComponent p_target_object){
        AdfFacesContext.getCurrentInstance().addPartialTarget(p_target_object);
    }
   
}

 


12:39:11 PM    

  Sunday, August 05, 2007


OC4J Instance Startup Dependencies

   OracleAS 10g can be configured to allow startup dependencies between OC4J instances.  In other words, OC4J instance OC4J_B cannot start until OC4J instance OC4J_A has started.  This is along the same lines as one MS Windows service being dependant on another at startup.    This is useful (and necessary) if OC4J instance OC4J_A contains services that provide some sort of critical data that some application in OC4J instance OC4J_B needs as part of its initialization.  The only catch to this is that the OC4J instances can't be located within the same ias-component.  An ias-component consists of one or more process-types.  An example of a process-type would be an OC4J instance.  For example, every time you create a new OC4J instance using dcmctl or OracleAS 10g Enterprise Manager, it is placed under the ias-component called "OC4J".  Other examples of common ias-components are: LogLoader, dcm-daemon,  HTTP_Server,  and WebCache.  Executing the command "opmnctl status" will give a listing of each ias-component and the name of each process-type associated with it.  

The easiest way to create a new ias-component and associated OC4J instance (or process-type) with a dependency on another OC4J instance is:

1. Create a new OC4J instance (process-type) using dcmctl or OracleAS Enterprise Manager (skip this step if you plan on moving an existing OC4J instance).
2. Open the opmn.xml file and create a new ias-component entry (make sure you back-up the existing file and shutdown opmn before doing this …)

           <ias-component id="My Custom Component">
           ...
           </ias-component>

3. Locate the OC4J instance you created in step-one (it should be under the "OC4J" ias-component).  Cut the entire tag set defining the OC4J instance (process-type) and paste it into your new ias-component (You can also do this with an existing OC4J instance):

           <ias-component id=" My Custom Component ">
            <process-type id="OC4J_B" module-id="OC4J">
               <module-data>
                  <category id="start-parameters">
                     <data id="java-options" value="-server -Djava.security.policy= /app/oracle/product/10.1.2/midtier/j2ee/ OC4J_B /config/java2.policy -Djava.awt.headless=true"/>
                     <data id="oc4j-options" value="-properties"/>
                  </category>
                  <category id="stop-parameters">
                     <data id="java-options" value="-Djava.security.policy= /app/oracle/product/10.1.2/midtier/j2ee/ OC4J_B /config/java2.policy -Djava.awt.headless=true"/>
                  </category>
               </module-data>
               <start timeout="900" retry="2"/>
               <stop timeout="120"/>
               <restart timeout="720" retry="2"/>
               <port id="ajp" range="12501-12600"/>
               <port id="rmi" range="12401-12500"/>
               <port id="jms" range="12601-12700"/>
               <process-set id="default_island" numprocs="1"/>
            </process-type>
         </ias-component>

4.  To define the dependency between the OC4J instance defined under your new ias-component and another OC4J instance defined under a different ias-component, you need to add a dependency definition under the ias-component tag:

           <ias-component id=" My Custom Component ">
               <dependencies>
                  <managed-process ias-component="OC4J" process-type="OC4J_A" process-set="default_island" autostart="true" />
               </dependencies>

            <process-type id="OC4J_B" module-id="OC4J">
               <module-data>
                  <category id="start-parameters">
                     <data id="java-options" value="-server -Djava.security.policy= /app/oracle/product/10.1.2/midtier/j2ee/ OC4J_B /config/java2.policy -Djava.awt.headless=true"/>
                     <data id="oc4j-options" value="-properties"/>
                  </category>
                  <category id="stop-parameters">
                     <data id="java-options" value="-Djava.security.policy= /app/oracle/product/10.1.2/midtier/j2ee/ OC4J_B /config/java2.policy -Djava.awt.headless=true"/>
                  </category>
               </module-data>
               <start timeout="900" retry="2"/>
               <stop timeout="120"/>
               <restart timeout="720" retry="2"/>
               <port id="ajp" range="12501-12600"/>
               <port id="rmi" range="12401-12500"/>
               <port id="jms" range="12601-12700"/>
               <process-set id="default_island" numprocs="1"/>
            </process-type>
         </ias-component>

5. Save the opmn.xml file and start up opmn and all of its processes.


That's all there is to it!  Now OC4J_B will not start until OC4J_A has started.


6:10:29 PM    

  Monday, July 23, 2007


Oracle ADF Faces Tip:  How to hide the Asterisk (*) when the "required" Property is Set to "true" 

  If this one is common knowledge, then forgive me.  I just jumped into ADF and JSF a few weeks ago (I was a STRUTS man), and am drinking from the firehose!  There is a property on some of the ADF Faces components (inputText, selectOneChoice, etc ...) called "required".  If set to true, this property will ensure that the user enters a value for the item (in DB terms, it makes the field a NOT NULL field).  This is a convenient feature with one small exception.  Setting the property to true places a green asterisk(*) on the left side of the data entry item.  The asterisk appears whether you want it to or not  and there is no option/property available for turning it off (NOTE: This is an ADF inputText feature, not a standard JSF inputText feature).  This is an issue if your development standards say to put an asterisk on the RIGHT side of the input field (as mine do).

   The solution to the problem was fairly straight forward.  Since I had implemented a custom skin for this ADF Faces application and created a custom CSS document (see this article by Jonas Jacobi), I simply over-rode the existing style for the required icon with this one:

   .AFRequiredIconStyle { display:none }

Presto!  No more little green asterisk.


6:23:15 PM    

  Saturday, June 23, 2007


Passing Mod_Plsql Basic Authentication Credentials Across Applications

 

I was recently tasked with the "seamless" integration of a large Web PL/SQL based application and newer J2EE application.  The older application uses a mod_plsql DAD to authenticate users (using the Basic Authentication method), and the newer application built with Oracle ADF and JSF.  Each user has a separate database account, and application security is database role based.  The problem: How do I pass the users credentials (basically their database login information) to the J2EE application in order to create a user specific database connection?  The user accesses the new application from a hyperlink on the existing application's main menu. It sounds easy enough.  Simply write a servlet filter to capture the "AUTHORIZATION" HTTP header and decode the credentials, right?  Wrong.  Since the J2EE application in seemingly different domain or session space, the "AUTHORIZATION" header is not passed. By the way, SSO and other "new" technologies were not currently available.  So, how do we get around the cross domain/session space issue?  It turns out to be fairly simple.  Both applications are served through the same Apache web-server (OracleAS 10g R1), allowing us to use a very simple method that leverages mod_rewrite (pre-installed and configured with OracleAS 10g).

 

The Method ...

The method uses the following three steps:

 

  1. The URL from the older (calling) application to the newer (target) application has to look as if it is using the mod_plsql DAD. This gives the false impression that the target of the URL is in the same domain/session space as the current application.  The URL would look something like: http://servername:7778/<;dad name>/application.do.  This basically fools the browser into passing the domain/session space HTTP headers (specifically the "AUTHORIZATION" header) along with the request.
  2. In the httpd.conf file (or a separate include .conf file) associated with your Apache instance, place a mod_rewrite directive like this one:

 

      RewriteRule ^(/<dadname>/application.do)$ /<context root>/application.do [PT]

 

  1. In the Servlet Filter (or whatever portion of your application that can capture incoming HTTP request headers), get the value of the "AUTHORIZATION" HTTP request header.  Here is an example of how this code looks using Java: String credentials = req.getHeader("AUTHORIZATION"); (req is an instance of HttpServletRequest).

Decrypting the Credentials

  After you get the credentials using the three step method above, you need to decrypt them.  The value of the "AUTHORIZATION" HTTP header will be a string encoded using the base64 encoding method.  It will look something like this:

   Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ== (<Encryption type> <Credentials>)

Decrypting the string is pretty simple using a base64 decoder such as Sun’s sun.misc.BASE64Decoder class.  Before decrypting the string, we  need parse out the credential portion of the string (i.e. we need to remove the "Basic" portion of the string). When decrypted, the above string will look like this:

    Aladdin:open sesame (<username>:<password>)

Once we get the credentials decrypted, simply parse the string to obtain the username and password.  The following is a simple Java class that will aid in decrypting and returning the username and password:

 

import java.util.StringTokenizer;

import sun.misc.BASE64Decoder;

 

public class BasicAuthDecoder {

 

    private String authType = "Basic";

   

    private String codedCredential = null;

    private String username        = null;

    private String password        = null;

 

    public BasicAuthDecoder(String rawCredentialString) {

   

        if (rawCredentialString != null){

            setCodedCredential(rawCredentialString);

            decodeCredentials();

        }

 

    }

   

    private void setCodedCredential(String rawCredentialString){

       

        StringTokenizer authTokens = new StringTokenizer(rawCredentialString);

       

        if (authTokens.hasMoreTokens()){

           

            if (authTokens.nextToken().equalsIgnoreCase(authType)){

                codedCredential = authTokens.nextToken();

            }

           

        }      

         

    }

   

    private void decodeCredentials(){

       

        String decodedCredentialString = null;

        try{

       

            BASE64Decoder decoder = new BASE64Decoder(); 

            decodedCredentialString = new String(decoder.decodeBuffer(codedCredential));

                      

             StringTokenizer authTokens = new StringTokenizer(decodedCredentialString,":");

            

             if (authTokens.hasMoreTokens()){

                

                 username = authTokens.nextToken();

                 password = authTokens.nextToken();

                

             }

        

         }catch(Exception e){

        

            System.err.println("Error decoding user credentials "+e.getMessage());

        }      

       

    }

   

    public String getUsername(){

        return username;

    }

   

    public String getPassword(){

        return password;

    }

}

 


1:09:54 PM    

  Tuesday, May 22, 2007


Outstanding Book! (Dreaming In Code)

I haven't used my weblog to recommend books in the past.  However, I have decided to intersperse recommendations of good books related to our profession as software developers.  The first book I'd like to recommend is called "Dreaming In Code" by  Scott Rosenberg.  The book takes the reader through the entire process of developing an innovative software project in Silicon Valley with some of the pioneers and legends of the internet age.  The book is not dry and reads almost like a novel.  I have even caught myself laughing out loud (and earning strange looks from my wife for laughing at a "computer book").  I believe everyone from project managers to developers will relate to and enjoy this very well written book!


7:34:32 AM    

  Thursday, October 26, 2006


Create Excel Spreadsheets with PL/SQL: ExcelDocumentType

 [Updated 04/14/2009 to allow for custom tag attributes on cell, row, and column definition. Document columns calculated differently, allowing for flexible

  layout.  Jason Bennett]

 

This little project started about a year ago (give or take), after the need arose to generate Excel formatted reports containing data from our primary Oracle database instance (at work).  In the past, I had done this by manually running a query in TOAD and saving the results as an Excel spreadsheet.  This works great for those little adhoc requests we all get from time to time from our customers.  However, the manual method can quickly get out of hand and distract us from our "real" work ... developing applications.  So, being the ever resourceful developer, I decided that it would be cool to automate the process.  My search for pre-existing, free, and easy to use tools to generate Excel spreadsheets directly from the database turned up very little.  The search did turn up with some very useful information on Excel XML.  Armed with this new knowledge, I set out to create a PL/SQL based utility that would easily allow the user to generate formatted Excel spreadsheets from their data.  I ended up creating an Oracle user defined object type called ExcelDocumentType.

 

   The ExcelDocumentType generates an Excel XML document as its end product.  Excel XML documents are automatically recognized by IE and the Windows OS as Excel documents, and are treated as such when opened (double click ...).  The documents generated by the object work very well with MS Office 2003 and Open Office 2.0 (However, you have to open the file thru Open Office Calc explicitly ... it will not open it automatically). The object gives the user the ability to create documents with the following features:

 

  • Creation of multiple Worksheets
  • Create and apply user defined styles
  • Apply formulas to cells
  • Create custom print headers
  • Define rows, columns, and cells

 

The object provides three methods of document retrieval:

  • The document can be retrieved as a CLOB.
  • The document can be delivered through mod_plsql to a web browser.
  • The document can be retrieved in a PL/SQL table via user defined type called ExcelDocumentLine.

 

Object Member Functions and Procedures

The ExcelDocumentType makes use of a global temporary table called ExcelDocumentStore .  The global temporary tables acts as storage for each document segment as the document is being constructed.  A previous version used VARRAYs, but that method had some serious performance issues for very large documents.  The object contains the following member functions and procedures:

 

  • ExcelDocumentType - Constructor (Function).
  • documentOpen -  Open a document for writing.
  • documentClose - Close document for writing.
  • worksheetOpen - Opens a new worksheet, and takes a parameter for naming the worksheet.
  • worksheetClose - Closes the worksheet.
  • worksheetHeaderOpen - Open the header segment (of a worksheet) for writing. Takes no parameters.
  • worksheetHeaderValues - Sets the worksheet header values (may be used one or more times if constructing a header with LEFT, RIGHT, and CENTER positioned values):
    • Header Text
    • Header location (LEFT,RIGHT,CENTER)
    • Header format string
    • Header font size
  • worksheetHeaderClose - Closes worksheet header segment.
  • stylesOpen - Open the style definition segment.
  • createStyle - Create user defined styles.  Each style has to be named so that it may be referenced later.  Styles items are limited to the following parameters:
    • Style name or label ('My Style')
    • Font (Font name in Upper lower format: 'Times New Roman')
    • Font Family ('Roman')
    • Font Size
    • Bold (Y or N)
    • Italic (Y or N)
    • Underline (underline type, such as 'Single')
    • Text Color ('Blue', 'Red', etc ...)
    • Cell Color ('Blue','Red', etc ...)
    • Cell Texture ('Solid' or other valid Excel texture types)
    • Vertical Alignment ('Center','Left','Right','Top', etc ...)
    • Horizontal Alignment ('Center','Left','Right','Top', etc ...)
    • Text Wrap (Y or N)
    • Number Format (Standard Excel number format strings)
    • Custom XML - add custom (but compliant) XML for additional style elements.
  • defaultStyle - use the default Excel document style.
  • stylesClose - Close styles segment
  • defineColumn - Defines column and takes index and width as parameters.
  • rowOpen - Starts a new row segment. Takes an optional style parameter to set row color, font, etc.
  • rowClose - Close row segment.
  • addCell - Add a cell segment to a row segment. Cell reside in a specific column within a specific row. This procedure takes the following types of parameters:
    • Column Index - as defined with defineColumn procedure
    • Data - The data that will occupy the cell.
    • Data Type - String, Date, Number, etc (Excel standard)
    • Style - Apply user defined style by label name.
    • Formula - Valid Excel cell formula string.
  • displayDocument - generates the entire Excel XML document for display thru a web browser or other application that can accept a document via HTTP (thru mod_plsql).
  • getDocument - Function that generates a CLOB version of the Excel XML document.
  • getDocumentData - Function that returns a Collection of type ExcelDocumentLine. Each bucket in the collection will contain a line from the XML document.  The collection type is a PL/SQL table.

 

The following code sample demonstrates how to use the ExcelDocument Object:

 

  CREATE OR REPLACE PROCEDURE excelObjectTest

  IS

 

      demoDocument  ExcelDocumentType;

 

      -- An array containing each line of the document.

      -- Very useful when writing the document to a file

      -- on the file system

      documentArray    ExcelDocumentLine := ExcelDocumentLine();

 

      -- The document can be retrieved as a CLOB and stored in a table.

      clobDocument     CLOB;

 

      -- File handle used in writing the document to file

      v_file        UTL_FILE.FILE_TYPE;

 

  BEGIN

     demoDocument := ExcelDocumentType();

     -- Open the document
     demoDocument.documentOpen;

     -- Define Styles

     demoDocument.stylesOpen;

     -- Include Default Style
     demoDocument.defaultStyle;

     -- Add Custom Styles

     /* Style for Column Header Row */
     demoDocument.createStyle(p_style_id =>'ColumnHeader',
                               p_font     =>'Times New Roman',
                               p_ffamily  =>'Roman',
                               p_fsize    =>'10',
                               p_bold     =>'Y',
                               p_underline =>'Single',
                               p_align_horizontal=>'Center',
                               p_align_vertical=>'Bottom');

    /* Styles for alternating row colors. */
    demoDocument.createStyle(p_style_id=>'NumberStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid',
                               p_number_format => '###,###,###.00',
                               p_align_horizontal => 'Right');

    demoDocument.createStyle(p_style_id=>'TextStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid');

    /* Style for numbers */
    demoDocument.createStyle(p_style_id => 'NumberStyle',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'ColumnSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Blue');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'RowSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Red'); 


     -- Close Styles
     demoDocument.stylesClose;

     -- Open Worksheet
     demoDocument.worksheetOpen('Weekly Earnings');

     -- Define Columns
     demoDocument.defineColumn(p_index=>'1',p_width=>30); -- Emp Name
     demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
     demoDocument.defineColumn(p_index=>'3',p_width=>16);
     demoDocument.defineColumn(p_index=>'4',p_width=>16);
     demoDocument.defineColumn(p_index=>'5',p_width=>16);
     demoDocument.defineColumn(p_index=>'6',p_width=>16);
     demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column

    -- Define Header Row
   demoDocument.rowOpen;

   --Define Header Row Data Cells
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');

   demoDocument.rowClose;

   /*------------------------------------*/
   /* Sheet Data would normally be       */
   /* data driven via cursor loops       */
   /* or other means.                    */
   /* The purpose here is to demonstrate */
   /* the features of the utility.       */
   /*------------------------------------*/

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'50000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'14000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 2
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Joe Smith');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Jones');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'300');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'350');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'2000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'159');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Chris P.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'45000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Summary Row 5
   demoDocument.rowOpen;
   demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.rowClose;

  -- Close the Worksheet
  demoDocument.worksheetClose;

  -- Open New Worksheet
  demoDocument.worksheetOpen('Weekly Earnings 2');

     -- Define Columns
     demoDocument.defineColumn(p_index=>'1',p_width=>30); -- Emp Name
     demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
     demoDocument.defineColumn(p_index=>'3',p_width=>16);
     demoDocument.defineColumn(p_index=>'4',p_width=>16);
     demoDocument.defineColumn(p_index=>'5',p_width=>16);
     demoDocument.defineColumn(p_index=>'6',p_width=>16);
     demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column

    -- Define Header Row
   demoDocument.rowOpen;

   --Define Header Row Data Cells
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');

   demoDocument.rowClose;

   /*------------------------------------*/
   /* Sheet Data would normally be       */
   /* data driven via cursor loops       */
   /* or other means.                    */
   /* The purpose here is to demonstrate */
   /* the features of the utility.       */
   /*------------------------------------*/

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'80000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'75000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'94000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 2
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Joe Smith');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Smith');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'77000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'850');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'359');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Jeff F.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'99000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Summary Row 5
   demoDocument.rowOpen;
   demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.rowClose;

  -- Close the Worksheet
  demoDocument.worksheetClose;

  -- Close the document.
  demoDocument.documentClose;

  -- Get CLOB Version

  clobDocument := demoDocument.getDocument;

  -- Display the document to browser.
  demoDocument.displayDocument;

  -- Write document to a file
  -- Assuming UTL file setting are setup in your DB Instance.
  -- 
  -- documentArray := demoDocument.getDocumentData;

   -- Use command CREATE DIRECTORY FOO as ''
   -- to create a directory for the file.

   --v_file := UTL_FILE.fopen('FOO','ExcelObjectTest.xml','W',4000);

   --FOR x IN 1 .. documentArray.COUNT LOOP
 
    -- UTL_FILE.put_line(v_file,documentArray(x));
   
  -- END LOOP;

  -- UTL_FILE.fclose(v_file); 

EXCEPTION
  WHEN OTHERS THEN
      /* For displaying web based error.*/
      htp.p(sqlerrm);
      /* For displaying command line error */
      dbms_output.put_line(sqlerrm);

 END;

/

 

The following document was generated by the code above (opens a new window):

 

ExcelObjectTest.xml

 

The Code ...

 

The following link will allow you to download a zip file containing the ExcelDocumentObject and the demonstration code above.  The object has been tested in both Oracle9i and Oracle 10g.

 

ExcelDocumentType Code

 

As with all of my code, please feel free to improve it (and pass along the improvement) and distribute it.

 

 

 

 

 

 


10:52:21 PM    

  Saturday, July 08, 2006


Pulling Down BLOBs and CLOBs with a PL/SQL Function

(Revised)

The functions below will allow the user to reach out to any website or web server and grab any binary or text file.  The cool thing is that it is executed straight from the database.  The functions can be used to populated BLOB  or CLOB columns in a table (great for grabbing images and storing them ... mugshots .. personel photos etc.).  Here are the function:

CREATE OR REPLACE FUNCTION getWebBLOB(p_url VARCHAR2 := NULL) RETURN BLOB
IS

   v_data       UTL_HTTP.html_pieces;
   v_chunk      VARCHAR2(32000) := NULL;
   v_blob BLOB;

BEGIN

   v_data := utl_http.request_pieces(p_url);

   DBMS_LOB.createTemporary(v_blob,FALSE,DBMS_LOB.CALL);

   FOR piece_cnt IN 1 .. v_data.count LOOP

      v_chunk := v_data(piece_cnt);

      DBMS_LOB.writeappend(v_blob,length(v_chunk),utl_raw.cast_to_raw(v_chunk));

   END LOOP;

   RETURN v_blob;

END;
/

CREATE OR REPLACE FUNCTION getWebCLOB(p_url VARCHAR2 := NULL) RETURN CLOB
IS

   v_data       UTL_HTTP.html_pieces;
   v_chunk      VARCHAR2(32000) := NULL;
   v_clob       CLOB;

BEGIN

   v_data := utl_http.request_pieces(p_url);

   DBMS_LOB.createTemporary(v_clob,FALSE,DBMS_LOB.CALL);

   FOR piece_cnt IN 1 .. v_data.count LOOP

      v_chunk := v_data(piece_cnt);

      DBMS_LOB.writeappend(v_clob,length(v_chunk),v_chunk);

   END LOOP;

   RETURN v_clob;

END;
/

If you have any questions, shoot me an email.


7:15:45 PM    

  Wednesday, January 25, 2006


PL/SQL Age Calculation Function

Ok ... a lot of people may already know this method for determining a person's age, but it is useful.  In fact, I used it today.  If you work in a business where you are frequently asked to generate reports or find data with an age range as a search criteria, or as an end result, then you need this little bit of code/SQL in your bag of tricks.

CREATE OR REPLACE FUNCTION getAge(p_dob DATE, p_base_date DATE := SYSDATE) RETURN NUMBER

IS

      v_age NUMBER(3) := 0;

BEGIN

     v_age := TRUNC(MONTHS_BETWEEN(p_base_date,p_dob)/12);

     RETURN v_age;

END;


10:39:45 PM    

  Thursday, January 05, 2006


Oracle Project Raptor

I was kind of excited yesterday morning when I opened my email from Oracle Technology Network and noticed that there was new product to play with!  The product is called Project Raptor (here after refered to as 'Raptor').  Raptor is a GUI tool for browsing and creating database objects, creating and running SQL and PL/SQL scripts.  Raptor was built on the JDeveloper framework, so it was very easy to get up and running with it.  The product is reminiscent of the old Enterprise Manager, but with a much better interface.  The product also offers some of the same features we TOAD users have come to rely on such as a script results windows, a DBMS OUTPUT window with buffer size control, an EXPLAIN PLAN feature for tuning, and even an OWA Output section that will display the HTML code generated by your HTP and OWA package procedures and functions.  The only thing that TOAD has over Raptor at this point (in my opinion), is the ability to export query results in XLS format (Excel Spreadsheet).  Raptor does have several SQL result export formats that are cool, such as XML, Insert script, text file, and CSV (which is sort of in Excel format).  Overall, I give it a thumbs up!  If you haven't downloaded it yet, I strongly encourage you to do so.  Remember, this product is still in pre-production status.  It will be bundled with JDeveloper in the future.

Here is the link:Project Raptor

 

 


8:37:54 AM    

  Tuesday, November 29, 2005


Automatically Print Web-based Oracle Reports (PDF format) to a  User's Local/Default Printer

 

Working for a major metropolitan police department, I get some interesting development assignments.  One such assignment was to allow officers connected to our network via VPN to send their printed reports to the default printer assigned to the machine they are using.  No problem!  Oracle Reports has a setting that will send a PDF version of the report back to the user's web browser using the Adobe Acrobat plug-in.  They can just hit the print button in the Acrobat viewer and print the report to any printer available to the machine! No sweat!  Oh ... by the way ... the officer is not allowed to save the report to his/her machine (for security and privacy reasons).  This is where the task got sticky.  The Acrobat Viewer has a 'Save' button.  This feature cannot be turned off programmatically.  A command has to be impeded in PDF document at the time it is created to keep it from being saved.  Unfortunately, Oracle Reports doesn't offer this as an option for reports being created in a PDF format (at least in our version).  After a little searching, I found a solution.  The rest of this entry will show you how to do it using an open source tool and some standard browser features.  Nothing is impossible ...

 

The Solution ...

The solution to the problem was to imbed a JavaScript command in the PDF document that forced an auto print to the default printer while accessing the document from a hidden IFRAME (width 0, height 0).  The only hitch … how the heck do we imbed a JavaScript command in a PDF document that is being generated by Oracle Reports.  This is where the open source option came into play.  After some searching, I came across an open source project called iText (http://www.lowagie.com/iText/). iText is a library that allows you to generate or modify (to some extent) PDF files on the fly.  iText is available in both Java and .NET flavors.  Being a Java fan and an Oracle Application Server customer, I chose the Java flavored iText solution.  I created a servlet that acted as a proxy to the Oracle Reports Servlet.  The servlet, acting as a proxy, is then able to received the data stream from the Oracle Reports servlet.  In this case, the data stream is a PDF document.  Using the iText library, I added the auto print command to the PDF document and then had the proxy servlet pass the altered document to the user's browser.  To keep the user from saving this document, I had the request for the report sent to the proxy servlet through an IFRAME with a width and height of 0.  This kept the user from seeing the document in the Adobe Acrobat plug-in and kept them from saving the document. 

 

The Code ...

Being a left-handed right-brained developer, I tend to learn more from actually seeing the code than reading about it.  The iText APIs are well documented at the iText website (http://www.lowagie.com/iText) along with examples.  JavaScript commands as they relate to PDF documents can be found at http://partners.adobe.com/public/developer/pdf/library/index.html.   I have included comments in the code in key locations.  As usual, if you have questions, shoot me an email.  Here is the code for the servlet:

 

import com.lowagie.text.PageSize;

import com.lowagie.text.pdf.PdfWriter;

import java.io.BufferedInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

 

import java.io.InputStream;

import java.net.URLConnection;

import javax.servlet.*;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import java.net.URL;

import java.net.MalformedURLException;

 

//iText Open Source PDF APIs ... awesome code!

import com.lowagie.text.Document;

import com.lowagie.text.DocumentException;

import com.lowagie.text.pdf.PdfReader;

import com.lowagie.text.pdf.PdfCopy;

import com.lowagie.text.pdf.PdfImportedPage;

 

public class PDFLocalPrint extends HttpServlet

{

  private static final String CONTENT_TYPE = "application/pdf";

 

  public void init(ServletConfig config) throws ServletException

  {

    super.init(config);

  }

 

  private ByteArrayOutputStream getDoc(String p_url)

  {

      Document document = null;

      PdfCopy  writer   = null;

      PdfImportedPage   page;

      ByteArrayOutputStream baos = null;

     

      int v_pages       = 0;

     try{

    

      URL url              = new URL(p_url);

      URLConnection urlc= url.openConnection();

      int length = urlc.getContentLength();

     

      InputStream in = urlc.getInputStream();

      baos = new ByteArrayOutputStream();

     

      PdfReader oracleReport = new PdfReader(in);

                                                oracleReport.consolidateNamedDestinations();

   

      v_pages = oracleReport.getNumberOfPages();

     

      document = new Document(oracleReport.getPageSizeWithRotation(1));

 

      writer   = new PdfCopy(document,baos);

     

      document.open();

 

      //Copy content from PDF streaming from Reports Server to new PDF Document.

      for(int i=0;i<v_pages;)

      {  i++;

         page = writer.getImportedPage(oracleReport,i);

         writer.addPage(page);   

      }

 

      //Append Javascript command Add Silent Print command

      //"this.print({bUI:false,bSilent:true,bShrinkToFit:true});"

     

     writer.addJavaScript(Constants.SILENT_PRINT, false);

     

      document.close();

     

     }catch(Exception e)

     {

       System.out.println("Error - "+e.getMessage());

       e.printStackTrace();

     }

   

     return baos;

  }

 

  public void service(HttpServletRequest request, HttpServletResponse response)

  throws ServletException, IOException

  {

    String v_report_url = "";

 

    boolean view_only   = false;

   

    //This output stream will be the carrier for the .PDF file.

    ServletOutputStream out = response.getOutputStream();

    ByteArrayOutputStream doc = null;

    try

    {

      //URL to Reports Server Servlet passed by the calling application.

      //The URL could actually point to any .PDF doc accessible via HTTP.

      v_report_url = request.getParameter("p_report_url");

     

   

    }

    catch(Exception e)

    {

      System.out.println(e.getMessage());

      e.printStackTrace();

    }

 

   

    if(v_report_url.length()>0)

    {

     

      //Pass the altered document back to the requesting browser or application.

      doc = getDoc(v_report_url);

      response.setContentType(CONTENT_TYPE);

      response.setContentLength(doc.size());

      doc.writeTo(out);

      doc.close();

      out.flush();

    }else{

        out.println("No Data");

        out.close();

       

    }

  }

}

 

 

 

 


10:59:20 AM    

  Tuesday, May 31, 2005


OracleAS 10G R2 Installation Issues

 

    I spent some time this weekend installing OracleAS 10g R2 on my Linux box (running SUSE 9.3 Professional Edition).  I came across an interesting and frustrating error when the installer got to the OPMN OHS configuration in the final phase of the installation.  I got an error similar to the following:

 

Error
--> Process (pid=<PID>)
failed to start a managed process after the maximum retry limit
Log:
<ORACLE_HOME>/opmn/logs/HTTP_Server~1

Configuration assistant "OPMN Configuration Assistant" failed

In <ORACLE_HOME>/opmn/logs/HTTP_Server~1 :
<ORACLE_HOME>/Apache/Apache/bin/apachectl start: execing httpd

 

The error log in /Apache/Apache/logs directory had only the following error:

 

<ORACLE_HOME>/apache/apache/logs/httpd.pid overwritten -- Unclean shutdown of
    previous Apache run?

 

Not very helpful error messages!  I did some searching on Metalink (and Google) and found references to the issue, but no answers (as usual J).

 

Anyway, I found the issue.  Here is the solution as I posted it in the Oracle forum for OHS:

 

This error only seems to occur if you are installing the OracleAS 10g R2 Infrastructure with the Oracle Certificate Authority option. The error seems to be somewhere in the ocm_apache.conf file. I spent about an hour troubleshooting this issue this weekend. I did an install on Suse 9.3 Professional and hit this issue. I had chosen OCA as an install option. I noticed that if I commented out the oracle_apache.conf reference in httpd.conf file OHS would start up with no problem. I tracked the issue in the oracle_apache.conf file to the ocm_apache.conf file. I was unable to determine which specific portion of the conf file was the issue. It may have to do with a package that included with the SuSe distribution related to SSL. I blew away my Infrastructure install and re-installed without the OCA option. The error did not occur the second time (no OCA option).

Another error I encountered had to do with a false report that OPMN had not started.  I received a gray popup indicating that the OPMN manager had failed to start.  This was a false report by the installer.  I opened a terminal window and navigated to the $ORACLE_HOME/opmn/bin and executed opmnctl start which told me that the process was already running.  I checked the installation log and noticed that the installer had located the process running, but the subsequent check for the ons.log had a ',' in it's request.  Looks like they parsed the return to their query incorrectly.  Anyway, all I had to do was choose the 'Continue' option in the gray dialogue box and everything ran smoothly (until I hit the error mentioned at the start of this entry).


12:21:20 PM    

  Tuesday, March 08, 2005


Converting a String To a CLOB and Converting a CLOB to a String

The following code snippets demonstrate: (1) how to convert a Java String object to an Oracle CLOB object and (2) how to convert an Oracle CLOB object into a Java String object.  The code assumes you know how to define a JDBC connection and that you have imported the Oracle SQL types library.  I did not invent this method, but I did have a hard time finding an example that filled in all of the blanks.  Let me know if you have questions.  Here is the code:

   try
   {
      String v_clob_string = <STRING OF ANY LENGTH>;
  
      Connection conn = <define JDBC Connection>;
     
      CLOB v_clob = CLOB.createTemporary(conn,false,CLOB.DURATION_CALL);
      
      CallableStament cs = conn.prepareCall("begin ClobPackage.insertClob(?); end;");
 
      int x = v_clob.putString(1,p_clob_string);

      cs.setClob(1,v_clob);

      cs.execute();

      cs.close();

   }catch(Exception e){

        <Your exception handling code>

   }finally{

      <Might want to clean up connections here>

   }

 

   try
   {
   
      Connection conn = <define JDBC Connection>;
      
      CallableStament cs = conn.prepareCall("begin ? = ClobPackage.getClob; end;");
 
      cs.registerOutParameter(1,OracleTypes.CLOB);

      cs.execute();

       Clob  v_clob         = cs.getClob(1);
       long  v_len          = v_clob.length();
       String v_clob_string = v_clob.getSubString(1,(int)v_len);

       cs.close();

   }catch(Exception e){

        <Your exception handling code>

   }finally{

      <Might want to clean up connections here>

   }


5:07:24 PM    

  Saturday, February 05, 2005


Uploading Binary Files (BLOBS) to An Oracle Table Using a Servlet

 

The purpose of this entry is to provide the reader with the actual code required to upload a binary file (image, excel spreadsheet, Word Doc, PDF, etc) from a web browser and store it in the BLOB column of database table.  After searching high and low for a specific example (I did find one that showed how to upload the file and save it to disk, which helped quite a bit), I vowed to publish my code for anyone else searching for a similar example. Some of this code comes from an actual application, and may not seem to 'flow', as I have omitted non-relevant portions.  The goal here is to impart the method for the upload and storage of BLOB data from the browser to the database.

 

Multipart Form-Data ... A brief explanation

It is important at the start to realize that binary data is uploaded as multipart/form-data  data from a form on a web page.  This means that the parameter values passed up from the form cannot be retrieved from the receiving servlet using the traditional request.getParameter method.  The form tag will look something like:

 

<FORM action="/uploadservlet/imageUpload.do"

               enctype="multipart/form-data"

               method="POST" >

 

The file upload component of the form uses an input tag of the format:

 

<INPUT type="file" name="my_image">

 

The rest of the form input parameter are just like any other:

 

<INPUT type="text" name="p_user">

 

All of the data is sent up in a specific format.  The passed parameters are strings of the form:

 

Content-Disposition: form-data; name="p_user"

Joe smith

--------------AaB03x

 

In the example above, p_user is a form parameter name and Joe Smith is the value.  NOTICE that there is a blank line between the parameter string and the actual value. Each of the parameter string value pairs is separated by a delimiter or boundary. The boundary line separating each individual data element looks something like:

 

--------------AaB03x

 

The value of this boundary varies. The first line in the stream will contain this value. It is important to grab and store this value, as the code will later demonstrate.

 

The binary content, or file, will be the last parameter string value pair in the input stream (basically the rest of the input stream).  The parameter string for the file looks like this:

 

content-disposition: form-data; name="my_image"; filename="test.jpeg"

Content-Type: image/pjpeg

 

... FILE CONTENT ...

 

 

The next string or line after the file parameter string is the content indicator string.  The actual content of the file follows content type string. NOTICE that the file data is separated from the content string by a blank line. For more info see

(http://www.faqs.org/rfcs/rfc1867.html).

 

The Code .....

 

The code for uploading the image into the data consists of a PL/SQL function on the data base side that takes the BLOB passed from the servlet and stores it in a new table, and the Java Servlet and related classes that handle the upload request.  First, lets look at the PL/SQL.  The following code comes from an application used to upload juvenile mugshots into a database in a juvenile arrest system:

 

   FUNCTION pre_load_image_table(p_juvmast_id JUVENILE_MASTERS.ID%TYPE       := NULL,

                                 p_label      JUVENILE_PHOTOS.label%TYPE     := NULL,

                                 p_user         JUVENILE_PHOTOS.created_by%TYPE   := NULL,

                                 p_mime_type  JUVENILE_PHOTOS.MIME_TYPE%TYPE := NULL) RETURN NUMBER

   IS

 

     v_return NUMBER := NULL;

 

   BEGIN

 

 

     EXECUTE IMMEDIATE 'SELECT jpo_seq.nextval FROM dual' INTO v_return;

     

     EXECUTE IMMEDIATE 'INSERT INTO juvenile_photos(id,juvmast_id,label,lineup_order,mime_type,juv_image,created_by,date_created) '||

                       ' values (:id,:p_juvmast_id,:p_label,:p_lineup,:p_mime_type,empty_blob(),:created_by,:date_created)'

     USING

           v_return,

           p_juvmast_id,

           p_label,

           getNextLineupOrder(p_juvmast_id),

           p_mime_type,

           p_user,

           SYSDATE;

 

     RETURN v_return;

 

   END;

 

   FUNCTION externalSaveImage(p_juvmast_id JUVENILE_MASTERS.ID%TYPE := NULL,

                              p_label      JUVENILE_PHOTOS.label%TYPE := NULL,

                              p_user         JUVENILE_PHOTOS.created_by%TYPE   := NULL,

                              p_mimetype   JUVENILE_PHOTOS.MIME_TYPE%TYPE := NULL) RETURN t_refcursor

   IS

 

     v_refcur kbcJuvenileImages.t_refcursor;

    

     v_pk     NUMBER(12) := 0;

 

   BEGIN

 

--Creates a new row in the image table with an empty_blob() and returns new PK.

      v_pk := pre_load_image_table(p_juvmast_id,UPPER(p_label),p_user,p_mimetype);

 

      OPEN v_refcur FOR 'SELECT juv_image FROM juvenile_photos WHERE id = :id FOR UPDATE' USING v_pk;

 

      RETURN v_refcur;

 

   END;

 

 

The first function is called by the second to create a new row in the image table containing the data passed from the webpage describing the image.  Please note that an EMPTY BLOB is inserted into the blob column of this table.  The second function selects the BLOB column of the new record for update and passes this value back to the calling Java application as a refcursor.  This creates an input stream that allows the blob data to be streamed back to the database. 

 

The image upload Java class is our next piece of code.  The class extends another class that I have not included.  The base class is merely a JDBC helper class containing code to obtain a connection object from a connection factory.  The important code here is the code that illustrates how to stream the binary data back to the database:

 

 

package imageupload;

 

import java.io.*;

import java.sql.*;

import javax.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

import javax.servlet.ServletInputStream;

 

/**

 * The purpose of this class is to take the remaining portion

 * of the input stream containing the body of the uploaded file

 * and pass it to a database stored proc that saves it to a

 * BLOB column in a table.

 * This class extends a JDBC helper class called

 * JDBC connection base.

 */

public class ImageLoader extends JDBCConnectionBase

{

 

  public void insertImageData(ServletInputStream p_image_stream,

                              int    p_id,

                              String p_label,

                              String p_user,

                              String p_mime_type,

                              String p_delimiter){

                                  

       try{

      

           BLOB    v_image_blob = null;

           String  lineCheck;

           setPooledConnection();

  

           //Using the stored procedure removes some of the complexity from

           //the Java application.

           CallableStatement cs =

                conn.prepareCall("begin ? := kbcJuvenileImages.externalSaveImage(?,?,?,?); end;");

       

           cs.registerOutParameter(1,OracleTypes.CURSOR);

           cs.setInt(2,p_id);

           cs.setString(3,p_label);

           cs.setString(4,p_user);

           cs.setString(5,p_mime_type);

           cs.execute();

       

           rset = (ResultSet)cs.getObject(1);

          

           if (rset.next()){

            v_image_blob = ((OracleResultSet)rset).getBLOB(1);

           }

          

           OutputStream outstream = v_image_blob.getBinaryOutputStream();

        

           byte[] buffer = new byte[256];

           int length = -1; 

          

           while ((length = p_image_stream.readLine(buffer,0,256)) != -1)

           {

             lineCheck = new String(buffer,0,length);

            

             if (lineCheck.indexOf(p_delimiter)== -1) {

               outstream.write(buffer,0,length);

             }

           }

           outstream.close();

           cs.close();

          

           conn.commit();

 

      

      

     }catch(SQLException e)

     {

       System.err.println("Error creating image record: "+e.getMessage());

       v_error.append("Error creating image record: ").append(e.getMessage());

     }catch(Exception e)

     {

       System.err.println("Error creating image record: "+e.getMessage());

       v_error.append("Error creating image record.").append(e.getMessage());

       e.printStackTrace();        

     }finally

     {

       closeConnections();    

     }

  }

}

 

The next bit of Java code is the actual servlet that handles the request for upload.  I think I have sufficiently commented the code, but if you have questions just shoot me an email.

 

package imageupload;

 

import oracle.sql.BLOB;

import java.io.ByteArrayInputStream;

import java.util.HashMap;

import javax.servlet.*;

import javax.servlet.http.*;

import javax.servlet.ServletInputStream;

import java.io.PrintWriter;

import java.io.IOException;

 

public class uploadServlet extends HttpServlet

{

 

 //The following six variables represent tokens

 //used to parse the content submitted in the

 //multi-part form.

  private static final String CONTENT_TYPE         

                                                   = "text/html; charset=windows-1252";

 

  private static final String DATA_INDICATOR       

                                                 = "Content-Disposition: form-data; name=\"";

 

  private static final String FIELD_INDICATOR       = "name=\"";

  private static final String FILE_INDICATOR        = "filename=\"";

  private static final String FILE_MIME_INDICATOR   = "Content-Type:";

  private String              REQUEST_DELIMITER     = null;

 

  //HashMap to store field names and values

  //passed from the calling form.

  private HashMap fields = new HashMap();

 

  /**

   * Check to see if the line passed in

   * contains a field type indicator.

  */

  private boolean checkField(String p_value)

  { 

     boolean v_return = false;

 

     if(p_value.indexOf(FIELD_INDICATOR) != -1)

     {

       v_return = true;

     }

    return v_return;

  }

 

  /**

   * Check to see if the line passed in

   * contains a file type indicator.

  */

  private boolean checkFile(String p_value)

  { 

     boolean v_return = false;

 

     if(p_value.indexOf(FILE_INDICATOR) != -1)

     {

       v_return = true;

     }

    return v_return;   

  }

 

  /**

   * Check to see if the line passed in

   * is a delimiter line. Delimiters

   * separate the different sections

   * of multipart form.

  */

  private boolean checkDelimiter(String p_value)

  { 

     boolean v_return = false;

 

     if(p_value.indexOf(REQUEST_DELIMITER) != -1)

     {

       v_return = true;

     }

    return v_return;   

  }

 

  /**

   * Check to see if the line passed in

   * contains the file content type indicator.

  */

  private boolean checkContentIdicator(String p_value)

  { 

     boolean v_return = false;

 

     if(p_value.indexOf(FILE_MIME_INDICATOR) != -1)

     {

       v_return = true;

     }

    return v_return;   

  }

 

  /**

   * Check to see if the line passed in

   * contains a data value type indicator.

  */

  private boolean checkDataIndicator(String p_value)

  { 

     boolean v_return = false;

 

     if(p_value.indexOf(DATA_INDICATOR) != -1)

     {

       v_return = true;

     }

     return v_return;  

  }

 

 

  /**

   * Returns the value of field stored in the

   * 'fields' HashMap based upon the key passed

   * in to the method.  The key relates directly

   * to the parameter names in the passing form.

  */

  private String getFieldValue(String p_name)

  {

     String v_value = new String();

 

     try{

       v_value =(String)fields.get(p_name);

     }catch(Exception e)

     {

       v_value = "";

     }

    return v_value.trim();

  }

 

 

  /**

   * Parse the value of the parameter passed in

   * on line containing parameter/field data.

  */ 

  private String parseFieldValue(ServletInputStream in) throws Exception

  {

     byte[] line             = new byte[128];

     int i                   = 0;

     StringBuffer fieldValue = new StringBuffer(128);

     String       newline;

    

    newline = new String();

   

    while (i != -1 && !checkDelimiter(newline)) {

       i = in.readLine(line, 0, 128);

       newline = new String(line, 0, i);

       if (!checkDelimiter(newline)){

          fieldValue.append(newline);

       }

    }

    return fieldValue.toString();

  }

 

  /**

   * Parse the value of the parameter name in

   * on line containing parameter/field data.

  */ 

  private String parseFieldName(String p_name)

  {

      int     pos = 0;

      String  v_content = new String();

 

      pos = p_name.indexOf(FIELD_INDICATOR);

     

      if (pos > 0){

        v_content = p_name.substring(pos+6, p_name.length()-3);

       

       }

      

    return v_content;

  }

 

    /**

   * Parse the value of the content type passed in

   * on line containing file content data.

  */ 

  private String getContentType(String p_name)

  {

      int     pos = 0;

      String  v_content = new String();

 

      pos = p_name.indexOf(":");

     

      if (pos != -1){

        v_content = p_name.substring(pos+1,p_name.length());

       

       }

      

    return v_content;

  }

 

  /**

   * Place field name and value in the "fields" HashMap

   * for easy retrieval later.  The field (or parameter)

   * name becomes the hashmap key.

  */ 

 

  private void setFieldValue(String p_name,

                             String p_value)

 {

 

      fields.put(p_name,p_value);                     

 }

 

  /**

   * This method takes the input stream containing the

   * multipart form data and processes it.  Processing

   * means separating out the different items such as

   * parameter values and passes off the binary data

   * to a handler object that loads the data into a

   * database field.  The method returns a success or

   * failure value in the form of a String.

  */ 

 

 private String processInputStream(ServletInputStream    in) throws Exception

 {

   String      v_return_value = new String();

   String      v_content      = new String();

   String      newline        = new String();

  

   ImageLoader imageLoader    = new ImageLoader();

   try{

              

       byte[] line = new byte[256];

       int i = in.readLine(line, 0, 256);

 

       //First line is delimiter/boundary

       REQUEST_DELIMITER = new String(line, 0, i);

      

          while (i != -1) {

         

              newline = new String(line, 0, i);

              //System.out.println(newline);

              if (checkDataIndicator(newline)) {

                if (checkFile(newline)) {

               

                  i = in.readLine(line, 0, 256);

                  newline = new String(line, 0, i);

                  //System.out.println(newline);

                 

                  while (!checkContentIdicator(newline))

                  { 

                     i = in.readLine(line, 0, 256);

                     newline = new String(line, 0, i);

                  }

                 

                  if (checkContentIdicator(newline)){

                     v_content = getContentType(newline);

                  }

                 

                  //Consume extra line

                  i = in.readLine(line, 0, 256);

                  //newline = new String(line, 0, i);

                  //System.out.println(newline);

                  if (getFieldValue(ApplicationConstants.PARAM_NAME).length() > 0){

                    imageLoader.insertImageData(in,

                                                Integer.parseInt(getFieldValue("p_id")),

                                                getFieldValue("p_label"),

                                                getFieldValue("p_user"),

                                                v_content,

                                               REQUEST_DELIMITER);

                  }else

                 {

                    v_return_value="Photo Upload failed: ID is required.";

                    break;

                 }

 

               }else{

 

               String fieldName =parseFieldName(newline);

              //System.out.println("fieldName:" + fieldName);

              // blank line

              i = in.readLine(line, 0, 128);        

              setFieldValue(fieldName,parseFieldValue(in));

           }

         }

        

          //read the next line.

          i = in.readLine(line, 0, 128);

 

        }

         

        if (imageLoader.existErrors())

        {

           v_return_value = "Photo Upload failed: "+imageLoader.getErrors();

        }

    }catch(Exception e)

     {

         v_return_value = "Photo Upload failed: "+e.getMessage();     

     }finally

    {

         in.close();

    } 

   

    if (v_return_value.length() < 1)

    {

      v_return_value = "Photo Uploaded Successfully.";

    }

   

    return v_return_value;

 }

 

 private void displayResponse(PrintWriter out,

                              String      p_text)

 {

 

   int v_error = p_text.indexOf("failed");

  

   out.println("<HTML>");

   out.println("<BODY BGCOLOR=\"C0C0C0">");

   out.println("<STYLE>");

   out.println(".errorText  {color:       red;");

   out.println("             font-style:  italic;");

   out.println("             font-weight: bold;}");

   out.println(" .greenText  {color:#006600;}");

   out.println("</STYLE>");

   out.println("<big>Upload Juvenile Master Photo</big><br>");

  

   if (v_error >= 0)

   {

     out.println("<div class=\"errorText">");

   }else

   {

     out.println("<div class=\"greenText">");

   }

  

   out.println(p_text);

   out.println("</div>");

  

   out.println("</BODY>");

   out.println("</HTML>");

 }

 

  public void init(ServletConfig config) throws ServletException

  {

    super.init(config);

  }

 

  public void service(HttpServletRequest request, HttpServletResponse response)

  throws ServletException, IOException

  {

 

    String              v_upload_result = new String();

    PrintWriter         out             = response.getWriter();

    ServletInputStream  in              = null;

   

    response.setContentType(CONTENT_TYPE);

 

   

   try{

    

          in = request.getInputStream();

          v_upload_result = processInputStream(in);

 

    }catch(Exception e)

     {

         v_upload_result="Image Upload failed: "+e.getMessage();     

     }finally

    {

         in.close();

    }

 

    displayResponse(out,v_upload_result);

   

    out.close();

  }

}

 


4:08:45 PM    

  Tuesday, November 16, 2004


PL/SQL List Parsing Utility

I have had need of a utility such as the one being provided in this entry on almost every major Oracle development project I've been involved in over the last ten years.  The utility, a PL/SQL package called ListUtils, performs three key functions on a delimited string or list:

(1) It provides the means to retrieve any element at a given position in the list in a single request.

(2) It will count and return the number of elements in a list.

(3) It will compare two lists (or strings) for absolute equality.

Another key feature of this utility is it ability to reduce a list by removing all of the null entries before retrieving a desired element.  Examples are given in the PL/SQL package spec below.  Please feel free to use or improve this code:

 

/*=======================================================*/
/*  Jason Bennett                                        */
/*  PL/SQL Package Containing Three Utilities for        */
/*  manipulating a list or string of delimited elements  */
/*  such as A,B,C,D or A B C D                           */
/*  11/15/2004                                           */
/*=======================================================*/
CREATE OR REPLACE PACKAGE ListUtils AUTHID DEFINER AS

/**
/* The getListElement Function returns the desired element in
/* a delimited list based upon the elements position in the list.
/*
/* Example:
/*    ListUtils.getListElement('A,B,C,D,E',4,',')
/*    would return D.
/*
/* This function can also reduce or clean a list of all null elements
/* using the p_clean_parse parameter. 
/*
/* Example:
/*    ListUtils.getListElement(',,,,,,A,,B,,,C,,,,,D,,,E',4,',','Y')  
/*    would still return D.
/*
/* Note: A delimiter can be any character or series of characters ','
/* and 'xyzsdd' are both acceptable delimiters.
**/

FUNCTION  getListElement( p_string_in       IN VARCHAR2,                                            
                                                p_desired_element IN NUMBER,                                              
                                                p_delimiter       IN VARCHAR2 := ',',                                     
                                                p_clean_parse     IN VARCHAR2 := 'N')

RETURN VARCHAR2;

 

PRAGMA RESTRICT_REFERENCES(getListElement,WNDS,WNPS);

/**
/* The listElementCount function returns the number of elements
/* in a delimited list.
/*
/* Example:
/*    ListUtils.listElementCount('A,B,C,D,E',',') 
/*    would return 5
/*
/* This function can also return a count of all none null elements
/* using the p_clean_count parameter.
/*
/* Example:
/*    ListUtils.listElementCount(',,,,,,A,,B,,,C,,,,,D,,,E',',','Y')  
/*    would still return 5.
/*
**/

FUNCTION listElementCount(p_string_in     IN VARCHAR2,
                                                  p_delimiter     IN VARCHAR2 := ',',
                                                  p_clean_count   IN VARCHAR2 := 'N' )

RETURN NUMBER;

 

PRAGMA RESTRICT_REFERENCES(listElementCount,WNDS,WNPS);


/**
/* The listComp Function compares two delimited lists to
/* determine if they are exact matches. White space and
/* case count.
/*
/* Example:
/*    ListUtils.listComp('a,b,c','a,b,c')
/*    returns TRUE
/*
/*    ListUtils.listComp('a,b,c','a,b, c')
/*    returns FALSE
**/

FUNCTION listComp(p_list_1 VARCHAR2,p_list_2 VARCHAR2) RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES(listComp,WNDS,WNPS);

END;
/
sho err;

 

/**********************************************************************/

CREATE OR REPLACE PACKAGE BODY ListUtils AS

 

FUNCTION removeRepeatChars(p_string VARCHAR2 := NULL,

                                                       p_repeat_char VARCHAR2 := NULL)

RETURN VARCHAR2 IS

 

   ret_val VARCHAR2(32000) := NULL;

 

BEGIN

  
   IF INSTR(p_string,p_repeat_char||p_repeat_char) = 0 THEN

     ret_val := p_string;
     
   ELSE

     ret_val := REPLACE(p_string,p_repeat_char||p_repeat_char,p_repeat_char);

     ret_val := removeRepeatChars(ret_val,p_repeat_char);
  
   END IF;

   RETURN ret_val;

END;

/**********************************************************************/

FUNCTION countString(p_string VARCHAR2 := NULL,

                                         p_target VARCHAR2 := NULL,

                                         p_pos NUMBER :=0)

 RETURN NUMBER IS

 

   ret_val NUMBER(12) := 0;

 

BEGIN

 

   IF INSTR(p_string,p_target,1,p_pos+1) < p_pos THEN

 

     ret_val := 0;
     
   ELSE

     ret_val := countString(p_string,p_target,p_pos+1) + 1;
  
   END IF;

 

   RETURN ret_val;

 

END;

 

/**********************************************************************/

FUNCTION  getListElement( p_string_in       IN VARCHAR2,                                            
                                                 p_desired_element IN NUMBER,                                              
                                                 p_delimiter       IN VARCHAR2 := ',',                                     
                                                 p_clean_parse     IN VARCHAR2 := 'N')                                    
RETURN VARCHAR2                                                                               
IS                                                                                            
       v_edit_string            VARCHAR2(32000);  

       v_delimiter_count    NUMBER(4) := 0;                                                       
       v_element_count      NUMBER(4) := 0;                                                                                              
       v_start_position        NUMBER(4);                                                            
       v_end_position         NUMBER(4);                                                            
       e_end_process          EXCEPTION;
                                                                
BEGIN  
                  
    IF (INSTR(p_string_in,p_delimiter)=0) THEN
       IF (p_desired_element != 1) THEN
          RETURN NULL;
       ELSE
          RETURN p_string_in;
       END IF;
    END IF;
 
                                                                    
    /* HANDLE NULL STRING */                                                                    
                                                             
    IF p_string_in IS NULL THEN                                                                
      RAISE e_end_process;                                                                    
    ELSE
      v_edit_string := p_string_in;
    END IF;  

                                                                                                                                                 
                      
    /* REMOVE EXTRA DELIMITERS */
                                          
    IF UPPER(p_clean_parse) = 'Y' THEN

        -- Remove Leading and trailing delimiters                                                       
       v_edit_string := RTRIM(LTRIM(v_edit_string,p_delimiter),p_delimiter);

       -- Reduce consecutive repeating delimiters to one delimiter between elements                                                     
       v_edit_string := removeRepeatChars(v_edit_string,p_delimiter);

       -- Get Count of delimiters
       v_delimiter_count := countString(v_edit_string,p_delimiter);

       -- There always be one more element in a list than there are delimiters.
       v_element_count   := v_delimiter_count+1;
                                                                           
       /*****************/                                                                       
       /* SPECIAL CASES */                                                                       
       /*****************/ 
                                                                     
       /* IF DELIMITER COUNT IS 0 THEN RETURN THE ENTIRE STRING. */ 
                            
       IF (v_delimiter_count = 0) AND (p_desired_element = 1) THEN                                   
           RAISE e_end_process;                                                                    
       END IF;      

                                                                             

       /* DISQUALIFY BOGUS REQUESTS FOR ELEMENTS */
                                             
       IF (p_desired_element > v_element_count) OR (p_desired_element < 1) THEN                        
          v_edit_string := NULL;                                                                   
          RAISE e_end_process;                                                                     
       END IF;

    ELSE

       -- There always be one more element in a list than there are delimiters.
       v_element_count   := listElementCount(v_edit_string,p_delimiter);  
                                                                                
    END IF; /* CLEAN PARSING */  
                                                               
    /********************************/                                                           
    /* ISOLATE THE DESIRED ELEMENT. */                                                           
    /********************************/ 
                                                         
    /* FIND STARTING POSITION OF DESIRED ELEMENT */
                                             
    IF (p_desired_element = 1) THEN                                                                
       v_start_position := 1;                                                                      
    ELSE                                                                                         
       v_start_position := (INSTR(v_edit_string,p_delimiter,1,p_desired_element-1)+length(p_delimiter));
    END IF;
                                                                                     
    /* FIND ENDING POSITION OF DESIRED ELEMENT */
                                               
    IF (p_desired_element = v_element_count) THEN 
       v_end_position := LENGTH(v_edit_string);                                                      
    ELSE                                                                                         
       v_end_position := (INSTR(v_edit_string,p_delimiter,v_start_position,1) - v_start_position);                                                       
    END IF; 
                                                                                    
    /* GET REQUESTED ELEMENT */                                                                  

    v_edit_string := SUBSTR(v_edit_string,v_start_position,v_end_position);
                              
    RETURN v_edit_string;
                                                                          
EXCEPTION                                                                                      
    WHEN e_end_process THEN                                                                     
       RETURN v_edit_string;
                                                               
END;

/*****************************************************************/

FUNCTION listElementCount(p_string_in     IN VARCHAR2,
                          p_delimiter     IN VARCHAR2 := ',',
                          p_clean_count   IN VARCHAR2 := 'N') RETURN NUMBER
IS                                                                                                                                 
                                                          
    e_end_process   EXCEPTION;
    v_count_string  VARCHAR2(32000) := p_string_in;
    v_element_count NUMBER(12)      := 0;
                                                                 
BEGIN        
                                                                                 
   /* HANDLE NULL STRING */ 
                                                                                                                             
   IF v_count_string IS NULL THEN                                                                
      RAISE e_end_process;                                                                    
   END IF;

   IF p_clean_count = 'Y' THEN

      -- Remove Leading and trailing delimiters                                                       
      v_count_string := RTRIM(LTRIM(v_count_string,p_delimiter),p_delimiter);

      -- Reduce consecutive repeating delimiters to one delimiter between elements                                                     
      v_count_string := removeRepeatChars(v_count_string,p_delimiter);

      -- Get Count of elements
   END IF;

   v_element_count := countString(v_count_string,p_delimiter) + 1;      
                                                                               
  RETURN v_element_count;
                                                                       
EXCEPTION                                                                                      
   WHEN e_end_process THEN                                                                     
     RETURN 0;     
                                                          
END;  


/**************************************************************/

FUNCTION listcomp(p_list_1 VARCHAR2,

                                    p_list_2 VARCHAR2) RETURN BOOLEAN
IS

 

  v_return BOOLEAN := TRUE;

 

BEGIN

 

 Lists of unequal length are not equal.
  IF LENGTH(p_list_1) != LENGTH(p_list_2) THEN
    v_return := FALSE;
  ELSE

    -- Perform a character by character comparison.
    FOR step IN 1 .. LENGTH(p_list_1) LOOP

 

     IF SUBSTR(p_list_1,step,1) != SUBSTR(p_list_2,step,1) THEN


       v_return := FALSE;


       EXIT;


     END IF;

   END LOOP;

 

  END IF;

 

  RETURN v_return;

 

END;
                                                      
END;  
/
SHO ERR

 

 

 


9:47:10 PM    

  Sunday, November 14, 2004


JOSSO: The Open Source Single Sign-On Project

I have spent quite a bit of time figuring out how to SSO enable applications developed in PHP, Java, and ColdFusion using the SSO implementation provided with OracleAS 10g (and Oracle 9iAS before it).  A recent reader (Gianluca Brigandi)  of my BLOG requested that I also mention JOSSO.  JOSSO, or Java Open Single Sign-On, is an open source J2EE-based SSO infrastructure aimed to provide a solution for centralized platform neutral user authentication (this sentence was borrowed from the JOSSO site).  It provides most of the same features that the other commercial SSO vendor supply.  I've decided to see how it works.  I think it would be worth every ones time to check it out.  The URL for the JOSSO site is http://www.josso.org .  This could prove useful for OracleAS 10g implementations where SSO might be required, but the larger Infrastructure implementation is not required or desired. 

I personallty think Oracle has done a fantastic job with it's SSO implementation and integration, but there is definately room out there for alternate solutions!  Open Source software keeps everyone on their toes and moving forward.

I want to note also, the JOSSO can be used to SSO enable PHP applications as detailed on the JOSSO site: http://www.josso.org/php-howto.html


1:40:25 PM    

  Monday, October 25, 2004


Installing Mod_python 2.7.10 on OracleAS 10g under Linux

 

   Python is a powerful and free  object-oriented programming language much like Perl, PHP, and Java.  Python is open source software and is maintained by the open source developer community (http://www.python.org).    Mod_python is an Apache module that allows the Python interpreter to run imbedded within the Apache server.  This article details how to build (compile) Mod_python 2.7.10 and install it under OracleAS 10g.

 

Prerequisites

(Note: The latest version of Mod_python is 3.1.3, but it only runs under Apache 2.0. OracleAS 10g is built on Apache version 1.3)   There are a few prerequisites for getting Mod_python 2.7.10 to run under OracleAS 10g. 

 

  • A Python interpreter must be installed on your Linux system in non-threading mode. Please refer to installation instructions provided with the Python distribution for more detail.

 

    • The latest release, Python 2.3.4, is not supported under Mod_python 2.7.10.  I recommend Python release 2.1.3.  It can be downloaded from http://www.python.org.  If you have a pre-existing version of Python, it was most likely compiled with threading enabled.  I recommend creating a second install, and placing the path to the second python  executable ahead of the existing python executable in the system path ($PATH). You may have to reboot the box make sure the path change takes affect if you made the change in /etc/profile file.
    • Python must be compiled in non-threading mode since Apache does not support threading.  Here is an example of how the configure command would look:
      • ./configure -–with-threads=no
  • Make sure that libdms2.so is included in your LD_LIBRARY_PATH.  I recommend placing it in /usr/lib.  libdms2.so is a shared object library that contains DSO related functions.  Oracle provides libdms2.so on the RepCA installation CD under the /utilities/plug-ins/oc4j directory.
  • Download Mod_python 2.7.10 binaries from http://httpd.apache.org/modules/python-download.cgi and explode the tarfile in your directory of choice.  I prefer to place mine in /opt. So, the path to my sources is /opt/mod_python-2.7.10

 

Building and Installing Mod_python

   Building and installing Mod_python is simply a matter of following the next few steps (do this as root):

 

  1. Before running the build scripts, I recommend copying all of the .h files from <parent> /Python-2.1.3/Include and $ORACLE_HOME/Apache/Apache/include to <parent>/mod_python-2.7.10/src/include. This will ensure that all of the required header files are present.
    1. Note:  Later, the compiler may complain about conflicts of the gethostname function in the ap_config.h header file with another .h file.  If this happens, edit the ap_config.h file that you copied into <parent>/mod_python-2.7.10/src/include and comment out the first and second occurrences of int gethostname.
    2. Note: The compiler may complain about functions in /usr/include/string.h. If this happens, then copy /usr/include/string.h to <parent>/mod_python-2.7.10/src/include. Edit the file and change the following line, # ifdef __USE_BSD to # ifdef __USE_LINUX
  2. The next step is to run the configure script which sets up the properties in the Makefile for linking and compiling mod_python.so .  Since we are utilizing DSO (Dynamic Shared Object) support with Apache, we need to specify the -–with-apxs=<path to Apache apxs script> flag with the configure script.  We will also need to include the path the Python interpreter sources using the -–with-python=<path to Python sources> flag.  That being said, execute the following command with paths specific to your environment:
    1. ./configure --with-python=/opt/Python-2.1.3 --with-apxs=/opt/oracle/product/OracleAS10g_app/Apache/Apache/bin/apxs
  3. The next step is to run the make command.  Before doing so, navigate to the <parent>/mod_python-2.7.10/src directory and edit the entries INCLUDES and LIBS in the file Makefile to match the following (substituting paths specific to your environment):
    1. INCLUDES=-I/opt/mod_python-2.7.10/src/include -I/include -I/opt/Python-2.1.3 -I/opt/Python-2.1.3/Include -DEAPI
    2. LIBS=-lm  /opt/Python-2.1.3/libpython2.1.a -ldl -lutil -lm /usr/lib/libc.a -ldl –lutil
  4. Navigate back to <parent>/mod_python-2.7.10 and execute the following command:
    1. make
  5. If no errors were reported, then execute the following command:
    1. make install

 

At this point, the shared object library, mod_python.so should have been created and automatically copied to $ORACLE_HOME/Apache/Apache/libexec.  Next, we need to configure Apache to load mod_python.so.

 

Configuring and Testing OracleAS 10g Apache

 

Since we compiled mod_python as a DSO, we need to instruct Apache to load the module at startup by adding the following line to the httpd.conf file.

 

LoadModule python_module libexec/mod_python.so

 

Now, we want to make sure that we can actually execute a Python file:

 

  1. Make a directory, such as htdocs/python, in which to store your .py or Python script.
  2. Add the following Apache directives to your httpd.conf file:

 

               Alias  /py/  /some/directory/htdocs/python”

 

               <Directory /some/directory/htdocs/python>

                   AddHandler python-program .py

                  PythonHandler mptest

                  PythonDebug On

              </Directory>

  1. Create a file called mptest.py in the /some/directory/htdocs/python directory with the following content:

                    from mod_python import apache

 

                  def handler(req):

                  req.send_http_header()

                 req.write("Mod_Python on OracleAS 10g!!")

                 return apache.OK

 

      4.  After restarting OHS,  open a web browser and point it to the URL referring to mptest.py (http://<;server>:<port>/py/mptest.py).  You should see “Mod_Python on OracleAS 10g!!”.

 

 

Wrapping it Up

After following the steps above, you should have a working version of Mod_python installed under OracleAS 10g.  For more information on what you can do with Mod_python and Python, please refer to the documentation included with each distribution (it’s pretty good), or visit http://www.python.org ot http://www.modpython.org


9:43:43 PM    

Click here to visit the Radio UserLand website. © Copyright 2009Jason Bennett.
Last update: 8/23/2009; 11:47:04 AM.

August 2009
Sun Mon Tue Wed Thu Fri Sat
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          
May   Sep