Manipulate data of a SQL SERVER 2000 database
417675Nov 18 2004 — edited Feb 22 2005 Problem:
I need to manipulate data of a SQL SERVER 2000 database that itâs installed in a Windows
from an oracle 8.1.7 database that is installed in a red hat linux server.
This access has to be made in a procedure created in this Oracle instance.
The Solutionâs Environment:
- oracle 8.1.7 database - red hat linux server - ORCL INSTANCE
- SQL SERVER 2000 version 5.0 database - Windows server - SQLSERV INSTANCE
- Oracle 8.1.7 database - windows 2000 professional server - HSDB INSTANCE
We did the following steps:
1) We installed an oracle database 8.1.7 in the windows 2000 professional.
The name of this instance is HSDB.
2) We configured the HSODBC resource in this HSDB instance so as to access the SQLSERVER database.
3) It was created a database link in the ORCL instance (LINUX) so as to access the oracle HSDB instance
4) It was created a procedure in the ORCL instance that insert data into the SQLSERVER database using the database link.
This procedure pass through the following instances:
ORCL Instance (database link) => HSDB Instance (HS Resource) => SQLSERV Instance
5) When I execute this simple procedure of the ORCL Instance, the process takes a long time. Although if I run this insert on a SQL Plus, the transaction ends fast. Why itâs happening?
PROCEDURE PRC_TESTE(P_CD_CMC7 IN VARCHAR2) IS
BEGIN
BEGIN
INSERT INTO VSolicitacaoRetirada@MSSQLDB
("CMC7")
VALUES
(P_CD_CMC7);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR '||SQLERRM);
END;
COMMIT;
END;
Tranks