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)');
> /
>
>