Saturday, July 11, 2020

Querying External Data with Autonomous Database

Below is an example of querying an externaltext file with a comma delimiter on OCI object storage using an Autonomous Database.  This example is from the documentation which had a couple of typos that are corrected here.

I am using an ADB on Dedicated Exadata Infrastructure.

channels.txt file:
1,Direct Sales,Direct
2,Tele Sales,Direct
3,Catalog,Indirect
4,Internet,Indirect
5,Partners,Others


BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'your OCI user name here',
    password => 'your token here'
  );
END;
/

select owner, credential_name, username, enabled from dba_credentials;

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'CHANNELS_EXT',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/youtenancynamehere/b/sampledata/o/channels.txt',
    format => json_object('delimiter' value ','),
    column_list => 'CHANNEL_ID NUMBER,
    CHANNEL_DESC VARCHAR2(20),
    CHANNEL_CLASS VARCHAR2(20)' );
END;
/

SELECT count(*) FROM channels_ext;
select * from channels_ext;
desc channels_ext;

No comments:

Post a Comment