473,320 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

cursor select/delete where current of not working

I just inherited support of this app so bear with me. The scenario: Oracle
9i replicated databases, same C++ server process runs on both boxes. On a 15
minute schedule, the server that is primary deletes records out of a lock
table based on age. Once a week, the primary box is rebooted and clients are
redirected to the secondary. Once the boot is complete, the primary box
continues to handle record cleanup although clients stay pointed to the
secondary. The next night, the secondary bounces and clients move back to
primary.

The problem that I have is that some (and only some) records that are
inserted by the secondary server are never cleaned up by the primary even
though the primary server logs the existence of these records when they
first age & the fact that a delete was executed. Further, despite the fact
that a command line sqlplus select displays them days later, the primary
server never logs their occurrence again. It is as if once the delete is
executed, they are no longer returned in the select to the server process.
This condition spans reboots such that the primary fails to recognize
records that are several weeks old. Hundreds of records are properly cleaned
each week but those that are not are always generated from the secondary
server. No errors are logged to indicate a problem.

At first blush, the answer is simple: broken code. But a code review could
not find any cause for this behavior. See code below:
<start of pseudo-code>

//agebuf set to '0 00:15:00'
exec sql at :instancename declare c cursor for
select * from lock_tbl where lock_timestamp at local <
(localtimestamp - to_dsinterval(:agebuf));
if (sqlca.sqlcode != 0) {
Jul 19 '05 #1
0 4440

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Philip Mette | last post by:
Does anyone have any good references they could recommend on Cursor based SQL writing? I have to create SQL that can loop though records simular to VB loops and I have been told that this is the...
15
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I...
0
by: Anthony Robinson | last post by:
I have a stored procedure that needs to loop through a record set, evaluate value, then either perform an operation or move tro the next record - depending on some criteria. Here's the procedure:...
7
by: Jean-Marc Blaise | last post by:
Dear all, To DELETE rows in a EEE context (AIX 5.1 / V7.2 FP11 or V8.1 FP5), I have used a CLI program and a shell that runs on each partition. What I am surprised is about the slowness of this...
10
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default)...
8
by: PaulR | last post by:
Hi, we have a procedure with following structure ... Insert into some regular tables using a unique 'call_id' returning a cursor to caller with the rows relevant to this CALL . CREATE...
0
by: gpspocket | last post by:
help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME,...
0
by: Will Potter | last post by:
I just inherited support of this app so bear with me. The scenario: Oracle 9i replicated databases, same C++ server process runs on both boxes. On a 15 minute schedule, the server that is primary...
9
by: Frank Swarbrick | last post by:
New to SQL here... We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT.ACCOUNT_ID , ACCT.APPL_ID , ACCT.BRANCH_NUMBER , ACCT.CATEGORY_CODE
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.