I have to compute a value involving data from several tables. I was wondering if using a stored procedure with cursors would offer a performance advantage compared to reading the data into a dataset (using simple select stored procedures) and then looping through the records? The dataset is not large, it consists in 6 tables, each with a...
Question: How do I store a selected field value into a variable from a query and use it in an update statement? Here is my procedure: I'm writing a sql server 2005 tsql stored procedure which does the following: 1. gets list of invoices id's from invoice table and stores to Cursor 2. Fetch invoice id from cursor -> tmp_key variable...
I have a following oracle stored procedure CREATE OR REPLACE PROCEDURE getRejectedReasons ( p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT * FROM reasons_for_rejection; END; However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this: Connecting to...
I'm struggling to find out how to programmatically enable the "Select objects" cursor type. I checked the object browser and expected to find a property like Application.CursorType or Application.DrawingMode. Changing the cursor type isn't picked up in the macro recorder and I must be searching for the wrong terms as I can't find infor...
I don't use SQL Server Cursors often but when I do, I always have to look up the syntax and options. So I wanted to ask, what is the best SQL Server Cursor reference on the web?. I'm looking for a reference that explains all of the (major?) options (I.E. FAST_FORWARD) and also shows quick snippets of how to use it. (I.E. How to impl...
I'd like to know your experience(s) with replacing SQL Server cursors in existing code, or how you took a problem that a procedural guy would use a cursor to solve, and did it set-based. What was the problem the cursor was used to solve? How did you replace the cursor? ...
What is the query to find the no. of current open cursors in an Oracle Instance? Also, what is the accuracy/update frequency of this data? I am using Oracle 10gR2 ...
I have some stored procedures that I need to write against a nasty beast of an database. I need to loop through a table (application) and pull values out of other tables (some are aggerate / averages /etc values) using the application_id from the application table. So far I have: declare @id INT declare app cursor for SELECT applicat...
Hey, I've been trying to write code that loads a .png file, attaches hotspot information, and saves it to a .cur file. So far I have code to create a System.Windows.Forms.Cursor object, which I'll post below: Bitmap bmp = new Bitmap(source_image); IconInfo inf = new IconInfo(); GetIconInfo(bmp.GetHicon(), ref in...
Hello, I heard it's not good to use cursors, as they "unnatural" to DBMS and they provide bad perfomance. But imagine the following situation: I have a stored procedure and I need to call this stored procedure for every customer from France (for example). I have a few options, such as using cursor, write all stuff in one query and call s...
I've got a PL/SQL package that returns a sys_refcursor based on the id that you pass it. I'd like to iterate through some ids and create a new ref cursor with one column from the original result set repeated for each id. (Sort of a cross tab.) A very simplified version of the PL/SQL block looks like: create or replace package body da...
My Google-fu is failing me. Please, consider the following: http://tcl.activestate.com:8000/man/tcl8.4/TkCmd/cursors.htm Some of the cursors I understand easily enough, "watch" tells the user to wait, "left_ptr" indicates that I can select something under the cursor, "hand2" seems to be the "you're dragging something" cursor. What are ...
Hello. I currently am working on a legacy application and have inherited some shady SQL with it. The project has never been put into production, but now is on it's way. During intial testing I found a bug. The application calls a stored procedure that calls many other stored procedures, creates cursors, loops through cursors, and many ot...
Hullo, Can any help me shed some light on what's going on when I try to prepare a statement from a string that contains a cursor-fetched variable? Below is the procedure that I'm trying to create. When I create it without the prepared statement, and replace those three lines with SELECT @export_sql_stmt, the output verifies that the S...
Hi there, How can I add a custom mouse cursor (say cursors from C:\Windows\Cursors directory) onto a ASP.NET user control? Thanks a bunch, dattebayo... ...
What is the best alternative to using a cursor in SQL if I am suffering from performance issues ? I got the following code wherein it uses Cursor to loop through and insert records. --Extract Column Data From Pit To Port AuditLog to QmastorAudit AuditLog DECLARE @AuditBatchID_logRow INT, @AuditOperationID_logRow INT, @RowIdentifie...
Hi, I'm working on the following code below, (edited for clarity), that is giving me a few problems with open cursors in Oracle. Basically I am trying to select data from the DB and for each row returned there is 0 or more rows of sub data to be selected and appended to the record. This is currently being achieved by calling out to anot...
I am in the midst of updating data in multiple tables. Currently I have a table that has one field, "sources", that is just a list of all tables that include the field "itemid". I also have a table that has 2 fields, "itemid" and "olditemid". In TSQL, I would like to iterate through the sources and create the update statements on the ...
I have a bit of a puzzle (at least for me) which I am hoping is mostly because I am not yet an SQL master of the universe. Basically I have three tables: Table A, Table B, and Table C. Table C has a FK (Foriegn Key) to Table B, which has FK to Table A. (Each of these is many to one) I need to remove an entry from Table A and of cours...
Hi Guys. I've been reading up on database cursors, and every bit of sample code I've seen explicitly opens and closes the cursor. I've only used them a few times and I've never had to do this. Can anyone tell me why it is necessary to do this? I know if you don't close a cursor you can create memory leakes but i've never had to open one...