Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there an easy and fast way to quickly perform a select of a BLOB value from Oracle, copy the result in a notepad and run the corresponding insert in another Oracle to insert data? Data is much longer than 4 K.

I cannot use external tools or copy files because I've access only to the sqlplus tool (PlSql developer, to be clearer) and from what I've seen googling for it is not so easy, so maybe the accepted answer will be simple a "NO, there is no way".

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Rather than using notepad as an intermediary, I expect you'd be much better served simply using the SQL*Plus COPY command, i.e.

COPY FROM source_table@source_tns_alias
       TO destination_table@destination_tns_alias
   CREATE dest_table_name ( column_list)
    USING SELECT column_list 
            FROM source_table_name

will copy the contents of SOURCE_TABLE at SOURCE_TNS_ALIAS to a newly created table DEST_TABLE_NAME at the DESTINATION_TNS_ALIAS. There are also options in the COPY command to load data into an existing table.

share|improve this answer

Unfortunately, BLOBs cannot be copied using the SQL*Plus COPY command (as they haven't done any work on that since Oracle8). Can you create a database link in either of the databases, and copy it over that ?

Failing that, and this may be getting desperate, Tim at Oracle-Base has a PL/SQL based FTP client at www.oracle-base.com. You'd need an FTP server to act as the intermediary though.

There is an email alternative by Michael O'Neill at the cleveridea4data sourceforge project

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.