How To cURL Oracle Database Data Pump Export on Windows

The Task: Export some data from your Oracle Database, purely via HTTPS

Data Pump: ‘dump’ in or out your Oracle objects and/or data.

Here’s what you want to do:

In your Oracle Database…
Export some or all of your database using Data Pump &
Do this via HTTPS &
Automate this via Shell scripting &
Yeah, you’re on Windows.

What you need

The following will be required:

an Oracle Database
database credentials, i.e. user name and password
Oracle REST Data Services (ORDS) available for said database
The Database API to be enabled for said instance of ORDS/Database
a Windows machine with network access to your ORDS mid-tier
cURL installed/available on said Windows machine
knowing how to use cURL

A Magical Place where most of this is already sorted

*Oracle Autonomous Database comes pre-configured with ORDS & the DB Management APIs.*

Wait, tell me more about these APIs…The TL;DR from that is, ORDS gives you:

more than 500 database management APIs
secured via database authentication
includes APIs for Data Pump

I can create an Always Free Autonomous instance of the database, and then immediately use the ADMIN database user account to use these 500+ APIs.

The Data Pump Endpoint Doc

Go here for the full description…

POST: we’re going to kick off a DBMS_DATAPUMP JOB.

Since it’s a POST, using a browser isn’t normally available. Instead, you would want to use a REST Client like Insomnia or Postman.

I like Insomnia, but we’re here to talk about cURL.

Building the REST Request

The following information is required to make our POST call, whether we’re using Postman, cURL, or whatever else.

Base URL for our database via ORDS
Database username & password
The API endpoint
The parameters we want to supply to the job
- what are we exporting
- where are we writing the DMP file(s) to

Base URL: https://abcdefghijklmnop-MYADBatp.adb.us-ashburn-1.oraclecloudapps.com/ords

DB User: ADMIN

API Endpoint: /admin/_/db-api/latest/database/datapump/export

Parameters: { "datapump_dir": "DATA_PUMP_DIR", "file_name": "jeff_demo.dmp", "filter": "ACTIVITIES, UNTAPPD", "job_mode": "TABLE", "threads": 2 }

The fun part: converting this to cURL for Windows

It took me about 20 minutes to go from copying the generated cURL code from my REST GUI client to code that was working.

I googled, read some StackOverflow answers, and then troubleshot error messages that indicated I was forgetting the escape characters or had too many, or forgot to include a ^ to indicate I had another line of input coming.

Here’s what I’m running, then we’ll break it down, line by line:

curl -su 'admin' --request POST ^
--url https://abc-dbatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/datapump/export ^
--header "Content-Type: application/json" ^
--data "{\"datapump_dir\": \"DATA_PUMP_DIR\", \"file_name\": \"jeff_demo.dmp\",   \"filter\": \"ACTIVITIES, UNTAPPD\",   \"job_mode\": \"TABLE\",   \"threads\": 2 }"

c:\curl\bin>curl -su admin --request POST ^

We’re running cURL.

-s, for filent

-u for I’m going to give you the username and password, if the password isn’t there, prompt for it

–request, doing a POST

^, in Windows expect more input after the CR/LF

--header "Content-Type: application/json" ^

Add a request header, we’re going to be sending JSON on the request body

—url https://abc-db.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/datapump/export ^

-url for here is where we are going to POST

--data "{\"datapump_dir\": \"DATA_PUMP_DIR\", \"file_name\": \"jeff_demo.dmp\", \"filter\": \"ACTIVITIES, UNTAPPD\", \"job_mode\": \"TABLE\", \"threads\": 2 }"

–data, here’s that JSON I was telling you about

\, escape the quotes (“)

What that looks like.

I just pasted in this entire 4 lines of code.

The Response, formatted:

{
    "job_name": "DATAPUMP_REST_EXPORT_20220329143637",
    "owner_name": "ADMIN",
    "operation": "EXPORT",
    "job_mode": "TABLE",
    "state": "EXECUTING",
    "degree": 2,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://abc-dbatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://abc-dbatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://abc-dbatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/datapump/jobs/ADMIN,DATAPUMP_REST_EXPORT_20220329143637/EXPDAT-2022-03-29-14_36_37.LOG"
        },
        {
            "rel": "self",
            "href": "https://abc-dbatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/datapump/jobs/ADMIN,DATAPUMP_REST_EXPORT_20220329143637/"
        }
    ]
}

The request is asynch. We make the request, we don’t wait for the Job to finish. Instead as the job is created, we let you know, and give you Links to follow along the process.