Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Scheduling Exports of Your Data Using the Cart

4 Mins Read

This post won’t show you how to acutally do the scheduling bits – I’m going to assume you know how to use contrab or Windows Scheduler -but I will show you how to get SQL Developer setup so that you can call it from the command-line to do your export.

Our requirements – from an AskTom question –

  • I want delimited text output, TABs
  • the numbers need to be quoted
  • I’m running on Windows BUT i want NIX style line feeds

This is totally doable.

However.

SQL Developer is ‘fat’ – make using the right tool. It will take a few moments to fire up and run when called from SDCLI. The benefit is that you have access to some very powerful GUI features, like the Cart (read this!)

SQLcl could also do much of this. But, to get the ‘custom’ line feeds, you’d probably need to write your own formatter using some JavaScript. And folks like GUIs, because they can point and click vs type/code – so let’s show how to do this in SQL Developer.

Step One: We’re Going to Need a View

We need a view for two reasons. One, don’t have a way to execute and export ad hoc queries from the command-line (unless you’re using SQLcl!). And two, this customer wants their numbers quoted – and there’s no setting in SQLDev to do that.

So let’s pretend we’re batch automating exports of LOCATIONS.

CREATE VIEW AUTOMATED_EXPORT_LOCATIONS
( LOCATION_ID
, STREET_ADDRESS
, POSTAL_CODE
, CITY
, STATE_PROVINCE
, COUNTRY_ID
)
AS SELECT to_char(location_id), street_address, postal_code, city, state_province, to_char(country_id) FROM locations;

Step Two We Need to Setup a Cart

So open the Cart panel (View menu), and drag the VIEW into it.

Now we have to setup the Export parameters.

So now our Cart has something in it. And we want to do something WITH the Cart.

We’re going to be asking SDCli to do an Export, so let’s click the Export button and fill in the details. These will get saved to an XML file and then referred to in the cli call.

This says, when doing an export of a Cart, here’s what I want to happen.

You’ll note that I didn’t include the Export File name in the Red Box area. That’s because the export file name is part of the SDCLI call, so you can call the file it spits out anything you want…which also means your export job could use a dynamically named export file if you wanted. We’ll show that later.

What we have to do now is actually save the Cart itself.

The cart file says these are the objects (and database connections in play) and/or files you want processed.

Executing the Scenario from the CLI

When I was writing this, I tested the Cart run from the GUI just to make sure I got out what I was expecting. Once that passed my sanity test, I jumped over into the place where we go to type vs click.

I’m on Windows, so I open a CMD window, and I navigate to my BIN directory where I have SQL Developer ‘installed.’

Then I call the CART feature.

I’m going to provide these bits of info:

cart name        - C:\Users\jdsmith\sf_blog_cart.sdcart
cart config      - C:\Users\jdsmith\sf_blog_cart_example.xml
cart export file - C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt

And here’s the call:

sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt

Notice I have the Export File defined on the -target flag, that determines the name of the delimited file that will get generated.

So I run it, and then open the file –

I’ve turned on in the Code Editor Display preferences to show the white space characters.

Seeing the file, I can see my tabs and LFs 🙂

If You Made it this Far and You Think This Should be Easier…

Pick up a book on JavaScript and have a go at it with SQLcl. Kris has made it quite easy to get started. I even managed to build a couple of my own custom formatters.

Here’s one I wrote that uses a ‘;’ to CSV files (yuck!)

customer result set query results sqlcl javascript
Any bugs here are mine – I’m not a great copy-paster to begin with and my JS skills are hilariously inept.
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.

Related Posts

10 Comments

  1. Kevin SP SPENCER Reply

    Hi,
    Thanks for your tips.
    How can i dynamically named export file ?
    Can you explain how can i export my file with Windows Scheduler?
    I need to put this “sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt” on Windows Scheduler?

    Thanks for your answer !

    • thatjeffsmith Post Author

      Now that we have SQLcl, I would simply use that to spool your query to the file format of your choice.

      Sqlcl is much lighter than sdcli (starts quicker, fewer resources).

  2. Joe Barbara Reply

    This works great but every time I run the cart commands it asks for my db password. I’ve used cmdline interface to make a connection first and save the password I looked in the connection file its all there, but cart export always wants a password. I need to script this but this nagging password entering is holding me up.

    here are my commands:
    sdcli migration -actions=mkconn -connDetails=”New Test Db”:oracle:${ora_dbUser}/${ora_dbPassword}@${ora_dbServer}/${ora_dbName}:”New Test Db”

    sdcli cart export -cart ${cwd}/ETL/data/My-Cart.sdcart -config ${cwd}/ETL/data/My-Export.xml -target ${cwd}/ETL/data/ -logfile ${LogPath}/export.log

    Any tips

    • thatjeffsmith Post Author

      for some reason access to the password file isn’t working…

      why not just script this using SQLcl? It’ll be faster, and you’ll have more options.

    • Jim Malone

      In one of the comments below it looks like you are advocating for switch from sdcli to SQLcl. If that is the case, do you have a quick example of using SQLcl to export the contents of a cart to an xslx file?

  3. Sap Reply

    Hi Jeff

    Very good article.

    How to deal with multiple objects in the cart? I mean how to specify multiple file names within sqlcli

    Thank you

    Kindest regards

    • thatjeffsmith Post Author

      You setup the config of the export of the cart in the GUI and that’s written in the file you reference in the sdcli cart run

    • Joe Barbara

      I already have a cart of tables that I can export. Not sure how to do with sqlcli as I need ^^ as a field delim and ~~ record delim due to normal characters being sprinkled through the data in the system.

      I don’t know how I could control query output with sqlcli to do that

      I am using sqlcli for executing lots of of other bits in this system — just not the exports.

      Due you have an idea how I could achieve that format with sqlcli?

      I am also using another cart I need to dump blobs in loader format that I massage into the same ^^,~~ format after the fact since that seemed the only way to pull out blob data as well

      I actually would much rather have my export be driven the result of a sql query vs a raw table export as I often need to create intermediary tables to unencrypt things before export, but could have been done by the same query filling those intermediary tables if I could get the delimiters right.

Write A Comment