Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL and DB LInk problem (Orcale 9i)
PL/SQL and DB LInk problem [message #381073] Thu, 15 January 2009 02:58 Go to next message
rajpalsingh
Messages: 1
Registered: January 2009
Junior Member
I am not able to insert record in another data base using dblink through the following pl/sql. But if I give the insert comand only in sql it is working.

DECLARE
num1 NUMBER(5);
begin
insert into XXBOB_LIMITS@CRISRAM(SYSTEM_ID,CUST_ID,FACILITY_ID,LINE_SERIAL) values ('ccbs','45646','565','464698'); END; /
SQL> @try
insert into XXBOB_LIMITS@CRISRAM(SYSTEM_ID,CUST_ID,FACILITY_ID,LINE_SERIAL) values ('ccbs','45646','565','464698');
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PL/SQL: ORA-04052: error occurred when looking up remote object CAMCC.XXBOB_LIMITS@CRISRAM
ORA-00604: error occurred at recursive SQL level 1
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from CRISRAM
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
Re: PL/SQL and DB LInk problem [message #381097 is a reply to message #381073] Thu, 15 January 2009 04:07 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
IMO correct Syntax is:
INSERT INTO <user>.<table>@DB_LINK(<col1>,..) VALUES (...)


Excuse, Your Syntax seems correct Embarassed .

[Updated on: Thu, 15 January 2009 04:10]

Report message to a moderator

Re: PL/SQL and DB LInk problem [message #381181 is a reply to message #381073] Thu, 15 January 2009 09:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What happens when you try it as a SQL. there is no reason this should be a PL/SQL for a single insert statement.
Re: PL/SQL and DB LInk problem [message #381217 is a reply to message #381181] Thu, 15 January 2009 20:07 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I find obscure errors like this tend to be caused by factors on a different line. Good bets are:

Quote:
1) privileges (my best guess)
2) product version limits (need higher level of some product (use 10.2 rather than 9.1 for example)
3) product version differences between two databases (10.2 sqlplus, 9.2 database)
4) syntax error in your code
5) actual oracle bug

You will have to do some experimenting to figure it out. For example:

Quote:
1) run from sqlplus
2) use dynamic sql
3) do the insert from the other database (i.e. pull vs. push)

These kinds of tests will give you some idea of where the problem may lay, as well as offer up alternative codings that can work.

You might also wish to open a tar with oracle support.

Good luck, Kevin
Previous Topic: Encoding special characters in a long field for csv file
Next Topic: Parsing in oracle
Goto Forum:
  


Current Time: Mon Dec 05 04:46:01 CST 2016

Total time taken to generate the page: 0.14909 seconds