Scheduling Exports of Your Data Using the Cart

thatjeffsmith SQL Developer 0 Comments

Tell Others About This Story:

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.

Related Posts

Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *