Re: Oracke BLOB to Postgres BYTEA using ora2pg

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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