Convert JSON to Oracle
Drop your JSON file to get a Oracle script with CREATE TABLE and INSERT statements. Each column gets a type, and every fix is listed with its row and column.
4 rows converted, 2 fixes, none rejected
| Row | ||||||
|---|---|---|---|---|---|---|
| 2 | 000481 | Peña Muñoz | 2024-02-03T10:15:00Z | 1234.5 | true | |
| 3 | 000482 | José Ángel Ruiz | 2024-02-15T16:40:00Z | 99.9 | false | Entrega en la tarde |
| 4 | 000483 | Zoë Ortíz | 2024-02-29T09:05:00Z | 12 | true | |
| 5 | 000484 | Ñuño Castañeda | 2024-03-11T13:30:00Z | 980.75 | sí | Factura con "RFC" |
-- Written by IntoSQL for Oracle.
SET DEFINE OFF
CREATE TABLE pedidos_2024 (
pedido VARCHAR2(8 CHAR) NOT NULL,
cliente VARCHAR2(16 CHAR) NOT NULL,
fecha TIMESTAMP WITH TIME ZONE NOT NULL,
total NUMBER(6,2) NOT NULL,
pagado NUMBER(1) NOT NULL,
notas VARCHAR2(32 CHAR)
);
INSERT ALL
INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES ('000481', 'Peña Muñoz', TIMESTAMP '2024-02-03 10:15:00 +00:00', 1234.5, 1, NULL)
INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES ('000482', 'José Ángel Ruiz', TIMESTAMP '2024-02-15 16:40:00 +00:00', 99.9, 0, 'Entrega en la tarde')
INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES ('000483', 'Zoë Ortíz', TIMESTAMP '2024-02-29 09:05:00 +00:00', 12, 1, NULL)
INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES ('000484', 'Ñuño Castañeda', TIMESTAMP '2024-03-11 13:30:00 +00:00', 980.75, 1, 'Factura con "RFC"')
SELECT 1 FROM DUAL;
COMMIT;
Fixes
- Leading zeros kept in "pedido": 000481 stays 000481, stored as text.
- "pagado" holds yes and no words (sí), stored as boolean.
Your file is converted on this computer and never uploaded.
Other samples:
From JSON to Oracle, column by column
The sample above, pedidos_2024.json, as IntoSQL writes it for Oracle: what each column holds in the file, the type it gets and how its first value lands in the script.
| In the file | First value | Column | Oracle type | In the script |
|---|---|---|---|---|
| pedido | 000481 | pedido | VARCHAR2(8 CHAR) | '000481' |
| cliente | Peña Muñoz | cliente | VARCHAR2(16 CHAR) | 'Peña Muñoz' |
| fecha | 2024-02-03T10:15:00Z | fecha | TIMESTAMP WITH TIME ZONE | TIMESTAMP '2024-02-03 10:15:00 +00:00' |
| total | 1234.5 | total | NUMBER(6,2) | 1234.5 |
| pagado | true | pagado | NUMBER(1) | 1 |
| notas | - | notas | VARCHAR2(32 CHAR) | NULL |
Load the script into Oracle
The download is a plain .sql file. Run it with Oracle's own tools.
From a terminal
sqlplus user@//localhost/mydb @pedidos_2024.oracle.sqlThe script turns off substitution variables (SET DEFINE OFF), so an ampersand in your data stays an ampersand.
From a database tool
In SQL Developer, open the script and choose Run Script (F5).
What to know about JSON files and Oracle
What IntoSQL reads in JSON files
- The file can be an array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON).
- Each key becomes a column. Objects and arrays inside a value are kept as JSON text.
- Numbers keep their exact digits, and ISO 8601 dates become dates or timestamps.
What to know about Oracle
- Yes and no values become NUMBER(1), holding 1 or 0, which every Oracle version accepts.
- Text is VARCHAR2 with a length in characters; over 4000 bytes it becomes CLOB. Oracle stores empty text as NULL.
- Rows are inserted with INSERT ALL, and long values are split so SQL*Plus accepts them.
How each kind of value is stored in Oracle
| Value | Oracle |
|---|---|
| Yes and no | NUMBER(1) |
| Whole numbers | NUMBER(10) |
| Large whole numbers | NUMBER(19) |
| Decimals | NUMBER(p,s) |
| Floating-point numbers | BINARY_DOUBLE |
| Dates | DATE |
| Times of day | INTERVAL DAY(0) TO SECOND |
| Dates with a time | TIMESTAMP |
| Dates with a time and offset | TIMESTAMP WITH TIME ZONE |
| Text | VARCHAR2(n CHAR)CLOB |
Questions about JSON to Oracle
Not answered here? Write to us from the contact page and a person replies. Contact page
How do I convert JSON to Oracle?
Drop your file on the converter at the top of this page. It finds the header, reads every value and writes the Oracle script, listing each fix it made. Download the script and run it with SQL*Plus.
What shape does the JSON file need?
An array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON). Each key becomes a column.
Which Oracle types does it use?
Yes and no values become NUMBER(1), whole numbers NUMBER(10), decimals NUMBER(p,s), dates DATE, dates with a time TIMESTAMP and text VARCHAR2(n CHAR). Sizes come from the longest value in each column.
Does it keep leading zeros in Oracle?
Yes. A code such as 000481 stays text, so Oracle stores it as VARCHAR2(8 CHAR) and the script writes it as '000481'.
Can it load the data into Oracle without a script?
No. Download the script and run it with SQL*Plus. Loading without a script works for PostgreSQL, MySQL, MariaDB and SQLite, from the desktop app or the command line.
How big can the JSON file be?
NDJSON, one object per line, is read as it streams in, so files of several gigabytes work in the browser. A JSON document is read whole, so its limit is the memory the browser gives this page. The desktop app and the command line take files of any size.