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.steh...@gmail.com>
Sent: Tuesday, January 15, 2019 9:46 AM
To: Mihalidesová Jana <jana.mihalides...@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.mihalides...@cetin.cz<mailto:jana.mihalides...@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<https://www.cetin.cz/cs/jak-cetin-vyjednava-o-smlouve>.
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<https://www.cetin.cz/en/jak-cetin-vyjednava-o-smlouve>.
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<https://www.cetin.cz/cs/jak-cetin-vyjednava-o-smlouve>.
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<https://www.cetin.cz/en/jak-cetin-vyjednava-o-smlouve>.
