HELP: How to move nested table across database link

From: charlie cs <cfs3526(no-spam)_at_ureach.com>
Date: Thu, 01 Jul 2004 00:42:32 GMT
Message-ID: <Y5JEc.24408$Xn.5164@nwrdny03.gnilink.net>

We are using Oracle 9.2.

We have a big table which has a user-defined objects. I need to move it to another database. "Insert .. select * from.." does not work. What I am doing now is "Insert .. select non_object column" to move all the non-user_defined objects. Then I made a big file, containing huge amount of sql statements, to update the user-defined objects.

The object is like this

SQL> desc Parent_OBJTYPE

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 Col1                                                COl1_OBJTYPE
 Col2                                                Col2_OBJTYPE

SQL> desc Col1_OBJTYPE
Name                                      Null?    Type


----------------------------------------- -------- -------------------------

-

Col1_1                                            VARCHAR2(255)
Col1_2                                            VARCHAR2(30)

SQL> desc Col2_OBJTYPE
Name                                      Null?    Type


----------------------------------------- -------- -------------------------

-

Col2_1                                            VARCHAR2(255)
Col2_2                                            VARCHAR2(30)

To update the table, I use

update table test
set Parent_Col=Parent_OBJTYPE(Col1_OBJTYPE ('test1','test2'),

                              Col2_OBJTYPE ('test3','test4'))

where id=1;

There are several millions of records to update.

Is there any other better way of doing this?

Thanks for your help. Received on Wed Jun 30 2004 - 19:42:32 CDT