Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
206 questions
0
votes
1
answer
643
views
"ERROR: Cursor does not exist" in PL/pgSQL procedure
I am trying to transfer a specific amount of film with an specific quantity from one store_id to another. For this I have tried doing a procedure:
CREATE OR REPLACE PROCEDURE transfer_inventory(
...
0
votes
1
answer
143
views
Run TSQL alter db set trustworthy on multiple times
We have a few SQL Server databases that needs to have trustworthy option set to 'ON' to allow for external assemblies to load, but every time there's any security patch, the option sets to off and the ...
- 3
1
vote
1
answer
121
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 ...
0
votes
1
answer
377
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 ...
- 1
0
votes
1
answer
501
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 ...
- 21
-1
votes
1
answer
194
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 ...
- 21
-1
votes
2
answers
3k
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 ...
- 503
0
votes
0
answers
173
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 "...
- 1
1
vote
0
answers
116
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 ...
- 141
2
votes
1
answer
80
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 ...
- 292
-1
votes
1
answer
5k
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 ...
- 67
0
votes
1
answer
200
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 ...
- 1
2
votes
1
answer
356
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 ...
- 123
4
votes
0
answers
157
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 ...
- 3,068
4
votes
1
answer
724
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 ...
- 32.6k
1
vote
0
answers
5k
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 ...
- 324
2
votes
1
answer
210
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 ...
- 428
4
votes
1
answer
586
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 ...
- 2,083
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
...
- 1
0
votes
0
answers
787
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 ...
- 255
2
votes
2
answers
5k
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;
...
- 121
0
votes
1
answer
3k
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
0
answers
373
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
734
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,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
...
- 35
0
votes
0
answers
67
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,935
0
votes
4
answers
291
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....
- 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 ...
1
vote
1
answer
1k
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
728
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
1k
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,935
0
votes
1
answer
233
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
623
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
466
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
524
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
3k
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
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 ...
- 111
0
votes
1
answer
4k
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
62
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
2k
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 ...
- 111
5
votes
1
answer
748
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.
...
- 2,083
0
votes
2
answers
331
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
867
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 = ...
- 39.5k
0
votes
1
answer
303
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
1k
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,505
-2
votes
1
answer
187
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
953
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
392
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 ...
- 39.5k
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 ...
- 21
1
vote
1
answer
1k
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 @...
- 741