Geek Answers Handbook

My export file is missing Oracle, Blob data

I am using Oracle 11g, I am trying to export my data (only data, not tables creating scripts, etc.). Thus, it can be imported by the client into its database

When I use the Oracle Sql export database, it only exports data, but my BLOB is missing! One of my tables has many image files that I want to export.

I wonder if I really need to use the oracle exp imp tool,

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

Any idea?

+4
oracle oracle11g oracle-sqldeveloper
source share
2 answers

From the documentation :

Format . Select the desired output format for the data being uploaded. Other options may appear depending on the selected format. For example, for xls (a Microsoft Excel file), you can specify worksheet names for data and a SELECT statement.

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

It does not explicitly reference BLOBs, but if CLOB can only be exported as a loader or PDF, it makes sense that BLOB will also have this limitation. If you want to recreate this data in another schema or database, the SQL * Loader format looks like a good choice.

What do you expect from insert ? You should have a text literal containing a binary value, which in itself is a problem, but you should also be limited to 4k - which may exceed the number of image files. For CLOB, this can give you the first 4903 characters followed by an ellipsis in a string literal, but I'm not sure; for BLOB even this makes no sense.

If you want to transfer data between databases, you should consider export / import of pump data , or if you (or your client) are limited by access to the server, then you can return to outdated export / import . Both support LOB. The data pump is excellent and should be used, if at all possible. The only drawback is that dump files are written to and accessed by the database server (or permissions on the directory object to write may be problematic in some organizations.

+6
source share

Any LOB cannot be inserted (export / import) directly as regular data. You need to write a place / SQL block to get blob from db and write it to. Check out this link so you will definitely do it.

http://www.dba-oracle.com/t_writing_blob_clob_os_file.htm Greeting At

0
source share

More articles:


All Articles