My Oracle Support Banner

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

Last updated on FEBRUARY 25, 2021

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 any schema, for example any test schema provided by Oracle.
  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 any offline table and choose "duplicate..." and Rename it to any unique name (for instance "TABLE2"). Then click OK.
  19. Double click on the new table, rename a Column adding a numeric prefix (for instance COL to COL2)
  20. Add new Unique Constrains for another column.
  21. Click OK.
  22. Right click on the newly created offline table (TABLE2) 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 the offline table 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.