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
/

That’s all folks! Hope it helps 🙂

See also this and this