Re: Oracke BLOB to Postgres BYTEA using ora2pg - Mailing list pgsql-general

út 15. 1. 2019 v 11:13 odesílatel Mihalidesová Jana <jana.mihalidesova@cetin.cz> napsal:

Hi,

 

These are original data in blob on oracle

 

SYS@CENIPR_1 > select distinct UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(serializable_value, 4000,1)) from NIP_NE.ALF_NODE_PROPERTIES where serializable_value is not null;

 

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(SERIALIZABLE_VALUE,4000,1))

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

▒▒ sr ,org.alfresco.repo.module.ModuleVersionNumberwG=▒▒▒�

  xpw 1.0x

▒▒ sr ,org.alfresco.repo.module.ModuleVersionNumberwG=▒▒▒�

  xpw 1.2.1212270143x

▒▒ sr java.util.ArrayListx▒▒▒▒a▒ I sizexp    w    x

▒▒ ~r ,org.alfresco.service.cmr.version.VersionType          xr java.lang.Enum          xpt MAJOR

▒▒ ~r ,org.alfresco.service.cmr.version.VersionType          xr java.lang.Enum          xpt MINOR

▒▒ sr ,org.alfresco.repo.module.ModuleVersionNumberwG=▒▒▒�

 xpw 2.0.1407280322x

 

After import into the postgres using ora2pg the data looks

 

 

 

nipjd=> select distinct serializable_value from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                                                                                                                                       serializable_value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

\x6465636f6465284527616365643030303537653732303032633666373236373265363136633636373236353733363336663265373336353732373636393633363532653633366437323265373636353732373336393666366532653536363537323733363936663665353437393730363530303030

\x6465636f6465284527616365643030303537333732303031333661363137363631326537353734363936633265343137323732363137393463363937333734373838316432316439396337363139643033303030313439303030343733363937613635373837303030303030303030373730343030

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537653732303032633666373236373265363136633636373236353733363336663265373336353732373636393633363532653633366437323265373636353732373336393666366532653536363537323733363936663665353437393730363530303030

(6 rows)

 

nipjd=>

 

when I use convert function to text, the result is

 

nipjd=> select distinct encode(serializable_value, 'hex') from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                                                                                                                                            encode

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353765373230303263366637323637326536313663363637323635373336333666326537333635373237363639363336353265363336643732326537363635373237333639366636653265353636353732373336393666366535343739373036353030303030

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353765373230303263366637323637326536313663363637323635373336333666326537333635373237363639363336353265363336643732326537363635373237333639366636653265353636353732373336393666366535343739373036353030303030

6465636f646528452761636564303030353733373230303133366136313736363132653735373436393663326534313732373236313739346336393733373437383831643231643939633736313964303330303031343930303034373336393761363537383730303030303030303037373034303030

(6 rows)

 

 

or

 

nipjd=> select distinct encode(serializable_value, 'escape') from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                             encode

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d494e4f52', 'hex')

decode(E'aced0005737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a6578700000000077040000000078', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e312e322e3132313232373031343378', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e322e302e3134303732383033323278', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c0000787077050003312e3078', 'hex')

decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d414a4f52', 'hex')

(6 rows)

 

So, how I convert bytea to text?

I use a function - when encoded data are in server encoding. looks like a issue, because Postgres uses every where utf8, and this doesn't look like it - maybe utf16

So first question is - what encoding is used on Oracle side?

CREATE OR REPLACE FUNCTION public.bytea_to_text(bytea)RETURNS textLANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting('server_encoding'))
$function$

 

Thanks,

JM

 

 

From: Pavel Stehule <pavel.stehule@gmail.com>
Sent: Tuesday, January 15, 2019 9:46 AM
To: Mihalidesová Jana <jana.mihalidesova@cetin.cz>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Oracke BLOB to Postgres BYTEA using ora2pg

 

Hi

 

út 15. 1. 2019 v 9:40 odesílatel Mihalidesová Jana <jana.mihalidesova@cetin.cz> napsal:

Hi,

 

We try to migrate from oracle to postgres using ora2pg but we hit some weird behavior of bytea. Or it’s just our ignorance.

Table migration were ok, but we are not able to read bytea data. What we did wrong.

 

Thank you for your help,

JM

 

nipjd=> \d alf_node_properties_zaloha

                  Table "nip_dms.alf_node_properties_zaloha"

       Column       |          Type           | Collation | Nullable | Default

--------------------+-------------------------+-----------+----------+---------

node_id            | bigint                  |           |          |

actual_type_n      | integer                 |           |          |

persisted_type_n   | integer                 |           |          |

boolean_value      | boolean                 |           |          |

long_value         | bigint                  |           |          |

float_value        | real                    |           |          |

double_value       | double precision        |           |          |

string_value       | character varying(1024) |           |          |

serializable_value | bytea                   |           |          |

qname_id           | bigint                  |           |          |

list_index         | integer                 |           |          |

locale_id          | bigint                  |           |          |

 

 

nipjd=> select distinct (serializable_value) from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                                                                                                                                       serializable_value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

\x6465636f6465284527616365643030303537653732303032633666373236373265363136633636373236353733363336663265373336353732373636393633363532653633366437323265373636353732373336393666366532653536363537323733363936663665353437393730363530303030

\x6465636f6465284527616365643030303537333732303031333661363137363631326537353734363936633265343137323732363137393463363937333734373838316432316439396337363139643033303030313439303030343733363937613635373837303030303030303030373730343030

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537333732303032633666373236373265363136633636373236353733363336663265373236353730366632653664366636343735366336353265346436663634373536633635353636353732373336393666366534653735366436323635373237373437

\x6465636f6465284527616365643030303537653732303032633666373236373265363136633636373236353733363336663265373336353732373636393633363532653633366437323265373636353732373336393666366532653536363537323733363936663665353437393730363530303030

(6 rows)

 

 

nipjd=> select distinct encode(serializable_value, 'hex') from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                                                                                                                                            encode

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353765373230303263366637323637326536313663363637323635373336333666326537333635373237363639363336353265363336643732326537363635373237333639366636653265353636353732373336393666366535343739373036353030303030

6465636f646528452761636564303030353733373230303263366637323637326536313663363637323635373336333666326537323635373036663265366436663634373536633635326534643666363437353663363535363635373237333639366636653465373536643632363537323737343733

6465636f646528452761636564303030353765373230303263366637323637326536313663363637323635373336333666326537333635373237363639363336353265363336643732326537363635373237333639366636653265353636353732373336393666366535343739373036353030303030

6465636f646528452761636564303030353733373230303133366136313736363132653735373436393663326534313732373236313739346336393733373437383831643231643939633736313964303330303031343930303034373336393761363537383730303030303030303037373034303030

(6 rows)

 

 

nipjd=> select distinct encode(serializable_value, 'escape') from alf_node_properties_zaloha where serializable_value is not null;

                                                                                                             encode

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d494e4f52', 'hex')

decode(E'aced0005737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a6578700000000077040000000078', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e312e322e3132313232373031343378', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e322e302e3134303732383033323278', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c0000787077050003312e3078', 'hex')

decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d414a4f52', 'hex')

(6 rows)

 

nipjd=>

 

 

Unfortunately, it is not clean, what is wrong?

 

What is original data?

 

Pavel

 

Obsah této zprávy má výlučně komunikační charakter. Nepředstavuje návrh na uzavření smlouvy či na její změnu ani přijetí případného návrhu. Smlouvy či jejich změny jsou společností Česká telekomunikační infrastruktura a.s. uzavírány v písemné formě nebo v podobě a postupem podle příslušných všeobecných podmínek společnosti Česká telekomunikační infrastruktura a.s., a pokud jsou dohodnuty všechny náležitosti. Smlouvy jsou uzavírány oprávněnou osobou na základě písemného pověření. Smlouvy o smlouvě budoucí jsou uzavírány výhradně v písemné formě, vlastnoručně podepsané nebo s uznávaným elektronickým podpisem. Podmínky, za nichž Česká telekomunikační infrastruktura a.s. přistupuje k jednání o smlouvě a jakými se řídí, jsou dostupné zde.

The content of this message is intended for communication purposes only. It does neither represent any contract proposal, nor its amendment or acceptance of any potential contract proposal. Česká telekomunikační infrastruktura a.s. concludes contracts or amendments thereto in a written form or in the form and the procedure in accordance with relevant general terms and conditions of Česká telekomunikační infrastruktura a.s., if all requirements are agreed. Contracts are concluded by an authorized person entitled on the basis of a written authorization. Contracts on a future contract are concluded solely in a written form, self-signed or signed by means of an advanced electronic signature. The conditions under which Česká telekomunikační infrastruktura a.s. negotiates contracts and under which it proceeds are available here.

Obsah této zprávy má výlučně komunikační charakter. Nepředstavuje návrh na uzavření smlouvy či na její změnu ani přijetí případného návrhu. Smlouvy či jejich změny jsou společností Česká telekomunikační infrastruktura a.s. uzavírány v písemné formě nebo v podobě a postupem podle příslušných všeobecných podmínek společnosti Česká telekomunikační infrastruktura a.s., a pokud jsou dohodnuty všechny náležitosti. Smlouvy jsou uzavírány oprávněnou osobou na základě písemného pověření. Smlouvy o smlouvě budoucí jsou uzavírány výhradně v písemné formě, vlastnoručně podepsané nebo s uznávaným elektronickým podpisem. Podmínky, za nichž Česká telekomunikační infrastruktura a.s. přistupuje k jednání o smlouvě a jakými se řídí, jsou dostupné zde.

The content of this message is intended for communication purposes only. It does neither represent any contract proposal, nor its amendment or acceptance of any potential contract proposal. Česká telekomunikační infrastruktura a.s. concludes contracts or amendments thereto in a written form or in the form and the procedure in accordance with relevant general terms and conditions of Česká telekomunikační infrastruktura a.s., if all requirements are agreed. Contracts are concluded by an authorized person entitled on the basis of a written authorization. Contracts on a future contract are concluded solely in a written form, self-signed or signed by means of an advanced electronic signature. The conditions under which Česká telekomunikační infrastruktura a.s. negotiates contracts and under which it proceeds are available here.