14

I have a table "Images" with two fields:

  • Name VARCHAR2
  • Data BLOB

I would like to export that table to a .sql file which I could import on another system. I tried to do so using the "Database unload" assistant of Oracle SQL Developer. However the generated file does just have the content for the names in it but not the data. Thus after importing I would end up with all the names but the data field would be null everywhere.

I'd really prefer it just to be one file (I saw some examples that included dumping the data to one file per field on the fs...)

Is it possible to generate such a script with SQL Developer? or is there any other way/tool to do so?

Improve this question
1
  • 1
    Are you able to use data pump to export and import on the servers, rather than from the SQL Developer client? Commented Nov 23, 2012 at 17:41

5 Answers 5

Reset to default
11

I don't think this is possible with SQL Developer (but then I don't use it very often).

The SQL client I am using - SQL Workbench/J - can do this.

There are several ways to export this data.

Generate a proprietary script

It can create a SQL script that uses a special (tool specific) notation to reference an external file, something like:

INSERT INTO images
  (name, data)
VALUES
  ('foobar', {$blobfile='blob_r1_c2.data'});

The above statement can only be executed with SQL Workbench again. It is not compatible with any other SQL client.

Use utl_raw

Another alternative is to use a "blob literal", but due to Oracle's limit on 4000 bytes for a character literal, this only works for really small blob values:

INSERT INTO images
  (name, data)
VALUES
  ('foobar', to_blob(utl_raw.cast_to_raw('......')));

where the character literal for the cast_to_raw call would contain the hex values of the BLOB. As this requires 2 characters per "blob byte", you can't handle BLOBs larger than 2000 bytes with that. But that syntax would work for nearly all Oracle SQL tools (if they can handle scripts with very long lines).

SQL*Loader input file

The third alternative is to export the data into a text file that can be imported using SQL*Loader:

The text file would contain something like this:

NAME    DATA
foobar  blob_r1_c2.data

Together with the following SQL*Loader control file:

OPTIONS (skip=1)
LOAD DATA CHARACTERSET 'WE8ISO8859P15'
INFILE 'images.txt'
APPEND
INTO TABLE IMAGES
FIELDS TERMINATED BY '\t' TRAILING NULLCOLS
(
  NAME,
  lob_file_data FILLER,
  DATA LOBFILE(lob_file_data) TERMINATED BY EOF
)

This can be loaded using SQL*Loader and is thus doesn't need SQL Workbench to import the data.

More details are in the manual

Edit

As Alex has pointed out in his comment, you can also use a DataPump export - but that requires that you have access to the file system on the server. The above solutions all store the data on the client.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

If you absolutely need to use a single .sql file to import the BLOB you can generate the script using PL/SQL:

set serveroutput on
declare
  lob_in blob;
  i integer := 0;
  lob_size integer;
  buffer_size integer := 1000;
  buffer raw(32767);
begin
  select
    data, dbms_lob.getlength(data)
    into lob_in, lob_size
  from images
  where name = 'example.png';

  for i in 0 .. (lob_size / buffer_size) loop
    buffer := dbms_lob.substr(lob_in, buffer_size, i * buffer_size + 1);
    dbms_output.put('dbms_lob.append(lob_out, hextoraw(''');
    dbms_output.put(rawtohex(buffer));
    dbms_output.put_line('''));');
  end loop;
end;

Its output will be the BLOB's content encoded like:

dbms_lob.append(lob_out, hextoraw('FFD8FFE0...0000'));
dbms_lob.append(lob_out, hextoraw('00000000...0000'));
...
dbms_lob.append(lob_out, hextoraw('007FFFD9'));

Which you can load into an already inserted row with PL/SQL:

declare
  lob_out blob;
begin
  select data into lob_out
  from images
  where name = 'example.png'
  for update;

  dbms_lob.append(lob_out, hextoraw('FFD8FFE0...0000'));
  dbms_lob.append(lob_out, hextoraw('00000000...0000'));
  ...
  dbms_lob.append(lob_out, hextoraw('007FFFD9'));
end;

Just remember the resulting .sql file will be huge.

Improve this answer

5 Comments

Where does this get written to? I ran the block on a database that I only have access to a specific set of tables and it ran but I am not sure where it has written it to and I was wondering if I could actually have this outputted to my local.
@Julaayi the first block uses dbms_output.put_line, a procedure that sends messages to STDOUT when run on SQL*Plus after enabling it with SERVER OUTPUT ON.
sorry am not an expert with PL/SQL. Are you saying that I should run this through SQL*Plus to write files to my local?
It works for me - thanks a lot. I had to adapt the first script to (lob_size / buffer_size)-1 instead of (lob_size / buffer_size).
Thanks for the solution! But, please, use ceil(lob_size / buffer_size) -1 as more accurate expression instead both (lob_size / buffer_size) and (lob_size / buffer_size)-1 . First one will make us one extra loop and give extra empty hextoraw('') when BLOBs content is exactly divided into complete blocks without a remainder. Second is more dangerous, because risk missing the last partial block.
3

Thx for your answer. I used the third alternative. First I downloaded SQL Workbench/J. Then I used the following command to make an export:

WbExport -type=text -file='c:\temp\Images' delimiter='|' -decimal=',' -sourcetable=Images -formatfile=oracle;

This produced a Images.txt file and many Images_r*_c2.data files and a Images.ctl file.

I could then use the following command to import:

sqlldr myuser@myhost/mypassword control=Images.ctl
Improve this answer

1 Comment

Corrected link : uhlme.ch/oracle-exportingimporting-tables-containing-clob-data
2

This is definitely possible in SQL developer.

  • First you need to export the table in the source location choosing appropriate table(s).

Tools > Database Export

  • Select output format as loader rather than insert , excel which we normally use.

Following these steps would create sqlldr control files and data files and also the create table ddl if you chose the option.You can use them to import(sqlldr) data in the destination.

This is a better solution and is portable in terms of extraction and distribution . It gives the flexibility of delivering components to be deployed through code repositories.

Here is a link that explains it step by step.

Exporting Multiple BLOBs with Oracle SQL Developer

Improve this answer

Comments

0

SQL workbench uses a special file format for blob data, in addition to .sql. If you can accept such files, an even simpler solution is to use Oracle's Original import and export. (It is deprecated, but unlike Oracle's DataPump, it does not require access rights on the server.)

Here is a nice tutorial on the export part.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.