Oracle JDeveloper 220.127.116.11.0 Generates Invalid SQL Scripts in Alter Mode from Offline Database
Last updated on JUNE 30, 2017
Applies to:Oracle JDeveloper - Version 18.104.22.168.0 and later
Information in this document applies to any platform.
When working with Offline Databases in JDeveloper 22.214.171.124.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.
Steps to reproduce the issue using JDeveloper 126.96.36.199.0
- Create new GENERIC application
- Choose "Database (Offline) in the Project Technologies.
- Click "Finish"
- Create new Project
- Choose "Offline Database Objects" in Tab "Current Technologies" in List "Categories"
- Choose "Offline Database" in List "Items"
- Name Database "Test"
- Choose Oracle10g R1 for Database emulation
- Click OK
- Generate Database connection. Use schema "SCOTT".
- Right-click in Application Navigator on Item "Test"
- Choose "Copy to Projects" from the context menu
- Choose the Database connection, which you created on step 10.
- On "Specify Target" the default should be ok.
- On "Object Picker" click "Query" to get the whole schema
- Then put all objects from the left list to the right list.
- On "Choose Operation" leaf the default (should be CREATE) and click finish.
- Right click on offline table EMP and choose "duplicate..." and Rename it to EMP2. Then click OK.
- Double click on EMP2, rename Column EMPNO to EMPNO2
- Add new Unique Constrains for ENAME.
- Click OK.
- Right click on offline table EMP2 and Choose "Generate to..."->"SQL-script".
- 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.
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