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
/******************************************
* Parse the storage location parameters. *
******************************************/
parser := xmlparser.newParser;
xmlparser.parseBuffer ( parser, IN_XML );
xmlDoc := xmlparser.getDocument ( parser );
xmlDocNode := xmldom.makeNode ( xmlDoc );
xslPattern := 'createStorageLocationXML/ParentLocation/text()';
xslNode := xslProcessor.selectSingleNode ( xmlDocNode,
xslPattern );
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, '.' ) );
/************************************************************
* Generate the next storage_location_id from the sequence. *
************************************************************/
SELECT storage_location_id_seq.NextVal INTO lv_storage_loc_id FROM
DUAL;
/*******************************************************************
* Derive the location_level using the parent_storage_location_id.
*
*******************************************************************/
SELECT SL.location_level
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;
/*****************************************************
* 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. *
*****************************************************/
INSERT INTO T_Temp_String VALUES ( TO_CHAR ( lv_storage_loc_id )
);
/*********************************
* 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' );
/**************************************************
* Open the CLOB XML created by the XMLGEN.getXML *
* function and read the CLOB in one chunk. *
**************************************************/
DBMS_LOB.open ( xmlString, DBMS_LOB.LOB_READONLY );
DBMS_LOB.read ( xmlString, amount, position, charString );
/*****************************************************************
* Insert the XML just encoded and return the XML with a cursor. *
*****************************************************************/
INSERT INTO T_Temp_XML values ( charString );
OPEN OUT_XML FOR SELECT xml_column barcode from t_temp_xml;
/****************************
* 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