Re: Oracke BLOB to Postgres BYTEA using ora2pg

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

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