Empty String and NULL in Oracle and PostgreSQL

Oracle treats empty string as NULL

SQL> create table test ( col1 varchar(10), col2 varchar(10) );

Table created.

SQL> insert into test values ( '',null);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test where col1 is null;

COUNT(*)
----------
1

SQL> select count(*) from test where col2 is null;

COUNT(*)
----------
1

PostgreSQL  does not treat empty string as NULL

This could bring some code issue when migrating Oracle database to PostgreSQL.

elms=> create table test ( col1 varchar(10), col2 varchar(10) );
CREATE TABLE

testdb=> insert into test values ( '',null);
INSERT 0 1

testdb=> select count(*) from test where col1 is null;
count
-------
0
(1 row)

testdb=> select count(*) from test where col2 is null;
count
-------
1
(1 row)

Heterogeneous Database Connections – Oracle to PostgreSQL

There are two ways to use Heterogeneous Services,  one is via ODBC which does not require a licence,  or you can use the licensed Gateways.

This article describes how to set up the Oracle configurations using ODBC , and then you can create database link between Oracle database and AWS PostgreSQL RDS.

Subscribe to get access

Read more of this content when you subscribe today.

aws dms table error

AWS DMS ( Data Migration Service ) task has been running for many hours, then failed with error “Table error” for Load State.

1)Check target database,  99% records have been migrated from Oracle source database to PostgreSQL RDS.

2)Check task log with bellow error:

00:04:00 2018-10-01T00:04:00 [SOURCE_UNLOAD ]E: ORA-01555: 
snapshot too old: rollback segment number 918 with name 
"_SYSSMU918_239767949$" too small [1020436] (oracle_endpoint_unload.c:226)

SOLUTION

Add more space to UNDO tablespace, and/or increase the value of parameter “undo_retention” for source Oracle database.

Migrate Oracle to AWS PostgreSQL by Using AWS DMS

This post step by step explains how to migrate on-premise Oracle database 12c data to AWS PostgreSQL RDS by using AWS DMS ( Database Migration Service). Assume the schema has been migrated by using  the AWS Schema Conversion Tool (AWS SCT).

Subscribe to get access

Read more of this content when you subscribe today.