Local File

/u01/data/employees.csv

EMP_ID,EMP_NAME,SALARY

1001,Mike,70000

1002,Larry,60000

1003,Steve,55000

CREATE TABLE emp_ext

(

  emp_id    NUMBER,

  emp_name  VARCHAR2(100),

  salary    NUMBER

)

ORGANIZATION EXTERNAL

(

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY ext_dir

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE

    SKIP 1

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (

      emp_id,

      emp_name,

      salary

    )

  )

 LOCATION ('employees.csv')

)

REJECT LIMIT UNLIMITED;

SELECT * FROM emp_ext;

Cloud File

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => 'OCI_CRED',

    username        => 'my.username@company.com',

    password        => 'my_auth_token');

END;

/

employees.csv

EMP_ID,EMP_NAME,SALARY

1001,Mike,70000

1002,Larry,60000

1003,Steve,55000

BEGIN

  DBMS_CLOUD.CREATE_EXTERNAL_TABLE

  (

    table_name      => 'EMP_EXT',

    credential_name => 'OCI_CRED',

    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/mynamespace/b/mybucket/o/employees.csv',

    column_list     => 'EMP_ID NUMBER,

                        EMP_NAME VARCHAR2(100),

                        SALARY   NUMBER',

    format          => JSON_OBJECT('type' VALUE 'csv',

                                   'skipheaders' VALUE '1')

  );

END;

/

SELECT * FROM emp_ext;

Bibliography