Offloading Oracle Data to Object Storage and Loading as External Table in an Autonomous Database | Part 3: Oracle OCI Object Storage


Following up the posts here and here, we are explaining today how to make same thing using Oracle OCI Object Storage. The procedure and steps are quite similar so, let’s go and try!

Steps

Create a bucket

Upload file to bucket

Create an Auth Token for your user as follows:

In a sql editor, execute the following:

BEGIN   DBMS_CLOUD.CREATE_CREDENTIAL(     
      credential_name => 'ocirfidcredential',     
      username => 'alext..o@g......l.com',     
      password => 'laralilaralá'   
  ); END;
  /

NOTE: If your user is federated in IdCS (not local) then prepend oracleidentitycloudservice/ to the username as follows:

BEGIN   DBMS_CLOUD.CREATE_CREDENTIAL(     
      credential_name => 'ocirfidcredential',     
      username => 'oracleidentitycloudservice/jvr.mgt@rcl.com',     
      password => 'borriquitocomotu,tururú'   
  );
 END;
 /

Construct a url with the following pattern:

https://swiftobjectstorage.region.oraclecloud.com/v1/object_storage_namespace/bucket/filename

Or get the url from here:

Execute the following putting the appropriate url of your object:

BEGIN
     DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name =>'ocirfid201901',
      credential_name =>'ocirfidcredential',
      file_uri_list =>'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/...../b/rfid/o/rfid201901.json',
      format => json_object('delimiter' value '|', 'truncatecol' value 'True'),
      column_list => 'jsoncol varchar2(4000)'
   );
  END;
/
  EXECUTE DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ('ocirfid201901')
/

If the validation ends up OK, then your table is ready for utilisation, let’s see.

set timing on
/
select count() from ocirfid201901
/
select count() from azurfid201901
/
select count(*) from s3rfid201901
/
Test #1
Test #2
Test #3

That’s all folks! Hope it helps 🙂

See also this and this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.