How to export clob field datas in oracle sql developer

Question

How to export clob field data's in oracle sql developer. Currently clob field data's can't export in oracle sql developer.

Kike

Possible duplicate? What kind of export are you trying to do? – Leverett

@AlexPoole I want to export clob data's in sql format. So i can re insert same data's when i want. – Kike

You mean as a list of insert statements? As the linked answer asks, what would expect the insert statement to look like in that case, particularly when the CLOB values exceeds 4k? What's wrong with a data pump export? – Leverett

Answer 1

If you don't want to (or can't) export and import your data, and really want it as a set of insert statements, you can use SQL Developer's built-in formatting tools to automatically split your CLOBs into multiple chunks that are small enough to be valid as string literals, and then spool the result to a file:

spool clob_export.sql
select /*insert*/ * from your_table;
spool off

With more recent versions you can use the sqlformat command to control the output format without needing to modify the query; this is equivalent:

set sqlformat insert
spool clob_export.sql
select * from your_table;
spool off

The generated insert statements will look something like:

REM INSERTING into YOUR_TABLE
SET DEFINE OFF;
Insert into YOUR_TABLE (ID,CLOB_COLUMN) values (1,TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
...
|| TO_CLOB('... up to 4k of characters with quotes escaped ...'));

Leverett

Important information for users of SQL Developer: In order for this to work you cannot execute the lines separately. You need to execute them as a Script. – Apthorp

I had some statements incorrectly have a double tick mark. I had to manually clean those up. Example: TO_CLOB('' should have been TO_CLOB(' – Solicitor

Answer 2

I had data in a NCLOB field that did not show up in the exported data when I right clicked the table and selected Export... as shown below.

Below is the approach that worked.

I opened the table and switched to the Data tab, right clicked a random field and chose Export..., clicked through the wizard and the NCLOB field was present in the exported sql statements.

Oresund

Works just like I needed it to! And it's the easiest solution. Clob is imported in my other table, just like I wanted. – Modestamodeste

Answer 3

The following EXP_IMP_LOB package can export and import CLOB, NCLOB, BLOB type column data using simple SQL (text) files.

How to use:

first of all install the package onto both source and target schemas. To export run this select

select * from table( EXP_IMP_LOB.EXPORT('table_name','lob_column_name','condition') );

where the Table_Name and LOB_Column_Name define the data column and the optional Condition defines the the row or rows. If there is no condition, then every row data will be exported row by row.

Example:

select * from table( EXP_IMP_LOB.EXPORT('person','image','id=103' ) );

Result:

/******************************************************
    TABLE  :PERSON
    COLUMN :IMAGE
    ROW    :103
******************************************************/
BEGIN
    EXP_IMP_LOB.IMPORT_NEW;
    EXP_IMP_LOB.IMPORT_APPEND ( 'FFD8FFE000104A464....23232323232');
    EXP_IMP_LOB.IMPORT_APPEND ( '32323232323232323....798999AA2A3');
    .........
    EXP_IMP_LOB.IMPORT_APPEND ( 'B2316524267279AA9....51401FFFD9');
    EXP_IMP_LOB.IMPORT_UPDATE ( 'PERSON','IMAGE','103' ); 
    COMMIT;
END;
/   

So, the export converts the binary data to 400 char length hexa strings and creates a script from it. I used ..... to symbolize many chars, because that is only a sample above.

DO NOT SORT THE RESULT!

To import, you only have to install the package onto the target schema too and run this script above in the target schema. That's all.

...more:

• The source and target table name, column name must be the same!
• The Table (both source and target) must have Primary key and they must be identical.
• The EXPORT function can detect the primary key automatically. Theoretically it can manage composed keys too...
• The size of a hexa string is defined in G_LENGTH global variable. 200 chars means 400 hexa chars.
• The additional procedures:
• IMPORT_NEW : resets the package variables to prepare it to accept a new LOB
• IMPORT_APPEND : converts the hexa string to a binary data and append it the package variable
• IMPORT_UPDATE : updates the given table, row, column with the package variable
• DIRECT_SQL : executes the given SQL using the global LOB variable as a parameter. eg: EXP_IMP_LOB.DIRECT_SQL( 'insert into ANY_TABLE ( ID, IMAGE ) values ( 123, :1 )' );

---

/*============================================================================================*/
create or replace package EXP_IMP_LOB is
/*============================================================================================*/

  type T_STRING_LIST is table of varchar2( 32000 );

    ---------------------------------------------------------------------------
    function  EXPORT ( I_TABLE_NAME  in varchar2
                     , I_COLUMN_NAME in varchar2
                     , I_WHERE       in varchar2 default null
                     ) return T_STRING_LIST pipelined;
    ---------------------------------------------------------------------------

    ---------------------------------------------------------------------------
    procedure IMPORT_NEW;
    ---------------------------------------------------------------------------

    ---------------------------------------------------------------------------
    procedure IMPORT_APPEND ( I_RAW         in varchar2);
    ---------------------------------------------------------------------------

    ---------------------------------------------------------------------------
    procedure DIRECT_SQL ( I_SQL  in varchar2 );
    ---------------------------------------------------------------------------

    ---------------------------------------------------------------------------
    procedure IMPORT_UPDATE ( I_TABLE_NAME  in varchar2
                            , I_COLUMN_NAME in varchar2
                            , I_PK          in varchar2
                            );
    ---------------------------------------------------------------------------

end;
/



/*============================================================================================*/
create or replace package body EXP_IMP_LOB is
/*============================================================================================*/


    G_TABLE_NAME    varchar(   40 );
    G_COLUMN_NAME   varchar(   40 );
    G_COLUMN_TYPE   varchar(   40 );
    G_PK_KEY        varchar( 4000 );
    G_PK_LST        varchar( 4000 );
    G_LENGTH        number := 200;
    G_BLOB          blob;
    G_CLOB          clob;

---------------------------------------------------------------------------
procedure GET_PK ( I_TABLE_NAME in varchar ) is
---------------------------------------------------------------------------
    L_SEP           varchar ( 40 ) := ',';
    L_DATA_TYPE     varchar2( 30 );
begin
    G_PK_KEY := '';
    G_PK_LST := '';
    for L_A_PK in ( select COLUMN_NAME
                      from USER_CONSTRAINTS UC
                         , USER_CONS_COLUMNS DBC
                     where UC.CONSTRAINT_TYPE  = 'P'
                       and DBC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                       and DBC.TABLE_NAME      = I_TABLE_NAME 
                     order by position 
                  ) 
    loop
        if nvl( length( G_PK_KEY ), 0 ) + length( L_A_PK.COLUMN_NAME ) < 4000 then
            select DATA_TYPE into L_DATA_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = L_A_PK.COLUMN_NAME;
            if instr( L_DATA_TYPE, 'CHAR') > 0 then
                G_PK_KEY := G_PK_KEY||'''''''''||'||L_A_PK.COLUMN_NAME||'||''''''''||'''||L_SEP||'''||';
            elsif instr( L_DATA_TYPE, 'DATE') > 0 then
                G_PK_KEY := G_PK_KEY||'''TO_DATE(''''''||TO_CHAR('||L_A_PK.COLUMN_NAME||',''YYYY.MM.DD HH24:MI:SS'')||'''''',''''YYYY.MM.DD HH24:MI:SS'''')''||'''||L_SEP||'''||';
            else
                G_PK_KEY := G_PK_KEY||L_A_PK.COLUMN_NAME||'||'''||L_SEP||'''||';
            end if;
            G_PK_LST := G_PK_LST||L_A_PK.COLUMN_NAME||L_SEP;
        end if;
    end loop;
    G_PK_KEY := substr( G_PK_KEY, 1, length( G_PK_KEY ) - ( 6 + length( L_SEP ) ) );
    G_PK_LST := substr( G_PK_LST, 1, length( G_PK_LST ) - length(L_SEP));
end;

---------------------------------------------------------------------------
function EXPORT ( I_TABLE_NAME  in varchar2
                , I_COLUMN_NAME in varchar2
                , I_WHERE       in varchar2 default null
                ) return T_STRING_LIST pipelined is
---------------------------------------------------------------------------
    V_BLOB          blob;
    V_CLOB          clob;
    V_CUR_SQL       varchar( 32000 );
    V_LOB_SQL       varchar( 32000 );
    V_RAW           varchar( 32000 );
    V_START         number;
    V_PK            varchar(  4000 );
    V_REC_SET       sys_refcursor; 

begin
    G_TABLE_NAME  := upper( trim( I_TABLE_NAME  ) );
    G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
    GET_PK( G_TABLE_NAME );
    select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
    if G_COLUMN_TYPE not in ('CLOB','NCLOB','BLOB') then
        raise_application_error ( -20001, 'The type of column '||I_COLUMN_NAME||' is not CLOB, NCLOB or BLOB' );    
    end if;

    V_CUR_SQL := 'select '||G_PK_KEY||' from '||G_TABLE_NAME||' where '||nvl( I_WHERE, ' 1 = 1 ');
    open V_REC_SET for V_CUR_SQL;
    loop
        fetch V_REC_SET into V_PK;
        exit when V_REC_SET%notfound; 
        PIPE ROW( '/******************************************************' );
        PIPE ROW( '   TABLE  :'||G_TABLE_NAME                               );
        PIPE ROW( '   COLUMN :'||G_COLUMN_NAME                              );
        PIPE ROW( '   ROW    :'||V_PK                                       );
        PIPE ROW( '******************************************************/' );
        PIPE ROW( 'BEGIN'                                                   );
        PIPE ROW( '   EXP_IMP_LOB.IMPORT_NEW;'                              );
        V_LOB_SQL := 'select '||G_COLUMN_NAME||' from '||G_TABLE_NAME||' where ('||G_PK_LST||') in ( select '||V_PK||' from dual )';

        if G_COLUMN_TYPE = 'BLOB' then
            execute immediate V_LOB_SQL into V_BLOB;
            if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
                V_START := 1;
                for L_I IN 1..ceil( dbms_lob.getlength( V_BLOB ) / G_LENGTH )
                loop
                    V_RAW   := dbms_lob.substr( V_BLOB, G_LENGTH, V_START );
                    PIPE ROW( '   EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');'         );
                    V_START := V_START + G_LENGTH;
                end loop;
                PIPE ROW( '   EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
                PIPE ROW( '   COMMIT;'                                              );
            end if;
        else
            execute immediate V_LOB_SQL into V_CLOB;
            if nvl( dbms_lob.getlength( V_CLOB ), 0 ) > 0 then
                V_START := 1;
                for L_I IN 1..ceil( dbms_lob.getlength( V_CLOB ) / G_LENGTH )
                loop
                    V_RAW   := UTL_RAW.CAST_TO_RAW( dbms_lob.substr( V_CLOB, G_LENGTH, V_START ) );
                    PIPE ROW( '   EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');'         );
                    V_START := V_START + G_LENGTH;
                end loop;
                PIPE ROW( '   EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
                PIPE ROW( '   COMMIT;'                                              );
            end if;
        end if;
        PIPE ROW( 'END;'                                                    );   
        PIPE ROW( '/'                                                       );
        PIPE ROW( ' '                                                       );
    end loop;
    close V_REC_SET;

    return;

end;

---------------------------------------------------------------------------
procedure IMPORT_NEW is
---------------------------------------------------------------------------
begin
    G_BLOB := null;
    G_CLOB := null;
end;

---------------------------------------------------------------------------
procedure IMPORT_APPEND ( I_RAW         in varchar2 ) is
---------------------------------------------------------------------------
    V_BLOB          blob;
begin
    V_BLOB := hextoraw( I_RAW );
    if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
        if nvl( dbms_lob.getlength( G_BLOB ), 0 ) = 0 then 
            G_BLOB := V_BLOB;
        else
            DBMS_LOB.APPEND( G_BLOB, V_BLOB );
        end if;
    end if;       
end;

---------------------------------------------------------------------------
procedure DIRECT_SQL ( I_SQL  in varchar2 ) is
---------------------------------------------------------------------------
begin
    if nvl( dbms_lob.getlength( G_BLOB ), 0 ) > 0 then
        execute immediate I_SQL using G_BLOB;
    else
        execute immediate I_SQL using G_CLOB;
    end if;
    commit;
end;

-- I downloaded this from the Net:
function clobfromblob( p_blob blob ) return clob is
    l_clob         clob;
    l_dest_offsset integer := 1;
    l_src_offsset  integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning      integer;
begin
    if p_blob is null then
        return null;
    end if;
    dbms_lob.createTemporary(lob_loc => l_clob
                            ,cache   => false);
    dbms_lob.converttoclob(dest_lob     => l_clob
                          ,src_blob     => p_blob
                          ,amount       => dbms_lob.lobmaxsize
                          ,dest_offset  => l_dest_offsset
                          ,src_offset   => l_src_offsset
                          ,blob_csid    => dbms_lob.default_csid
                          ,lang_context => l_lang_context
                          ,warning      => l_warning);
    return l_clob;
end;


---------------------------------------------------------------------------
procedure IMPORT_UPDATE ( I_TABLE_NAME  in varchar2
                        , I_COLUMN_NAME in varchar2
                        , I_PK          in varchar2
                        ) is
---------------------------------------------------------------------------
    V_SQL           varchar( 32000 );
begin
    G_TABLE_NAME  := upper( trim( I_TABLE_NAME  ) );
    G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
    GET_PK( G_TABLE_NAME );
    select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
    V_SQL := 'update '||I_TABLE_NAME||' set '||I_COLUMN_NAME||' = :1 where ('||G_PK_LST||') in ( select '||I_PK||' from dual )';
    if G_COLUMN_TYPE in ( 'CLOB', 'NCLOB' ) then
        G_CLOB := clobfromblob ( G_BLOB );
        G_BLOB := null;
        DIRECT_SQL( V_SQL );
    elsif G_COLUMN_TYPE in ( 'BLOB' ) then
        DIRECT_SQL( V_SQL );
    end if;
end;


end;
/

Drisko

I do not understand... This is my code. What do you mean for? <github.com/frankiechapson/Export-and-Import-LOB-values> – Perth

O.K. Sorry, I did not see the link :-). Yes, me, Ferenc Toth = Frankie Chapson. – Perth

that's okay then – Brokenhearted

Answer 4

I was faced with this issue when tried to copy a table that has CLOB column from one database server to another. Oracle SQL Developer's "Tools > Copy Database" tool wasn't a good choice for me because I needed to have another name for the destination table, so I had to use "Tools > Export Database" which generates the insert statements for you in a .sql file if you choose so.

Steps I did:

1. Run this in the source DB:

   SELECT 
   someColumA,
   someColumB,
   SubStr(myClobColumn, 1, 4000)    myClobColumn_part1, 
   SubStr(myClobColumn, 4001, 8000) myClobColumn_part2, 
   . . .
   SubStr(Clob_field, .., ..) Clob_field_partN, 
   OtherColumns
   FROM YourTable ;
   

2. Right click and export to a file, all insert statements would be written to a temporary table: EXPORT_TABLE.

3. Run that file in the destination DB server to create EXPORT_TABLE

4. Now, run this to import the first 4000 chars:

   Insert Into YourDestinationTable(.., myClobColumn, ..)
   SELECT .., myClobColumn_part1, ..
   FROM EXPORT_TABLE ;
   

5. Run this to append the rest of the Clob parts:

   Update YourDestinationTable A
      set myClobColumn = myClobColumn 
           || (Select myClobColumn_part2 
               From EXPORT_TABLE B
               Where B.tableKey = A.tableKey 
                 and myClobColumn_part2 is not null)
   ;
   
   
   Update YourDestinationTable A
      set myClobColumn = myClobColumn 
           || (Select myClobColumn_part3 
               From EXPORT_TABLE B
               Where B.tableKey = A.tableKey 
                 and myClobColumn_part3 is not null)
   ;
   ... 
   

till part N.

You're done.

Woodpile

Answer 5

I have same problem when i try to export insert, fields that are clob or blob then it not included in insert query.

This is Problem due to insert limit that is not avail to clob datatype size.

Solution:

I export data in XML or PDF or loader or Json. Then i got data that is clob or blob.

Tools > Database Export > select connection > uncheck export DDl if you want only data > check export data > select format: XML > next > next > next > next >finish.

open file where you save that XML file and verify.

enjoy...

Revive

How do you import it later? – Alumina

Answer 6

the below code can be used to create a update script for clob data.

DECLARE
   TYPE varchar2_t IS TABLE OF VARCHAR2 (32767)
      INDEX BY BINARY_INTEGER;

   read_str    VARCHAR2 (32000);
   V_records   varchar2_t;

   PROCEDURE Clob_to_records (p_clob IN CLOB, p_records OUT varchar2_t)
   IS
      v_record_separator        VARCHAR2 (2) := CHR (13) || CHR (10);
      v_last                    INTEGER;
      v_current                 INTEGER;
   BEGIN
      -- If the file has a DOS newline (cr+lf) - manually created - use above
      -- If the file does not have a DOS newline, use a Unix newline (lf)
      IF (NVL (DBMS_LOB.INSTR (p_clob,
                               v_record_separator,
                               1,
                               1),
               0) = 0)
      THEN
         v_record_separator := CHR (10);
      END IF;

      v_last := 1;

      LOOP
         v_current :=
            DBMS_LOB.INSTR (p_clob,
                            v_record_separator,
                            v_last,
                            1);
         EXIT WHEN (NVL (v_current, 0) = 0);
         p_records (p_records.COUNT + 1) :=
            DBMS_LOB.SUBSTR (p_clob, v_current - v_last, v_last);
         v_last := v_current + LENGTHB (v_record_separator);
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error ' || SQLERRM);
   END Clob_to_records;
BEGIN
  -- DBMS_OUTPUT.put_line ('Processing Clob');

   FOR eachrec
      IN (SELECT a.clob_data
            FROM table1 a
           WHERE   COLUMN1=CONDITION1  )
   LOOP
      Clob_to_records (eachrec.data, v_records);
      DBMS_OUTPUT.put_line ('update table2 a set a.clob_data =');
      FOR i IN 1 .. v_records.COUNT
      LOOP
         read_str := v_records (i);

         IF i = 1
         THEN
            DBMS_OUTPUT.put_line ('TO_CLOB(''' || read_str || ''')');
         ELSE
            DBMS_OUTPUT.put_line ('||CHR(10)|| TO_CLOB(''' || read_str || ''')');
         END IF;
      END LOOP;
        DBMS_OUTPUT.put_line (' WHERE COLUMN1=CONDITION1;');
   END LOOP;
END;

Aletaaletha

Answer 7

1. In Sql developer, open SQL Worksheet
2. Run this query (alter it according to your table structure) :

SELECT COL1, COL2, COL3,SubStr(CLOBCOLUMN, 1, 4000) FROM TABLENAME

3. In Query results page right click on grid select "export"

4. Open exported file in notepad++ replace SubStr(CLOBCOLUMN, 1, 4000) with CLOBCOLUMN

5. now insert statements are ready you can run them with any tool you wish. I prefer sqlplus

   sqlplus sys/oracle as sysdba

   @"/mnt/hgfs/temp/QERY.sql";

   commit;

Stopcock