[ oracle texte] migration oracle vers postgresql

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