ThatJeffSmith

Introducing the SQL Developer ‘Shopping’ Cart

Just in time for the upcoming holiday shopping season, SQL Developer v3.1 (now available as an Early Adopter release), allows the developer or DBA to build ad-hoc deployment scripts. Need to ‘order’ that list of objects and underlying data for your test environment? Just add them to your cart and you’re ready for check-out!

Nothing beats the convenience of on-line shopping!

Once connected to 1 or more instances, you can use SQK Developer to pull selected objects from multiple databases into a single ‘cart.’ Carts can be saved and re-used as necessary. You can mark certain items for immediate ‘checkout’, and you can also specify if you want the data associated with the tables to also be included.

Here’s a quick over-view of how to use the new cart in SQL Developer.

Open the Cart

You can find the cart feature on the ‘View’ menu in v3.1 of SQL Developer. Now you’re ready to start adding items to your cart.

Drag objects to the cart

You can multi-select any object from the connections tree and drag to the cart. The objects can span schemas and databases. Data objects like tables have the option of also exporting the data to your deployment script. Today this data will be exported via INSERT script, but that could change in the very near future. I would like to see either datapump or SQL*Loader also made available, but this is the first go at it and we needed to start somewhere.

Move one or more objects to the cart

If you DO decide to include the data, you can get picky and tell us WHAT data to include. If you can write a WHERE clause, you can use this tool. Clicking in the ‘Where’ dialog will bring up a quick preview of the data. You can input your WHERE clause and quickly validate it with the ‘Go’ button.

Limit the data that gets exported in your deployment script

Save your cart, or re-open a previous cart

Need to move stuff around frequently? Save your cart and re-use it later. Save yourself a few dozen clicks! If you are project driven, there’s no reason why you could not create a cart for each of your projects. We want you to be able to re-use your work, so you will see this becoming more and more of a common theme in the application.

Deployment Options

Truncate data - think about that one for a second please.

A few things to consider: where do you want the files to go, do you want the SQL formatted, do you want the objects schema-prefixed, do we truncate the data first? Here’s a free bonus tip: When in doubt, don’t truncate!

Once you click ‘Apply’, SQL Developer will generate the script in the background. When it’s finished, look for the…

Generated-%TIMESTAMP%.sql file


This is the file you want to execute. It will kick off the other scripts to create and populate your objects. In the cart UI, you’ll notice there are button to move objects ‘Up’ or ‘Down’ the cart. SQL Developer is intelligent enough to create tables before populating them – even if you move a view up to the top of the cart. It will also load the data before it creates or enables any constraints to avoid any referential violations. However, we have not built a tool that removes any need for planning by the end-user.

If you export a VIEW, you will only get the DDL for the view. You will need to be savvy enough to realize the underlying table objects also need included in the cart. Views get created after the tables, so you ARE OK there.

Pre and Post Scripting

You can also define before and after scripts that help setup and tear down your environments. You may need to create users and tablespaces, or after the objects have been created you may want to FLUSH the SGA or update your statistics.

My Favorite Features Are Ones Built BY Devs for Devs

When you have database developers working on a tool designed for database development, you can imagine the developers might be tempted to build features for their own personal use cases. I find that these features are usually the ones the provide the highest value to the end users. In this case the cart was implemented to help with some of the new Cloud deployment features for 12c. It turns out that it wasn’t ultimately needed, but it sure was handy for a lot of people out there. It’s great to get instant feedback on new features. Gary was kind enough to do so, he apparently loves this feature!