?
Solved

Copy Data with CLOB data field

Posted on 2005-02-25
17
Medium Priority
?
7,095 Views
Last Modified: 2010-05-18
Hi,
I am doing the data migration, Oracle 9i
One table I have to copy from staging to Production has CLOB type.
I using copy command to copy over the data. All the tables are getting copied but the one with field CLOB data type.

When I try to do that it gives me the following error:
" CPY0012: Object datatypes cannot be copied"

Please suggest  how to copy the data of this table into production  having CLOB data rtype.

Table looks like this
CREATE TABLE ORDEREDSERVICE
(
  ID               NUMBER(8)                    NOT NULL,
  LOCATION         VARCHAR2(128 BYTE),
  USE_CLOB         NUMBER(1)                    DEFAULT 0                     NOT NULL,
  PARAMETER        CLOB,
  PARAM_TEXT1      VARCHAR2(4000 BYTE),
  PARAM_TEXT2      VARCHAR2(4000 BYTE),
  SVCDESC_ID       NUMBER(8),
  SVCADDRESS_ID    NUMBER(8)                    NOT NULL,
  SERVICE_ID       NUMBER(8),
  SCHEMA_NAME      VARCHAR2(30 BYTE)            DEFAULT USER
)


Thanks
0
Comment
Question by:tarun_gaba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +4
17 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 13403962
Export/Import.  

I don't know if this will ever be fixed but I believe that it is still impossible to use LOBs across a DB link.
0
 
LVL 9

Assisted Solution

by:neo9414
neo9414 earned 1000 total points
ID: 13403973
you can use import/export of oracle9i to export the table from staging and then import it into production server

c:> exp user/password file=filename.dat tables=ORDEREDSERVICE log=logfile.log

This will export ORDEREDSERVICE table. you can then import it into your production database.

If you want you can use DIRECT=y parameter for direct path export to speed up the export process.

0
 
LVL 9

Expert Comment

by:konektor
ID: 13404226
create database link between 2 databases and use insert into xx@db_link select * from xx.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:tarun_gaba
ID: 13404284
Hi, I cannot to Export /Import, since it will overwrite the data in production, I want to append the recored rather than completely overwriting it

Thanks
0
 

Author Comment

by:tarun_gaba
ID: 13404334
For all other tables I am using the following command.
Dont know for one with CLOB data type

copy from schemaowner/schemaowner@oasisd to schemamig/schemamig@oasisd append -
CUSTOMERORDER Using -
select * from CUSTOMERORDER

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13405024
konektor:  Have you tried this with a CLOB?

You can provide a query string to the export statement and only get the rows you wish:
exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"


Check out the docs:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1013411
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13405070
Did you try the database link?  That would appear to solve your problem.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13405096
jrb1:  I know that in early 9i ( 9.0 ) LOBS were still forbidden fruit in queries across links.  Don't know about 9.2.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13405128
jrb1 & konektor:

Just tried it with 10g and it is still an issue:

ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13416472
OK, how about the copy statement?

copy from username/password@instance1 to username/password@instance2 -
insert table1  using select * from table2;
0
 
LVL 1

Expert Comment

by:ora_user2003
ID: 13418709
eg u have a table in the source dt named

TAB_CLOB_SOURCE

and the table to ewhich u have to transfer data is

TAB_CLOB_DEST

Steps
1) prepare a DB link from the source to the DEST DB
2) create a small utility proc as follows

      declare
         p_clob varchar2(32767);
      begin
         
           select COL_CLOB into p_CLOB from TAB_CLOB_SOURCE ;

       insert into TAB_CLOB_DEST@link values (p_CLOB);

      end;

with relevant exception handles of course ......


give it a try and let me know










         
 

 




0
 

Expert Comment

by:upss
ID: 13469350
It is very complicated  to do in Oracle. May be you can try third party tools. How large is your table?
If table is very big and contains CLOBs, you can use something like Fastreader. Fastreader www.wisdomforce.com is a high-speed extract oracle table into flat file, support blobs, clobs and will create loader to  another ddatabase as well.

Hope this will help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14311787
even split?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 14311803
I think slightvw and neo9414 hit with the right answer at about the same time.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question