procedure

[kavithared]

HIII, I heard to migrate data one db to another db where table structure and their datatypes are different.we can do by using stored procedure with cursor.
Ex---scott.abc table id char(10)
name char(20)
My olddb name A

my newdb Name B
xyz.bc table eid char(20)
address varchar2(30)
name char(20)

Now migration of data from abc to bc table by using procedure and cursor.
IS it possible?
If yes any one can write procedure for above table.
thanks
kavitha

[sabita_ban]

This is how I proceeded

create table table1 (id char(10),name char(20));

insert into table1 values('1111','ABCD');
insert into table1 values('2222','XYZ');

select * from Table1;

ID NAME
---------- --------------------
1111 ABCD
2222 XYZ


create table table2 (eid char(20),name char(20),address varchar2(30));

SQL> insert into table2 (select id,name,'ADDRESS' from table1);

2 rows created.

SQL> select * from table2;

EID NAME ADDRESS
-------------------- -------------------- ------------------------------
1111 ABCD ADDRESS
2222 XYZ ADDRESS


After all this stuff...update table2 to change each address

[kavithared]

Hii,thanks
But my new database in windows2000 and old database in sunsolaris.
so any conflict?
Because i have thousands of mobileusers data.
thanks
kavitha

[ales]

Of course you can do that with a procedure but it's unnecessary and complicated, in my opinion.

If you have the both databases running and a db link created, you can better do that with the COPY command of SQL*Plus or a single SQL-statement.

If the tables are large use a single SQL-statement + direct-load-insert to minimize redo + time.

[ales]

It's not clear where the bc.address comes from.

I'd suggest this:

1. connect to the B database as the xyz user
2. create db link to the A db

create database link A connect to scott identified by tiger using 'A'

3. drop all indexes and triggers on the bc table
4. make the bc table nologging

 alter table bc nologging;

5. migrate the data with the direct-load-insert

insert /*+ APPEND */ into bc (eid, address, name)
select id, null, name from abc@A;

6. recreate indexes (possible as nologging)
7. set the bc+indexes logging, enable triggers
8. BACKUP THE B DATABASE

[kavithared]

Hiii,it bc.address is coming from old database,
now we modify the old database,so we need to export the data from old db to new db,but here some columns different.
thanks
kavitha

[sambavan]

Your question is not clear, could you please re-phrase it on what you are looking for?

Sam