OraFAQ Forum: SQL & PL/SQL » Inserting data using DBLINK

Home » SQL & PL/SQL » SQL & PL/SQL » Inserting data using DBLINK (Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Inserting data using DBLINK [message #577848]

Thu, 21 February 2013 03:17

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

Hi Experts,

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 SC_NO                                  NOT NULL NUMBER(25)                  
 LT_DATE                                         TIMESTAMP(6)                
 METHOD                                          XMLTYPE

Target table structure(another DB)

Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 SC_NO                                  NOT NULL NUMBER(25)                  
 LT_DATE                                         TIMESTAMP(6)                
 METHOD                                          XMLTYPE

Please help me how to insert XMLTYPE data using DBLINK.

Thanks in advance.

Report message to a moderator

Re: Inserting data using DBLINK [message #577849 is a reply to message #577848]

Thu, 21 February 2013 03:37

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-22804: remote operations not permitted on object tables or user-defined type columns
 *Cause:  An attempt was made to perform queries or DML operations on
          remote object
          tables or on remote table columns whose type is one of object,
          REF, nested table or VARRAY.

Quote:

Please help me how to insert XMLTYPE data using DBLINK.

You can't.

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577850 is a reply to message #577849]

Thu, 21 February 2013 03:42

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

Thanks for your prompt response.

Could you please help me what is the work around for this.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577853 is a reply to message #577850]

Thu, 21 February 2013 04:51

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on your final need.

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577855 is a reply to message #577853]

Thu, 21 February 2013 04:57

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

I don't want to create ant DBLINK at target DB .

Can you please provide a simple work around for this.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577863 is a reply to message #577855]

Thu, 21 February 2013 05:46

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You cannot use a db link so you have to use something else: an external program.

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577872 is a reply to message #577863]

Thu, 21 February 2013 06:18

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

Can you please provide the code.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577878 is a reply to message #577872]

Thu, 21 February 2013 07:35

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use you favourite programming language.

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577884 is a reply to message #577878]

Thu, 21 February 2013 08:29

_jum
Messages: 450
Registered: February 2008

Senior Member

If the length of METHOD doesn't exceeds 4000, you can INSERT the XLMTYPE as STRING;

INSERT INTO APSP.SALES_HISTORY@APSP_LINK (sc_no , lt_date, method )
SELECT sc_no ,lt_date, xd.method.getstringval() 
  FROM KMBS.SALES_HISTORY xd;

[Updated on: Thu, 21 February 2013 08:30]

Report message to a moderator

Re: Inserting data using DBLINK [message #577940 is a reply to message #577884]

Thu, 21 February 2013 23:36

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

Thanks for your reply.

My METHOD column is having some records more than 4000 characters.

Please help me .

How to insert data using DB link.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577946 is a reply to message #577884]

Fri, 22 February 2013 00:59

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

I tried your query

still I am getting the error.

ORA-22804: remote operations not permitted on object tables or user-defined type columns.

Please help me.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577950 is a reply to message #577946]

Fri, 22 February 2013 01:07

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 21 February 2013 12:46

You cannot use a db link so you have to use something else: an external program.

What did you try since?

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577955 is a reply to message #577950]

Fri, 22 February 2013 01:28

_jum
Messages: 450
Registered: February 2008

Senior Member

Quote:

My METHOD column is having some records more than 4000 characters.

- no chance with my idea then.

Use program like datapump as @Michel recommended.

Report message to a moderator

Re: Inserting data using DBLINK [message #577979 is a reply to message #577955]

Fri, 22 February 2013 05:32

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

Can you please provide work around.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577980 is a reply to message #577979]

Fri, 22 February 2013 05:40

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you click on the link jum_ gave you?

Regards
Michel

Report message to a moderator

Re: Inserting data using DBLINK [message #577982 is a reply to message #577980]

Fri, 22 February 2013 06:05

ajaykumarkona
Messages: 261
Registered: August 2010

Senior Member

I have gone through the link.

But as per our process we have one procedure it's picking data from 20 tables
and inserting into remote db.
Out of 20 tables 2 tables having XMLTYPE data.
I am not able to insert data into those 2 tables using my DBLINK.

Can you please provide workaround using DBLINKS to insert data into remote DB
with minor changes to my procedure.

Please help me.

Thanks.

Report message to a moderator

Re: Inserting data using DBLINK [message #577984 is a reply to message #577982]

Fri, 22 February 2013 06:13

cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester

Senior Member

There is no work-around that uses DB links.
This is what everyone has been trying to tell you repeatedly.
You need to use an external program written in something other than PL/SQL - java, C etc.

Report message to a moderator

Previous Topic:	record event if no rows are returned between two timestamp
Next Topic:	SQL_QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 00:11:50 CDT 2013

Total time taken to generate the page: 0.13789 seconds