SharePoint list to Oracle Database for an APEX report and a PL/SQL job

23 October 2026

Tags: Relational databasesDesktopCommand line

The maintenance planners at a manufacturing firm keep their asset register and their maintenance requests in SharePoint lists, because that is where the shop floor raises them. Finance and operations live in Oracle. Someone built an APEX page showing open requests by production line, and a PL/SQL job that totals downtime each night for the cost report. Both read a staging table that a hand-written loader fills from a CSV export. When the loader fails, nobody notices until the planners ask why last week’s breakdowns are missing.

That loader is the usual starting point when a SharePoint list has to reach Oracle Database. This post covers getting the list into Oracle as proper tables, what a DBA has to set up once, what the tables look like, and how an APEX report or a PL/SQL job reads them.

Why the CSV loader keeps breaking

The loader breaks for reasons that have nothing to do with Oracle. A planner adds a column to the list and the export gains a field the loader does not expect. A lookup column such as Asset exports as display text, so the PL/SQL job joins on a name that someone later corrects in the asset register, and the join stops matching. Person columns export as a name in one export and an email address in another. A request deleted in SharePoint stays in Oracle for ever, because a file of current rows says nothing about rows that have gone.

Size adds its own trouble. Exports and flows that read a list in one go struggle once a list passes the 5,000-item list view threshold, and a maintenance list on a busy site gets there in a year or two.

How do you get a SharePoint list into Oracle Database?

There are three routes people take, and each fits a different team.

An Azure Data Factory pipeline can copy from the SharePoint Online List connector into the Oracle connector as a sink. It signs in with a service principal and copies rows. The table design, the handling of deletes and the change of shape when someone adds a column are yours to build, so it works best for a team that already runs Data Factory and is happy to own that logic.

A PL/SQL job can call Microsoft Graph itself with UTL_HTTP. That needs a network access control list for the Graph host, a wallet holding the certificates for HTTPS, a token flow, paging through @odata.nextLink, and code to parse each column type. It keeps everything inside the database, at the cost of a fair amount of code to maintain.

SPSync replicates the list for you. It reads the list through Microsoft Graph, creates Oracle tables from the list’s columns, loads every item on the first run, and after that reads only what changed. It runs as a desktop app or a command-line tool on a machine you choose, and writes straight to your database. The rest of this post follows that route.

Setting up the two Oracle users

In Oracle a schema is a user account, and only a DBA can create one. SPSync cannot create its own schema the way it does on PostgreSQL or SQL Server, so the DBA does this once before the first sync.

SPSync writes to two users. One holds the replicated tables. The second, named after the first with _staging on the end, holds staging tables that each run loads into before it merges the changes across. The connecting user needs table privileges across both, and in Oracle those are ANY TABLE privileges rather than grants on one schema:

CREATE USER sync_app IDENTIFIED BY "choose-a-password";
GRANT CONNECT, RESOURCE TO sync_app;
ALTER USER sync_app QUOTA UNLIMITED ON USERS;

CREATE USER sync_app_staging IDENTIFIED BY "choose-another-password";
GRANT CONNECT, RESOURCE TO sync_app_staging;
ALTER USER sync_app_staging QUOTA UNLIMITED ON USERS;

GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE,
      INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO sync_app;

Those privileges reach every schema in the database, so a DBA will want to think about where this runs. Many teams give the replica its own pluggable database, or an instance that serves reporting, and expose the tables to production schemas through grants or a database link.

SPSync connects with python-oracledb in thin mode, so the machine running the sync needs no Oracle client. The connection string names the pluggable database as a service:

oracle+oracledb://sync_app:password@db-host:1521/?service_name=REPORTING

In the desktop app you add Oracle Database as a destination and paste that string. With the command-line tool the string goes in an environment variable, and the replication’s YAML config names the variable rather than holding the password:

destination:
  kind: oracle
  connection_string_env: SPSYNC_ORACLE_DSN
  dataset_name: sync_app

On the SharePoint side, you sign in with your own account or an app registration, and Sites.Selected can limit SPSync to the one site the lists live on.

What does a SharePoint list look like as Oracle tables?

With a table prefix of plant, the Maintenance Requests list becomes a table called plant_maintenance_requests, and the Asset Register becomes plant_asset_register. Column names come from the SharePoint internal names, lowercased and split at word boundaries, so DowntimeMinutes becomes downtime_minutes. SPSync creates the names unquoted, so Oracle stores them in upper case and you can query them in either case without quotes.

Longer text goes into CLOB columns and yes/no columns into NUMBER(1), since older Oracle releases have no Boolean type. The schema reference has the full mapping. When someone adds a column to the list, it appears as a new column on the next run. If someone changes a column’s type, the new values go into a _v2 copy of the column, so the PL/SQL job written against the old type keeps working.

Running the first sync and checking what arrived

Tick the lists in the desktop app, pick the Oracle destination, and run. The first run reads every item, paging through large lists past the 5,000-item threshold, and commits each table once its read is complete. Then check the counts against the lists:

SELECT status, COUNT(*) AS requests
FROM sync_app.plant_maintenance_requests
WHERE sp_deleted_at IS NULL
GROUP BY status
ORDER BY status;

If the totals match the list views in SharePoint, the copy is ready for the report. Switch on reporting views when you set up the replication and SPSync also creates one view per list, such as v_plant_maintenance_requests, which hides deleted rows and joins the person columns to sp_users so names appear without a join of your own.

How an APEX report or a PL/SQL job reads the replica

APEX runs its pages as a parsing schema, so grant that schema read access to the tables it needs, for example GRANT SELECT ON sync_app.plant_maintenance_requests TO maint_apex;, and the same for the asset register and sp_users. The open requests page then becomes an ordinary SQL query region:

SELECT a.title AS asset, a.line, r.title AS request, r.priority,
       u.display_name AS requested_by, r.sp_created
FROM sync_app.plant_maintenance_requests r
JOIN sync_app.plant_asset_register a ON a.sp_item_id = r.asset_id
LEFT JOIN sync_app.sp_users u ON u.id = r.requested_by_user_id
WHERE r.sp_deleted_at IS NULL
  AND a.sp_deleted_at IS NULL
  AND r.status <> 'Closed'
ORDER BY a.line, r.priority;

The join is on asset_id, the lookup’s item id, so correcting an asset’s name in SharePoint no longer breaks it. The nightly downtime job works the same way. It can sum downtime_minutes by asset and line, then insert the totals into the cost report’s own tables in the finance schema, with sp_modified telling it which requests changed since its last run.

Keep the replica tables read-only from your side. SPSync rewrites rows as SharePoint changes, so anything you add belongs in your own schema, joined to the replica by sp_item_id.

Keeping the Oracle copy current

After the first run, SPSync asks Graph only for the items that changed. The desktop app runs that every five minutes by default, and the command-line tool runs whenever your scheduler calls spsync run. An edited request is updated in place. A deleted one keeps its row with sp_deleted_at set, so the history in Oracle survives and the queries above leave it out. If you would rather the row went, the command-line config can switch a replication to hard deletes.

A reconcile also compares every item id with SharePoint, weekly by default and configurable per replication. It is the QA check when someone asks whether Oracle matches the list, and the step to run after an outage on your server or in Microsoft 365.

Can Copilot Studio or Power Apps read the Oracle copy?

Microsoft lists Oracle Database among the Power Platform connectors that Copilot Studio can use as real-time knowledge, a feature in preview when we checked. Copilot Studio indexes only table and column names, and each question runs against the database with the asking user’s own credentials. An agent pointed at the replica can answer questions about the maintenance list from Oracle rather than from SharePoint.

The Oracle Database connector is a premium connector, and it needs an on-premises data gateway to reach an on-premises database or one on an Azure virtual machine. It does not support tables with composite keys. The main list tables have a single key on sp_item_id, but the child tables use a composite key, so point the connector at the main tables or at the reporting views, which it reads as read-only tables.

Where another route fits better

If the list is small, changes rarely, and only one report needs it, a scheduled Data Factory copy may be all you need, and the Data Factory comparison sets out where each fits. If your team would rather not give any tool ANY TABLE privileges, put the replica in a database of its own and pull from it with a database link.

SPSync does not copy file contents, item permissions or version history. Library columns replicate as metadata rows, and the files themselves stay in SharePoint. The same lists can also go to other destinations, with one replication each, so the planners’ lists could feed Oracle for finance and PostgreSQL for a web app at the same time. If the database you need is missing from the destinations list, tell us.

The SharePoint to Oracle Database page covers names, types and keys in more detail, and the reporting and analytics use case shows the wider pattern. Oracle is part of the relational databases family on the licensing page and is sold after a call. Book a call and we will walk through it against your own schema.