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.

Looking at the Export in SQL Developer Web

From my OCI Console, I can go directly into ‘Database Actions,’ or SQL Developer Web, and open the Data Pump screen.

Here’s I’ve got a list of jobs, and their resulting DMP and Log files.

I asked for a jeff_demo.dmp file, so that’s what I got.

Since we’re here already, we can pull up the Log via the GUI –

Ta-da!

I really hate cURL

Yes, it’s powerful and ubiquitous. But is it user-friendly if the user is a human being? Not really.

If you’re on a Mac, httpie is some much easier!

Mmmmm, pie.

Sneak Peek cURL in ORDS/SQLDev Web 22.1

The team has made it much easier to copy out CORRECT cURL snippets. For example, we’ll recognize you’re on Windows 🙂

22.1 is coming out SOON, real soon.
thatjeffsmith
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.

2 Comments

Reply To Eddie Cancel Reply

RSS
Follow by Email
LinkedIn
Share