Useful scripts when you migrate from Oracle to PostgreSQL

During the migration from Oracle to PostgreSQL with ora2pg the question arose from a mismatch of data types between different databases. By default, not all columns will be converted correctly, and the lack of Boolean in Oracle at all creates ambiguity is part of columns you want to transfer as numbers, some logical values. At the same time, hibernate knows all about data types and can create a reference scheme.

The final migration process was as follows: create a table structure using ora2pg, the correction of the structure of the reference scheme, data transfer, conversion blob and Boolean, add missing PostgreSQL functions (nvl, nvl2, regexp_substr), create the remaining structures, indexes, view and others.

Under the cut is accumulated during the time of the conversion sql scripts for semi-automatic migration.

the

Preparation

As a utility for data conversion was used ora2pg. The process of using very well described in article.

Create a project in ora2pg, a custom project, and generated the schema.

the

$./export_schema.sh

Create PostgreSQL schema 'ora_schema' table, file './schema/tables/table.sql'
Toggle Hibernate mode, the create and create another reference scheme 'hb_schema'. In the use case view in the project, the number of tables in different schemas will not converge. Hibernate will generate the full table instead of a view and this should be considered.

the

Fix column types

Look in any column it is necessary to make changes

the

select
hb.table_name as table , hb.column_name as column, hb.data_type as hb_type, ora.data_type as ora_type
from
information_schema.columns hb
join information_schema.columns ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
where
hb.table_schema = 'hb_schema'
and ora.table_schema= 'ora_schema'
and ora.data_type != hb.data_type
ORDER BY hb.table_name, hb.column_name;

Replacement for simple cases.

the

select 'ALTER TABLE ora_schema.' || ora.table_name || 'ALTER COLUMN' || ora.column_name || 'TYPE' || hb.data_type
|| case when hb.data_type = 'numeric' then '('|| hb.numeric_precision ||','|| hb.numeric_scale||')' else " end || '; '
from information_schema.columns as hb
INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and ora.data_type != hb.data_type
and (hb.data_type = 'integer' or hb.data_type = 'numeric' or hb.data_type = 'bigint' or hb.data_type = 'date')
ORDER BY hb.table_name, hb.column_name;

the

Move the data

Before transferring data, check that it is not coincidence types remained only in bytea and boolean columns(oid), and all columns with numerical values have the correct precision and are not ‘double precision’ to avoid accidental rounding.

By default, the copy process is in one thread. Change in the configuration for the desired number of

the

JOBS 4
ORACLE_COPIES 4

Start the process of copying the data

the

ora2pg -t COPY-o data.sql -b ./data-c ./config/ora2pg.conf

the

Processed boolean

They need to remove if there is a default value, change the type and return the default value

the

--mytabe ALTER TABLE ALTER COLUMN mycolumn DROP DEFAULT;
--ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn is null then null when mycolumn=0 THEN FALSE ELSE TRUE END;
Mytabe --ALTER TABLE ALTER COLUMN mycolumn SET DEFAULT FALSE;

select case when ora.column_default is not null then
'ALTER TABLE ora_schema.'|| ora.table_name || 'ALTER COLUMN' || ora.column_name || 'DROP DEFAULT; 'else" end ||
'ALTER TABLE ora_schema.'|| ora.table_name || 'ALTER' || ora.column_name ||
'TYPE bool USING CASE WHEN' || ora.column_name ||' is null then null WHEN ' || ora.column_name ||'=0 THEN FALSE ELSE TRUE END;'
|| case when cast(ora.column_default as NUMERIC) = 0 then
'ALTER TABLE ora_schema.'|| ora.table_name || 'ALTER COLUMN' || ora.column_name || 'SET DEFAULT 'FALSE'
when cast(ora.column_default as NUMERIC) = 1 then
'ALTER TABLE ora_schema.'|| ora.table_name || 'ALTER COLUMN' || ora.column_name || 'SET DEFAULT to TRUE'
else " end
from information_schema.columns as hb
INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and hb.column_name = ora.column_name
and hb.udt_name != ora.udt_name
and hb.data_type = 'boolean'
ORDER BY hb.table_name, hb.column_name;

the

Processed oid(bytea)

Create a procedure to convert a bytea to oid

the

CREATE OR REPLACE FUNCTION ora_schema.bytea_to_oid(p_blob bytea)
RETURNS oid AS
$BODY$
declare
v_oid oid;
v_int integer;
begin
if octet_length(p_blob)=0 then
v_oid:=null;
else
select lo_create(0) into v_oid;
select lo_open(v_oid, x'20000'::int | x'40000'::int) into v_int;
select lowrite (0, p_blob) into v_int;
end if;
return v_oid;

LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ora_schema.bytea_to_oid(bytea)
OWNER TO postgres;

Create a temporary column

the

select
'ALTER TABLE ora_schema.' || ora.table_name || 'ADD COLUMN' || hb.column_name || '_oid oid; '
from information_schema.columns as hb
JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and hb.udt_name != ora.udt_name
and hb.data_type = 'oid'
ORDER BY hb.table_name, hb.column_name;

Transfer data
the

select
'update' || ora.table_name || ' set ' || hb.column_name || '_oid = bytea_to_oid(' || hb.column_name || ');'
from information_schema.columns as hb
JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and hb.udt_name != ora.udt_name
and hb.data_type = 'oid'
ORDER BY hb.table_name, hb.column_name;

Delete the old column.

the

select 'ALTER TABLE ora_schema.' || ora.table_name || 'DROP COLUMN' || hb.column_name || '; '
from information_schema.columns as hb
INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and hb.udt_name != ora.udt_name
and hb.data_type = 'oid'
ORDER BY hb.table_name, hb.column_name;

Rename the temporary column

the

select 'ALTER TABLE ora_schema.' || ora.table_name || 'RENAME COLUMN' || hb.column_name ||'_oid to '|| hb.column_name||'; '
from information_schema.columns as hb
INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name ||'_oid' = ora.column_name
where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
and hb.data_type = 'oid'
ORDER BY hb.table_name, hb.column_name;

the

Adding the missing features to PostgreSQL

To avoid the need to rewrite the code, just create that missing in PostgreSQL, but which are in Oracle and used in the project.

last_day(date)

the

create function last_day(dt date) returns date
LANGUAGE SQL
AS $$
select cast (date_trunc('month',dt)+interval '1 month' - interval '1 day' as date)
$$;

nvl(date, date)

the

create function nvl(date, var1, var2 date) returns date
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(integer, integer)

the

create function nvl(var1 integer, var2 integer) returns integer
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(numeric, numeric)

the

create function nvl(var1 numeric, var2 numeric) returns numeric
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(text, text)

the

create function nvl(var1 text, var2 text) returns text
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(timestamp with time zone, timestamp with time zone)

the

create function nvl(var1 timestamp with time zone; var2 timestamp with time zone) returns timestamp with time zone
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(timestamp, timestamp)

the

create function nvl(var1 timestamp without time zone; var2 timestamp without time zone) returns timestamp without time zone
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl(varchar, varchar)

the

create function nvl(character varying var1, var2 character varying) returns character varying
LANGUAGE plpgsql
AS $$
begin
return case when var1 is null then var1 else var2 end;
end;
$$;

nvl2(date, date, date)

the

create function nvl2(date, var1, date var2, var3 date) returns date
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl2(integer, integer, integer)

the

create function nvl2(integer var1, integer var2, var3 integer) returns integer
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl2(numeric, numeric, numeric)

the

create function nvl2(var1 numeric, var2 numeric var3 numeric) returns numeric
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl2(text, text, text)

the

create function nvl2(var1 text, var2 text, var3 text) returns text
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl(timestamp with time zone, timestamp with time zone)

the

create function nvl2(var1 timestamp with time zone; var2 timestamp with time zone, var3 timestamp with time zone) returns timestamp with time zone
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl2(timestamp, timestamp, timestamp)

the

create function nvl2(var1 timestamp without time zone; var2 timestamp without time zone, var3 timestamp without time zone) returns timestamp without time zone
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

nvl2(varchar, varchar, varchar)

the

create function nvl2(var1 character varying, character varying var2, var3 character varying) returns character varying
LANGUAGE plpgsql
AS $$
begin
return case when var1 is not null then var2 else var3 end;
end;
$$;

regexp_substr(text, text)

the

create function regexp_substr(str text, pattern text) returns text
LANGUAGE SQL
AS $$
SELECT (regexp_matches(str, pattern))[1]
$$;

regexp_substr(varchar, varchar)

the

create function regexp_substr(str character varying, pattern character varying) returns text
LANGUAGE SQL
AS $$
SELECT (regexp_matches(str, pattern))[1]

trunc(date, varchar)
 

 
the 
create function trunc(dt date, formatstr character varying) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
 
trunc(timestamp with time zone, varchar)
 

 
the 
create function trunc(dt timestamp with time zone, character varying formatstr) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
 
trunc(timestamp, varchar)
 

 
the 
create function trunc(dt timestamp without time zone, character varying formatstr) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
 
the 
Text fields
 
In Oracle there is an unlimited text field, instead it uses the Lob. In PostgreSQL there is a special type — text. To be able to use both bases of the annotation text fields should be in the following
 

 
the 
@Column(name = "script", nullable = true)
@Type(type = "org.hibernate.type.MaterializedClobType")
public String scriptText;
 

 
For Postgres, also use home-made dialect, aware of the added functions
 
the 
public class PostgresDialect extends PostgreSQL9Dialect {
public PostgresDialect() {
super();
this.registerFunction("nvl", new StandardSQLFunction("nvl"));
}

public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
Object descriptor;
switch (sqlCode) {
case 2005:
descriptor = LongVarcharTypeDescriptor.INSTANCE;
break;
default:
descriptor = super.getSqlTypeDescriptorOverride(sqlCode);
}
return (SqlTypeDescriptor) descriptor;
}
}
 

 
the 
Sequence
 
Postgress and Oracle have different syntax nextval from the Sequence. 
 
Oracle
 
the 
SELECT my_seq.nextval;
 
Postgres
 
the 
select nextval('my_seq');
 
Driven to the common view. To do this, create a function in Postgres and Oracle, and everywhere rewrite to use this function.
 
Oracle
 
the 
create or replace function seq_nextval(p_sequence_name varchar)
return integer
as
l_statement varchar(4000);
l_value integer;
begin 
l_statement := 'select '||upper(p_sequence_name)||'.nextval from dual';
execute immediate l_statement
into l_value;
return l_value;
end;
 
Postgres
 
the 
create function seq_nextval(p_sequence_name text)
returns bigint
as
$$
select nextval(p_sequence_name);
$$
language sql;
 
Use
 
the 
select seq_nextval('my_seq')
 
To use directly the function of Postgres nextval('my_seq') does not work, as though Oracle will create this function, but will not allow you to select values.
 
the 
Transformers.ALIAS_TO_ENTITY_MAP
 
If you are using Transformers.ALIAS_TO_ENTITY_MAP you need to specify return types. Oracle by default will cause the keys to upper case PotgresSql to the bottom and the key mismatch is corrected manually.
 
the 
Sysdate
 
No PotgresSql Sysdate, and current_timestamp works in both databases. Model on it
 
the 
substring
 
Unlike java, in databases the indices are one-and correctly specify substring('str', 1, 2) to get the first two characters and not substring('str', 0, 2). But Oracle allows a second option. You can correct it and not use index 0

Article based on information from habrahabr.ru