DBMS_CLOUD and USER_LOAD_OPERATIONS with Oracle Autonomous Database

Loading data is a hot topic when it comes to databases, and it always has been. INSERTs, Data Pump, SQL*Loader, External Tables, IMP, RMAN, CREATE/INSERT as SELECTs, using ORDS and AutoREST, importing from Excel…and that’s maybe half of your options for Oracle Database.

One of the PL/SQL packages in Oracle Autonomous is DBMS_CLOUD (Docs) – and it allows you to access files in an Object Store, including the one you get in the Oracle Cloud (OSS).

I can read these files, create new ones, delete them – from a database session. So a very common use case for this package is to be able to read data from one of these files and put that data into a table.

ORACLE-BASE has a nice tutorial, and I don’t want to re-hash covered ground, but I did want to do a quick example, and give a shout-out to a logging view that DBMS_CLOUD uses.

Pre-Authenticated Requests

Objects (files, directories, buckets…) in the Object Store require you to be authenticated and authorized in order to be able to read or write or even get a listing of what’s in your Object Store.

But…what if you had a FILE that you wanted to make available to ANYONE who had its address? The Oracle Cloud and the Object Store allows you to create a ‘pre-authenticated request’ – that, everything you need to access the resource is included in the URI for said resource.

Loading the data

I have an EXISTING table :

CREATE TABLE CHANNELS
   (channel_id CHAR(1),
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20)
   );

I have my file in the Object Store:

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
J,thatJeffSmith,Direct

I need to create my pre-authenticated request…and copy that generated URL, then feed that to a very simplified call to DBMS_CLOUD.COPY_DATA:

BEGIN
  DBMS_CLOUD.COPY_DATA(
     table_name =>'CHANNELS',
     file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/p/b/something/o/channels.txt',
     format => json_object('delimiter' VALUE ',') );
END;
/

Don’t bother with that URI – you’ll need to upload and create your own. But that’s a VERY simple call to load data.

Here’s my TABLE. Here’s my FILE. Here’s how to PARSE the data in my file.

Cue SQL Developer Web

I can run my PL/SQL call directly in the SQL worksheet in SQL Developer Web. Just login as the user, and use the Execute as Script button (2nd green button in toolbar).

USER_LOAD_OPERATIONS

Your schema has a VIEW that tracks all data load operations you’ve attempted with the DBMS_CLOUD package (Docs).

If we query our new table and our view, we can see what we’ve got going on –

Takeaways

• if you’re going to be using Oracle Autonomous – get comfortable with the DBMS_CLOUD package
• the USER/DBA_LOAD_OPERATIONS views are handy for tracking what’s you’ve been doing
• you can use pre-authenticated requests to make access to your files in the object store – PUBLIC

We (the Database Tools Team) have built other interfaces to take advantage of the Object Store and DBMS_CLOUD. And we continue to build more!

Some related articles.