A friend of mine asked how he could generate CSV for 40 Oracle tables in Oracle SQL Developer.
He could of course use Tools > Database Export to accomplish this.
But, it was always the same 40 tables. And using the object picker in the wizard can get tedious, especially if it’s the same 40 tables every day/week/month/epoch.
So, I told him to go shopping.
The Cart, Again
I’ve talked about the Cart a few times. It was even one of my 30 Tricks in 30 Days Posts awhile back. But in that post I talked about building deployment scripts, not table exports.
So let’s do this.
Open the Cart. View > Cart.
Go shopping. Literally, select one or more tables and drag them over into the cart.
Then check the options you want. In this case, no DDL, just Data.
Save your cart. Give it a good name. Then you can easily re-use it later.
Hit the Export button.
Set your options. In this case, I want a file per table in a single directory. And I want the data format to be CSV.
Say ‘Apply’ and SQL Developer will start generating the files.
You can run the process in the background if you’d like…
So it’s done, now let’s go take a look.
But Jeff, GUIs are so Yesterday
Sure. So use the SQL Developer CLI – not to be confused with SQLcl.
This would be sdcli. It’s the full SQL Developer sans the graphics. You can use it to export carts. Just set all of your cart options and save them to files. So you need to save your cart. And you need to save your database export options to a file.
And then feed that cart filename and database export config filename to sdcli.
┌─[12:55:28]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$ └─>./sdcli cart help Oracle SQL Developer Copyright (c) 1997, 2015, Oracle and/or its affiliates. All rights reserved. Invalid CART command: help CART Usage: cart <command> <command arguments> cart <command> -help|h Supported commands: export -cart <savedcart.xml> -config|cfg <exportconfig.xml> [-target|tgt <dirorfilename>] [-logfile <filenameorstderr>] [-deffile <exportdefinitionfile>] cloud -cart <savedcart.xml> -config|cfg <deploycloudconfig.xml> [-target|tgt <filename>] [-logfile <filenameorstderr>] [<clouddefinitionfile>] copy -cart <savedcart.xml> -config|cfg <copyconfig.xml> [-logfile|log <filenameorstderr>] [-deffile <copydefinitionfile>] Examples: cart export -cart /home/carts/cart.xml -cfg /home/carts/exporttools.xml Export the objects included in cart.xml using the options saved in exporttools.xml cart cloud -cart /home/carts/cart.xml -cfg /home/carts/cloudtools.xml Deploy the objects included in cart.xml using the options saved in cloudtools.xml. cart copy -cart /home/carts/cart.xml -cfg /home/carts/copytools.xml Copy the objects included in cart.xml using the options saved in copytools.xml
Don’t Forget the Cloud!
I talk about using the Cart to batch automate uploads to our Database Schema service here…and it goes over the syntax for the CLI some more in case you need help.
5 Comments
Wow. Really worked good but it is not exporting headers and not changing Sheet name if we export in excel.
I tried it just now and got the Headers. There is a ‘headers’ checkbox on the Cart export dialog you need to check.
Check, enable ‘Headers’ – and on worksheet name, leave that blank.
I show all that here.
Another great post Jeff.
Inspired me to share how I have been using sdcli and carts for a while now.
See http://wp.me/pUer6-1Z.
Hey that’s great! I shared it on my Twitter timeline. Thanks for the write-up.