Performing a Pre-Migration Check

D.1 Performing a Pre-Migration Check

You use PL/SQL procedure DBMS_JSON.json_type_convertible_check to check whether a given column of textual JSON data can be converted to JSON data type.

For example, this checks the convertibility of textual JSON column data of table j_purchaseorder, which is owned by database schema table_owner. The status of the convertibility check is written to table my_precheck_table.

EXECUTE DBMS_JSON.json_type_convertible_check(
                   owner => 'table_owner',
                   tableName => 'j_purchaseorder',
                   columnName => 'data',
                   statusTableName => 'my_precheck_table');

The result of checking is stored in the table named by the third parameter to the procedure (e.g., my_precheck_table). This table is created if it does not yet exist. By default, an existing table is truncated, but if the optional fifth parameter is TRUE then the procedure appends new result rows to the existing table.

By default, convertibility is checked by attempting to convert the data using (in effect) the JSON type constructor. If optional fourth parameter is TRUE, then convertibility is checked only using SQL condition is json, which just checks that the JSON data is well-formed, not whether it is actually convertible to JSON type.

The procedure checks each JSON value in the column to be checked. It logs each value that cannot be converted in the status table, along with the reason it's not convertible.

The procedure also logs its progress in the status table, in increments of 10% for a table of less than 10,000 rows, 5% for a table of 10,000 to 99,999 rows, and 1% for a table of 100,000 or more rows. A progress entry also shows you the number of errors found so far.

Table D-1 describes the resulting status table.

Table D-1 JSON-Type Convertibility-Check Status Table

Column Name	Data Type	Description
`STAMP`	`TIMESTAMP(6)`	Time and date this status row (log entry) was written.
`SCHEMA_NAME`	`VARCHAR2(130)`	Name of the database schema that owns table `TABLE_NAME`.
`TABLE_NAME`	`VARCHAR2(130)`	Database table containing textual JSON column `COLUMN_NAME`.
`COLUMN_NAME`	`VARCHAR2(130)`	Name of textual JSON column to be migrated to `JSON`-type data.
`ERROR_ROW_ID`	`ROWID`	Address of the row in which a convertibility error was detected.
`ERROR_CODE`	`VARCHAR2(250)`	Designation/description of the error in `ERROR_ROW_ID`. Example: `JSON SYNTAX ERROR`.
`STATUS`	`VARCHAR2(100)`	If the log entry is because an error was found, then this is `ERROR FOUND` — see columns `ERROR_CODE` and `ERROR_ROW_ID`. For a periodic, in-progress log, this is `M% completed (Errors found: N)`, where `M` is the percentage of checking completed, and `N` is the number of errors found, so far. If checking is finished then this is `Process completed (Errors found: N)`, where `N` is the total number of errors found.

Example D-1 Locating Problematic JSON Data Reported By DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK

This example shows how to select invalid data reported with error code JSON SYNTAX ERROR in status table my_precheck_table for ROWID AAAWf+AAEAAAAEMAAC of column data of table table_owner.j_purchaseorder.


SELECT data FROM table_owner.j_purchaseorder
  WHERE ROWID IN (SELECT pt.ERROR_ROW_ID
	             FROM my_precheck_table pt
	             WHERE pt.schema_name = table_owner
	               AND pt.table_name = j_purchaseorder
	               AND pt.column_name = data);

Parent topic: Migrating Textual JSON Data to JSON Data Type