September 29, 2008 at 6:42 am
Hi,
we want to switch to linked server instead of installing the Oracle client in the IIS. We are using .NET to create the applications.
I would like to convert the following:
DECLARE l_num_count NUMBER := 0;
BEGIN
SELECT COUNT(*) into l_num_count FROM tablename WHERE fieldname = 'value';
IF l_num_count = 0 THEN
INSERT INTO tablename (fieldlist) VALUES (valuelist);
ELSE
UPDATE tablename SET list(field = value);
END IF;
COMMIT;
END;
Any idea welcome - at the moment I am stuck with the transaction and commit on Oracle side. I found the statements for SELECT, INSERT and UPDATE and they are working - every one for itself.
September 29, 2008 at 7:08 am
I'm really sorry but I do not understand your question.
Anyway...
1- Your commit statement is Okay.
2- Your update statement is updating all rows in the table.
3- I would use merge statement instead of you insert into/update logic.
_____________________________________
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.September 29, 2008 at 7:19 am
My problem is to convert this statement from Oracle (it is working perfectly) to SQL linked server.
The syntax for Insert and Update is clear:
INSERT OPENQUERY (linkedserver, 'SELECT fieldlist FROM tablename') VALUES (valuelist);
UPDATE OPENQUERY (linkedserver, 'SELECT * FROM tablename WHERE field = ''value'') SET list(field=value);
SELECT * from OPENQUERY(linkedserver,'SELECT COUNT(*) FROM tablename WHERE field = ''value''')
But I am not sure how to get the rest via linked server to Oracle. And: I have to send the select, check the result and then send the INSERT or UPDATE.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply