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.

Warning: be VERY careful with these.

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).

That’s very simple – just imagine some SERIOUS files, not a 6 or 7 line CSV.

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 –

I’m not very smart, it took me 2 tries to get it right.

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.

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.

Write A Comment

RSS
Follow by Email
LinkedIn
Share