Oracle, Blob data are missing in my export file

Question

I use Oracle 11g, I am trying to export my data(only the data, not table create scripts etc.) So it can be imported by customer to their database

When I use Oracle Sql developer's export database, it only exports the data but my BLOB's are missing! There are a lot of image files in one of my tables which I want to export as well.

I wonder If I really need to use oracle exp imp tool;

http://docs.oracle.com/cd/B28359_01/server.111/b28319/exp_imp.htm#i1004777

Any idea?

Answer 1

From the documentation:

Format: Select the desired output format for the data to be unloaded. Depending on the selected format, other options may appear. For example, for xls (Microsoft Excel file), you can specify worksheet names for the data and the SELECT statement.

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

It doesn't explicitly refer to BLOBs, but if CLOBs can only be exported as loader or pdf, it makes sense that BLOBs would also have that restriction. If you want to recreate this data in another schema or database, SQL*Loader format seems like a good choice.

What would you expect the insert statement to look like? You'd have to have a text literal containing the binary value, which is a problem in itself, but you'd also be limited to 4k - which many image files might exceed. For a CLOB it might give you the first 4903 chars followed by an ellipsis in the string literal, but I'm not sure; for a BLOB even that wouldn't make any sense.

If you want to transfer data between databases you should consider data pump export/import, or if you (or your client) are restricted by server access then you could fall back to legacy export/import. Both support LOBs. Data pump is superior and should be used in preference if at all possible. The only downside really is that the dump files are written to the database server and getting access to them (or permissions on a directory object to write to can be problematic in some organisations.