My Oracle Support Banner

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

Last updated on FEBRUARY 03, 2019

Applies to:

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


When working with Offline Databases in JDeveloper 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.


Steps to reproduce the issue using JDeveloper

  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.


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

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