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.

Lots and lots of choices here.
Lots and lots of choices here.

Say ‘Apply’ and SQL Developer will start generating the files.

I always pick the wrong line at check-out.
I always pick the wrong line at check-out.

You can run the process in the background if you’d like…

So it’s done, now let’s go take a look.

Ding, ding, ding. We're good to go.
Ding, ding, ding. We’re good to go.

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.

remember, use good names
remember, use good names

And then feed that cart filename and database export config filename to sdcli.

Raw text below in case the print's too small to read here.
Raw text below in case the print’s too small to read here.
┌─[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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. Wow. Really worked good but it is not exporting headers and not changing Sheet name if we export in excel.

Write A Comment