RE: [ oracle texte] migration oracle vers postgresql

Bonjour

Dans le cadre du réseau métier, Dalibo nous a fait une présentation de orapg (https://rbdd.cnrs.fr/spip.php?article241)
11h40-12h10 : Migrer d’Oracle à PostgreSQL avec Ora2Pg - Philippe Beaudoin (Dalibo) télécharger la présentation (PDF)

A+

MCQ

***************************
Marie-Claude QUIDOZ
CEFE / CNRS
1919, Route de Mende
34293 Montpellier Cedex 5
Tel : 04 67 61 32 39
Marie-Claude(dot)Quidoz(at)cefe(dot)cnrs(dot)fr
http://www.cefe.cnrs.fr/fr/pf/sie

-----Message d'origine-----
De : Guillaume Clercin <guillaume(dot)clercin(at)billy482(dot)net>
Envoyé : jeudi 27 janvier 2022 12:37
À : pgsql-fr-generale(at)lists(dot)postgresql(dot)org
Objet : Re: [ oracle texte] migration oracle vers postgresql

Bonjour,

Avez-vous regardé cette page:
https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

Cordialement,
Guillaume CLERCIN

Le 26/01/2022 à 11:25, CRUMEYROLLE Pierre a écrit :
> Bonjour
> Quelqu'un a-t-il des pistes pour migrer de l'oracle text vers PostgreSQL ? C'est faisable ?
>
> Ci-dessous un aperçu de l'existant oracle (un peu usine à gaz)
>
> Cordialement
>
> create or replace procedure PR_ITEM_FEEDER (r in rowid, c in out
> nocopy clob) as begin for x in (select D.CONCLUSION, S.DESC_REPH,
> S.BEST_PRAC, S.ASSO_DOC, S.MIT_ACT, S.ROOT_CAUSE, S.FINAL_SOL,
> S.SC_DEC, S.MAJ_STAT, I.DESCRIPTION, I.TITLE, I.REFERENCE from ITEM I
> left join DETAIL D on D.ID = I.DETAIL_FK left join SUMMARY S on S.ID =
> I.PUBLISHED_SUMMARY_FK where I.ROWID = r) loop if x.CONCLUSION is not
> null then dbms_lob.writeappend(c, length(x.CONCLUSION), x.CONCLUSION);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.DESC_REPH is not null
> then dbms_lob.writeappend(c, length(x.DESC_REPH), x.DESC_REPH);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.BEST_PRAC is not null
> then dbms_lob.writeappend(c, length(x.BEST_PRAC), x.BEST_PRAC);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.ASSO_DOC is not null
> then dbms_lob.writeappend(c, length(x.ASSO_DOC), x.ASSO_DOC);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.MIT_ACT is not null then
> dbms_lob.writeappend(c, length(x.MIT_ACT), x.MIT_ACT);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.ROOT_CAUSE is not null
> then dbms_lob.writeappend(c, length(x.ROOT_CAUSE), x.ROOT_CAUSE);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.FINAL_SOL is not null
> then dbms_lob.writeappend(c, length(x.FINAL_SOL), x.FINAL_SOL);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.SC_DEC is not null then
> dbms_lob.writeappend(c, length(x.SC_DEC), x.SC_DEC);
> dbms_lob.writeappend(c, 1, ' '); end if; if x.MAJ_STAT is not null
> then dbms_lob.writeappend(c, length(x.MAJ_STAT), x.MAJ_STAT);
> dbms_lob.writeappend(c, 1, ' '); end if; dbms_lob.writeappend(c,
> length(x.TITLE), x.TITLE); dbms_lob.writeappend(c, 1, ' ');
> dbms_lob.writeappend(c, length(x.DESCRIPTION), x.DESCRIPTION);
> dbms_lob.writeappend(c, 1, ' '); dbms_lob.writeappend(c,
> length(x.REFERENCE), x.REFERENCE); dbms_lob.writeappend(c, 1, ' ');
> end loop; end; / GRANT ALL ON PR_ITEM_FEEDER TO D220_FAIR_SERVICE; /
>
> create or replace trigger TR_UPDATE_TEXT_ITEM
> before update of DESCRIPTION, REFERENCE, TITLE on ITEM
> for each row
> begin
> :new.TEXT_INDEX := :old.TEXT_INDEX;
> end;
> /
> show errors trigger TR_UPDATE_TEXT_ITEM; /
>
> create or replace trigger TR_UPDATE_TEXT_DETAIL
> before update of CONCLUSION on DETAIL
> for each row
> begin
> update ITEM set TEXT_INDEX=NULL where DETAIL_FK=:old.ID;
> end;
> /
> show errors trigger TR_UPDATE_TEXT_DETAIL; /
>
> create or replace trigger TR_UPDATE_TEXT_SUMMARY
> before update of DESC_REPH, BEST_PRAC, ASSO_DOC, MIT_ACT,
> ROOT_CAUSE, FINAL_SOL, SC_DEC, MAJ_STAT on SUMMARY
> for each row
> begin
> update ITEM set TEXT_INDEX=NULL where PUBLISHED_SUMMARY_FK=:old.ID;
> end;
> /
> show errors trigger TR_UPDATE_TEXT_SUMMARY; /
>
> begin
> ctx_ddl.create_preference('itemdatastore', 'user_datastore');
> ctx_ddl.set_attribute('itemdatastore', 'procedure', 'PR_ITEM_FEEDER');
> ctx_ddl.set_attribute('itemdatastore', 'output_type', 'CLOB'); end; /
>
> begin
> ctx_ddl.create_preference('fairstorage', 'BASIC_STORAGE');
> ctx_ddl.set_attribute('fairstorage', 'I_TABLE_CLAUSE', 'tablespace
> D220_ISP_TSDATA_CTX lob (token_info) store as (tablespace
> D220_ISP_TSLOB_CTX)'); ctx_ddl.set_attribute('fairstorage',
> 'K_TABLE_CLAUSE', 'tablespace D220_ISP_TSDATA_CTX');
> ctx_ddl.set_attribute('fairstorage', 'R_TABLE_CLAUSE', 'tablespace
> D220_ISP_TSDATA_CTX lob (data) store as (tablespace D220_ISP_TSLOB_CTX
> disable storage in row cache)'); ctx_ddl.set_attribute('fairstorage',
> 'N_TABLE_CLAUSE', 'tablespace D220_ISP_TSDATA_CTX');
> ctx_ddl.set_attribute('fairstorage', 'I_INDEX_CLAUSE', 'tablespace
> D220_ISP_TSINDEX_CTX compress 2');
> ctx_ddl.set_attribute('fairstorage', 'P_TABLE_CLAUSE', 'tablespace
> D220_ISP_TSDATA_CTX'); end; /
>
> begin
> ctx_ddl.create_preference('fairwordlist', 'BASIC_WORDLIST');
> ctx_ddl.set_attribute('fairwordlist', 'substring_index', 'TRUE'); end;
> /
>
> create index IX_ITEM on ITEM(TEXT_INDEX) indextype is ctxsys.context
> parameters ('datastore itemdatastore storage fairstorage wordlist
> fairwordlist sync (on commit)'); /
>
> create index IX_COMMENTS on COMMENTS(CONTENT) indextype is
> ctxsys.context parameters ('storage fairstorage wordlist fairwordlist
> sync (on commit)'); /
>
> create index IX_ASSESSMENT on ASSESSMENT(CONTENT) indextype is
> ctxsys.context parameters ('storage fairstorage wordlist fairwordlist
> sync (on commit)'); /
>
> create index IX_VOTE on VOTE(CONTENT)
> indextype is ctxsys.context
> parameters ('storage fairstorage
> wordlist fairwordlist
> sync (on commit)');
> /
>
> create index IX_FEEDBACK on FEEDBACK(CONTENT) indextype is
> ctxsys.context parameters ('storage fairstorage wordlist fairwordlist
> sync (on commit)'); /
>
>