FYI: DOM, XML and PL/SQL
Date: 15 Apr 2002 21:13:42 -0700
Message-ID: <73986c9d.0204152013.49914d67_at_posting.google.com>
<<<First, I create a couple temporary tables to hold the results of the XML operations like so (it's either temp tables or, yuck - flat files:>>>
CREATE GLOBAL TEMPORARY TABLE T_Temp_String (
String_Column VARCHAR2 ( 4000 ) ) ON COMMIT PRESERVE ROWS; CREATE GLOBAL TEMPORARY TABLE T_Temp_XML (
XML_Column VARCHAR2 ( 4000 ) ) ON COMMIT PRESERVE ROWS; <<<Next, the PL/SQL stored procedure:>>>
PROCEDURE CreateStorageLocation
(
IN_XML IN VARCHAR2, OUT_XML OUT REF_CURSOR_T) AS
/************
* Variables.
************/ lv_barcode_count INTEGER; parser XMLPARSER.Parser; xslPattern VARCHAR2 ( 512 ); xmlDoc XMLDOM.DOMDocument; xmlDocNode XMLDOM.DOMNode; xslNode xmldom.DOMNode;
lv_parent_loc_id
t_storage_location.parent_storage_location_id%TYPE; lv_location_level t_storage_location.location_level%TYPE; lv_barcode t_storage_location.barcode%TYPE; lv_type t_storage_location.type%TYPE; lv_name t_storage_location.name%TYPE; lv_description t_storage_location.description%TYPE; lv_created_by t_storage_location.created_by%TYPE; lv_storage_loc_id t_storage_location.storage_location_id%TYPE; xmlString CLOB := NULL; amount INTEGER := 4000; position INTEGER := 1; charString VARCHAR2 ( 4000 );
BEGIN
/******************************************xslPattern := 'createStorageLocationXML/ParentLocation/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode, xslPattern );
* Parse the storage location parameters. *
******************************************/ parser := xmlparser.newParser; xmlparser.parseBuffer ( parser, IN_XML ); xmlDoc := xmlparser.getDocument ( parser ); xmlDocNode := xmldom.makeNode ( xmlDoc );
lv_parent_loc_id := TO_NUMBER ( xslProcessor.valueOf ( xslNode, '.' ) );
xslPattern := 'createStorageLocationXML/Barcode/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode, xslPattern );
lv_barcode := xslProcessor.valueOf ( xslNode, '.' ); xslPattern := 'createStorageLocationXML/Type/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode,xslPattern );
lv_type := xslProcessor.valueOf ( xslNode, '.' ); xslPattern := 'createStorageLocationXML/Name/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode, xslPattern );
lv_name := xslProcessor.valueOf ( xslNode, '.' ); xslPattern := 'createStorageLocationXML/Description/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode, xslPattern );
lv_description := xslProcessor.valueOf ( xslNode, '.' ); xslPattern := 'createStorageLocationXML/CreatedBy/text()'; xslNode := xslProcessor.selectSingleNode ( xmlDocNode, xslPattern );
lv_created_by := TO_NUMBER ( xslProcessor.valueOf ( xslNode, '.' ) );
/************************************************************SELECT storage_location_id_seq.NextVal INTO lv_storage_loc_id FROM DUAL;
* Generate the next storage_location_id from the sequence. *
************************************************************/
/*******************************************************************SELECT SL.location_level
* Derive the location_level using the parent_storage_location_id.
* *******************************************************************/
INTO lv_location_level
FROM t_storage_location SL
WHERE SL.storage_location_id = lv_parent_loc_id; lv_location_level := lv_location_level + 1;
/*****************************************************INSERT INTO T_Temp_String VALUES ( TO_CHAR ( lv_storage_loc_id ) );
* Instantiate the storage location object using *
* the parameters passed in and parsed from the XML. *
*****************************************************/ INSERT INTO t_storage_location ( storage_location_id, parent_storage_location_id, location_level, barcode, type, name, description, created_by, create_date ) VALUES ( lv_storage_loc_id, lv_parent_loc_id, lv_location_level, lv_barcode, lv_type, lv_name, lv_description, lv_created_by, SYSDATE ); /*****************************************************
* Persist the ID of the instantiated storage *
* location object into the generic temporary table. *
*****************************************************/
/*********************************
* Generate a result set in XML. *
*********************************/
XMLGEN.setRowTag ( NULL );
XMLGEN.setRowsetTag ( 'STORAGE_LOCATIONS' ); xmlString := XMLGEN.getXML ( 'select string_column storage_location_id from t_temp_string' );
/**************************************************DBMS_LOB.open ( xmlString, DBMS_LOB.LOB_READONLY ); DBMS_LOB.read ( xmlString, amount, position, charString );
* Open the CLOB XML created by the XMLGEN.getXML *
* function and read the CLOB in one chunk. *
**************************************************/
/*****************************************************************INSERT INTO T_Temp_XML values ( charString ); OPEN OUT_XML FOR SELECT xml_column barcode from t_temp_xml;
* Insert the XML just encoded and return the XML with a cursor. *
*****************************************************************/
/****************************
* CLOB and XMLGEN cleanup. *
****************************/
DBMS_LOB.close ( xmlString );
DBMS_LOB.freeTemporary ( xmlString ); XMLGEN.resetOptions;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_LOB.close ( xmlString ); DBMS_LOB.freeTemporary ( xmlString ); XMLGEN.resetOptions; WHEN OTHERS THEN XMLGEN.resetOptions;
END; <<<And finally, a sample 'driver' that calls the SP (written in C# not Java, sorry)>>>
using System;
using System.Data;
using System.Data.OleDb;
using Microsoft.Win32;
class createStorageLocation
{ static void Main ( string [ ] args ) { String strXML; /************************************************* * Data reader for the stored procedure results. * *************************************************/ OleDbDataReader OleDR = null; /********************************** * Verify command line arguments. * **********************************/ if ( args.Length != 6 ) { Console.WriteLine ( "Usage: createStorageLocation " + "<parent location id> " + "<barcode> " + "<type> " + "<name> " + "<description> " + "<created by>" ); return; } /*********************************************** * Get the connect string out of the registry. * ***********************************************/ RegistryKey hklm = Registry.LocalMachine; RegistryKey hkgino = hklm.OpenSubKey ( "SOFTWARE\\DNA Sciences\\Gino\\v2.0" ); String strConnect = hkgino.GetValue ( "GinoProviderString").ToString ( );
/*********************************************************** * Instanciate an Oracle connection object with connection * * string and prepare the stored procedure dispatch. * ***********************************************************/ OleDbConnection OleCn = new OleDbConnection ( strConnect ); OleDbCommand OleCm = new OleDbCommand ( "pkg_sample_management.CreateStorageLocation", OleCn ); OleCm.CommandType = CommandType.StoredProcedure; strXML = "<createStorageLocationXML><ParentLocation>" + args [ 0 ] + "</ParentLocation><Barcode>" + args [ 1 ] + "</Barcode><Type>" + args [ 2 ] + "</Type><Name>" + args [ 3 ] + "</Name><Description>" + args [ 4 ] + "</Description><CreatedBy>" + args [ 5 ] + "</CreatedBy></createStorageLocationXML>"; OleCm.Parameters.Add ( "IN_XML", OleDbType.VarChar ); OleCm.Parameters [ "IN_XML" ].Value = strXML; OleCm.Parameters [ "IN_XML" ].Direction =ParameterDirection.Input;
try { OleCn.Open ( ); OleDR = OleCm.ExecuteReader (CommandBehavior.CloseConnection );
/******************** * Display results. * ********************/ while ( OleDR.Read ( ) ) { Console.WriteLine ( OleDR [ 0 ].ToString ( ) ); } } catch ( Exception e ) { Console.WriteLine ( e.ToString ( ) ); } finally { if ( OleCn.State == ConnectionState.Open ) { OleCn.Close ( ); } }
}
}
Hope this saves everyone a little time; I struggled with this stuff
for several days before I got it right,
Matt Houseman
Received on Tue Apr 16 2002 - 06:13:42 CEST