Home » SQL & PL/SQL » Client Tools » Insert postgresql BYTEA datatype data into oracle blob column (11.2.0.1.0)
Insert postgresql BYTEA datatype data into oracle blob column [message #639312] Mon, 06 July 2015 01:58 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I am migrating my database from postgresql to oracle. I have converted the BYTEA postgresql datatype to BLOB datatype in oracle.
But when I insert the data into oracle, getting the error 'ORA-01704: string literal too long'.


create table test(a1 number, b1 blob);

Below is my data from Postgresql.
insert into test values(1,'\x5a6c4237000026d57801a5...);



Please suggest if I am doing wrong or is there any other way.


[EDITED by LF: shortened a long '\x5a6...' string]

[Updated on: Mon, 06 July 2015 02:57] by Moderator

Report message to a moderator

Re: Insert postgresql BYTEA datatype data into oracle blob column [message #639315 is a reply to message #639312] Mon, 06 July 2015 02:44 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many things are wrong and the first one is that '\x...' does not match for a binary data.
You cannot insert binary data like that in a BLOB, you have to use LOB functions.
Now it depends where your current data are.
If they are in a file you can use SQL*Loader or DBMS_LOB.LOADFROMFILE function.
If the data are not too big you can use UTL_RAW to convert a string to binary data.

[Updated on: Mon, 06 July 2015 02:46]

Report message to a moderator

Previous Topic: how to clean recyclebin in 11g
Next Topic: Displaying Object Name after creating it in sql plus
Goto Forum:
  


Current Time: Tue Jan 16 14:15:53 CST 2018

Total time taken to generate the page: 0.01272 seconds