How to Import CSV Into Oracle Database

Oracle's own documentation covers SQL*Loader, external tables, the Data Import Wizard inside SQL Developer, and DBMS_CLOUD.COPY_DATA on Autonomous Database. SQL*Loader runs from a machine that can reach both the data file and the database. An external table wants a directory object and a grant on it. The wizard runs from a desktop holding a connection. DBMS_CLOUD.COPY_DATA wants the file in cloud object storage or in a database directory first. Each one starts from a seat that holds database credentials. Your customer sits outside that seat, holding an asset register out of the system their plant is leaving.

Their export arrives looking like this.

1Anlagennummer;Bezeichnung;Technischer Platz;Kritikalität;Inbetriebnahme;Wiederbeschaffungswert2P-1042;Wälzlagerprüfstand Süd Halle 3 (Umbau);WERK1-H3-MECH;A;14.03.2019;128.500,003P-1043;Förderband Süd;WERK1-H2-FOERD;kritisch;02.11.2021;64.200,004P-1044;Kühlmittelpumpe;WERK1-H3-MECH;B;07.09.2020;12.750,005P-1042;Wälzlagerprüfstand Süd Halle 3;WERK1-H3-MECH;A;14.03.2019;128.500,006P-1051;Späneförderer;WERK1-H1-SPAN;C;30.06.2018;⋮480 rows not shown487P-1527;Hydraulikaggregat Nord;WERK1-H1-HYDR;B;18.05.2022;41.900,00

The plant's old system wrote six German headers and put semicolons between them. Anlagennummer is the asset tag and Technischer Platz is the functional location. Two rows carry the same tag, P-1042, with two different descriptions. The criticality column mixes A, B and C with the word kritisch. Dates run day first with dots, prices carry a comma for the decimal point, and one price is missing.

The maintenance planner opens your app and hands the register to the importer. Updog Importer matches the German headers to your fields, puts every value in front of them, and checks each one. Your onComplete handler receives the rows. The handler cuts them into chunks and sends each chunk to a route you own. The route holds the Oracle credentials and runs one MERGE per record through executeMany. Oracle writes the table.

No Updog server sits anywhere in that chain.

The table and its widths

Oracle stores a nonquoted identifier in uppercase, whatever case you typed. Names run to 128 bytes once COMPATIBLE is 12.2 or higher, and to 30 bytes below that, which is why an older plant schema is full of abbreviations.

create table maint.asset_register (
  asset_id        number generated always as identity primary key,
  plant_id        number        not null,
  asset_tag       varchar2(20)  not null,
  description     varchar2(40)  not null,
  func_loc        varchar2(30)  not null,
  crit_cd         varchar2(1)   not null,
  commissioned_on date,
  repl_value      number(12, 2),
  updated_at      timestamp default systimestamp not null,
  constraint uq_asset_plant_tag unique (plant_id, asset_tag),
  constraint ck_asset_crit check (crit_cd in ('A', 'B', 'C'))
);

varchar2(40) on the description column holds forty bytes while NLS_LENGTH_SEMANTICS is BYTE. That is where Oracle sets it and where Oracle recommends leaving it. On an AL32UTF8 database each of those German umlauts takes two bytes, so Wälzlagerprüfstand Süd Halle 3 (Umbau) runs 38 characters and 41 bytes. A length check written in JavaScript counts 38 and waves it through. Oracle answers ORA-12899, value too large for column, and reports both widths in bytes for a column with byte semantics.

The unique constraint on plant_id and asset_tag decides which rows merge and which arrive new. Both columns are made of digits and short codes, so the width question never reaches them. The description is where it lands, and the browser is where it gets answered.

The columns the person sees

The columns array carries asset_register in the words the planner reads. Each entry holds a title for the header, an editor that decides how a cell gets typed, and validators that mark failures.

import type { CellValidator, DataEditorColumn } from "@updog/data-editor";

const CRITICALITY = ["A", "B", "C"];
const encoder = new TextEncoder();

const maxBytes = (limit: number): CellValidator => {
  return (value) => {
    const size = encoder.encode(String(value ?? "")).length;
    if (size <= limit) return null;
    return {
      level: "error",
      message: "Too long for Oracle by " + (size - limit) + " bytes",
    };
  };
};

export const columns: DataEditorColumn[] = [
  {
    id: "assetTag",
    title: "Asset tag",
    size: 120,
    transformer: (value) => String(value).trim(),
    validators: [
      { type: "required" },
      { type: "function", fn: maxBytes(20) },
      { type: "unique" },
    ],
  },
  {
    id: "description",
    title: "Description",
    size: 280,
    validators: [
      { type: "required" },
      { type: "function", fn: maxBytes(40) },
    ],
  },
  {
    id: "funcLoc",
    title: "Functional location",
    size: 180,
    validators: [
      { type: "required" },
      { type: "function", fn: maxBytes(30) },
    ],
  },
  {
    id: "criticality",
    title: "Criticality",
    size: 120,
    editor: { type: "select", options: CRITICALITY, enableCustomValue: false },
    validators: [{ type: "oneOf", values: CRITICALITY }],
  },
  {
    id: "commissionedOn",
    title: "Commissioned on",
    size: 160,
    editor: { type: "date" },
  },
  {
    id: "replacementValue",
    title: "Replacement value",
    size: 160,
    editor: { type: "number" },
    validators: [
      { type: "number", min: 0, max: 9_999_999_999.99, decimalPlaces: 2 },
    ],
  },
];

maxBytes is where Oracle's width reaches the browser. A function validator receives the cell value and returns a ValidationError or null, so TextEncoder measures the same UTF-8 bytes the column counts. Every limit in that file comes off a varchar2 declaration and nowhere else. { type: "unique" } on the asset tag catches the repeated P-1042, and it runs once every other validator on the column passes, so a cell holding an invalid value reports that error first. The select editor with enableCustomValue off holds criticality to three options and sends everything else to the value matching step.

The synonyms prop hands matching the German words your customers already use, and the mount below puts every prop in one place.

<DataEditor<Asset>
  apiKey="your-license-key"
  variant="uploader"
  open={open}
  onClose={closeImporter}
  columns={columns}
  primaryKey="assetTag"
  synonyms={{
    columns: {
      assetTag: ["anlagennummer", "equipment no"],
      description: ["bezeichnung", "benennung"],
      criticality: ["kritikalität", "abc kennzeichen"],
    },
    values: { A: ["kritisch"], B: ["wichtig"], C: ["gering"] },
  }}
  onComplete={onComplete}
/>

Anlagennummer, Bezeichnung and Kritikalität each score too low against an English field title, so they reach their column through synonyms. Technischer Platz reaches nothing at all, so the planner maps it by hand, and that pair comes back on the result as learnedSynonyms, ready to store and feed back next time. How to remember CSV import mappings between uploads walks that pair from the grid into the next register. kritisch reaches option A the same way, from synonyms.values.

Date order is one verdict for the whole file. The first value carrying a part above 12 settles it, and 14.03.2019 does that on the opening row, so the column reads day first and every date normalises to 2019-03-14. A price carrying both separators settles the number format on its own, because the last separator is the decimal point, and 128.500,00 reaches your handler as 128500.00. Common CSV import errors walks the rest of that list.

primaryKey decides how an imported row meets a row already in the grid. Inside one import the plant is fixed, so the asset tag alone identifies an asset on the browser side. For the install and the modal wiring underneath this snippet, see how to import a CSV file into a React app.

Every snippet here is React. The web component build takes the same props, so a Vue, Angular or Svelte front end declares the same columns and the same handler.

The chunked post to your own route

The planner presses submit, and Updog Importer hands your handler every asset row grouped by source, carrying isNew, isChanged, isDeleted and isValid. Rows nobody touched stay out. A planner can drop three register exports in one import, and each file lands as its own source entry, so the handler flattens before it slices.

import type { DataEditorResult } from "@updog/data-editor";

const CHUNK_SIZE = 400;

const onComplete = useCallback(async (result: DataEditorResult<Asset>) => {
  const rows = result.sources
    .flatMap((source) => source.rows)
    .filter((entry) => entry.isValid && !entry.isDeleted)
    .map((entry) => entry.row);

  for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
    const response = await fetch("/api/assets/import", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ rows: rows.slice(start, start + CHUNK_SIZE) }),
    });

    if (!response.ok) {
      const failure = await response.json();
      throw new Error(failure.message);
    }
  }
}, []);

Four hundred comes from two numbers you can work out before the first chunk leaves. The first is the bind buffer. bindDefs declares a maxSize in bytes for every string bind, the driver allocates that many bytes for each value it processes, and 20 plus 40 plus 30 plus 1 gives 91 bytes a record. Four hundred records allocate about 36KB, whatever the values themselves hold.

The second is the body your own route accepts. The JSON parser Express bundles defaults to a 100kb limit and answers a larger body with 413 and entity.too.large.

{"assetTag":"P-1042","description":"Wälzlagerprüfstand Süd Halle 3","funcLoc":"WERK1-H3-MECH","criticality":"A","commissionedOn":"2019-03-14","replacementValue":"128500.00"}

One asset row of these six fields serializes to 176 bytes, so four hundred of them reach roughly 69KB. Run that division against your own widest row before raising the number. The driver's own documentation names no row ceiling for executeMany. It names the failure instead, DPI-1015: array size is too large, with the instruction to call the method again over subsets of the data.

A failed chunk has to throw. Updog holds the editor open only while your handler runs, and empties it the moment the promise resolves. A handler that catches its own failure and returns reads as success, and the asset rows leave the screen unsaved. A spinner covers the confirm dialog until the last chunk answers, which is the other reason chunks stay small. Throwing leaves every row, every mapping and every correction on the screen, so the planner submits again on data still in front of them.

Copy anything off the result you plan to keep while the handler is still running. The editor releases its rows, its sources, its history and its learned synonyms as soon as the promise resolves.

The isValid filter in that snippet costs this register a row. A failed validator marks the cell and lets the person submit anyway, so an over-wide description reaches your handler and the filter leaves it behind. Pass blockSubmitOnError to the editor and submit stays locked until every row passes.

The write from your own server

One statement carries the whole chunk, run once per record.

merge into maint.asset_register t
using (select :plantId as plant_id, :assetTag as asset_tag from dual) s
   on (t.plant_id = s.plant_id and t.asset_tag = s.asset_tag)
 when matched then
   update set t.description     = :description,
              t.func_loc        = :funcLoc,
              t.crit_cd         = :critCd,
              t.commissioned_on = :commissionedOn,
              t.repl_value      = :replValue,
              t.updated_at      = systimestamp
 when not matched then
   insert (plant_id, asset_tag, description, func_loc,
           crit_cd, commissioned_on, repl_value)
   values (:plantId, :assetTag, :description, :funcLoc,
           :critCd, :commissionedOn, :replValue)

MERGE reads one row out of dual for each record, matches it against the unique constraint, and takes the update branch or the insert branch. plant_id and asset_tag stay out of the when matched half, since Oracle refuses to update a column referenced in the on condition. The statement wants INSERT and UPDATE on the target table and SELECT on the source.

The browser goes no further than your route, and the Oracle credentials wait on the other side of it.

import express from "express";
import oracledb from "oracledb";

const app = express();
app.use(express.json());

await oracledb.createPool({
  user: process.env.ORACLE_USER,
  password: process.env.ORACLE_PASSWORD,
  connectString: process.env.ORACLE_CONNECT_STRING,
  poolMin: 2,
  poolMax: 10,
});

const bindDefs = {
  plantId: { type: oracledb.NUMBER },
  assetTag: { type: oracledb.STRING, maxSize: 20 },
  description: { type: oracledb.STRING, maxSize: 40 },
  funcLoc: { type: oracledb.STRING, maxSize: 30 },
  critCd: { type: oracledb.STRING, maxSize: 1 },
  commissionedOn: { type: oracledb.DB_TYPE_DATE },
  replValue: { type: oracledb.NUMBER },
};

const toOracleDate = (iso: string) => {
  if (!iso) return null;
  const [year, month, day] = iso.split("-").map(Number);
  return new Date(year, month - 1, day);
};

app.post("/api/assets/import", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });

  const { rows } = request.body as { rows: Asset[] };
  const binds = rows.map((row) => ({
    plantId: session.plantId,
    assetTag: row.assetTag,
    description: row.description,
    funcLoc: row.funcLoc,
    critCd: row.criticality,
    commissionedOn: toOracleDate(row.commissionedOn),
    replValue: row.replacementValue === "" ? null : Number(row.replacementValue),
  }));

  const connection = await oracledb.getConnection();
  try {
    const result = await connection.executeMany(MERGE_ASSET, binds, {
      autoCommit: false,
      batchErrors: true,
      bindDefs,
    });

    if (result.batchErrors?.length) {
      await connection.rollback();
      return response.status(409).json({
        message: "Oracle rejected part of this batch",
        failures: result.batchErrors.map((error) => ({
          row: error.offset,
          code: error.errorNum,
          message: error.message,
        })),
      });
    }

    await connection.commit();
    response.json({ written: result.rowsAffected });
  } finally {
    await connection.close();
  }
});

MERGE_ASSET holds the statement above as a string. node-oracledb runs in Thin mode by default, connects to Oracle Database 12.1 or later straight from Node, and needs no Oracle Client libraries. getVerifiedSession() stands in for your own server-side authentication check. plant_id comes off that session and never off the request body, because a planner who can post rows can also post a different plant id.

bindDefs names a type and a width for every bind, and those widths are the ones in the table. Leaving it out makes the driver scan every record to find the longest value, which is work the browser already did. oracledb.DB_TYPE_DATE is the Oracle DATE type. The constant named oracledb.DATE maps to TIMESTAMP WITH LOCAL TIME ZONE, so the two constants stand for two different column types. toOracleDate splits the ISO date the browser produced and builds the value out of its three parts.

What a failed chunk hands back

With batchErrors off, executeMany stops at the first error and the whole call rejects. With it on, every valid record is processed and the failures come back as an array on the result.

[
  { Error: ORA-00001: unique constraint (MAINT.UQ_ASSET_PLANT_TAG) violated
    errorNum: 1, offset: 12 },
  { Error: ORA-12899: value too large for column
    "MAINT"."ASSET_REGISTER"."DESCRIPTION" (actual: 41, maximum: 40)
    errorNum: 12899, offset: 37 }
]

Each entry carries an offset, the 0-based index of the record inside the binds array. Add the offset to the start of the chunk and you have the row the planner is looking at. errorNum is Oracle's own number. ORA-00001 is a unique constraint violated. ORA-12899 is a value too large for a column. ORA-01438 is a number carrying more digits than the precision allows.

The transaction stays open. batchErrors starts one and leaves it uncommitted even when autoCommit is true, so your route examines the errors and then commits or rolls back. The route above rolls back and returns the offsets, which keeps a chunk whole. Committing the survivors is the other choice, and it costs the planner a partial import to reconcile.

One class of failure never reaches that array. A value longer than the maxSize you declared throws out of executeMany itself and takes the whole chunk with it, and no transaction is created. That failure is what the byte validator in the browser exists to prevent.

A repeated asset tag inside one chunk behaves differently again. executeMany runs the statement once per record, so the second MERGE finds the row the first one inserted and updates it. No error is raised, and the later row wins. ORA-30926, where the operation attempted to update the same row twice, belongs to a MERGE whose source is a table or a query carrying two rows for one target key. The unique validator is what puts both rows in front of the planner while they can still choose.

Oracle's own import paths

Updog Importer integrates with nobody. There is no Oracle connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the route between it and Oracle is yours to write.

Oracle already ships an import for the other case. SQL*Loader loads external files into database tables, driven by a control file, with an express mode for simple data types. An external table reads the file in place and needs a directory object, plus READ on that directory granted to the user directly. SQL Developer's Data Import Wizard offers a plain insert, a generated insert script, an external table, a staging external table and a SQL*Loader run, and it sizes the columns of a new table from the rows it previewed. On Autonomous Database, DBMS_CLOUD.COPY_DATA loads into a table that already exists, from cloud object storage with a credential stored in the database, or from a directory the loading user can read.

Every one of those is the shorter path when the file belongs to the developer. Everything above exists for the asset register that belongs to a customer, arriving through a browser, in a session your app issued. Client-side and server-side CSV import lays out which of the two a job wants.

The finished chain

The plant's file never left the planner's laptop. The rows travelled from your own front end to your own route, and from there into Oracle, and the only party added to the chain is yourself. The widths in the schema became the widths in the bind, and the description that would have overflowed was flagged while somebody could still fix it.

If the validator counts the same bytes the column counts, the rows the planner cleaned in the browser reach Oracle whole.