Thread: Oracke BLOB to Postgres BYTEA using ora2pg

Oracke BLOB to Postgres BYTEA using ora2pg

From

Mihalidesová Jana

Date:

15 January 2019, 11:39:46

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,

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

(6 rows)

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

encode

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

(6 rows)

nipjd=>

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.

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Pavel Stehule

Date:

15 January 2019, 11:45:45

ú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.

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Achilleas Mantzios

Date:

15 January 2019, 12:12:29

Dobro jutro

On 15/1/19 10:39 π.μ., Mihalidesová Jana wrote:

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.

Assuming your data are jpeg images, try smth like :
psql -c "copy (select encode(serializable_value,'hex') from alf_node_properties_zaloha where serializable_value is not null LIMIT 1) to stdout" | xxd -p -r > foo.jpg

then use some program (gwenview) to see the image.

If you want to dump all images (assuming again we are dealing with images here), then loop over each row and do the same as above.

But it is essential that you know what those values represent. Are they pdf docs? images? binaries? octet-streams?

Thank you for your help,

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

RE: Oracke BLOB to Postgres BYTEA using ora2pg

From

Mihalidesová Jana

Date:

15 January 2019, 13:13:09

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

(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

(6 rows)

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

encode

decode(E'aced0005737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a6578700000000077040000000078', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e312e322e3132313232373031343378', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c000078707710000e322e302e3134303732383033323278', 'hex')

decode(E'aced00057372002c6f72672e616c66726573636f2e7265706f2e6d6f64756c652e4d6f64756c6556657273696f6e4e756d62657277473de5d9c7c28c0c0000787077050003312e3078', 'hex')

(6 rows)

So, how I convert bytea to text?

Thanks,

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

ú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.

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Pavel Stehule

Date:

15 January 2019, 13:37:43

ú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.

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Pavel Stehule

Date:

15 January 2019, 13:56:15

út 15. 1. 2019 v 11:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

ú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 think so it its correct, but the string is in utf16, not in utf8

So the data looks ok, but are not visible in Postgres. Try to use Java application - or some client, that can draw utf16 content

Pavel

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.

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Pavel Stehule

Date:

15 January 2019, 14:44:41

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 was wrong - it looks like utf8 - but still it is not possible to see these data, because there are lot of 00 chars, that are disallowed in Postgres.

select bytea_to_text(decode(E'7372136a6176612e7574696c2e41727261794c69737478', 'hex'));
┌────────────────────────────┐
│ bytea_to_text │
╞════════════════════════════╡
│ sr\x13java.util.ArrayListx │
└────────────────────────────┘
(1 row)

so looks so these data are correctly passed, but PostgreSQL utf8 routines are much more stricter - and you cannot to convert these really binary data to string

Pavel

RE: Oracke BLOB to Postgres BYTEA using ora2pg

From

"Daniel Verite"

Date:

15 January 2019, 14:56:05

Mihalidesová Jana wrote:

> nipjd=> select distinct encode(serializable_value, 'escape') from
> alf_node_properties_zaloha where serializable_value is not null;
>                                                                                                             encode
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d494e4f52',
> 'hex')

These contents are code, in the form of function calls, instead of data.
Possibly if you ran these function calls they would output the
original data, but that's a pretty weird form of storage.  If the
insert was done by Ora2Pg, you want to report this as a bug.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

Pavel Stehule

Date:

16 January 2019, 08:50:56

út 15. 1. 2019 v 12:56 odesílatel Daniel Verite <daniel@manitou-mail.org> napsal:

Mihalidesová Jana wrote:

> nipjd=> select distinct encode(serializable_value, 'escape') from
> alf_node_properties_zaloha where serializable_value is not null;
> encode
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d494e4f52',
> 'hex')

These contents are code, in the form of function calls, instead of data.
Possibly if you ran these function calls they would output the
original data, but that's a pretty weird form of storage. If the
insert was done by Ora2Pg, you want to report this as a bug.

https://github.com/darold/ora2pg

a author is very cooperative

Regards

Pavel

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Oracke BLOB to Postgres BYTEA using ora2pg

From

"Peter J. Holzer"

Date:

18 January 2019, 22:32:48

On 2019-01-15 12:56:05 +0100, Daniel Verite wrote:
>     Mihalidesová Jana wrote:
> > nipjd=> select distinct encode(serializable_value, 'escape') from
> > alf_node_properties_zaloha where serializable_value is not null;
> >                                                                                                             encode
> >
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
decode(E'aced00057e72002c6f72672e616c66726573636f2e736572766963652e636d722e76657273696f6e2e56657273696f6e5479706500000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400054d494e4f52',
> > 'hex')
>
> These contents are code, in the form of function calls, instead of data.

I doubt this (code in what language?). Since the column name is
"serializable_value" and there are what looks like Java class names
embedded in the blob ("org.alfresco.service.cmr.version.VersionType",
"java.lang.Enum", ...) these are most likely Java objects in
java serialization format.

The OP should be able to deserialize them with Java. I would expect the
JDBC to return a proper byte array (or maybe a ByteBuffer). That the
field looks different (hex in psql, raw in sqlplus) is most likely
a red herring - that's just for the benefit of humans, but humans can't
read binary data directly.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment

signature.asc