11 Replies Latest reply on Oct 20, 2017 2:56 PM by thatJeffSmith-Oracle

    cart does not generate DDL in requested order

    B.Delmée

      observed under 17.2: "export cart" does not generate DDL for objects in the order they appear in the list (e.g. a view comes before a table it references, despite being correctly ordered in the GUI list)

        • 1. Re: cart does not generate DDL in requested order
          thatJeffSmith-Oracle

          The order of items in the cart is not observed when generating DDL. We have our own order, but I thought views would have come after tables. Will need to check with the developer.

          • 2. Re: cart does not generate DDL in requested order

            observed under 17.2: "export cart" does not generate DDL for objects in the order they appear in the list (e.g. a view comes before a table it references, despite being correctly ordered in the GUI list)

            Just a CAVEAT: no matter what order DDL is generated in it can NEVER guarantee that the objects can all be created in a VALID state. There are numerous ways objects can be cross-referenced (i.e. in a circular, or psuedo-circular fashion). Your build process should ALWAYS include checking for invalid objects and (possibly multiple) recompiles to make everything valid.

             

            I agree, though, that since some objects (e.g. views) will typically depend on other objects (e.g. tables) some ordering of object types will be helpful.

             

            But views can also depend on functions, packages, sequences so, as said, no guarantees are available.

            1 person found this helpful
            • 3. Re: cart does not generate DDL in requested order
              thatJeffSmith-Oracle

              Here's the order. I would say VIEWS should be moved down to post-tables.

               

              This order is followed by export from Nav, Tools or cart.  Note within each type ( say tables, they are done in the order they are in the cart).

               

              Also, indexes, constraints and ref-constraints may be added automatically by reconciliation and would follow objects of that type that were manually added.

               

              // Objects created pre-data

              VIEW

              TYPE

              DBLINK

              TABLE

              SEQUENCE

              MATVIEW

               

                      // Then all the data loads

               

               

              // Objects created post-data

              INDEX

              TRIGGER

              MATVIEWLOG

              PROCEDURE

              PACKSPEC

              PACKBODY

              FUNCTION

              OPERATOR

              SYNONYM

              QUEUE

              QUEUETABLE

               

                      DIRECTORY

              APEX

              APEX_PAGE

               

                      CONSTRAINT

               

              REFCONSTRAINT

              1 person found this helpful
              • 4. Re: cart does not generate DDL in requested order
                B.Delmée

                Thanks for checking this out. I understand final validity of all deployed objects cannot be guaranteed in general. At the very least the GUI is confusing in letting you think you can fine-tune scripting order, and starting with views is an odd choice. I'd swear i questioned it on this very forum long ago, when i noticed this is what is also happening when using the "export db" fuinctionality. On the other hand the cart allows the inclusion of a finalising script and i have included one which just recompiles invalid objects in the current schema.Given time I might just grow to like he cart, who knows ?

                • 5. Re: cart does not generate DDL in requested order
                  thatJeffSmith-Oracle

                  >>Given time I might just grow to like he cart, who knows ?

                  Sounds like you already do

                   

                   

                  We use the cart to do cloud uploads - we can't rely on users being savvy enough to order things correctly in their carts, so we use the logic as provided above to get a close to successful as possible. I've yet to find a grocery store that unloads my hsopping cart in the order i loaded it either ...

                  • 6. Re: cart does not generate DDL in requested order

                    I bet one or more of your DB teams could do a quick review of your list and make suggestions as to the order.

                    // Objects created pre-data

                    VIEW

                    TYPE

                    DBLINK

                    TABLE

                    SEQUENCE

                    MATVIEW

                    Tables might also depend on SEQUENCES - especially in 12c with sequences being used to support IDENTITY columns.

                     

                    // Objects created post-data

                    INDEX

                    TRIGGER

                    MATVIEWLOG

                    PROCEDURE

                    PACKSPEC

                    PACKBODY

                    FUNCTION

                    OPERATOR

                    SYNONYM

                     

                    . . .

                    Nothing is guaranteed so I can only offer what I have typically seen in 30+ years:

                     

                    1. packages are much more often dependent on standalone PROCEDUREs and FUNCTIONs than the other way around

                    2. packages are also much more dependent on SYNONYMs than the other way around.

                    I've yet to find a grocery store that unloads my hsopping cart in the order i loaded it either ...

                    Maybe that is because the items you purchase generally don't have any dependencies between them. But when there are dependencies (5 for $10 but only if you buy 5 or more) I have seen customers try to unload their carts to put those dependent items together and also seen cashiers try to spot/process all 5 or more items together to get the proper price.

                     

                    Dependencies, dependencies, dependencies.

                    • 7. Re: cart does not generate DDL in requested order
                      thatJeffSmith-Oracle

                      no matter the order, there will always be circular references, but yeah

                       

                      on sequences, .... i'm not sure. constraints are added to the end of the order, and that would catch primary key constraints

                       

                      and identify columns use sequence under the covers, but there's no user defined sequence for an identify column, that's the appeal of the identity clause, you don't have to worry about triggers/sequences

                      • 8. Re: cart does not generate DDL in requested order
                        B.Delmée

                        Another annoyance i noticed: package gets dropped before package body, hence the second one fails (dropping the package has already dropped the body as well, so script execution stops/raises an error).

                        If the cart's purpose is to generate solid deployment scripts, it's not quite there yet.

                        • 9. Re: cart does not generate DDL in requested order
                          thatJeffSmith-Oracle

                          please start a new thread with details

                          • 10. Re: cart does not generate DDL in requested order
                            B.Delmée

                            I cannot think of anything more more to contribute yet as this was my first endeavour with the cart.

                            As pointed by rp0428, it is not possible to guarantee a script that will run without leaving any intermediary invalid object.

                            A lot of noise/error messages, though, can likely be avoided by a better adjusted ordering of object types.

                            • 11. Re: cart does not generate DDL in requested order
                              thatJeffSmith-Oracle

                              You're left with a package that's valid, you just don't like the error message re: the drop not being successful b/c the object was already dropped. Could that be better, sure, not sure I see a bug here though. You could also uncheck DROPs, and let the package go in via CREATE OR REPLACE.

                               

                              Your issue is with 'noisy' deployment scripts, if you want to file an ER, that would be for My Oracle Support.