Questions tagged [cursors]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
60 views

Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?

Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond... "Msg 6401, Level 16, State 1 ... Cannot roll back ...
Chris Marriott's user avatar
0 votes
0 answers
58 views

Does Athena have ref cursors?

Does Athena have a Ref Cursor type construct ? Our team members have written pyspark sql in the following format SELECT dr.case__c as caseId, collect_list(struct( dr.id as documentId, ...
adbdkb's user avatar
  • 103
0 votes
1 answer
121 views

Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL

I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
Niki's user avatar
  • 1
0 votes
1 answer
148 views

Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows

1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values: [a,b,Null], [c,d,Null], [e,f,Null] 2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
M S's user avatar
  • 21
-1 votes
1 answer
97 views

SQL Server Cursor preceed by IF statement? [closed]

The SELECT statement to populate my Sql Server CURSOR can (often) return zero rows. Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ...
M S's user avatar
  • 21
-1 votes
2 answers
1k views

SQL Cursor vs WHILE loop

When you need to loop a set of data, is there a benefit of using a SQL Server cursor. Or is using a WHILE loop (see below) just the same. I ask because the WHILE loop seems clearer and easier to ...
Craig's user avatar
  • 503
0 votes
0 answers
147 views

How to use variable in the select query PostgreSQL

I'm looking for a way to write a recursive query, but I'm getting an error. "SQL Error [42601]: ERROR: syntax error at or near "with recursive" Position: 287" query result "...
AleksTr's user avatar
1 vote
0 answers
79 views

How to get the columnLabel (not columnName) from a cursor

I've seen code to do it with a result set, but the Python code I am using has a cursor not a result set. It is thus printing the wrong labels for the columns. select c1 as Hello, c2 as World from ...
intel_chris's user avatar
2 votes
1 answer
73 views

Insert rows into a table from another table row?

I've got a table Schedule Id unserId Sunday Monday Tuesday Wednesday Thursday Friday Saturday 0 10 0 1 1 1 1 0 0 1 20 1 0 0 0 0 0 0 I need to insert lines from one row in table Schedule into seven ...
XMehdi01's user avatar
  • 302
-1 votes
1 answer
4k views

How to use temp table or while loop instead of cursor

i have an sp to delete data from grandchildren tables . But I need to change the cursor and use a temp table or while loop instead of the cursor. I have tried but was not working can somebody help. Sp ...
Subin Benny's user avatar
0 votes
1 answer
155 views

Loop through schemas and execute dml on the same table in each schema

I have a table "active_t" in a schema called "test", having the following structure: instance active IN 1 SI 0 The ask is to loop ...
Yuvraj's user avatar
  • 1
2 votes
1 answer
301 views

Procedure not looping properly

I have several client databases on one server. They all have a prefix. Lets use "piggy_" as the prefix for the purposes of this. I.e. piggy_client_a piggy_client_b piggy_client_c Below is ...
Chris's user avatar
  • 123
4 votes
0 answers
143 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 ...
DForck42's user avatar
  • 3,038
4 votes
1 answer
656 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 ...
Joe Obbish's user avatar
  • 32.1k
1 vote
0 answers
4k 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 ...
Appleoddity's user avatar
2 votes
1 answer
173 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 ...
Tom's user avatar
  • 428
4 votes
1 answer
466 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 ...
SE1986's user avatar
  • 1,950
0 votes
1 answer
3k 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 ...
Depeche's user avatar
0 votes
0 answers
600 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 ...
user1664043's user avatar
2 votes
2 answers
3k 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; ...
XxaemaethxX's user avatar
0 votes
1 answer
2k 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 ...
PeterPepper's user avatar
0 votes
0 answers
359 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 ...
Bibek Shah's user avatar
0 votes
1 answer
575 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 ...
Kahn's user avatar
  • 1,813
0 votes
1 answer
1k 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 ...
Badger's user avatar
  • 35
0 votes
0 answers
56 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 ...
MattyZDBA's user avatar
  • 1,933
0 votes
4 answers
204 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....
wistlo's user avatar
  • 119
1 vote
2 answers
2k 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 ...
Diomidis Spinellis's user avatar
1 vote
1 answer
804 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, ...
Cotex's user avatar
  • 51
0 votes
1 answer
543 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, ...
user1337's user avatar
  • 103
1 vote
2 answers
867 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 ...
MattyZDBA's user avatar
  • 1,933
0 votes
1 answer
202 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: ...
Josh's user avatar
  • 23
1 vote
1 answer
564 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 ...
ECie's user avatar
  • 111
-1 votes
1 answer
403 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 ...
Errol Neal's user avatar
1 vote
0 answers
456 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 ...
Nurav's user avatar
  • 11
0 votes
1 answer
2k 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 `...
Manohar Kumar's user avatar
1 vote
1 answer
2k 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 ...
R0bert2's user avatar
  • 111
0 votes
1 answer
3k 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 ...
user1930901's user avatar
0 votes
0 answers
55 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 ...
jbrunoxd's user avatar
  • 101
0 votes
1 answer
1k 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 ...
R0bert2's user avatar
  • 111
4 votes
1 answer
636 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. ...
SE1986's user avatar
  • 1,950
0 votes
2 answers
280 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 ...
geert3's user avatar
  • 101
2 votes
1 answer
631 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 = ...
J.D.'s user avatar
  • 37.1k
0 votes
1 answer
183 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' ...
Ryan Park's user avatar
0 votes
2 answers
962 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 ...
Saeed Neamati's user avatar
-2 votes
1 answer
159 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 ...
david's user avatar
  • 115
0 votes
1 answer
812 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 (...
Prasenjit Debnath's user avatar
8 votes
1 answer
369 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 ...
J.D.'s user avatar
  • 37.1k
0 votes
2 answers
3k 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 ...
SakZepelin's user avatar
1 vote
1 answer
847 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 @...
Elaskanator's user avatar
0 votes
0 answers
744 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, ...
SakZepelin's user avatar

1
2 3 4 5