Oracle JDeveloper 11.1.1.1.0 Generates Invalid SQL Scripts in Alter Mode from Offline Database (Doc ID 1086390.1)

Last updated on JUNE 30, 2017

Applies to:

Oracle JDeveloper - Version 11.1.1.1.0 and later
Information in this document applies to any platform.

Symptoms

When working with Offline Databases in JDeveloper 11.1.1.1.0 the "generate to" function generates a SQL script but with some code that is causing errors in ALTER mode.

For a simple column, JDeveloper generates the following, and is not expected:

Drop column X
Add column X1 (same as X)
Rename column X1 to X


Expected would be that the DROP and ADD commands are not generated.

Changes

Steps to reproduce the issue using JDeveloper 11.1.1.1.0

  1. Create new GENERIC application
  2. Choose "Database (Offline) in the Project Technologies.
  3. Click "Finish"
  4. Create new Project
  5. Choose "Offline Database Objects" in Tab "Current Technologies" in List "Categories"
  6. Choose "Offline Database" in List "Items"
  7. Name Database "Test"
  8. Choose Oracle10g R1 for Database emulation
  9. Click OK
  10. Generate Database connection. Use schema "SCOTT".
  11. Right-click in Application Navigator on Item "Test"
  12. Choose "Copy to Projects" from the context menu
  13. Choose the Database connection, which you created on step 10.
  14. On "Specify Target" the default should be ok.
  15. On "Object Picker" click "Query" to get the whole schema
  16. Then put all objects from the left list to the right list.
  17. On "Choose Operation" leaf the default (should be CREATE) and click finish.
  18. Right click on offline table EMP and choose "duplicate..." and Rename it to EMP2. Then click OK.
  19. Double click on EMP2, rename Column EMPNO to EMPNO2
  20. Add new Unique Constrains for ENAME.
  21. Click OK.
  22. Right click on offline table EMP2 and Choose "Generate to..."->"SQL-script".
  23. Choose "ALTER" and then finish. (this is the ONLY option)

Then,Repeat the following steps:
     24. Right click on offline table EMP2 and choose "Generate to..." -> "SQL-script".and
     25. Choose "ALTER" and then click finish. (No other options are needed).

After that you will see the number added and the ADD and DROP commands.

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