Migration d’une base de données Oracle vers PostgreSQL

L’objectif de cet article est de documenter la procédure pour migrer une base de données simple d’Oracle vers PostgreSQL.

A propos des bases de données

La base de données de destination est une installation locale de PostgreSQL 9.3.1 sur poste Windows.

La version de la base Oracle sera Oracle Database 10g Express Edition Release 10 installée sur le même poste de travail. Nous utiliserons le schéma de test HR qui peut être installé à partir de l’environnement de développement Oracle Application Express (APEX) qui est fourni en standard sur les éditions Oracle et permet de gérer les bases de données depuis une interface web.

L’interface est accessible par défaut sur http://localhost:8080/apex, nous utiliserons le compte utilisateur sys pour se connecter avec des privilèges administrateur.

L’application APEX permet d’accéder rapidement aux informations sur le support des langues nationales
Page d’accueil > Administration > A propos de la base de données

La base de donnée exemple HR

Par défaut vous pouvez trouver un schéma d’exemple qui sert à certains tutoriels Oracle et aussi d’environnement assez complet pour tester des fonctionnalités. Un utilisateur nommé HR (acronyme pour Human Resources) est associé à la base, il faut cependant déverrouiller le schéma HR pour pouvoir l’exploiter.

Cela peut se faire depuis l’interface APEX ou au travers d’une commande SQL (n. Où password est le mot de passe utilisateur HR, mémorisez le) :

ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;

La base HR sur notre environnement est assez complète pour procéder à un test de migration simple dans le sens où elle comporte quelques tables reliées entre elles par des relations de clefs étrangères, des clefs primaires sur différents types de données, des séquences, un déclencheurs, deux procédures et une vue.

Si vous ne possédez pas le schéma HR vous pouvez l’installer manuellement en exécutant le fichier de démonstration sous votre répertoire ORACLE_HOME
C:\oraclexe\app\oracle\product\10.2.0\server\demo\schema\human_resources\hr_main.sql

Pour les opérations sur la base Oracle nous utiliserons le logiciel Oracle SQL Developer (version 3.0.02), plus souple que l’environnement APEX.

A ce stade nous avons une connexion au schéma HR paramétrée. Pour référence, l’arborescence des objets sous SQL Developer :

Migration de la base de données

Afin de rejouer le scénario de migration il peut être intéressant de travailler sur une copie de la base Oracle, sur laquelle on peut procéder en amont à certaines opérations par exemple afin de réduire la volumétrie.

Voyons comment exporter une base.

Oracle SQL Developer possède des fonctionnalités d’import et d’export de base de données (nommées par les termes charger et décharger sous certaines versions du logiciel).

J’ai de mauvaises expériences de ces outils qui ne fonctionnent que rarement du premier coup, voir pas du tout. Je suppose que le problème vient d’une mauvaise prise en charge des version d’Oracle plus anciennes que les versions de SQL Developer utilisées pour l’export, de ce fait je vais m’orienter vers les utilitaires d’import et d’export inclus à Oracle XE, cela doit permettre d’éviter les problèmes de compatibilité de versions.

Pour tester l’import de fichiers au format SQL pour les instructions de construction de schéma et au format SQL Loader pour les données (*.ctl et *.ldr) il peut être nécessaire de supprimer le schéma au préalable pour repartir sur une base vierge. Tous les objets de base de données appartiennent à un utilisateur, que vous pouvez supprimer.

Vous devez être connecté avec un compte utilisateur qui possède suffisamment de privilèges, et vous déconnecter avec l’utilisateur à supprimer. Vous pouvez procéder au travers de l’interface APEX ou bien en ligne de commande.

Avant import vous pouvez (re)créer un utilisateur avec les privilèges nécessaires à la création des objets.

DROP USER HRMIG CASCADE;
CREATE TABLESPACE THRMIG DATAFILE 'C:/oraclexe/oradata/XE/thrmig.dbf' SIZE 200M;
CREATE USER HRMIG IDENTIFIED BY HRMIG DEFAULT TABLESPACE THRMIG QUOTA UNLIMITED ON THRMIG;
GRANT CONNECT, RESOURCE, DBA TO HRMIG;
GRANT ALL PRIVILEGES TO HRMIG;

Les utilitaires en ligne de commande sont une solution fiable pour gérer les exports et les imports. Ils sont inclus avec la base. Par défaut sous Windows ils sont sous le répertoire
C:\oraclexe\app\oracle\product\10.2.0\server\BIN

La première étape est de définir un objet répertoire sous Oracle auquel les utilitaires en ligne de commande pourront accéder.
L’objet répertoire pointe vers un répertoire physique (C:/Temp/ dans l’exemple), il faut ensuite accorder des droits aux utilisateurs afin de pouvoir accéder en lecture et écriture sur ce répertoire.

CREATE OR REPLACE DIRECTORY hrdir AS 'C:/Temp/';
GRANT READ, WRITE ON DIRECTORY hrdir TO hr;

J’ai accordé des droits à l’utilisateur HR, car il va utiliser le répertoire en écriture et lecture. Seul cet utilisateur est nécessaire car l’outil d’import en ligne de commande permet de ré-allouer le schema à un autre utilisateur.

cd C:\oraclexe\app\oracle\product\10.2.0\server\BIN

Exporte le schéma HR vers un fichier de sauvegarde

expdp HR/HR directory=hrdir schemas=HR dumpfile=hr.dmp logfile=hr.log

Importe le fichier de sauvegarde avec ré-attribution du schéma de HR vers HRMIG et du tablespace SYSTEM vers THRMIG

impdp hrmig/hrmig directory=hrdir remap_schema=hr:hrmig remap_tablespace=system:thrmig dumpfile=hr.dmp logfile=hrmig.log

Cette solution est fiable comparée aux exports sous SQL Developer, si l’on respecte toutefois un principe : d’après la documentation, les exports seraient compatibles à partir du moment où l’on tente un import sur une base de version équivalente ou supérieure.

Pensez à transférer le fichier de log en même temps que le fichier d’export, il contient des informations utiles !

L’outil de migration Ora2Pg

Nous possédons une base de test pour évaluer les possibilités de migration d’Oracle vers PostgreSQL.

L’outil que l’on va utiliser est un utilitaire écrit en langage Perl, il s’agit de Ora2Pg.

C’est un outil gratuit qui se connecte à une base de données Oracle, parcours automatiquement la base et extrait structure et données. Il génère alors des scripts SQL que vous pouvez charger dans PostgreSQL.

Installation

Téléchargez et décompressez l’archive

Vous devez disposer d’une version de Perl supérieure à 5.6

perl --version
This is perl 5, version 12, subversion 3 (v5.12.3) built for (...)

Sous Windows vous pouvez installer l’édition communautaire d’ActivePerl, distribuée par ActiveState, de nombreux modules sont compilés dont les modules dont nous avons besoin.

L’installation de la distribution ajoute le chemin vers l’exécutable Perl à la variable d’environnement PATH, et elle inclus le gestionnaire de paquets ppm qui permettra d’installer les librairies requises : l’installation requiert des modules Perl qui à leur tour demandent d’avoir les librairies clients Oracle installées.

J’ai une installation d’Oracle XE je n’ai pas à me préoccuper des librairies clientes cependant l’occasion est bonne pour ajouter des variables d’environnement au système.

Vous aurez besoin de la variable d’environnement ORACLE_HOME.

J’ajoute le répertoire des exécutables Oracle XE à la variable PATH

C:\oraclexe\app\oracle\product\10.2.0\server\BIN

Je créé la variable ORACLE_HOME et la variable LD_LIBRARY_PATH

C:\oraclexe\app\oracle\product\10.2.0\server
C:\oraclexe\app\oracle\product\10.2.0\server\LIB
Ouvrez le gestionnaire de paquets Perl ppm listez les modules installés et si besoin installez les modules suivants :
ppm list

ppm install DBI
ppm install DBD-Pg
ppm install DBD-Oracle
ppm install Time-HiRes
ppm install Compress-Raw-Zlib
Procédez alors à l’installation du module Ora2pg
ppm install dmake

cd "C:\Program Files\ora2pg"
perl Makefile.PL
dmake && dmake install

Le programme d’installation ous informe que le fichier de configuration est placé sous C:\ora2pg\ora2pg_dist.conf

Configuration et exécution

L’utilitaire peut s’utiliser avec des paramètres en ligne de commande, par exemple pour préciser un fichier de configuration ou avoir plus de granularité sur les les éléments à exporter. Dans la suite du document on utilisera uniquement le fonctionnement par fichier de configuration…
Ouvrez le fichier de configuration C:\ora2pg\ora2pg_dist.conf et enregistrez le sous ora2pg.conf

Nous pouvons demander à l’utilitaire d’exporter directement le schéma Oracle vers un schéma PostgreSQL, dans ce cas il faut paramétrer les directives PG_DSN, PG_USER, PG_PWD et EXPORT_SCHEMA

Les options sont nombreuses, consultez toutes les directives à partir de la documentation.

Au plus simple il suffit de paramétrer les directives pour l’accès à la base Oracle, les directives pour la sortie sous forme de fichier et la directive SCHEMA qui permet de restreindre l’export à ce schéma (Attention sur ce dernier point !).

La directive type permet d’indiquer quels types d’objets exporter, elle est également indispensable.

Modifiez les directives de connexion à Oracle
ORACLE_DSN	dbi:Oracle:host=localhost;sid=xe
ORACLE_USER	HRMIG
ORACLE_PWD	HRMIG

ATTENTION. L’utilisateur HRMIG possède ici un profil avec des droits étendus qui lui permettent d’accéder aux objets système Oracle.

Modifiez les directives qui permettent de préciser les fichiers en sortie, afin d’examiner au mieux les fichiers SQL produits nous demanderons au programme d’enregistrer un fichier par table.
OUTPUT		migration.sql
OUTPUT_DIR	C:/Temp
FILE_PER_TABLE	1
La directive SCHEMA permet de préciser que l’on souhaite uniquement le schéma HR, on souhaite exporter ce schéma vers un schéma PostgreSQL.
La directive TYPE permet de renseigner le type d’export souhaité : ici on souhaite la structure des tables et les données, les séquences, déclencheurs, procédures stockées, et les vues.
EXPORT_SCHEMA	1
SCHEMA		HRMIG
TYPE		TABLE SEQUENCE VIEW PROCEDURE TRIGGER INSERT
ATTENTION ! Le script produira les instructions SQL dans l’ordre que vous spécifiez dans la directive TYPE.

Dans notre exemple j’ai mis les tables en premier car c’est à ce moment que sont produites les instructions de création de schéma.
Vous devrez éditer le fichier SQL généré pour :

  • Contrôler et adapter les codes PL/SQL des procédures et déclencheur transcrits partiellement en PL/pgSQL
  • Contrôler l’ordre d’appel aux instructions INSERT de chaque table pour respecter les références de clef étrangères (ou bien désactiver la création des clefs étrangères avant insertion qui est l’option que j’ai choisie et exécuter ce code plus tard)
  • Contrôler l’ordre de création des fonctions, procédures et déclencheur

Le fichier est configuré par défaut, vous pouvez modifier les autres directives si vous le souhaitez, plus particulièrement NLS_LANG et NLS_NCHAR qui permettent de forcer les paramètres d’encodage Oracle au lieu de considérer les paramètres détectés.

Il est possible de demander à l’utilitaire les informations sur l’encodage de la base source.

ora2pg -t SHOW_ENCODING
        Using character set: NLS_LANG=AMERICAN_AMERICA.AL32UTF8, NLS_NCHAR=AL32U
TF8.
        Using Perl output encoding :utf8.
        Using PostgreSQL client encoding UTF8.
Trying to connect to database: dbi:Oracle:host=localhost;sid=xe
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Showing Oracle encoding...
NLS_LANG AMERICAN_AMERICA.AL32UTF8
CLIENT_ENCODING UTF8

Par défaut l’encodage client sous PostgreSQL est fixé automatiquement à UTF8 pour éviter les problèmes d’encodage.

Il ne reste qu’à exécuter l’utilitaire et contrôler le processus, pour cela il peut être utile d’afficher les traces (Basculer la directive DEBUG à 1)

Lancer l’exécution de la migration, l’utilitaire est sous un répertoire d’exécutables de Perl, C:\Perl\site\bin\ora2pg.bat avec l’installation par défaut de la distribution ActivePerlSoyez patient… l’opération peut être longue.
C:\Perl\site\bin\ora2pg.bat --quiet --log "C:/Temp/migration.log"

L’exécution nous averti d’une erreur que l’analyse du fichier de log nous permet d’ignorer : le programme tente d’importer plusieurs fois des tables, et ignore la seconde tentative.

Par contre si vous regardez attentivement les fichiers générés, vous verrez l’absence de la table countries !

Que ce passe-t-il ?

Pour tenter de le découvrir nous pouvons vérifier sous Oracle les tables système pour vérifier les informations disponibles sur cette table.

La première tentative est de vérifier le propriétaire de chaque objet et la validité, en effet seuls les objets qui ont pour propriétaire HRMIG sont exportés et par défaut Ora2pg ne prend pas les objets invalides. Si vous avez des procédures stockées, des fonctions ou des déclencheurs vérifiez bien qu’ils soient compilés !

SELECT DISTINCT OBJECT_NAME, OWNER FROM ALL_OBJECTS WHERE OWNER = 'HRMIG' AND STATUS='VALID';
COUNTRIES	HRMIG	VALID

L’objet existe, HRMIG est bien le propriétaire et l’objet est valide… la seconde tentative est de vérifier les propriétés de la table.

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, IOT_TYPE FROM DBA_TABLES WHERE OWNER = 'HRMIG';
HRMIG	REGIONS	        THRMIG    (null)
HRMIG	LOCATIONS	THRMIG    (null)
HRMIG	DEPARTMENTS	THRMIG    (null)
HRMIG	JOBS	        THRMIG    (null)
HRMIG	EMPLOYEES	THRMIG    (null)
HRMIG	JOB_HISTORY	THRMIG    (null)
HRMIG	COUNTRIES	(null)    IOT

Le problème se précise. La table n’appartient pas au TABLESPACE THRMIG et c’est une table IOT (index organized tables)

En tant que table IOT elle n’est pas enregistrée directement dans la table système Oracle DBA_SEGMENT mais emploie un segment indexé nommé SYS_IOT_TOP_{TABLE_OBJECT_ID}

Qu’est ce que cela implique ? Pourquoi Ora2pg n’a pas connaissance de cette table ?

Pour obtenir une réponse à ces questions il faut entrer en détail dans le code de l’utilitaire. Comme il s’agit d’un script Perl c’est assez facile, il suffit d’éditer le fichier Ora2Pg.pm !

Faites une copie de sauvegarde du fichier
C:\Perl\site\lib\Ora2Pg.pm
Basculez le mode lecture seule puis éditez le fichier

Le script utilise le module Perl DBI pour les accès à la base de données, cela rend facile le débogage des requêtes SQL utilisées par le module Ora2Pg.pm. Il suffit de paramétrer l’objet de connexion pour rediriger les traces vers un fichier de log.

sub _oracle_connection
{
	my ($self, $quiet) = @_;

	$self->logit("Trying to connect to database: $self->{oracle_dsn}\n", 1) if (!$quiet);

	my $dbh = DBI->connect($self->{oracle_dsn}, $self->{oracle_user}, $self->{oracle_pwd}, {ora_envhp => 0, LongReadLen=>$self->{longreadlen}, LongTruncOk=>$self->{longtruncok} });

	# Fix a problem when exporting type LONG and LOB
	$dbh->{'LongReadLen'} = $self->{longreadlen};
	$dbh->{'LongTruncOk'} = $self->{longtruncok};

	# Check for connection failure
	if (!$dbh) {
		$self->logit("FATAL: $DBI::err ... $DBI::errstr\n", 0, 1);
	}

	# Use consistent reads for concurrent dumping...
	$dbh->begin_work || $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	if ($self->{debug} && !$quiet) {
		$self->logit("Isolation level: $self->{transaction}\n", 1);
	}
	my $sth = $dbh->prepare($self->{transaction}) or $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	$sth->execute or $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	$sth->finish;
	undef $sth;

       $dbh->trace($dbh->parse_trace_flags('SQL|2|test'), 'C:\Temp\traces.txt');
(...)

La ligne suivante a été ajoutée après obtention de la connexion à Oracle :

       $dbh->trace($dbh->parse_trace_flags('SQL|2|test'), 'C:\Temp\traces.txt');

La méthode trace() se voit passer un paramètre SQL qui précise d’enregistrer les déclarations SQL dans le fichier de log. Le niveau 2 indique d’enregistrer les paramètres et on passe également le chemin vers le fichier de destination.

Alors que fait Ora2pg ?

SELECT A.TABLE_NAME,
  A.COMMENTS,
  A.TABLE_TYPE
FROM ALL_TAB_COMMENTS A
JOIN DBA_SEGMENTS S
  ON (S.SEGMENT_TYPE LIKE 'TABLE%'
  AND S.SEGMENT_NAME=A.TABLE_NAME)
WHERE A.OWNER     ='HR'
  AND ( NOT REGEXP_LIKE(TABLE_NAME,'^USLOG\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MLOG\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^RUPD\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDXT_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDRT_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDRS_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^DR\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^CLI_SWP\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^LOGMNR\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^REPCAT\$.*$'))
  AND (A.OWNER, A.TABLE_NAME) NOT IN
    (SELECT OWNER, MVIEW_NAME FROM ALL_MVIEWS
     UNION ALL
     SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS
    )

L’origine du problème est là : il fait une jointure sur la table système DBA_SEGMENTS qui l’empêche d’avoir connaissance des tables IOT, et pas seulement, il va aussi ignorer les tables externes et toutes les tables qui n’apparaissent pas dans cette table système.

On peut se rendre compte que la table DBA_SEGMENTS ne référence pas toutes les tables à l’aide d’une simple requête SQL

SELECT OWNER, TABLE_NAME, CLUSTER_NAME, PARTITIONED, TEMPORARY, IOT_TYPE
  FROM DBA_TABLES
  WHERE (OWNER, TABLE_NAME) NOT IN
          (SELECT OWNER, SEGMENT_NAME FROM DBA_SEGMENTS
          WHERE SEGMENT_TYPE='TABLE')

Le problème est une limitation d’Ora2pg qu’il faut contourner.

Comment faire ? J’ai tenté sans succès une approche pour changer le mode d’indexation de la table COUNTRIES, et au final ma seule solution est de dupliquer la table, supprimer l’original et renommer la copie.

Commençons par dupliquer la table et recréer des contraintes de clef primaire et clef étrangère. Cette nouvelle table est par défaut sous organisation HEAP avec des lignes stockées sans ordre particulier.

CREATE TABLE HRMIG.COUNTRIES_COPY AS SELECT * FROM HRMIG.COUNTRIES;
ALTER TABLE HRMIG.COUNTRIES_COPY ADD CONSTRAINT "C_ID_PK" PRIMARY KEY ("COUNTRY_ID");
ALTER TABLE HRMIG.COUNTRIES_COPY ADD CONSTRAINT "C_REG_FK" FOREIGN KEY ("REGION_ID")
	  REFERENCES "HRMIG"."REGIONS" ("REGION_ID");

Nous pouvons alors supprimer l’original en précisant à Oracle de supprimer les informations indexées dans le segment (PURGE), et renommer la copie.

DROP TABLE "HRMIG"."COUNTRIES" CASCADE CONSTRAINTS PURGE;
ALTER TABLE "HRMIG"."COUNTRIES_COPY" RENAME TO COUNTRIES;

Finalement il reste à recompiler la vue qui exploite la table sans quoi elle est en état invalide et ne sera pas prise en compte par Ora2pg !

ALTER VIEW "HRMIG"."EMP_DETAILS_VIEW" COMPILE;
Relancez la migration…

Vous pouvez si la migration semble complète importer les fichiers SQL créés sous PostregSQL via l’utilitaire psql

Vous devez éditer le fichier de migration principal et adapter ce fichier. J’ai procédé en plusieurs étapes, d’abord la création des tables et séquences, et l’insertion des données en base, puis la création des contraintes de clefs étrangères, puis la réécriture du code PL/pgSQL et la création des procédures et déclencheurs.

Import des résultats sous PostgreSQL

Attention à la casse de caractères ! (PostgreSQL requiert d’entourer les identifiants par des guillemets lorsque l’on veut accéder à des objets enregistrés avec des majuscules)

Avant tout, créons un utilisateur !

createuser --host=localhost --port=5432 --username=postgres --no-superuser --no-createrole --no-createdb hrmig
psql --host=localhost --port=5432 --username=postgres --no-password
ALTER ROLE hrmig SET search_path TO hrmig,public;
ALTER ROLE hrmig WITH PASSWORD 'hrmig';

Créons une base de données où exécuter les commandes

CREATE DATABASE hrmig
  WITH ENCODING='UTF8'
       OWNER=hrmig
       CONNECTION LIMIT=-1;

Créons un tablespace

CREATE TABLESPACE thrmig
  OWNER hrmig
  LOCATION 'C:\Program Files\PostgreSQL\9.3\data\base';
Tentez d’importez le fichier !
cd C:\Temp
psql --host=localhost --port=5432 --username=hrmig --dbname=hrmig --file="C:\Temp\migration.sql" --quiet --log-file="C:\Temp\import.log"

Résultats

En procédant par étape, le schéma est d’abord créé correctement avec tables et vue, et les données sont insérées. Les séquences sont crées avec le dernier numéro adéquat. Puis les contraintes de table que l’on a déplacées sous un script à exécuter post insertion sont correctement appliquées.

Il reste toutefois à migrer procédure et trigger.

Sous Oracle la procédure est définie ainsi

create or replace PROCEDURE         "ADD_JOB_HISTORY" 
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type 
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

Le script Ora2pg génère un squelette de procédure PL/pgSQL qu’il nous faut adapter

CREATE OR REPLACE FUNCTION HRMIG.add_job_history (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type 
   )
 RETURNS VOID AS $body$
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
 
$body$
LANGUAGE PLPGSQL;

En fait il ne reste que très peu de travail sur une fonction aussi simple pour obtenir un résultat qui compile

CREATE OR REPLACE FUNCTION hrmig.add_job_history (  
  p_emp_id          job_history.employee_id%type, 
  p_start_date      job_history.start_date%type, 
  p_end_date        job_history.end_date%type, 
  p_job_id          job_history.job_id%type, 
  p_department_id   job_history.department_id%type 
)
 RETURNS VOID AS $body$
BEGIN
  INSERT INTO hrmig.job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END;
$body$
LANGUAGE PLPGSQL;

La fonction prend des paramètres qui servent à insérer une nouvelle ligne dans une table.

Par rapport au code généré par Ora2pg il a fallu :

  • Supprimer l’étiquette add_job_history de l’instruction END add_job_history;
  • Mettre en minuscule hrmig dans la déclaration de fonction CREATE OR REPLACE FUNCTION hrmig.add_job_history
  • Préciser la table dans laquelle on insère : INSERT INTO hrmig.job_history

Notez que les paramètres sont convertis selon leur type lors de la création.

C’est évidemment un exemple très très simple, parfait pour commencer. Vous pouvez trouver dans la documentation PostgreSQL des instructions de portage de PL/SQL vers PL/pgSQL.

Le portage de l’autre procédure est aussi simple (elle compile en tout cas, mon objectif n’est pas de vérifier les aspects fonctionnels)

CREATE OR REPLACE FUNCTION hrmig.secure_dml () 
RETURNS VOID AS $body$
BEGIN
  IF TO_CHAR (LOCALTIMESTAMP, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (LOCALTIMESTAMP, 'DY') IN ('SAT', 'SUN') THEN
	RAISE EXCEPTION 'You may only make changes during normal office hours';
  END IF;
END;
$body$
LANGUAGE PLPGSQL;

Le portage du déclencheur a nécessité l’emploi du mot clef PERFORM pour exécuter la fonction crée précédemment. Le fonctionnement n’est pas vérifié mais la création est valide.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION hrmig.trigger_fct_update_job_history () 
RETURNS trigger AS $BODY$
BEGIN
  PERFORM hrmig.add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP, 
                  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history
	AFTER UPDATE ON employees FOR EACH ROW
	EXECUTE PROCEDURE hrmig.trigger_fct_update_job_history();

Conclusion

Nous avons vu comment utiliser le script Ora2pg sur le schéma de démonstration HR pour tester les possibilités de migration d’Oracle vers PostgreSQL.

La migration qui semblait à priori simple nous a tout de même permis de nous rendre compte des limites du script et du travail à faire soit en aval soit en amont, d’abord par rapport aux tables organisées en indexe et aux tables externes d’Oracle, puis par rapport au code PL/pgSQL.

L’outil semble posséder des limitations mais il est déjà très complet et fiable. Il est exploitable sur des cas de migration simple (donc sur des projets où le coût d’une licence Oracle ne se justifie pas), je l’ai par ailleurs depuis utilisé avec succès sur deux projets en environnement professionnel.

 

Je partages volontiers savoir faire et expériences et au delà du temps passé, cela représente chaque année quelques frais d’hébergement auxquels vous pouvez contribuer si vous appréciez les services rendus. Merci !

Faites découvrir ce billet...Email this to someone
email
Print this page
Print
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on Google+
Google+
Share on LinkedIn
Linkedin

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *