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: Kevin A Lewis <>
Date: Wed, 24 Mar 2004 14:12:35 GMT
Message-ID: <nHg8c.6$>

Have you considered setting up a Database Link - to enable a single piece of SQL to know about two Oracle Databases (even two different version : albeit with limits)

You can then update (INSERT, UPDATE or DELETE) based on data read in the source database.

Does this help! Not sure why you needed the file based approach.

Regards Kevin
"alederer" <> wrote in message news:406194de$
Michel Cadot schrieb:
> "alederer" <> a écrit dans le message de
>>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
> 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:12:35 CST

Original text of this message