Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

translation errors migrating stored procedure temp tables from Microsoft Sqlserver 2019 to OCI ADW/TP 19c

I found what appears to be a sqldeveloper app bug when migrating stored procedures from Microsoft Sqlserver 2019 to OCI ADW/TP 19c using the latest SQLDeveloper app Version 24.3.0.284+jdk 17 on both Mac and Windows.

When selecting the Migrate to Oracle... feature on Microsoft Sqlserver 2019 DB, the sqldeveloper app generates many translation mistakes for stored procedures when the procedure code includes temp table reuse and multiple temp table names. We see many sql server use cases of the #<temp table name> operator to create named temp tables with session TTL. The issue is the sqldeveloper app is not generating syntactically correct master.sql when offline is selected and in the scratch editor . We expect valid CREATE GLOBAL TEMPORARY TABLE column/types for each TSQL temp table, but instead we see splats of the source procedure code into the CREATE GLOBAL TEMPORARY TABLE definition including unexpected local variables and other code from the procedure body that is not compilable and requires time consuming reengineering.

For example, the second box below is the source TSQL procedure being migrated. This first box below is the result produced in migration scratch editor where the tt_loop and tt_data includes the incorrect original procedure body code , instead of a valid CREATE GLOBAL TEMPORARY TABLE body with column names and types.

/*Global Temporary Tables:3 *//* Translation Extracted DDL For Required Objects*/
CREATE GLOBAL TEMPORARY TABLE tt_results
(
  key VARCHAR2(50) ,
  VALUE CLOB 
);
/
CREATE GLOBAL TEMPORARY TABLE tt_data
AS (
   SELECT ICB.indication_id ,
   ICB.country_id ,
   B.breakout_id 
     FROM TABLE(udf_split(v_package_ids, ',')) P
     JOIN CommonProductSLX.Package_Expanded PE   ON PE.package_id = P.element
     JOIN Indication_Country_Breakout ICB   ON ICB.icb_id = PE.icb_id
     JOIN ( SELECT * 
            FROM ( SELECT 1 rid  ,
                          breakout_id ,
                          UTILS.CONVERT_TO_NUMBER(VALUE,10,0) breakout_id_to_substitute  
                   FROM Indication_Country_Breakout_Option 
                    WHERE  option_ = 'package_substitute_breakout_id'
                             AND breakout_id = v_breakout_id
                   UNION 
                   SELECT 9 rid  ,
                          v_breakout_id breakout_id  ,
                          v_breakout_id breakout_id_to_substitute  
                     FROM DUAL  ) S
              ORDER BY rid
              FETCH FIRST 1 ROWS ONLY ) B   ON B.breakout_id_to_substitute = ICB.breakout_id
   WHERE 1=2
);
/
CREATE GLOBAL TEMPORARY TABLE tt_loop
AS (
   SELECT element_id rid  ,
   LTRIM(RTRIM(LOWER(element))) VALUE  
     FROM TABLE(udf_split(v_options, ','))
   WHERE 1=2
);
/


                         
CREATE OR REPLACE FUNCTION usp_mafapp_package_ids_2_underlying_ids
(
  -- Required parameters
  v_package_ids IN VARCHAR2,
  v_breakout_id IN NUMBER,
  iv_options IN VARCHAR2 DEFAULT NULL 
)
RETURN NUMBER
AS
   -- Optional parameters
   v_options VARCHAR2(50) := iv_options;
   /*
    	Object: usp_mafapp_package_ids_2_underlying_ids
    	Usage: EXEC usp_mafapp_package_ids_2_underlying_ids @package_ids=requested package_ids
    	Example:
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null, 'c'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null, 'i,c'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8251, null, 'i,c'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8251, null, 'ic'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4086, 'i,c'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4087, 'i,c'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4088, 'i,c'

    			EXEC usp_mafapp_package_ids_2_underlying_ids 8069, 4096, 'i'
    			EXEC usp_mafapp_package_ids_2_underlying_ids 8072, 4096, 'i'
    			EXEC usp_mafapp_package_ids_2_underlying_ids '8069,8072', 4096, 'i,c'
    */
   v_s CLOB;
   v_i NUMBER(10,0);
   v_imax NUMBER(10,0);
   v_limiting_package_ids CLOB;
   v_cursor SYS_REFCURSOR;

BEGIN
   DELETE FROM tt_results;
   IF v_options IS NULL THEN
    v_options := 'i,c,b' ;
   END IF;
   -- NO, the following note is not the case:
   --------NOTE: any changes to using 'package_substitute_breakout_id' AND 'ids_limited_by_package_id' entries
   -------- in Indication_Country_Breakout_Option should be replicated in usp_mafapp_cascade_data_get
   DELETE FROM tt_data;
   UTILS.IDENTITY_RESET('tt_data');
   
   INSERT INTO tt_data SELECT ICB.indication_id ,
                              ICB.country_id ,
                              B.breakout_id 
        FROM TABLE(udf_split(v_package_ids, ',')) P
               JOIN CommonProductSLX.Package_Expanded PE   ON PE.package_id = P.element
               JOIN Indication_Country_Breakout ICB   ON ICB.icb_id = PE.icb_id
               JOIN ( 
                      -- Translate/substitute any breakout_ids (currently for PACER based Tableau products.  Eventually

                      -- we should only need to pass the package_id, but for now they need indication_ids and country_ids

                      -- passed, and instead of junking up the Indication_Country_Breakout table with entries for each

                      -- indication/country/year of data, using one "breakout" set of icb_ids (currently breakout_id 4096

                      -- for PACER based products) that can be re-used.  If there isn't any "substitute" set, then just

                      -- using the current breakout_id (i.e. substituting itself for itself)
                      SELECT * 
                      FROM ( SELECT 1 rid  ,
                                    breakout_id ,
                                    UTILS.CONVERT_TO_NUMBER(VALUE,10,0) breakout_id_to_substitute  
                             FROM Indication_Country_Breakout_Option 
                              WHERE  option_ = 'package_substitute_breakout_id'
                                       AND breakout_id = v_breakout_id
                             UNION 
                             SELECT 9 rid  ,
                                    v_breakout_id breakout_id  ,
                                    v_breakout_id breakout_id_to_substitute  
                               FROM DUAL  ) S
                        ORDER BY rid
                        FETCH FIRST 1 ROWS ONLY ) B   ON B.breakout_id_to_substitute = ICB.breakout_id
        GROUP BY ICB.indication_id,ICB.country_id,B.breakout_id;
   -- Now check to see if ids (currently just indication_ids) need to be limited for this breakout
   v_limiting_package_ids := ' ' ;
   SELECT v_limiting_package_ids || ',' || VALUE 

     INTO v_limiting_package_ids
     FROM Indication_Country_Breakout_Option 
    WHERE  option_ = 'ids_limited_by_package_id'
             AND breakout_id = v_breakout_id;
   IF SQL%ROWCOUNT > 0 THEN
    
   BEGIN
      v_limiting_package_ids := SUBSTR(v_limiting_package_ids, 2, LENGTH(v_limiting_package_ids) - 1) ;
      DELETE tt_Data

       WHERE  indication_id NOT IN ( SELECT DISTINCT indication_id 
                                     FROM TABLE(udf_split(v_limiting_package_ids, ',')) P
                                            JOIN CommonProductSLX.Package_Template PT   ON PT.package_id = P.element
                                      WHERE  LTRIM(RTRIM(NVL(P.element, ' '))) <> ' ' )
      ;
   
   END;
   END IF;
   --SELECT * FROM tt_data
   DELETE FROM tt_loop;
   UTILS.IDENTITY_RESET('tt_loop');
   
   INSERT INTO tt_loop ( 
   	SELECT element_id rid  ,
           LTRIM(RTRIM(LOWER(element))) VALUE  
   	  FROM TABLE(udf_split(v_options, ','))  );
   SELECT MAX(rid)  

     INTO v_imax
     FROM tt_loop ;
   v_i := 1 ;
   WHILE v_i <= v_imax 
   LOOP 
      
      BEGIN
         SELECT VALUE 

           INTO v_options
           FROM tt_loop 
          WHERE  rid = v_i;
         v_s := ' ' ;
         IF v_options IN ( 'i' )
          THEN
          
         BEGIN
            SELECT v_s || ',' || UTILS.CONVERT_TO_VARCHAR2(indication_id,9) 

              INTO v_s
              FROM tt_data 
             WHERE  indication_id >= 0
              GROUP BY indication_id
              ORDER BY indication_id;
         -- indications
         
         END;
         ELSE
            IF v_options IN ( 'c' )
             THEN
             
            BEGIN
               SELECT v_s || ',' || UTILS.CONVERT_TO_VARCHAR2(country_id,9) 

                 INTO v_s
                 FROM tt_data 
                WHERE  country_id >= 0
                 GROUP BY country_id
                 ORDER BY country_id;
            -- countries
            
            END;
            ELSE
               IF v_options IN ( 'b' )
                THEN
                
               BEGIN
                  SELECT v_s || ',' || UTILS.CONVERT_TO_VARCHAR2(breakout_id,9) 

                    INTO v_s
                    FROM tt_data 
                   WHERE  breakout_id >= 0
                    GROUP BY breakout_id
                    ORDER BY breakout_id;
               -- breakouts
               
               END;
               ELSE
                  IF v_options IN ( 'ic' )
                   THEN
                   
                  BEGIN
                     SELECT v_s || ',' || UTILS.CONVERT_TO_VARCHAR2(indication_id,9) || '|' || UTILS.CONVERT_TO_VARCHAR2(country_id,9) 

                       INTO v_s
                       FROM tt_data 
                      WHERE  indication_id >= 0
                       GROUP BY indication_id,country_id
                       ORDER BY indication_id,
                                country_id;
                  -- IndicationsCountries
                  
                  END;
                  ELSE
                     IF v_options IN ( 'ci' )
                      THEN
                      
                     BEGIN
                        SELECT v_s || ',' || UTILS.CONVERT_TO_VARCHAR2(country_id,9) || '|' || UTILS.CONVERT_TO_VARCHAR2(indication_id,9) 

                          INTO v_s
                          FROM tt_data 
                         WHERE  country_id >= 0
                          GROUP BY country_id,indication_id
                          ORDER BY country_id,
                                   indication_id;
                     -- CountriesIndications
                     
                     END;
                     END IF;
                  END IF;
               END IF;
            END IF;
         END IF;
         IF v_s <> ' ' THEN
          v_s := SUBSTR(v_s, 2, LENGTH(v_s) - 1) ;
         END IF;
         INSERT INTO tt_results
           ( SELECT v_options key  ,
                    v_s VALUE  
               FROM DUAL  );
         v_i := v_i + 1 ;
      
      END;
   END LOOP;
   OPEN  v_cursor FOR
      SELECT * 
        FROM tt_results  ;
      DBMS_SQL.RETURN_RESULT(v_cursor);
   RETURN 0;

EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);
END;

The source TSQL stored procedure . Thanks for your time and appreciate any advice or suggestions

 CREATE PROCEDURE dbo.usp_mafapp_package_ids_2_underlying_ids
 	-- Required parameters
 	@package_ids varchar(1000),
 	@breakout_id int,

 	-- Optional parameters
 	@options varchar(50) = null
 /*
 	Object: usp_mafapp_package_ids_2_underlying_ids
 	Usage: EXEC usp_mafapp_package_ids_2_underlying_ids @package_ids=requested package_ids
 	Example:
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null, 'c'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, null, 'i,c'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8251, null, 'i,c'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8251, null, 'ic'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4086, 'i,c'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4087, 'i,c'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8250, 4088, 'i,c'

 			EXEC usp_mafapp_package_ids_2_underlying_ids 8069, 4096, 'i'
 			EXEC usp_mafapp_package_ids_2_underlying_ids 8072, 4096, 'i'
 			EXEC usp_mafapp_package_ids_2_underlying_ids '8069,8072', 4096, 'i,c'
 */
 AS
 	SET NOCOUNT ON

 	DECLARE @s varchar(8000), @i int, @imax int, @limiting_package_ids varchar(8000)
 	CREATE TABLE #results ([key] varchar(50), value varchar(8000))
 	IF @options IS NULL SET @options = 'i,c,b'

 	-- NO, the following note is not the case:
 	--------NOTE: any changes to using 'package_substitute_breakout_id' AND 'ids_limited_by_package_id' entries
 	-------- in Indication_Country_Breakout_Option should be replicated in usp_mafapp_cascade_data_get

 	SELECT ICB.indication_id, ICB.country_id, B.breakout_id
 	INTO #data
 	FROM dbo.udf_split(@package_ids, ',') P
 	INNER JOIN CommonProductSLX.dbo.Package_Expanded PE
 		ON PE.package_id = P.element
 	INNER JOIN Indication_Country_Breakout ICB
 		ON ICB.icb_id = PE.icb_id
 	INNER JOIN (
 		-- Translate/substitute any breakout_ids (currently for PACER based Tableau products.  Eventually
 		-- we should only need to pass the package_id, but for now they need indication_ids and country_ids
 		-- passed, and instead of junking up the Indication_Country_Breakout table with entries for each
 		-- indication/country/year of data, using one "breakout" set of icb_ids (currently breakout_id 4096
 		-- for PACER based products) that can be re-used.  If there isn't any "substitute" set, then just
 		-- using the current breakout_id (i.e. substituting itself for itself)
 		SELECT TOP 1 *
 		FROM (
 			SELECT 1 AS rid, breakout_id, CAST(value AS int) AS breakout_id_to_substitute
 			FROM Indication_Country_Breakout_Option
 			WHERE [option] = 'package_substitute_breakout_id'
 				AND breakout_id = @breakout_id
 			UNION
 			SELECT 9 AS rid, @breakout_id AS breakout_id, @breakout_id AS breakout_id_to_substitute
 		) S
 		ORDER BY rid
 	) B
 		ON B.breakout_id_to_substitute = ICB.breakout_id
 	GROUP BY ICB.indication_id, ICB.country_id, B.breakout_id


 	-- Now check to see if ids (currently just indication_ids) need to be limited for this breakout
 		SET @limiting_package_ids = ''

 		SELECT @limiting_package_ids = @limiting_package_ids + ',' + value
 		FROM Indication_Country_Breakout_Option
 		WHERE [option] = 'ids_limited_by_package_id'
 			AND breakout_id = @breakout_id

 		IF @@ROWCOUNT > 0 BEGIN
 			SET @limiting_package_ids = SUBSTRING(@limiting_package_ids, 2, LEN(@limiting_package_ids) - 1)

 			DELETE
 			FROM #Data
 			WHERE indication_id NOT IN (
 				SELECT DISTINCT indication_id
 				FROM dbo.udf_split(@limiting_package_ids, ',') P
 				INNER JOIN CommonProductSLX.dbo.Package_Template PT
 					ON PT.package_id = P.element
 				WHERE LTRIM(RTRIM(IsNull(P.element, ''))) <> ''
 			)
 		END

 --SELECT * FROM #data


 	SELECT element_id AS rid, LTRIM(RTRIM(LOWER(element))) AS value
 	INTO #loop
 	FROM dbo.udf_split(@options, ',')

 	SELECT @imax = MAX(rid) FROM #loop
 	SET @i = 1
 	WHILE @i <= @imax BEGIN
 		SELECT @options = value FROM #loop WHERE rid = @i

 		SET @s = ''

 		IF @options IN ('i') BEGIN -- indications
 			SELECT @s = @s + ',' + CAST(indication_id AS varchar(9)) FROM #data WHERE indication_id >= 0 GROUP BY indication_id ORDER BY indication_id
 		END ELSE IF @options IN ('c') BEGIN -- countries
 			SELECT @s = @s + ',' + CAST(country_id AS varchar(9)) FROM #data WHERE country_id >= 0 GROUP BY country_id ORDER BY country_id
 		END ELSE IF @options IN ('b') BEGIN -- breakouts
 			SELECT @s = @s + ',' + CAST(breakout_id AS varchar(9)) FROM #data WHERE breakout_id >= 0 GROUP BY breakout_id ORDER BY breakout_id
 		END ELSE IF @options IN ('ic') BEGIN -- IndicationsCountries
 			SELECT @s = @s + ',' + CAST(indication_id AS varchar(9)) + '|' + CAST(country_id AS varchar(9)) FROM #data WHERE indication_id >= 0 GROUP BY indication_id, country_id ORDER BY indication_id, country_id
 		END ELSE IF @options IN ('ci') BEGIN -- CountriesIndications
 			SELECT @s = @s + ',' + CAST(country_id AS varchar(9)) + '|' + CAST(indication_id AS varchar(9)) FROM #data WHERE country_id >= 0 GROUP BY country_id, indication_id ORDER BY country_id, indication_id
 		END

 		IF @s <> '' SET @s = SUBSTRING(@s, 2, LEN(@s) - 1)
 		
 		INSERT INTO #results
 		SELECT @options AS [key], @s AS value

 		SET @i = @i + 1
 	END

 	SELECT * FROM #results

 	RETURN 0
Comments
Post Details
Added on Nov 21 2024
0 comments
27 views