CODEWITHSUNDEEP
databaseoracle-databaseoracle11gr2
Arya Sharma
Arya Sharma

Reputation: 1

Data pumping in oracle

my problem in using remap_data option while importing data. scenario: 

 create table test(fname varchar(20), lname varchar(20),fullname varchar(40));
 insert into test values('govind', 'verma','');

by mistake I forgot to insert data in full name (I have huge data so I can't perform update operation so I went for data pumping ) I exported data using expdp but while loading back i want to perform a operation using remap_data option in such a way input of that remap function should be fname and lname but wanna modify in 

fullname=concat(fname,':',lname)

for each row. Is it possible ?

Upvotes: 0

Views: 74

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

If the full name is always the concatenation of the first and last name, rather than try to supply and maintain that manually you could use a virtual column instead:

alter table test drop column fullname;
alter table test add fullname generated always as (fname||' '||lname);

select * from test;

FNAME       LNAME       FULLNAME
----------- ----------- ------------------
govind      verma       govind verma

SQL Fiddle demo.

You don't need to update existing rows or export/import; the virtual value is generated automatically when queried. Depending on how it will be used you might benefit from adding an index on it like any other column.

Upvotes: 1

Related Questions