Home icon

Migrate JSON data from Oracle’s CLOB to Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

Database Blog

This article discusses how to migrate JSON data from Oracle's CLOB data type to Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL, which require JSON data to be well-formed according to RFC 8259.

Specifically, the article covers:

  • The challenges of migrating invalid JSON data from Oracle's CLOB to PostgreSQL's JSON/JSONB data types
  • Using AWS Database Migration Service (AWS DMS) to streamline the migration process
  • A PL/pgSQL procedure to identify and isolate invalid JSON records from the source Oracle CLOB columns
  • Step-by-step instructions for setting up AWS DMS, generating invalid JSON data, creating the PL/pgSQL procedure, and using the procedure
  • Examples of validating JSON data using the PL/pgSQL procedure and handling invalid JSON records
  • Limitations of the solution and cleanup steps after successful migration
Go to article

The AWS News Feed is currently looking for gold sponsors. If you want to support the AWS community and reach a large audience of AWS professionals, consider sponsoring the AWS News Feed.

Related articles

Jan 8
2024
Migrate an Oracle associative array to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL
Feb 8
2024
Migrate Oracle collections to Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL
Jun 10
2026
How to migrate from Oracle to Amazon Aurora PostgreSQL using AWS CloudFormation (Part 1)
Jun 3
2026
Migrating data from Oracle to Amazon Aurora DSQL

The AWS News Feed is currently looking for silver sponsors. If you want to support the AWS community and reach a large audience of AWS professionals, consider sponsoring the AWS News Feed.