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!
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.
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.
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
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!
31 Comments
Hi Jeff,
I used SQL developer Cart to create scripts to export data to a csv file. When I run the scripts in CMD It runs successfully and export the data. When another user other than me run the same cart script in CMD. The csv file gets created but no data is imported. my username that runs the scripts successfully do not have direct access to the database from where the data is being exported from. The credential to connect to the database from SQL developer is saved. I guess thats what the cart scripts uses. (Sdcart and xml). Please is there a particular kind of windows permission that other users require to run the cart scripts successfully. We are all Administrators on the system. I want other users to be able to run the cart scripts successfully like I do.
Each OS user needs their own connections defined, else the CLI can’t see them. So have user 2 use the gui, create a connection by the same name as yours.
Thanks a lot Jeff. Works Perfectly well. The other users are able to run the scripts after creating their own connections on Sql developer.
Hi Jeff,
Through the cart it is possible to write blobs(clob etc.) to file(s) at the client side without the need of an Oracle Directory. Is it also possible (not using UTL_FILE) to script this through SqlCL? And if yes, could you point me into the right direction?
SQLcl, no. SDSQL – yes.
Hi there!
As a total novice I would like to know if it’s possible to automate these kinds of tasks such ass adding tables to the cart and deploying them to the cloud?
Thanks in advance.
someone figured out how to write a script to manipulate the Cart xml file
Well look at that! I was already doing the same in C# since I know that programming language. Also fun to see is that the article was posted just 4 days ago.
Thanks Jeff!
Been using SQL Developer for quite some time and only today discovered the Cart. Thanks for the training session! I’ve been using Export Database all the time but I think I can make better use of the Cart.
Still, the reason I stumbled upon the Cart is I’ve been trying to figure out how to have the exported scripts contain “create or replace” rather than simply “create”. Is that a feature in SQL Developer today? I’m using v.4.1.1.19 due to constraints on upgrading java in this rdp/vmware environment. Perhaps it’s available in the newer versions?
it SHOULD be doing create or replace…for example I dropped a procedure into the cart, and then exported the cart to a worksheet, and I got this =>
DROP PROCEDURE “HR”.”OWA_HELLO”;
——————————————————–
— DDL for Procedure OWA_HELLO
——————————————————–
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE “HR”.”OWA_HELLO” is
begin
HTP.P(‘Hello World!’);
end OWA_HELLO;
/
Thanks for the blazingly fast response, Jeff.
I think I have a permission issue as it doesn’t do that and I only now noticed that the top of the exported code contains this error or warning
— Unable to render PROCEDURE DDL for object . with DBMS_METADATA attempting internal generator.
So I guess that whatever the “internal generator” is, it isn’t adding the ” OR REPLACE” bit.
I’ll check with the DBA to try and figure this out.
Oops that didn’t post correctly …
— Unable to render PROCEDURE DDL for object SCHEMA.PROCNAME with DBMS_METADATA attempting internal generator.
Problem solved … missing permissions to the package. I guess the internal generator is SQL Developer’s generator which doesn’t seem to have an option to include this feature.
Thanks for the confirmation … I’m happy.
Hello Jeff,
Is it possible to add many queries into my shopping cart and export their result all at the same time?
I mean, in a similar tool we have the option to export multiple query results into one Excel file with multiple sheets (one query result each sheet), are there something similar within SQL Developer limits?
I was wondering if it is possible with the new shopping cart feature like using some external .sql file or something like that.
Regards,
Renan.
Your queries would have to be views, otherwise, not possible today.
Hi, Jeff
I’m playing with cart.
Tables, functions, procedures, package that’s ok, but when i try with scheduler objects (I need chains and programs in particular) . The drag doesn’t work. How can I export these objects ?
Thanks
Enrico
I’m using Developer 4.0.2.15
did you try Tools > Database Export?
The scheduler objects are not present among the items you can select for database export :-(.
Hi
Enrico
yeah, you’d have to export them to a script and then add that script as a extra item to your cart
SQL Developer 4.0.3.16
So I was just starting to play with the cart, and i have the following objects listed visually (and I’ve verified that the Cart.xml follows suit):
TABLE (with a trigger)
PACKAGE SPEC
PACKAGE BODY
TRIGGER
PACKAGE SPEC
PACKAGE BODY
and the export.xml generates items in the following order:
TABLE
TRIGGER
TRIGGER (from the initial table)
PACKAGE SPEC
PACKAGE SPEC
PACKAGE BODY
PACKAGE BODY
This doesn’t work, necessarily, because the standalone trigger references a procedure in one of the package specs. I mean, I can always recompile, but I’m specifying the order for a reason. Any tips?
I really like the feature, btw.
Thanks,
Chuck
we set the order such that it should work regardless of how a user adds it to the cart
generally triggers are created after tables
you can code around this by removing your trigger from the cart, and adding an after script for your dependent package that creates the trigger manually
That works, but if it means I have to visually inspect the order of items each time to determine if an object belongs in a script, then it removes some of the utility of the cart, especially as the number of objects increases.
I did have another colleague open my cart.xml file and export just to see if it was my installation of SQL Developer that was having an issue, and the undesirable order remains the same as I reported above.
This is a good feature, I was trying to figure out if I can specify the extensions somewhere.. like PKS, PKB. Is it there somewhere and I am missing or it is not there. I am fine even if it is somewhere inside XMLs that I need to specify, but it will be good to have it.
Ok, thx, but I like your drag and drop deployment packaging 🙂
Hi Jeff,
I didn’t mention that I am trying to do it from the Oracle Cloud.
Just talked to my coworker and he can drag from a different invironment to the Oracle Cloud but not able to drag from Oracle Cloud to the other environment.
Thank you, Bill
Oh….that’s a big difference. You can get data UP to our Cloud using the cart. But you can’t get it down using the cart.
You can use the cloud console (login to MyServices), go to your MyServiceURL. On the left you’ll see ‘Exports.’ Then you can export your DDL and optionally the data. And that goes to your SFTP server in your outgoing folder.
Wow thx for the quick reply..
Version 4.0.0.13
Build MAIN-13.30
Just tried a table then a couple different tables just to make sure.
Hi Jeff, The cart sounds great! But I am dragging objects over and the icon changes as it should but the object does not show up.
Thanks, Bill
Well that’s not cool. What version of SQL Developer are you running and what types of objects are you dragging?
Pingback: Free Beer or How to Get Free Demo Data from iTunes