Issue: Executing a query that generates INSERT statements to migrate data from Postgres to Oracle. Results show the expected number of rows, except more and more rows are blank. NULL values in the data appear to be the cause of the blank rows. Sample (contrived) data (3 rows):
Id FirstName LastName PhoneNumber Gender City
1 Fred Flinstone 1111111111 M Bedrock
2 Barney Rubble <no Ph#> M Bedrock
3 Wilma Flinstone 2222222222 F <No City>
Execute this type of query from the Postgres psql prompt:
postgresdb=# Select 'INSERT into TABLE1.POSTGRESXFER VALUES ('||"Id"||', '''||"FirstName"||', '''||"PhoneNumber"||', '''||"Gender"||', '''||"City||''');' from "PostgresSourceDB"."SourceTable";
This should create an Oracle compliant INSERT statement for each row. It does not. This is what I will get:
Insert into TABLE1.POSTGRESXFER VALUES (1, 'Fred', 'Flinstone', '1111111111', 'M', 'Bedrock');
<blank row with empty strings for all values, caused by blank PhoneNumber based on testing>
<blank row with empty strings for all values, caused by blank City based on testing>
I have never seen concatenations cause problems with a query. What makes this more confusing is if I run either one of the two queries below, I see all of the data, to include blank (null values) without issue:
Select * from "PostgresSourceDB"."SourceTable";
Select "Id", "FirstName", "LastName", "PhoneNumber", "Gender", "City";
Anyone have an idea why the concatenations I'm using are causing Postgres to show blank rows?
NULL
and any expression involving NULL yields NULL. Oracle doesn't comply with the SQL standard with regards to NULL values and strings, so concatenating a string/varchar with NULL in Oracle, does not yield NULL