Home > IT Answers > Database > inserts through a database link
Help

Question

  Asked: Mar 31 2006   10:13 AM GMT
  Asked by: carlosdl

inserts through a database link


Database, Oracle, DB2, SQL, Windows, Oracle administration, Architecture/Design, Availability, Backup & recovery, Installation, Migration, Performance/Tuning, Security, Oracle Database Versions, Oracle 8i, Oracle development, XML

Hello every body. Here I go again...

Do you know if there is a restriction about performing inserts through a database link ?

We are experiencing the following situation:
We have two Oracle 8i instances running on a win 2k3 server, and a Forms applications needs to insert data from one instance to the second one. It executes an "insert - select", inserting data on a table which resides in instance b from a table which resides in instance a, but when the app try to execute the insert, it hangs, and the only way to stop it is killing the process.
If the same application executes the "insert" (with fixed values) and the "select" separately, it has no problems.

On instance "a" we have a public synonym for the instance "b" table. (create public synonym TABLE1 for TABLE1@DBLINK).

We tried the same insert-select from Sql Plus, and it was done without any problem.

We tried to remove the synonym with DBA Studio, but it was not possible. DBA Studio hanged too.

Questions:
- Would it be better to use <TABLE>@<DBLINK> in the application instead of creating the synonym ?
- Is there some restriction about inserting through a dblink ?
- Is it possible that we have this problem due to a "bad" Oracle (database) or Developer installation ?

Any ideas will be really appreciated.

Thanks in advance.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Mar 31 2006  10:45 AM GMT  by MrOracle




It has been a long time since I used Forms, but the situation sounds peculiar. I also haven't worked on Oracle version 8 in a whaile.

The only thing I can imagine that would cause a hang - would be an insert in progress for the same primary key. I can duplicate the hung insert, but Oracle 9i let me drop the synonym while the two sessions were fighting over the insert. ;^> Perhaps that was different in version 8.

Sometiimes when a session gets killed, especially over a db link, the background operation can "live on". It should time out after a while,or you could try bouncing the remote database.

I hope this helps.

-MrO-
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database, Oracle and Development.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

randym  |   Mar 31 2006  10:58AM GMT

I haven’t tried to use a synonym. I use the <a href="mailto:table@dbLink">table@dbLink</a> method. Have you tried that?

If that still doesn’t work, I have used the INSERT INTO SELECT FROM statement in Forms with the dbms_sql procedure. I needed to do this because I needed to dynamically create the sql statement because I could have variable database links; but it will work for just one database link as well. I would say that this would make it work like SQL*Plus.

DECLARE cursor ccomp is select prodgroup from <a href="mailto:eisuser.company@wpc;">eisuser.company@wpc;</a>
source_cursor integer;
rows_processed integer;
sqlstmt varchar2(1000);
BEGIN
set_application_property(CURSOR_STYLE,’BUSY’);
commit;
for pg IN ccomp loop
sqlstmt := ‘delete from <a href="mailto:owner.table@’">owner.table@’</a> || pg.prodgroup || ‘ where customer = ”’ || :customer || ””;
— message(sqlstmt);
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor, sqlstmt, 1);
rows_processed := dbms_sql.execute(source_cursor);
— message(’Rows deleted (’ || pg.prodgroup || ‘): ‘ || TO_CHAR(rows_processed));
sqlstmt := ‘insert into <a href="mailto:owner.table@’">owner.table@’</a> || pg.prodgroup || ‘ select * from owner.table where customer = ”’ || :customer || ””;
— message(sqlstmt);
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor, sqlstmt, 1);
rows_processed := dbms_sql.execute(source_cursor);
— message(’Rows added (’ || pg.prodgroup || ‘): ‘ || TO_CHAR(rows_processed));
end loop;
commit;
set_application_property(CURSOR_STYLE,’DEFAULT’);
END;