SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 cursor declaration using linked oracle server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

javaisok
Starting Member

11 Posts

Posted - 10/28/2003 :  08:12:41  Show Profile  Reply with Quote
i need to declare cursor that selects from a linked oracle server but openquery() does not receives variables as parameters. on the other hand i need to bind some parameters to that select statement. how can i build and execute this declaration?
thanks in advance.

declare cr_test cursor for
select * from openquery(oracle_server,'select * from users where user_id = ? ')

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/28/2003 :  08:27:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why do you need a cursor? The worst thing you could do is to open a cursor on a linked server data source. You could raise a family in the time it would take to complete.

When using OPENQUERY and dynamic SQL, you need to make the entire statement dynamic:

DECLARE @sql varchar(8000)
SET @sql=Replace('SELECT * FROM OPENQUERY(oracle_server,''select * from users where user_id=?'')', '?', 123)
EXEC(@sql)


This won't let you use a cursor either, unless you encapsulate the entire sequence of statements in dynamic SQL. Which is a good thing, don't use cursors for whatever you're trying to do.

If you post your code, we may be able to help with a more efficient solution.
Go to Top of Page

javaisok
Starting Member

11 Posts

Posted - 10/28/2003 :  08:33:16  Show Profile  Reply with Quote
thanks a lot robvolk but i want to make a loop over result rowset. that's why i am trying to declare cursor. is it possible to make a loop over recordset using only select statement?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/28/2003 :  08:36:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
My point is, WHY do you want to have a loop? This is SQL, it can manage an entire set of data in one operation. Once you start looping over rows you slow down the process tremendously.
Go to Top of Page

javaisok
Starting Member

11 Posts

Posted - 10/28/2003 :  08:45:11  Show Profile  Reply with Quote
a have a table (in oracle) with tasks that SQL server must perform. Depends on the task command there are different ways to proceed. one task (command) is 'new', other is 'modifyed' or 'deleted'. SQL server must listen from new commands and perform them - insert , modify or remove some records from a lot of tables. This is something like replications. :)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/28/2003 :  09:54:03  Show Profile  Reply with Quote
quote:
Originally posted by javaisok

a have a table (in oracle) with tasks that SQL server must perform. Depends on the task command there are different ways to proceed. one task (command) is 'new', other is 'modifyed' or 'deleted'. SQL server must listen from new commands and perform them - insert , modify or remove some records from a lot of tables. This is something like replications. :)



Inbound replication? Listen?

Sounds like you're trying to react to changes...why isn't the Oracle side handing off data? Or are you trying to perform updates on the oracle side. If so, why do you need a cursor?

I'm really curious what you mean by the listen part...



Brett

8-)
Go to Top of Page

javaisok
Starting Member

11 Posts

Posted - 10/30/2003 :  09:14:09  Show Profile  Reply with Quote
Hi,
i have already solved my problem! :)) i have linked oracle with SQL server and at the moment i may perform distributed transactions as well but here comes the next problem: how can i use oracle sequence inserting records into oracle table?

SQL server side performs:
insert into server..shema.table
(id,data)
values
(sequence.nextval, value)

this expression is not allowed in TransactSQL but i need it in stored procedure.

thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000