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