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