Home » SQL & PL/SQL » SQL & PL/SQL » Inserting data using DBLINK (Oracle 11g)
Inserting data using DBLINK [message #577848] Thu, 21 February 2013 03:17 Go to next message
ajaykumarkona
Messages: 399
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.
Re: Inserting data using DBLINK [message #577849 is a reply to message #577848] Thu, 21 February 2013 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
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
Re: Inserting data using DBLINK [message #577850 is a reply to message #577849] Thu, 21 February 2013 03:42 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your prompt response.

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

Thanks.
Re: Inserting data using DBLINK [message #577853 is a reply to message #577850] Thu, 21 February 2013 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your final need.

Regards
Michel
Re: Inserting data using DBLINK [message #577855 is a reply to message #577853] Thu, 21 February 2013 04:57 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
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.
Re: Inserting data using DBLINK [message #577863 is a reply to message #577855] Thu, 21 February 2013 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
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
Re: Inserting data using DBLINK [message #577872 is a reply to message #577863] Thu, 21 February 2013 06:18 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Can you please provide the code.

Thanks.
Re: Inserting data using DBLINK [message #577878 is a reply to message #577872] Thu, 21 February 2013 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use you favourite programming language.

Regards
Michel
Re: Inserting data using DBLINK [message #577884 is a reply to message #577878] Thu, 21 February 2013 08:29 Go to previous messageGo to next message
_jum
Messages: 490
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 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
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.
Re: Inserting data using DBLINK [message #577946 is a reply to message #577884] Fri, 22 February 2013 00:59 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
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.
Re: Inserting data using DBLINK [message #577950 is a reply to message #577946] Fri, 22 February 2013 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
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

Re: Inserting data using DBLINK [message #577955 is a reply to message #577950] Fri, 22 February 2013 01:28 Go to previous messageGo to next message
_jum
Messages: 490
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.
Re: Inserting data using DBLINK [message #577979 is a reply to message #577955] Fri, 22 February 2013 05:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Can you please provide work around.

Thanks.
Re: Inserting data using DBLINK [message #577980 is a reply to message #577979] Fri, 22 February 2013 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59285
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you click on the link jum_ gave you?

Regards
Michel
Re: Inserting data using DBLINK [message #577982 is a reply to message #577980] Fri, 22 February 2013 06:05 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
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.
Re: Inserting data using DBLINK [message #577984 is a reply to message #577982] Fri, 22 February 2013 06:13 Go to previous message
cookiemonster
Messages: 10988
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.
Previous Topic: record event if no rows are returned between two timestamp
Next Topic: SQL_QUERY
Goto Forum:
  


Current Time: Wed Oct 01 05:55:54 CDT 2014

Total time taken to generate the page: 0.20629 seconds