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

learn more… | top users | synonyms

3
votes
4answers
262 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
18 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
19 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
63 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
13 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
77 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
117 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
86 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
39 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
211 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
100 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
32 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
160 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
71 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
192 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 ...
0
votes
1answer
72 views

Insert Into does not work with multiple rows

Here Is my code : CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER ...
1
vote
0answers
90 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
131 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
46 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
2k 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
213 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
129 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
811 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
218 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
73 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
666 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
125 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
9k 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
2k 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
109 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
667 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
499 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
249 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
4k 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; ...
2
votes
1answer
219 views

Internal architecture of cursors

I know that cursor isn't very good idea, but I have some questions for internal structure of cursor. First step in cursor is determining result set which cursor iterate over. So, did cursor execute ...
1
vote
0answers
64 views

Map records to a time interval

I have two tables: SamplesTbl contains samples every 5 seconds of a bike ride RxTbl contains the prescribed stages and other values of a bike ride SamplesTbl SamplesID int SessionNum int StageNum ...
5
votes
1answer
1k views

Executing SPs in parallel (maybe with a cursor) [closed]

We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted ...
3
votes
0answers
490 views

SQL Server 2008 R2 clustered index issue

Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization. A stored procedure is written for our application which contains a cursor in it. ...
0
votes
2answers
680 views

limit and then group by in MySQL

I have a mysql table having following structure : payment_id(int auto_increment), emp_id(int), chargeTime(datetime), payment(double) I need to have three latest (with respect to chargeTime) ...
2
votes
2answers
4k views

MySQL: Loop over cursor results ends ahead of schedule

I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that: EDIT: Sorry, stupid mistake. The trigger is executed ...
2
votes
2answers
355 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
1
vote
1answer
447 views

This refactoring from cursor on a dblink'ed Oracle table OK?

The data is being compared from the local version of the table to one in the contracts linked server. I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle ...
2
votes
2answers
105 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
3
votes
1answer
428 views

Optimization around static cursor use

I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no ...
2
votes
3answers
710 views

Find and remove gaps in sequence across two different columns

I've tried to solve this problem using a set-based approach. However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong. The table: Project, item, method, ...
1
vote
2answers
4k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...