RE: Oracke BLOB to Postgres BYTEA using ora2pg

Mihalidesová Jana <jana.mihalidesova@xxxxxxxx> · Tue, 15 Jan 2019 10:13:09 +0000

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?

Thanks,
JM

From: Pavel Stehule <pavel.stehule@xxxxxxxxx> 

Sent: Tuesday, January 15, 2019 9:46 AM

To: Mihalidesová Jana <jana.mihalidesova@xxxxxxxx>

Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx

Subject: Re: Oracke BLOB to Postgres BYTEA using ora2pg

Hi

út 15. 1. 2019 v 9:40 odesílatel Mihalidesová Jana <jana.mihalidesova@xxxxxxxx> 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.