Question
SQL Developer Cart deployment script dependency ordering
Rin Vale
0 reputation · 16 Jun 2020, 09:32 UTC
18.1K views1
Loading…
0 reputation · 16 Jun 2020, 09:32 UTC
The Cart feature in SQL Developer 21.2 allows for the bundling of schema modifications, such as table alterations and constraint updates, into a single exportable SQL script for deployment.
When staging changes for an application migration, the generated script preserves the order in which objects were added to the Cart. However, there is no built-in mechanism to automatically reorder these statements based on object dependencies, such as foreign-key relationships or trigger requirements.
In scenarios requiring zero-downtime schema updates, executing a script with incorrect statement ordering can lead to integrity constraint violations during the migration process.
Oracle SQL Developer does not provide a configuration setting to automatically resolve object dependencies (such as foreign keys or view references) during the Cart script generation process. The generated DDL follows the order in which objects were added to the Cart, meaning the tool does not perform a topological sort of the schema changes.
Because the Cart treats the object list as a simple queue, it is common to encounter ORA-00942 (table or view does not exist) or integrity constraint violations. This occurs when a child table is defined before its parent table, or a view is defined before its underlying base tables, simply because of the sequence in which they were dragged into the Cart interface.
To ensure a successful migration without manual script reordering, use one of the following strategies:
Add objects to the Cart in a strict hierarchical order. While tedious, this is the only way to control the output sequence natively within the tool:
For complex migrations, separate the table creation from the constraint application. Generate the Cart script for tables first, then create a second Cart specifically for ALTER TABLE ADD CONSTRAINT statements. This eliminates the risk of parent-child ordering conflicts.
Before executing a Cart script against a production or staging environment, verify the sequence using these steps:
sqldeveloperCONSTRAINT keywords to ensure ADD CONSTRAINT calls appear after all referenced tables are created.ORA- errors related to object existence.Diagnostic Detail Needed: Are you deploying new objects exclusively, or are you modifying existing objects? If you are performing ALTER operations on existing tables, the dependency risk is lower than during initial object creation.
Use comments to ask for clarification. Post a solution as an answer.
30,275 reputation · 16 Jun 2020, 18:32 UTC
The Cart pane does not analyze object dependencies; it simply exports the DDL in the exact sequence shown in the list. However, you can change that sequence without leaving the Cart:
If you need automatic dependency‑ordered DDL, you must use the Database Export wizard (right‑click → Export → Database Export) and enable the Generate DDL in dependency order option. This setting is ignored when exporting from the Cart, so the two paths produce different statement orders.