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

learn more… | top users | synonyms

0
votes
1answer
35 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 ...
0
votes
1answer
44 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
10 views

Update or Insert on a Table based on a column value [migrated]

I have two tables BASE and DAILY as shown below: BASE Cust ID IP address 1 10.5.5.5 2 10.5.5.50 3 10.5.5.6 DAILY Cust ID IP address 1 10.5.5.5 2 10.5.5.70 ...
0
votes
0answers
56 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
80 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
64 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
19 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 ...
2
votes
1answer
274 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 ...
0
votes
0answers
60 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 ...
0
votes
1answer
62 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 ...
0
votes
1answer
371 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 ...
0
votes
0answers
21 views

Cursor brings back Null when run

I'm trying to create a proc which assists in creating an XMLSitepath. I am trying to use a cursor but its not behaving as it should. Please could someone take a look and tell me where I am going ...
2
votes
1answer
665 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 ...
3
votes
4answers
527 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 = ...
2
votes
0answers
32 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
0answers
79 views

Using mysql cursor data to do something for each row

I have a table incoming_sms and another table subscribers_register and another table test_subject. Some of the subscribers in the table subscribers_register have sent in some sms and the messages are ...
0
votes
2answers
90 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
16 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 ...
0
votes
0answers
203 views

merge in cursor is slow ,any alternative for that in oracle 11g

we have a requirement like below. we need to update one table A from another table B based on joining criteria. both tables are having porper joining columns. Table A has 2 columns missing_order_cw ...
2
votes
1answer
387 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
107 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 ...
1
vote
1answer
86 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 ...
2
votes
1answer
346 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
0answers
158 views

Oracle Calling a Procedure Returning a CURSOR in another Procedure

I have 2 packages and a procedures that return a sysref cursor. Now i want to call either of the 2 packages and procedures in another package and procedure that will take the decision on an input ...
0
votes
1answer
51 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 ...
0
votes
1answer
296 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
107 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
616 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 ...
1
vote
0answers
115 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
435 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.
0
votes
1answer
59 views

Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB

As far as I know in Microsoft SQL Server it is possible to use CURSOR to "scan" database and get all tables/entries where specified string is mentioned. Can I do this in Oracle 11g DB? Any ...
0
votes
1answer
1k 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
1answer
3k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
1
vote
1answer
277 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 ...
0
votes
0answers
174 views

Save a dynamic cursor for reuse?

I have a search page where users can select from multiple variables. When the page is submitted to a package in the database a cursor is constructed and opened. a snippet to show what I mean ...
1
vote
2answers
1k views

How can I keep a result set without keeping the transaction open?

The following documentation describes how to see the refcursor returned from a function, here, like this: CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT ...
1
vote
1answer
257 views

To use a cursor or something else in my while loop?

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select ...
0
votes
1answer
97 views

How to arrange rows selected in MYSQL query?

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) . I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A ...
1
vote
0answers
1k views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
0
votes
0answers
774 views

FOR loop oracle query with calulation

I have a list of distinct areas, and would like to track statistics for those districts in a SQL statement. I currently have a setup that runs some summary information, but it doesn't include the ...
0
votes
1answer
194 views

Selecting from a function that returns a sys refcursor or an alternative

I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can: Create a view and then query it. Create a function and return resultset If I ...
1
vote
3answers
3k views

Convert all ntext columns to nvarchar(max)

I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max). This is the code ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar] AS BEGIN SET NOCOUNT ...
3
votes
2answers
13k 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 ...
2
votes
1answer
3k views

How to loop through AS400 table with cursor

I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for ...
0
votes
1answer
153 views

Is the data fetched by an explicit cursor immutable in PL/SQL?

This seems like an obvious question, but Googling has so far failed me... Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a ...
-2
votes
1answer
35 views

Following procedure generates an error and I cannot find the reason [closed]

create or replace procedure buyunits is mnav number; mpno varchar2(100); mtransactioncharge number; mservicecharge number; maccno number; cursor ecursor is select accno from sipholder where ...
-1
votes
1answer
1k views

how cursor implementations are different for each cursor type in sql server

In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
-1
votes
1answer
1k views

I need help in Nested Cursors in SQL server

I need to use Member table, Member_Account table and Class table to calculate Monthly Fee of any member, i've created a procedure and nested cursors for this purpose, please help if anyone understands ...
0
votes
1answer
270 views

Invalid rowid error

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations. declare cursor update_lock is select empno from emp where deptno=&no for update of sal; num ...
1
vote
1answer
5k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...