Search code examples
oraclebloboracle-sqldeveloper

how to obtain blob type data for insertion in oracle?

hello friends I have a problem with the blob data type, I want to migrate some data from one bd to another bd however I have not been able to some tables that have blob type columns, what I have tried is to export a single record in the following way.

first I make a select of the record I want to export to my other bd

select TEMPLATE_DOCUMENT_ID,blob_file  from example_table where template_document_id = 32;

then I export the result to obtain the insert

enter image description here

I configure as follows

enter image description here

when I do this I get a script with the data of the record that I want to migrate

enter image description here

if I run this it gives me the following error

Error report -
ORA-01465: invalid hex number

Do you have any idea how I could get the correct data to make my insert?

NOTE: MIGRATION IS DONE FROM ONE ORACLE DATABASE TO ANOTHER ORACLE DATABASE.

Solution

  • Obviously the source database is Oracle. You did not mention what is the target database. In case it is Oracle as well I would suggest using the Oracle Data Pump tool (expdp/impdp). Doc is here: https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm

    In case you need it, at least I use it quite often is the VIEW_AS_TABLE option of the tool as it allows me to export a subset of the data.