2 thoughts on “New version of Migration Workbench available”

  1. 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.

Comments are closed.