Re: Oracke BLOB to Postgres BYTEA using ora2pg

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

> Hi,
>
>
>
> These are original data in blob on oracle
>
>
>
> SYS(at)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 text
LANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting('server_encoding'))
$function$

>
> Thanks,
>
> JM
>
>
>
>
>
> *From:* Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> *Sent:* Tuesday, January 15, 2019 9:46 AM
> *To:* Mihalidesová Jana <jana(dot)mihalidesova(at)cetin(dot)cz>
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Oracke BLOB to Postgres BYTEA using ora2pg
>
>
>
> Hi
>
>
>
> út 15. 1. 2019 v 9:40 odesílatel Mihalidesová Jana <
> jana(dot)mihalidesova(at)cetin(dot)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>.
>