|
View previous topic :: View next topic
|
| Author |
Message |
r2k1984
New User
.jpg)
Joined: 21 Jun 2005
Posts: 69
Location: chennai
|
|
|
|
hi,
I have a small query .
1. I am reading a table with cursor based on account number .So all the account numbers are been fetched in to temp memory
2. Now iam fetching the records one by one.
3. On some processing Iam deleting some next ten rows from the table.
4. On the next fetch which records will be retrived from the fetch, whether the records deleted or the 11th record from table.
please let me know asap |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
On some processing Iam deleting some next ten rows from the table.
|
???
Whichever rows satisfied the cursor will still be available .... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
ramakrishnan
| Quote: |
| I am reading a table with cursor based on account number |
what exactly is the DEFINE CURSOR statement.
| Quote: |
| So all the account numbers are been fetched in to temp memory |
temp memory? are you trying to be technical? What do you mean by this?
| Quote: |
| Now iam fetching the records one by one |
that depends on the DEFINE CURSOR statement and the clauses of the FETCH statement.
| Quote: |
| On some processing Iam deleting some next ten rows from the table. |
which table? Do you issue a COMMIT anytime during this processing.
| Quote: |
| On the next fetch which records will be retrived from the fetch, whether the records deleted or the 11th record from table. |
that depends on the answers to the above questions.
| Quote: |
| please let me know asap |
you are kidding, right?
have you tried this yourself - not a very involved test program.
and you realize, the lack of information provided, precludes any answer other than Ashmir's?
You think we have nothing else to do except use our time for your technically unqualified theoretical BS? |
|
| Back to top |
|
|
r2k1984
New User
Joined: 21 Jun 2005
Posts: 69
Location: chennai
|
|
|
|
hey see Iam explaingin in detail.
I have declared a cursor to read all the records from the database whcih contains some 500 accounts.
on processing Iam deleting 100 with delete statement ,
Now I need to know whether it will be updated in the table and also in the cursor.
I dont want the accounts to come in the fetch statement and process the records .
Whether the problem will be solved after if I put a commit statement after the delete.
Or I should use the cursor with update option in the cursor definition.
Thanks in advance for the help |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
r2k1984,
| Quote: |
| [ I should use the cursor with update option in the cursor definition. |
....How would this help you in your current scenario.
| Quote: |
Whether the problem will be solved after if I put a commit statement after the delete.
|
Your processing should be logical, Open the cursor, fetch from the cursor, delete rows from the table and then issue a commit ....Again open the cursor and carry on similar lines as per your logic or requirement.
WTH |
|
| Back to top |
|
|
r2k1984
New User
Joined: 21 Jun 2005
Posts: 69
Location: chennai
|
|
|
|
| I dont want to close the cursor and open it again because it will be overburden and it will go in loop if last record is deleted during the processing. |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
| Quote: |
| I dont want to close the cursor and open it again because it will be overburden |
All the open cursors will close on commit until and unless you are using 'WITH HOLD' option....so you don’t go and explicitly close them.
| Quote: |
| it will go in loop if last record is deleted during the processing. |
There is nothing like first and last in the result set until you are not using order by in your query. Secondly, what makes you think it would go in a loop, use the SQL codes to determine your logic.
Do let us know your final results.
WTH |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| On what basis are you deleting the records ???... why dont you keep track of all records to be deleted ( values from cursor) and issue a single delete ???? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I dont want to close the cursor and open it again because it will be overburden |
Where did this belief originate?
| Quote: |
it will go in loop if last record is deleted during the processing.
|
No, it won't if the code is written properly. FWIW - there is no "last record". |
|
| Back to top |
|
|
|
|
