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

Last updated on OCTOBER 27, 2016

Applies to:

Oracle SQL Developer - Version: 3.0 to 3.0 - Release: 3 to 3
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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms