Inside Oracle APEX by Patrick Wolf

2 Responses to “New version of Migration Workbench available”

1. 
   ImwatIm Says:
   July 2nd, 2007 at 03:47

   Hi,
   I am using oracle migration workbench to port the t-sql code to corresponding pl/sql code..most of the migration has been done by the workbench..but few stored procedures having xml elements hasnt been mapped..hope u can help me with this.

   here is the exact problem..
   hi ,

   How do i map the xml handling code in t-sql to pl/sql??
   for example , here is the t-sql code :

   CREATE PROCEDURE [dbo].[usp_LIBRARYHideUnhideTreeNodes]
   — Add the parameters for the stored procedure here
   @paramMyXml text,
   @XmlHandle int output
   AS
   BEGIN
   — SET NOCOUNT ON added to prevent extra result sets from
   — interfering with SELECT statements.
   SET NOCOUNT ON;

   /*Update statements*/
   EXEC sp_xml_preparedocument @XmlHandle output,@paramMyXml

   UPDATE LIBRARYTree SET IsVisible = ~IsVisible WHERE NodeId in(
   SELECT xNodeID FROM OPENXML (@XmlHandle, ‘HiddenNodes/Node’,1)
   WITH ( xNodeID int ‘@id’))

   EXEC sp_xml_removedocument @XmlHandle

   END

   The corresponding code in pl/sql by sql developer is :

   CREATE OR REPLACE PROCEDURE usp_LIBRARYHideUnhideTreeNodes

   (
   — Add the parameters for the stored procedure here
   v_paramMyXml IN CLOB DEFAULT NULL ,
   v_XmlHandle OUT NUMBER
   )
   AS
   BEGIN
   — SET NOCOUNT ON added to prevent extra result sets from
   — interfering with SELECT statements.
   NULL/*TODO:SET NOCOUNT ON*/;
   /*Update statements*/
   sp_xml_preparedocument(v_XmlHandle,
   v_paramMyXml);
   UPDATE LIBRARYTree
   SET IsVisible = UTL_RAW.BIT_COMPLEMENT(IsVisible)
   WHERE NodeId IN ( SELECT xNodeID
   FROM DUAL/*TODO:OPENXML (@XmlHandle, ‘HiddenNodes/Node’,1)
   WITH ( xNodeID int ‘@id’))*/ );
   sp_xml_removedocument(v_XmlHandle);
   END;

   This on compilation for obvious reasons gives errors.

   how is sp_xml_preparedocument,openxml,sp_xml_removedocument handled in pl/sql??

   any help in this direction will be appreciated..

   Cheers,
   Shishir.

2. 
   Patrick Wolf Says:
   July 2nd, 2007 at 04:05

   Shishir,

   it’s probably better to get in contact with Donal Daly, because I’m not really doing migrations from MS SQL Server.

   For XML handling in Oracle, have a look at http://inside-apex.blogspot.com/2007/03/xml-processing-in-database.html

   Patrick