Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 cursor declaration using linked oracle server

Author  Topic 

javaisok
Starting Member

11 Posts

Posted - 2003-10-28 : 08:12:41
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

15732 Posts

Posted - 2003-10-28 : 08:27:24
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 - 2003-10-28 : 08:33:16
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

15732 Posts

Posted - 2003-10-28 : 08:36:55
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 - 2003-10-28 : 08:45:11
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 - 2003-10-28 : 09:54:03
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 - 2003-10-30 : 09:14:09
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
   

- Advertisement -