SQL Cursor : Database Object That Provisions Access to Records in a Database

learn more… | top users | synonyms

1
vote
1answer
40 views

Does the function inside the Select clause evaluate the passed in columns before the where clause?

I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question. select ...
1
vote
1answer
144 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
1
vote
3answers
159 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
1
vote
0answers
45 views

How to use set-based statement instead of cursor for converting 1 table to two, keeping relations

I'm relatively new to databases and currently working with SQL. I am doing a conversion for a customer. This conversion points to local files on a filesystem. In the old database there simply was a ...
3
votes
2answers
75 views

Pass (comma separated) -yet single parameter to cursor

I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like: CURSOR ...
2
votes
0answers
26 views

DB2 Dynamic Load from cursor

We have to load a number of huge tables using Load From cursor. The query formed for the same is a dynamic query as the column names are to be specified. we do the same using the below code in a ...
2
votes
1answer
31 views

DB2 : Load from cursor Dynamic query

We need to move multiple tables using Load from cursor. But the number columns in the source and destination tables are not the same. Also the tables structures can change so we need the same to ...
0
votes
0answers
23 views

Filtering with cursor pagination

We're using cursor pagination as described in this article over a large dataset using PostgreSQL and Django Rest Framework. I'm trying to get a better understanding of the internals of this to solve ...
1
vote
1answer
33 views

Cursor with Begin and End Transaction

I've got the following script that keeps hanging on the 2nd iteration through the loop. BEGIN BEGIN TRY BEGIN TRANSACTION DECLARE @itemID int DECLARE LoopCursor CURSOR FOR ...
0
votes
0answers
16 views

Mysql Procedure insert one to many with cursor

I would like to take a course, or just show me the way. There is a table called Questionario from it I get the records in a procedure and organize the data into it. Here is my code:      CREATE ...
-1
votes
1answer
58 views

Error with Loop using Cursors - Invalid Object Name with Set = @

Working on a script to Loop through databases to query one table that each Identical Database has with cursors then return result set to table for each database. I think I have the structure for Set ...
1
vote
1answer
1k views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
4
votes
2answers
431 views

Can two sessions create #temp tables with the same name?

I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate ...
0
votes
1answer
123 views

Dynamic SQL To Send HTML Email

I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email. However, anytime I try to execute my statement ...
2
votes
1answer
66 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
1answer
42 views

INSERT from SELECT using a CURSOR

I have a table that looks like this: +====================================================+ | aid | uid | eid | name | dob | paid | ...
2
votes
1answer
60 views

What is a cursor?

I am new to database concepts and I found some sources that try to explain what is a cursor, but I can't understand what they mean. Can someone explain in a very basic manner what a cursor is and what ...
8
votes
3answers
188 views

Lots of “FETCH API_CURSOR0000…” on sp_WhoIsActive ( SQL Server 2008 R2 )

I have a strange situation. Using sp_whoisactive I can see this: Ok, with this query, I can see what is triggering ( does this word exists in english? ) it: SELECT c.session_id, c.properties, ...
1
vote
1answer
133 views

Tracing the origin of noTimeout cursors

I'm currently managing a Mongo DB instance that has several applications using it. It's been accumulating noTimeout cursors, and, while it's easy enough to cull them if they start getting ...
2
votes
1answer
1k views

Get values from Select statement and Insert them in Temp table Stored procedure [closed]

Here is my stored procedure. I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come ...
0
votes
1answer
1k views

How to FETCH from a cursor into a table variable?

I have a cursor defined as: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempTable And I have a variable defined as: DECLARE @currentId TABLE ( ...
0
votes
2answers
130 views

Auto increment a value using cursor in SQLServer

I am developing an employee leave management system and My table structure with data is : +-----------+---------+----------+----------+------------+--------------+ | EmpID | CL | PL | ...
7
votes
1answer
296 views

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM ...
3
votes
2answers
125 views

Stored procedure using cursors [closed]

I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as emp (eid, ename, age, salary) works ...
1
vote
1answer
33 views

Update Using a cursor

I know that instead of cursor we can have the tables updated with different queries but I wanted to try using cursor so as to have a knowledge on cursor This was what I was trying I have inserted a ...
0
votes
1answer
97 views

Replacing a Cursor with a single Query possible?

I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor. The problem is I need to get items from a batch table based on a list of ...
0
votes
0answers
75 views

How can I replace the cursor in the below query?

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the ...
3
votes
1answer
131 views

SQL Server index causing incorrect behavior on cursors

I have a SQL Cursor that's behaving incorrectly as soon as we added an index to a table. Here's a trimmed-down example of the query causing issue: DECLARE @CUR_CRDATE NVARCHAR(8) DECLARE ...
0
votes
0answers
282 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...
0
votes
0answers
49 views

Creating a table of possible values from multiple inputs

I have a list of datasets. I want to put the column names, and types, into a new consolidated table with a unique reference number. e.g.: Table 1 has 6 unique columns Table 2 has 22 unique columns ...
1
vote
2answers
2k views

Cursor doesn't print text when I tell it to

I have used WAITFOR combined with a cursor in the following script: create table orders(orderId int primary key,productId int,productName varchar(20)); insert into orders values(1,11,'book'); insert ...
2
votes
1answer
739 views

T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

My sample database (in fact a CTE statement data) looks like this: eventdate val 2012-03-23 3965 2012-03-26 3979 2012-03-27 3974 2012-03-28 3965 2012-03-29 3967 2012-03-30 3959 2012-04-02 ...
1
vote
1answer
110 views

Modifying a result set in a Stored Procedure, then returning that modified set

I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the ...
3
votes
2answers
18k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
0
votes
1answer
1k views

Compare two big tables in PL/SQL

I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input ...
3
votes
1answer
2k views

Why choose a top query and temporary table instead of a cursor for a loop?

When it comes to looping over a large set of data in T-SQL, the majority of examples I see on the Internet use a pattern like this: declare @someVariable int declare @remainingRows int select ...
4
votes
4answers
806 views

Keep track of time intensive stored procedure with prints

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = ...
0
votes
1answer
2k views

Oracle strong ref cursor with dynamic sql

I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic ...
1
vote
2answers
158 views

Update specific records of table based on subquery's output (sql server)

have this query which produce below output : select sec.site,sec.Name,sec.Freq from ( select Name from sec group by name having count(Freq) > 3)as dt inner join sec on dt.Name = sec.Name where ...
0
votes
0answers
19 views

Curious Regarding filtering behaviour and performance of where condition

So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each ...
2
votes
1answer
178 views

Find all potentially redundant records in a table using cursors and fuzzy string matching

I have been given an interesting challenge and I believe I have a valid approach, but before I set about implementing it I want to see if there are some better ways to be doing things. The Problem We ...
3
votes
1answer
1k views

Converting Cursor based filtering logic to CTE based

I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition ...
2
votes
1answer
511 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
0
votes
1answer
652 views

How to reduce the run time of stored procedure by getting rid of cursors?

I have a task of reducing the run time of a stored procedure that runs for hours. Description of task: The cursor takes a set of values @Master and @Slave obtained from a query(5000+ rows), finds ...
0
votes
1answer
156 views

Nested cursors, multiple result sets

I'm using a SQL Server stored procedure to generate an Excel report. Basically each "entry" in the report has three parts: Object, Observation, and Points. The observations have a reference to the ...
1
vote
1answer
373 views

How to use the dbms_sql to get the analyze for insert into statement

declare v_cursor number; stmt varchar2(2000); s varchar2(40); cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID where voc.SID in(select sid from ...
3
votes
2answers
18k views

declaring cursor to loop over some values in SQL server 2008 R2

I have some business unit such as India, International, US, UK. I have to: Create a table variable (virtual in-memory table) with columns that match SPROC SP_Report resultset. Declare a cursor that ...
1
vote
1answer
1k views

MongoDB Shard - Disable Cursor timeout

I'm using an sharded-cluster(Mongo version 2.4.1) with a java application. After a specific time (10 minutes or more) I got the following exception: com.mongodb.MongoException$Network: Read operation ...
2
votes
0answers
143 views

SQL SERVER 2008 Cursor Problem

I have a table that has some missing values for certain transactions. The value for a particular transaction is going to depend on the value of all preceding transactions. Because the preceding ...
0
votes
1answer
803 views

PL/SQL cursors and records

I am a new to Pl/sql and I need to learn to write simple pl/sql scripts. Is there any difference between cursors and records in PL/sql? So far I find them both similar.