Loading JSON Data

The usual ways to load data to a TimesTen database table work with tables with JSON type columns. All of the usual database APIs used to load or bulk load data, such as ODBC and JDBC, can be used for tables with JSON type columns. Textual JSON input is automatically converted to JSON type.

All available methods to bulk load data into a TimesTen database support tables with JSON type columns, such as:

• The ttBulkCp utility. This utility enables you to copy data between a TimesTen table and a ASCII file. ttBulkCp only supports loading JSON data into tables with JSON type columns from files generated by ttBulkCp. To improve performance, ttBulkCp copies and loads JSON type columns in hexadecimal format of Oracle Binary JSON. See Example 2-4.

• The ttMigrate utility. This utility enables you to save one or more tables with JSON type columns into a data file and use that data file to load the tables into a different TimesTen database. ttMigrate can be used to migrate tables to a database in a different TimesTen release. See Example 2-5.

• The createandloadfromoraquery command in the ttIsql utility. This command takes a TimesTen table name and a SELECT statement as input. Then, automatically creates the TimesTen table, runs the SELECT statement on the Oracle database, and loads the result set into the TimesTen table. See Example 2-6.

• The ttLoadFromOracle built-in procedure. This built in procedure takes an existing TimesTen table and SELECT statement as input. Then, runs the SELECT statement on the Oracle database and loads the result set into the TimesTen table. See Example 2-7.

See also:

• ttBulkCp in Oracle TimesTen In-Memory Database Reference

• ttMigrate in Oracle TimesTen In-Memory Database Reference

• createandloadfromoraquery in Oracle TimesTen In-Memory Database ttIsql User's Guide and Reference

• ttLoadFromOracle in Oracle TimesTen In-Memory Database Reference

Example 2-4 Loading JSON Data with the ttBulkCp Utility

This example uses the ttBulkCp utility to copy the pat.j_purchaseorder table in the database1 database into an ASCII file, j_purchaseorder.dump.

Note:

ttBulkCp copies the JSON data in the JSON type column, po_document, in hexadecimal format of Oracle Binary JSON.

% ttBulkCp -o database1 pat.j_purchaseorder j_purchaseorder.dump
10000/10000 rows copied

% vim j_purchaseorder.dump

##ttBulkCp
#
# PAT.J_PURCHASEORDER, 3 columns, dumped Tue May 13 18:51:20 2025
# columns:
#      1. ID           VARCHAR2(32 BYTE)
#      2. DATE_LOADED  TIMESTAMP(6)
#      3. PO_DOCUMENT  JSON
# end
#

"0",2025-05-13 18:45:27.000000,{ff4a5a0121061800d90230000209122 ... c109}
"1",2025-05-13 18:45:27.000000,{ff4a5a0121061800d90223000209122 ... c10a}
"2",2025-05-13 18:45:27.000000,{ff4a5a0121061800d90186000209122 ... c107}
...

Then, the example uses ttBulkCp to load the data in the j_purchaseorder.dump file into the terry.j_purchaseorder table in the database2 database.

% ttBulkCp -i database2 terry.j_purchaseorder j_purchaseorder.dump

j_purchaseorder.dump:
    10000 rows inserted
    10000 rows total

Example 2-5 Loading JSON Data with the ttMigrate Utility

This example uses the ttMigrage utility to copy the pat.j_purchaseorder table in the database1 database into a data file, j_purchaseorder.ttm.

% ttMigrate -c database1 j_purchaseorder.ttm pat.j_purchaseorder

Saving table PAT.J_PURCHASEORDER
  Saving rows...
.
  10000/10000 rows saved.
Table successfully saved.

Then, the example uses ttMigrate to create and load the pat.j_purchaseorder table in the j_purchaseorder.ttm file into the database2 database.

Note:

If the user does not exist in the target database, ttMigrate creates the user without a password and privileges and locks the user account.

% ttMigrate -r database2 j_purchaseorder.ttm pat.j_purchaseorder;

Creating new user PAT (disabled)
User successfully created.

Restoring table PAT.J_PURCHASEORDER
  Restoring rows...
.
  10000/10000 rows restored.
Table successfully restored.

The following users were created implicitly during restore:
  PAT
These users have no privileges, no passwords, and cannot log in.

Example 2-6 Loading JSON Data with the createandloadfromoraquery Command

This example uses the createandloadfromoraquery command of the ttIsql utility to create and load the j_purchaseorder table in the database1 TimesTen database from a query to the oracledb Oracle database. The third column of the queried table, po_document, contains JSON data.

Note:

The TimesTen database user must exist in the Oracle database and the user in the Oracle database must have privileges to SELECT the queried objects. You must provide the password for the user in the Oracle database when connecting to the TimesTen database for the createandloadfromoraquery command to work.

% ttisql -connstr "DSN=database1;UID=pat;PWD=password;OraclePWD=password"

Copyright (c) 1996, 2025, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=database1;UID=pat;PWD=********;OraclePWD=********";
Connection successful: DSN=database1;UID=pat;DataStore=/timesten/sample_db/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogFileSize=64;
DRIVER=/timesten/install/lib/libtten.so;LogBufMB=64;PermSize=128;TempSize=128;
OracleNetServiceName=oracledb;
(Default setting AutoCommit=1)
Command> createandloadfromoraquery j_purchaseorder 2 SELECT * FROM pat.j_purchaseorder;
Mapping query to this table:
    CREATE TABLE "PAT"."J_PURCHASEORDER" ( 
    "ID" varchar2(32 byte) NOT NULL,
    "DATE_LOADED" timestamp(6),
    "PO_DOCUMENT" json
     )
    
Table j_purchaseorder created
< 10000, 0, 0, Started=2025-05-07 18:43:46 (GMT); Ended=2025-05-07 18:43:46 (GMT); 
Load successfully completed; OracleSCN=undefined; Rows Loaded=10000; Errors=0 >
1 row found.

Example 2-7 Loading JSON Data with the ttLoadFromOracle Built-In Procedure

This example uses the ttLoadFromOracle built-in procedure to load the pat.j_purchaseorder table in the database1 TimesTen database from a query to the oracledb Oracle database. The third column of the queried table, po_document, contains JSON data.

Note:

The TimesTen database user must exist in the Oracle database and the user in the Oracle database must have privileges to SELECT the queried objects. You must provide the password for the user in the Oracle database when connecting to the TimesTen database for ttLoadFromOracle to work.

ttisql -connstr "DSN=database1;UID=pat;PWD=password;OraclePWD=password"

Copyright (c) 1996, 2025, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=database1;UID=pat;PWD=********;OraclePWD=********";
Connection successful: DSN=database1;UID=pat;DataStore=/timesten/sample_db/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogFileSize=64;
DRIVER=/timesten/install/lib/libtten.so;LogBufMB=64;PermSize=128;TempSize=128;
OracleNetServiceName=oracledb;
(Default setting AutoCommit=1)
Command> CALL ttLoadFromOracle ('pat', 'j_purchaseorder', 'SELECT * FROM pat.j_purchaseorder');
< 10000, 0, 0, Started=2025-05-15 21:26:25 (GMT); Ended=2025-05-15 21:26:25 (GMT); 
Load successfully completed; OracleSCN=undefined; Rows Loaded=10000; Errors=0 >
1 row found.