Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: how to use exp/imp only for specific columns?

Re: how to use exp/imp only for specific columns?

From: alederer <>
Date: Wed, 24 Mar 2004 15:01:59 +0100
Message-ID: <406194de$>

Michel Cadot schrieb:

> "alederer" <> a écrit dans le message de news:40616570$

>>i have for example the following situation:
>>a table on site a tab1(a_key integer,
>> a_val1 varchar(500),
>> a_val2 varchar(1000))
>>and a table on site b tab2((b_key integer,
>> b_val2 varchar(1000),
>> b_val3 integer)
>>is it possible to use the export/import utility or the sql loader to
>>transfer only the data of the columns a_key and a_val2 from tab1 to
>>tab2(into b_key, b_val2)?
>>in the documentation of export, i have only found the QUERY parameter,
>>but it can only limit the rows not the columns which should be exported.
>>if i have a direct database connection i can use
>>"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"
>>but how can a make this with export/import utility or sql loader?
>>what i need is a way to export only specific columns of a table and to
>>import these export into tables with different column names and order.
>>where can i find the missing information?
> You can execute your first query with SQL*Plus and spool the result in a file
> which can be loaded with SQL*Loader.

I see the following problems:
* I need to make the export/spool within a c++ application, ok i can

   call a external process which make the work. * because the application is generic, i don't know what data is in the

   table columns, so what termination character should i use for the    sqlldr control section

i have downloaded the sqlldr_exp skripts from but these have some limits.
* what about the limit, that one row/line is limited to 4000 characters, how can i do larger exports/spools?
* what about newline characters in the column data?

Why is it so complicated in oracle to transfer data via files from one database to another, between different database versions ? Is there no way like in db2 where the export recognizes a select statement, the data is dumped to ixf file format which can be used on all plattforms and on the load site, you can specify a different order for the columns to load from the ixf file. I do not need to worry about a termination character.

andreas Received on Wed Mar 24 2004 - 08:01:59 CST

Original text of this message