How to Insert / Update to Oracle via Linked Server from SQL2005 - with commit?
-
Hello,
I have an Oracle table, where I have to insert / update records. It all works using the Oracle client on the IIS server.
Now, we want to remove this client and the necessity of installing it and created a linked server to Oracle on SQL 2005.
Select is achieved like this:
SELECT * from OPENQUERY(linkedservername,'select fields from table where field = ''value'' order by field') - the compare values must be put between double apostrophs.
This is working - and faster then the direct Oracle select.
Now, I would like to check for a record. If the record exists, I have to update it - else it has to be inserted. The problem in Oracle (for me) is that you have to use COMMIT; at the end - which I do not know how to put into my query.
Here is the principle structure (using a direct Oracle connection):
DECLARE l_num_count NUMBER := 0;
BEGIN
SELECT COUNT(*) into l_num_count FROM table WHERE field = ''value'';
IF l_num_count = 0 THEN
INSERT ...;
ELSE
UPDATE...;
END IF;
COMMIT;
END;
In principle, I know the syntax for INSERT and UPDATE - but how to add the rest and the COMMIT;?
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles') VALUES ('NewTitle');
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') SET name = 'ADifferentName';
-
I would rely in distributed transactions, here is how.
On the SQL Server side initiate a distributed transaction by issuing BEGIN DISTRIBUTED TRAN
Do something commitable to a SQL Server table -even if your business specs do not ask for it.
Do whaterver you have to do against Oracle tables
Commit the distributed transaction by issuing COMMIT TRAN statement.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
The Oracle Commit; is necessary to write data permanently to the tables - it has nothing to do with a transaction in SQL ;). So in Oracle you need it for any INSERT or UPDATE, even for one record.
So I still need the syntax for what I have to do: INSERT + COMMIT or UPDATE + COMMIT. Or - even better - a replacement for my mentioned structure.
-
birgit.schelloeh (1/29/2009)
The Oracle Commit; is necessary to write data permanently to the tables - it has nothing to do with a transaction in SQL ;). So in Oracle you need it for any INSERT or UPDATE, even for one record.So I still need the syntax for what I have to do: INSERT + COMMIT or UPDATE + COMMIT. Or - even better - a replacement for my mentioned structure.
:w00t: Did you miss the part that says "DISTRIBUTED TRANSACTION" on my post?
When you set a distributed transaction and issue a commit, commit happens on all RDBMS involved in the distro e.g.: SQL Server and Oracle in your case
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
I do not know about distributed transactions, how to set them up, etc. I simply call the whole thing in .NET via direct SQL. What I could do is call a SP instead. Would that help together with the linked server?
-
You code should look like...
BEGIN DISTRIBUTED TRAN
Do your Oracle stuff
Do any commitable SQL stuff -even if your business specs do not ask for it.
COMMIT TRAN
You can read about distributed transactions on Books Online, it's free!
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply