Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
199
questions
4
votes
0
answers
52
views
Log Backup occasionally blocked by automated update
We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
- 2,968
4
votes
1
answer
272
views
Why does CURSOR_STATUS return unexpected results for an output cursor inside the stored procedure that created it?
I have a stored procedure that has an output parameter of the CURSOR VARYING type. I would like to verify that the output cursor can be used by the code that called the stored procedure. It seemed ...
- 30.6k
0
votes
0
answers
397
views
How do I loop through a cursor in Redshift?
Rather than pollute this thread with the dozens of different things I've tried, including using a stored procedure, dynamic SQL, etc., all of which results in one error or another, I will ask what is ...
- 254
0
votes
0
answers
28
views
I get this error message: Could not complete cursor operation because the table schema changed after the cursor was declared [duplicate]
I get this error message occasionally when I execute a stored procedure. My stored procedure is the next:
USE [GAUDI2020_ODSPrecarga]
GO
/****** Object: StoredProcedure [dbo].[Obsoletos] Script ...
- 1
2
votes
1
answer
69
views
Query Performance for Reports in PostgreSQL
I use PostgreSQL 12 to store my raw data and ElasticSearch as the datawarehouse for reports.
It happens that there are situations with JOIN (eg, data that exists in one table but does not exist in ...
- 295
4
votes
1
answer
200
views
Changing SET options within cursor - Different behavior based on compatibility level
I have found a difference in behavior on a piece of code between database compatibility levels and wondered what the cause of this is. Below is a simple example that iterates through a tally table and ...
- 1,448
0
votes
1
answer
506
views
Executing a query with a variable in a loop
I have two select, how can I do the same using a loop and a variable in PL SQL Developer.
I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
...
- 1
0
votes
0
answers
237
views
Trying to read a json lines file with OPENROWSET and cursor
I've got a pile of json lines files that I'm trying to process into a set of Sql Server database tables. Can't use OPENJSON() because jsonlines is not valid in total, only line by line. Plus the ...
- 287
1
vote
2
answers
176
views
SELECT... with LIMIT, but iterate forward getting other records?
I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g.
$$
DECLARE
BEGIN
LOOP
exit when get diagnostics n_rec_count = 0;
...
- 111
0
votes
1
answer
744
views
Procedure insert into another table using cursor [closed]
I'm creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ...
0
votes
2
answers
212
views
Use a cursor to copy the data from tables in one DB and populate empty tables in another DB?
Basically "DB A" contains the same tables as "DB B", but "DB B" tables are empty, could I create a cursor that creates a backup by filling the tables in DB B with the ...
- 11
0
votes
0
answers
159
views
How to transfer data from one table to another with exception handling in Postgresql?
So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the ...
- 111
0
votes
1
answer
199
views
Creating a dynamic db2 reorg script with in-built delay for concurrency
So I'm new to db2, and some of the syntax and other execution restrictions give me trouble. My DB is using DB2 v11.1.2.2. I've created a reorg script which calls the REORGCHK_TB_STATS and ...
- 1,745
0
votes
1
answer
502
views
How do I get DBeaver to do a DB2 UPDATE via Cursor
The following query works when run from the console via db2 command. But fails when running via DBeaver.
BEGIN NOT ATOMIC
FOR R AS C CURSOR WITH HOLD FOR
SELECT ROW_NUMBER() OVER() AS INDEX, ID
...
- 35
0
votes
0
answers
36
views
How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor
Please refer to the following schema at SQL Fiddle when reading the use case below.
In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
- 1,913
0
votes
4
answers
49
views
use a string array without CREATE privileges
I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.
select 'Accounts', row_id Acct_id, pr_postn_id
from schema....
- 109
1
vote
2
answers
605
views
How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
1
vote
1
answer
313
views
Inserting Data using Cursor but check if the data already in the table
I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, ...
- 51
0
votes
1
answer
61
views
Replicate multiple times a set of rows with unique column in a table
I wish to replicate a set of rows in a table but with handling unique column in SQL Server 17.
The table is:
CREATE TABLE test1 (
id int CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED IDENTITY,
...
- 103
1
vote
2
answers
427
views
Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?
I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to ...
- 1,913
0
votes
1
answer
115
views
Preserve capitalization of column names/keys returned by SYS_REFCURSOR in Oracle
I am using a SYS_REFCURSOR to return JSON data to an ORDS endpoint. I need the case of the returned column names/JSON keys to be preserved for use in my node application. I have tried the following:
...
- 23
1
vote
1
answer
244
views
How to improve performance merging multiple table via cursor
I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query
BEGIN
DECLARE done INT ...
- 111
-1
votes
1
answer
159
views
Stored Proc hangs when no rows exist in cursor? [closed]
I'm on 8.0.19 if that matters.
Here is my test proc:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`()
main:BEGIN
DECLARE fileID INTEGER;
DECLARE finished INTEGER ...
- 21
1
vote
0
answers
93
views
How to write Procedure to fetch data from other table?
drop procedure if exists findClosestTimeStamp;
delimiter $$
create procedure findClosestTimeStamp()
begin
declare tt1 time;
declare dt1 date;
declare view1 varchar(30);
declare done int ...
- 11
0
votes
1
answer
430
views
Set variable inside cursor for to use in select statement [closed]
This is my query to create procedure, but its displaying error like :
'DECLARE curBalRows CURSOR FOR
select if(
What I'm missing here?
DELIMITER $$
drop procedure if exists `...
- 101
1
vote
1
answer
992
views
MySQL - Pass input parameter into Cursor query
Is it possible to pass input parameter into Cursor
SELECT statement WHERE clause?
For some reason I think it isn't working.
I'm trying to pass _TAG and _ITEM_NAME into where clause.
DECLARE ...
- 11
0
votes
1
answer
559
views
Get availability group and listener of the current DB (dynamic SQL)
I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
- 349
0
votes
0
answers
28
views
Is it possible for a FOR LOOP cursor execute operations asynchronously?
I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the ...
- 101
0
votes
1
answer
445
views
MySQL cursor always exits out of loop
The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.
Anything wrong here?
I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it ...
- 1
4
votes
1
answer
353
views
Cursoring over sys.databases skips databases
I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:
I have the following code to perform database backups.
...
- 1,448
0
votes
2
answers
146
views
Overview of leaked/unclosed cursors/statements on DB2 LUW server?
We're encountering a "too many open cursors issue" on DB2/LUW.
Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?
We're doing ...
- 101
2
votes
1
answer
36
views
Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?
In this example:
DECLARE @KeyField INT, @ValueField INT
DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.ValueField = ...
- 25.5k
0
votes
1
answer
34
views
Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows
I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...
0
votes
2
answers
557
views
How to drop a procedure across a lot of databases (SQL Server 2016)
I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
- 1,287
-2
votes
1
answer
83
views
SQL Server: Use Implicit or Explicit Cursor? [closed]
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHILE (1=1)
BEGIN
EXEC 'drop view ' + Table_Name
END
or
DECLARE my_cursor CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
OPEN ...
- 115
0
votes
1
answer
375
views
Procedure MySQL cursor taking a long time to execute
I have a MySQL procedure, with a cursor in it. The sample cursor query is given below
DECLARE cs CURSOR FOR
(SELECT a.mag_id FROM A a WHERE
a.creation_date BETWEEN (...
8
votes
1
answer
220
views
If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?
If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
- 25.5k
0
votes
2
answers
2k
views
Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server
I get this error when I execute this cursor.
Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared.
But ...
- 13
1
vote
1
answer
270
views
Safeguard against infinite looping in case of missing FETCH NEXT
It was found that this code mistake caused an infinite loop in production:
DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @...
- 681
0
votes
0
answers
624
views
Must declare the scalar variable in cursor SQL SERVER
I have this problem with this code.
I don't know why, because before set values.I declare a variable.
Moreover, How i can execute every results that cursor give
Error message: Msg 137, Level 15, ...
- 13
0
votes
1
answer
49
views
Insert top 253 values where value in select distinct tradernames
I am trying to insert top 253 values for each trader from a temp traders table into temp results table.
I am using following code to do it.
CREATE TABLE #trad ( Trad varchar(50))
IF @trad is ...
- 93
6
votes
2
answers
2k
views
Infinite loop in CURSOR
I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an ...
- 239
-1
votes
1
answer
2k
views
Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle
Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query
just with the aim of practicing 'Cursor's .
I have three tables with below structures :
Student:(Student_id , ...
- 1,304
0
votes
2
answers
33
views
finding duplicates and updating a column with values according with number of duplicates using mysql stored procedure
I am using Mysql 10.1.29-MariaDB Database to create a new table.
What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For example,...
0
votes
1
answer
37
views
sp_BlitzCache and cursors
I'm running sp_BlitzCache (Brent Ozar) to get some information about database performance. The program that "attacks" this database uses cursors everywhere, so sp_BlitzCache gives me several lines ...
- 31
0
votes
1
answer
853
views
In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?
Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!.
The problem is that I have a cursor that is crossed, but believe ...
1
vote
1
answer
504
views
Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL
Hi everyone hope you're doing well.
I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not ...
- 1,304
0
votes
2
answers
51
views
Split the results of a query into parts
I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor:
An extra 4% for the first quarter with the highest salary.
An extra 6% ...
3
votes
2
answers
205
views
How do I extend the character limit of a string in management studio
The SQL below works to an extend however when I view the output in SSMS tables with a lot of columns appear to be getting cut of in that the string that is generated by the cursor only appears to go ...
- 219
-1
votes
1
answer
487
views
T SQL Cursor is only updating row with same value [closed]
I'm using MS SQL Server and I have the below table 'tmp_AVG_Weekly_Sales':
RowID SKU Shop Week Avg_Value LAMBDA PMF Value
1 ABC 200 2 1 2 0.13 ...
- 123