30 SQL Developer Tips in 30 Days, Day 8: Use the Cart to Build Deployment Scripts

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:

Let’s say you have some objects you need to quickly create somewhere else. And you want to it such that it can be scripted/scheduled.

I am using the Cart right now to build out a demo environment for folks that want to show off some SQL Developer features. For instance, I’m building a ‘shopping list’ to cover things such as:

  • Spatial data
  • Redaction
  • 12c Scheduler Chains
  • BLOB viewer
  • Partitioned data – enough rows to make queries interesting
I can simply drag database objects off the tree and build a 'shopping list'

I can simply drag database objects off the tree and build a ‘shopping list’

I can add additional scripts. I can have:

  1. A Pre-Script – runs first to setup the environment
  2. For each object
    1. A Before create script
    2. An After create script
    3. A Before populate/insert script
    4. An After populate/insert script
  3. A Post-Script – runs after everything else has finished

When I click the ‘Export’ cart toolbar button – I end up with something that looks like this:

The 'Generated...SQL' scripts kick off everything in the proper order.

The ‘Generated…SQL’ scripts kick off everything in the proper order.

You don’t have to grab everything in your tables

You can generate JUST the DDL or JUST the data. And if it’s the data, you can use WHERE clauses to filter out the records you don’t need. If you’re going to build out the scripts such that they will put the data/objects in the connected user, make sure to toggle OFF the ‘schema’ option in the EXPORT dialog. This will prevent the CREATE or REPLACE & INSERTS from including the original object schema(s).

Tip/Trick: When Dealing with BLOBs and SPATIAL Data…

Use the SQL*Loader export format. Simple INSERTs won’t cut it here.

Tell Others About This Story:

Comments 5

  1. Started using the “Cart” after stumbling upon it and reading your tips. Unlike the “database export”, I can’t find an option in the Cart export to generate “insert statements” rather than a dsv (csv) file. Is there a way to accomplish cart data exporting to a sql file loaded with insert statements?

    As usual, thanks bunch!

  2. I’m using SQL Developer 4.0, and I have yet to find a way to drag objects into the cart when they are in schemas other than my own. I do not log into the development schemas directly. Our policy is that I have to use my DBA account to access objects in other schemas. When I try to drag an object, it does not go into the cart. Is there a configuration that would allow this?

  3. Jeff,

    I find Cart to be very useful to collect all the database objects I am working on so that after some part of development is finished I could generate the final script, review it and send it to be executed in test/production environment. However, my final script that I usually make often includes some PL/SQL scripts or SELECT, DML statements that need to be executed in addition to the DDLs that I can generate from the Cart. For such scenario it would be much more convenient to drag and drop such scripts/statements from the Files navigator (or even better include one of the open worksheets to the cart without ever saving them) as separate items to the cart than to add them as the initial/final scripts. I see initial/final scripts as an envelope where database objects and other scripts should be put in. And after/before create scripts reserved for the things that are actually related for what needs to be done after or before the object creation. I would like to see these scripts/statements on the same level as database objects, included to the Cart, because for my final script, which I want to generate from the Cart, they are as important as other objects, thus, should not be put in the after/before create part, which is kind of hidden from the main Cart view.

    Do you see this as a possible enhancement to the Cart? It doesn’t seem as a difficult thing to implement, but maybe SQL Developer’s team’s idea for what the Cart should be used for is different and including files/worksheets as standalone objects to the cart does not fit the idea of the Cart functionality?

    It would be very interesting to hear your view on this.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *