Questions tagged [cursors]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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; ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 `...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
  • 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 = ...
user avatar
  • 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' ...
user avatar
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 ...
user avatar
-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 ...
user avatar
  • 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 (...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 @...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 , ...
user avatar
  • 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,...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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% ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 123