My Oracle Support Banner

Wrong Translation In Sql*Developer Scratch Editor of SQL*Server Code Using Temporary Tables (Doc ID 1375369.1)

Last updated on FEBRUARY 26, 2019

Applies to:

Oracle SQL Developer - Version 3.0 to 3.0 [Release 3]
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

Symptoms

SQL*Server code that uses temporary tables is not translated correctly by SQL*Develoepr 3.0.

For example, the following code -

SELECT COL1
INTO #temptable1
FROM TABLE1
WHERE COL1 = COL2
AND COL3 IN( SELECT COL3 FROM TABLE2)
group by col1;


is translated as -

/*Global Temporary Tables:1 *//* Translation Extracted DDL For Required
Objects*/
CREATE GLOBAL TEMPORARY TABLE tt_temptable1
AS (
SELECT COL1
FROM TABLE1

);
/


DELETE FROM tt_temptable1;

INSERT INTO tt_temptable1 (
SELECT COL1
FROM TABLE1
WHERE COL1 = COL2
AND COL3 IN ( DELETE FROM tt_temptable1;

INSERT INTO tt_temptable1 (
SELECT COL3
FROM TABLE2 ) )

GROUP BY col1 );


but the DELETE and INSERT inside the subquery are wrong.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.