Loading company data into Oracle Autonomous Database

This guide loads company data from bulk files into Oracle Autonomous Database with the DBMS_CLOUD package: files in an object store, one stored credential, a table that fits the file, a load you can audit and queries that open the JSON columns. Fokals files are the example because their layout is documented, and the same steps load any CSV or JSON file. Oracle's documentation now calls the service Oracle Autonomous AI Database, a rename its release notes date to 14 October 2025. This guide keeps the name in its title for the same service and follows Oracle's documentation for loading data in the Serverless deployment, read on 4 October 2026.

What you load, and from where

Fokals delivers by REST API and as bulk files. The CSV files are UTF-8 with one header row, and lists and objects are JSON in a single cell. The export endpoints return CSV, JSON or JSON Lines page by page for the period you name, and a packaged export of files comes with a manifest that names its period, label versions and licence. Fokals is delivered direct, and you load the files with DBMS_CLOUD from your own object store. The delivery page describes the formats. The examples use two datasets of the hiring data, Hiring Activity (company_hiring_daily) and Job Postings (job_postings), and the data dictionary defines every column named below.

DBMS_CLOUD reads from Oracle Cloud Infrastructure Object Storage, Azure Blob Storage or Azure Data Lake Storage, Amazon S3, S3-compatible stores such as Wasabi, GitHub repositories and Google Cloud Storage. Every URI must use HTTPS. On Oracle Cloud Infrastructure the files must sit in a bucket of the Object Storage tier, because Autonomous Database does not support the Archive Storage tier.

Pick a way to load

Oracle documents several routes, and four of them suit bulk company files.

• DBMS_CLOUD.COPY_DATA copies rows into a table you have created. Use it for tables you will join and query often.
• DBMS_CLOUD.CREATE_EXTERNAL_TABLE lets you query the file where it lies. Its credential is a table-level property, so its files must sit on one object store. Use it to profile a sample file before you load anything.
• The Data Studio Load tool runs the same package. Oracle says it also analyses the source, creates the table definition and performs validation checks, which suits a first load.
• A load pipeline runs COPY_DATA on a schedule for files that keep arriving, and is the route for daily files.

Store a credential once

A credential is stored once, encrypted, and reused by name, as Oracle's page on credentials and COPY_DATA describes. For Oracle Cloud Infrastructure Object Storage the username is your user name, with its identity domain unless it is the default, and the password is an auth token. For Amazon S3 the username is your AWS access key ID and the password is your user access key. Oracle also documents signing-key credentials, and resource principals and Amazon Resource Names that need no stored credential. The statement set define off stops SQL*Plus and SQL Developer from reading an ampersand in a token as a substitution variable.

set define off
begin
  dbms_cloud.create_credential(
    credential_name => 'FOKALS_BUCKET',
    username        => 'loader@example.com',
    password        => '<auth token>'
  );
end;
/

-- What can the database see in the folder?
select object_name, bytes
from   dbms_cloud.list_objects('FOKALS_BUCKET',
         'https://<namespace>.objectstorage.<region>.oci.customer-oci.com/n/<namespace>/b/fokals/o/hiring_daily/');

LIST_OBJECTS returns the object names, sizes and checksums, so compare it with the files you meant to send before you load. The URI above is the form Oracle's page on URI formats recommends for accounts in OC1, its commercial cloud. Elsewhere the form is https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/<file>.

Create a table that fits the file

COPY_DATA loads into a table that already exists, and when field_list is empty it takes the fields and their types from that table. Give the table one column for every field in the file, in the order of the header row. The format option detectfieldorder maps fields to columns by the names in the first row instead, with restrictions Oracle lists: no quoted names, no spaces between names, and as many table columns as file fields. The data dictionary says every file carries company_id and company, and the identifiers ticker, exchange, mic, isin, lei and figi where the company or its parent is listed, so read the header of your file before you write the table.

The sketch below stages Hiring Activity with its JSON cells as text. It lists the columns in dictionary order, which is not necessarily file order. Autonomous Database sets MAX_STRING_SIZE to EXTENDED by default, so a VARCHAR2 column can hold up to 32,767 bytes, according to Oracle's page on data types. Size each JSON column from the longest cell in a sample. The files are UTF-8, and the option characterset defaults to the database character set, so name it if yours differs.

create table hiring_daily_stage (
  company_id varchar2(200), company varchar2(500), ticker varchar2(50),
  exchange varchar2(20), mic varchar2(10), isin varchar2(12), lei varchar2(20),
  figi varchar2(20), day date, reconstructed varchar2(10),
  open_postings number, new_postings number, closed_postings number,
  by_function varchar2(32767), by_seniority varchar2(32767),
  by_country varchar2(32767), by_work_mode varchar2(32767),
  ai_postings number, paid_media_postings number,
  tech_mentions varchar2(32767), median_salary_usd number
);

Load with COPY_DATA

begin
  dbms_cloud.copy_data(
    table_name      => 'HIRING_DAILY_STAGE',
    credential_name => 'FOKALS_BUCKET',
    file_uri_list   => 'https://<namespace>.objectstorage.<region>.oci.customer-oci.com/n/<namespace>/b/fokals/o/hiring_daily/2026-10-03*.csv',
    format          => json_object(
                         'type'         value 'csv',
                         'delimiter'    value ',',
                         'skipheaders'  value '1',
                         'dateformat'   value 'YYYY-MM-DD',
                         'rejectlimit'  value '0',
                         'logretention' value 14)
  );
end;
/

Each option answers a documented default or limit.

• delimiter is documented with the pipe character as its default, so state the comma. skipheaders skips the header row.
• rejectlimit defaults to 0, so the first rejected row ends the load. Rejected rows go to a bad-file table you can query.
• dateformat takes the mask of the ISO dates in day. Timestamps such as first_seen_at are ISO 8601, which puts a T between date and time, and the formats Oracle lists for AUTO have none, so give an explicit mask and check it against a sample row.
• Leave truncatecol off. It cuts a field that is too long instead of rejecting the row, and a cut JSON cell is no longer JSON.
• The wildcards * and ? in file_uri_list select several files, so one call can load a day's files.

Free text such as the excerpt of a Company News item (company_news) can contain line breaks and quotes. Oracle's example of a CSV file with a line break uses the type csv with embedded on an external table. External tables take regular expressions in a URI only when regexuri is true, not wildcards. The format options page lists every option.

Check the load

Every DBMS_CLOUD load is logged in user_load_operations, which names the log table and the bad-file table of each run, as Oracle's page on monitoring loads describes.

select table_name, status, start_time, logfile_table, badfile_table
from   user_load_operations
where  type = 'COPY'
order  by start_time desc;

select * from copy$1_bad;   -- rows rejected by the first operation
select min(day), max(day), count(*) from hiring_daily_stage;

Log and bad-file tables are kept for two days unless logretention says otherwise. Compare the row count with the number of data rows the file holds, counted by a CSV reader and not by lines, because a quoted field can span lines. DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE checks the files of an external table against the format options and puts the rows that fail them in a bad-file table, without loading anything.

Open the JSON columns

Oracle's JSON Developer's Guide says textual JSON can sit in VARCHAR2, CLOB or BLOB, and that the JSON data type avoids parsing the text again on every query. JSON text inserted into a JSON column is parsed implicitly, and the constructor json(...) does it explicitly. The type needs the compatible parameter at 20 or higher.

create table company_hiring_daily as
select s.company_id, s.company, s.ticker, s.exchange, s.mic, s.isin, s.lei, s.figi,
       s.day, s.reconstructed, s.open_postings, s.new_postings, s.closed_postings,
       json(s.by_function) as by_function, json(s.by_seniority) as by_seniority,
       json(s.by_country) as by_country, json(s.by_work_mode) as by_work_mode,
       s.ai_postings, s.paid_media_postings, json(s.tech_mentions) as tech_mentions,
       s.median_salary_usd
from   hiring_daily_stage s;

-- Open sales roles per company on one closed day, joined to your own security master
select m.internal_id, h.day, h.open_postings,
       json_value(h.by_function, '$.sales' returning number) as open_sales_roles
from   company_hiring_daily h
left join security_master m on m.isin = h.isin
where  h.day = date '2026-10-03';

-- One row per location of a posting; locations is a JSON array in job_postings
select p.posting_id, l.city, l.region, l.country
from   job_postings p,
       json_table(p.locations, '$[*]'
         columns (city    varchar2(200) path '$.city',
                  region  varchar2(200) path '$.region',
                  country varchar2(2)   path '$.country')) l;

The JSON_TABLE function is a row source in the FROM clause: it makes one row for each JSON value its path selects. A join on isin leaves out every company with no listing, its own or its parent's, private companies among them, because their identifier columns are empty. Join those on company_id or domain, and count the rows with no match to get your match rate. The dictionary adds that an ISIN or LEI whose check digit fails is never stored.

JSON Lines

Oracle documents two paths for line-delimited JSON. DBMS_CLOUD.COPY_COLLECTION loads each line of a file as one document in a collection, with 'recorddelimiter' value '''\n''' in Oracle's example. COPY_DATA with 'type' value 'json' and a columnpath array, one JSON path for each table column, puts fields into relational columns, and field_list is then ignored. Oracle's example for the second path does not show the layout of its source file, so run the first call on a sample of a few hundred lines. The default maxdocsize is 1 MB.

Daily increments

Daily and weekly Fokals tables are written once, after the period closes, so a file for a closed day is complete and later files add days without changing earlier ones. That suits a load pipeline, which looks for new files at a location at an interval set in minutes, loads each file once with COPY_DATA, marks a file that fails as FAILED and carries on with the others. Oracle says a file whose content changes after loading is not loaded again, so give each day's file its own name.

begin
  dbms_cloud_pipeline.create_pipeline(
    pipeline_name => 'HIRING_DAILY_LOAD',
    pipeline_type => 'LOAD',
    description   => 'Fokals company_hiring_daily files into the stage table');
  dbms_cloud_pipeline.set_attribute(
    pipeline_name => 'HIRING_DAILY_LOAD',
    attributes    => json_object(
      'credential_name' value 'FOKALS_BUCKET',
      'location'        value 'https://<namespace>.objectstorage.<region>.oci.customer-oci.com/n/<namespace>/b/fokals/o/hiring_daily/*.csv',
      'table_name'      value 'HIRING_DAILY_STAGE',
      'format'          value '{"type":"csv", "delimiter":",", "header":true, "dateformat":"YYYY-MM-DD"}',
      'priority'        value 'MEDIUM',
      'interval'        value '60'));
  dbms_cloud_pipeline.run_pipeline_once(pipeline_name => 'HIRING_DAILY_LOAD');  -- test run
end;
/

insert into company_hiring_daily
select ...   -- the select list of the create table statement above
from   hiring_daily_stage s
where  not exists (select 1 from company_hiring_daily t
                   where t.company_id = s.company_id and t.day = s.day);

Start the pipeline with START_PIPELINE once the test run is clean. RESET_PIPELINE clears the record of loaded files and, with purge_data true, truncates the target table, so use it only where you can reload. The insert moves new stage rows into the typed table and does nothing on a rerun. Keep the reconstructed flag: a period written more than seven days after it closed carries reconstructed=true, and a back-test can then leave it out. Event feeds that run oldest first from a time you set can be written to one file for each pull, named by the last cursor, and loaded the same way. The guide to incremental sync of company data covers the bookmark.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.

What this page says about the products it names was checked against their public documentation on 4 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.