January 29, 2009 at 3:36 am
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';
January 29, 2009 at 11:13 am
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) Berzukov
Author 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.January 29, 2009 at 11:10 pm
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.
January 30, 2009 at 9:35 am
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) Berzukov
Author 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.February 2, 2009 at 12:41 am
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?
February 2, 2009 at 4:20 am
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) Berzukov
Author 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy