| Lists: | pgsql-fr-generale |
|---|
| From: | CRUMEYROLLE Pierre <pierre(dot)crumeyrolle(at)csgroup(dot)eu> |
|---|---|
| To: | "pgsql-fr-generale(at)postgresql(dot)org" <pgsql-fr-generale(at)postgresql(dot)org> |
| Subject: | [ oracle texte] migration oracle vers postgresql |
| Date: | 2022-01-26 10:25:32 |
| Message-ID: | PR2P264MB0559D9D35ACF0436E431916DF1209@PR2P264MB0559.FRAP264.PROD.OUTLOOK.COM |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-fr-generale |
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)');
/
| From: | Guillaume Clercin <guillaume(dot)clercin(at)billy482(dot)net> |
|---|---|
| To: | pgsql-fr-generale(at)lists(dot)postgresql(dot)org |
| Subject: | Re: [ oracle texte] migration oracle vers postgresql |
| Date: | 2022-01-27 11:36:35 |
| Message-ID: | 635b8880-6ea9-c090-374f-f8d499f8b8be@billy482.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-fr-generale |
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)');
> /
>
>
| From: | Marie-Claude Quidoz <marie-claude(dot)quidoz(at)cefe(dot)cnrs(dot)fr> |
|---|---|
| To: | 'Guillaume Clercin' <guillaume(dot)clercin(at)billy482(dot)net>, pgsql-fr-generale(at)lists(dot)postgresql(dot)org |
| Subject: | RE: [ oracle texte] migration oracle vers postgresql |
| Date: | 2022-01-27 12:37:29 |
| Message-ID: | 1352087274.843196.1643287049008.JavaMail.zimbra@zstore-b1-043 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-fr-generale |
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)'); /
>
>
| From: | CRUMEYROLLE Pierre <pierre(dot)crumeyrolle(at)csgroup(dot)eu> |
|---|---|
| To: | "pgsql-fr-generale(at)postgresql(dot)org" <pgsql-fr-generale(at)postgresql(dot)org> |
| Subject: | RE: [ oracle texte] migration oracle vers postgresql |
| Date: | 2022-01-27 13:31:46 |
| Message-ID: | MR2P264MB054875B6D5A01B61C776B455F1219@MR2P264MB0548.FRAP264.PROD.OUTLOOK.COM |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | Postg스포츠 토토 베트맨SQL |
Volia je que ca donne comme migration coté oracle et cote postgres voir ci dessous
cote oracle
set serveroutput on size 1000000
set verify on
set echo on;
set feedback on;
set heading on;
drop table base_table;
drop table another_table1;
drop table another_table2;
drop index CRUMETEST.YOUR_INDEX;
exec ctx_ddl.drop_preference('single_ds');
create table base_table (id number, column1 varchar2 (20), column2 varchar2 (20), column3_id varchar2 (20), alltext varchar2 ( 1));
create table another_table1 (id number, column1 varchar2 (20));
create table another_table2 (id number, column1 varchar2 (20), column2 varchar2 (20));
insert into base_table values (1, 'btcol1 row1 TEST1', 'btcol2 row1', 'btcol3 row1', null);
insert into base_table values (2, 'btcol1 row2', 'btcol2 row2', 'btcol3 row2', null);
insert into base_table values (3, 'btcol1 row3', 'btcol2 row3', 'btcol3 row3', null);
insert into base_table values (4, 'btcol1 row4', 'btcol2 row4', 'btcol3 row4', null);
insert into another_table1 values (1, 'at1col1 row1');
insert into another_table1 values (2, 'at1col1 row2 TEST2');
insert into another_table1 values (3, 'at1col1 row3');
insert into another_table1 values (4, 'at1col1 row4');
insert into another_table2 values (1, 'at2col1 row1', 'at2col2 row1');
insert into another_table2 values (2, 'at2col1 row2', 'at2col2 row2');
insert into another_table2 values (3, 'at2col1 row3', 'at2col2 row3 TEST3');
insert into another_table2 values (4, 'at2col1 row4', 'at2col2 row4');
create or replace procedure base_table_datastore
(rid in rowid,
alltext in out nocopy clob)
is
begin
for c1 in
(select * from base_table where rowid = rid)
loop
dbms_lob.writeappend (alltext, length (c1.column1), c1.column1);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c1.column2), c1.column2);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c1.column3_ID), c1.column3_ID);
for c2 in
(select * from another_table1 where id = c1.id)
loop
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c2.column1), c2.column1);
end loop;
for c3 in
(select * from another_table2 where id = c1.id)
loop
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c3.column1), c3.column1);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c3.column2), c3.column2);
end loop;
end loop;
end base_table_datastore;
/
show errors;
set serveroutput on
declare
v_clob clob;
begin
dbms_output.put_line ('------------------------------------------');
for r in
(select rowid from base_table)
loop
dbms_lob.createtemporary (v_clob, true);
base_table_datastore (r.rowid, v_clob);
dbms_output.put_line (v_clob);
dbms_output.put_line ('------------------------------------------');
dbms_lob.freetemporary (v_clob);
end loop;
end;
/
begin
ctx_ddl.create_preference ('single_ds', 'user_datastore');
ctx_ddl.set_attribute ('single_ds', 'procedure', 'base_table_datastore');
ctx_ddl.set_attribute ('single_ds', 'output_type', 'clob');
end;
/
create index your_index on base_table (alltext) indextype is ctxsys.context parameters ('datastore single_ds sync (on commit)');
select token_text from dr$your_index$i;
select id from base_table where contains (alltext, 'test1') > 0;
select id from base_table where contains (alltext, 'test2') > 0;
select id from base_table where contains (alltext, 'test3') > 0;
coté postgresql
drop index idx_base_table_column1_full_text;
drop index idx_another_table1_column1_full_text;
drop index idx_another_table2_column2_full_text;
drop view search_items;
drop table base_table;
drop table another_table1;
drop table another_table2;
create table base_table (id integer , column1 varchar(20), column2 varchar(20), column3_id varchar(20), alltext varchar( 1));
create table another_table1 (id integer, column1 varchar(20));
create table another_table2 (id integer, column1 varchar(20), column2 varchar(20));
insert into base_table values (1, 'btcol1 row1 TEST1', 'btcol2 row1', 'btcol3 row1', null);
insert into base_table values (2, 'btcol1 row2', 'btcol2 row2', 'btcol3 row2', null);
insert into base_table values (3, 'btcol1 row3', 'btcol2 row3', 'btcol3 row3', null);
insert into base_table values (4, 'btcol1 row4', 'btcol2 row4', 'btcol3 row4', null);
insert into another_table1 values (1, 'at1col1 row1');
insert into another_table1 values (2, 'at1col1 row2 TEST2');
insert into another_table1 values (3, 'at1col1 row3');
insert into another_table1 values (4, 'at1col1 row4');
insert into another_table2 values (1, 'at2col1 row1', 'at2col2 row1');
insert into another_table2 values (2, 'at2col1 row2', 'at2col2 row2');
insert into another_table2 values (3, 'at2col1 row3', 'at2col2 row3 TEST3');
insert into another_table2 values (4, 'at2col1 row4', 'at2col2 row4');
CREATE INDEX idx_base_table_column1_full_text
ON base_table
USING GIN (to_tsvector('english', column1)) ;
CREATE INDEX idx_another_table1_column1_full_text
ON another_table1
USING GIN (to_tsvector('english', column1)) ;
CREATE INDEX idx_another_table2_column2_full_text
ON another_table2
USING GIN (to_tsvector('english', column2)) ;
CREATE VIEW search_items AS
SELECT id, text 'base_table' AS origin_table, column1 AS column1, to_tsvector('english', column1) AS searchable_element FROM base_table
UNION ALL
SELECT id, text 'another_table1' AS origin_table, column1 AS column1, to_tsvector('english', column1) AS searchable_element1 FROM another_table1
UNION ALL
SELECT id, text 'another_table2' AS origin_table, column2 AS column2, to_tsvector('english', column2) AS searchable_element2 FROM another_table2 ;
SELECT id FROM search_items WHERE plainto_tsquery('english', 'test2') @@ searchable_element;
SELECT id FROM search_items WHERE plainto_tsquery('english', 'test1') @@ searchable_element;
SELECT id FROM search_items WHERE plainto_tsquery('english', 'test3') @@ searchable_element;
________________________________
De : CRUMEYROLLE Pierre
Envoyé : mercredi 26 janvier 2022 11:25
À : pgsql-fr-generale(at)postgresql(dot)org <pgsql-fr-generale(at)postgresql(dot)org>
Objet : [ 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)');
/