• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Count records...

Hi,
I developed DB in ORACLE 11g XE and since there is a limitation (11GB), the DB is keeping only the records with certain criteria. The records are deleted (with stored procedure) once in a day (23:59)...
The client requested to have statistics / reports on the records.
I think that it will be the best solution to count the deleted records and write the count in one table with time stamp... Can someone help me?
Thank you in advance,
Best,
Andreja
0
andrejaTJ
Asked:
andrejaTJ
  • 2
  • 2
3 Solutions
 
MikeOM_DBACommented:
Perhaps you could persuade your client to PURCHASE at minimum the Standard edition.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
directly after the delete, use SQL%ROWCOUNT value.
this would be the pl/sql draft to start with:
DECLARE 
  v_rows NUMBER;
BEGIN
DELETE yourtable WHERE .... ;
  v_rows := SQL%_ROWCOUNT;
 INSERT INTO your_log_table ( datetime, rows_deleted ) VALUES ( SYSDATE, v_rows);
 COMMIT;
END; 

Open in new window

0
 
andrejaTJAuthor Commented:
Thank you for your prompt reply.
But the "DELETE yourtable WHERE" is not applicable here...
Can you please help me without the "DELETE yourtable WHERE"?I will schedule job prior the one that is calling the DELETE stored procedure...
Thank you,
Best,
Andreja
0
 
MikeOM_DBACommented:
You may need to add Guy's suggested code inside the DELETE stored procedure.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly.
you cannot count "outside" the stored procedure on how many records you deleted exactly.

well, you COULD do the count of the records to be deleted using the same condition the stored procedure is doing, but then you will just waste db power to do 2 times the same thing.
you COULD also counter the number of records in the table before and after the procedure, but this will give erroneous results if during that time, new records are added.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now