| procedure to delete data based on date [message #130117] |
Thu, 28 July 2005 04:35  |
gautamvv
Messages: 254
Registered: June 2005
|
Senior Member |
|
|
I HAVE A TABLE WHICH DOES NOT HAVE A DATE FIELD, ie, all the columns are
either varchar2 or number datatype columns.
let us assume that we enter data regularly in this table.
i want to write a procedure which preserves the data for last 10 days and deletes
all the data before the last 10 days.
example, today is 28th july, i want a procedure which should delete the data from
the beginning till 18th july.
now our task becomes simple if the table has a date column.
i can say
SQL> SELECT * FROM ANEXAM;
COL1 MYDATE
--------- ---------
1 20-JUL-05
2 21-JUL-05
3 22-JUL-05
4 23-JUL-05
5 24-JUL-05
6 25-JUL-05
7 26-JUL-05
8 27-JUL-05
9 28-JUL-05
9 rows selected.
SQL> CREATE OR REPLACE PROCEDURE APROC IS
2 BEGIN
3 DELETE FROM ANEXAM WHERE MYDATE BETWEEN MYDATE AND SYSDATE-2;
4 COMMIT;
5 END;
6 /
Procedure created.
SQL> EXECUTE APROC;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM ANEXAM;
COL1 MYDATE
--------- ---------
8 27-JUL-05
9 28-JUL-05
but how to achieve the same functionality, if we dont have a date column in the table?
|
|
|
|
| Re: procedure to delete data based on date [message #130154 is a reply to message #130117] |
Thu, 28 July 2005 08:09  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You are going to have to introduce something, whether in your existing table or in some other table, that associates specific rows with specific dates. How else can it be done?
Options would include altering your table to add another field which is the date, and always putting sysdate into that when you insert rows (either through your insert statement or a trigger).
You might could also accomplish this with Oracle Workspace Manager. I'm guessing, but I would think it could handle this.
You could probably also work up a solution using oracle auditing to tables.
Or you could have some sort of lookup table with your pk in this table having an entry in some other table that has a date, or some sort of translation from id to date.
Or you could use ranges of ids (pks in this table) instead of dates to use as the basis of your deletion criteria.
|
|
|
|
| Re: procedure to delete data based on date [message #130243 is a reply to message #130117] |
Thu, 28 July 2005 23:40 |
gautamvv
Messages: 254
Registered: June 2005
|
Senior Member |
|
|
thanks
the procedure that i posted previously works fine,
but what if i want to pass an argument, say , number of days to
delete .
like i want to call the procedure passing in the number of days
to delete .
i am getting an error;
QL> desc myexam2;
Name Null? Type
----------------------------------------------------- -------- -------------
COL1 NUMBER(4)
MYDATE DATE
SQL> SELECT * FROM MYEXAM2;
COL1 MYDATE
--------- ---------
1 14-JUL-05
2 15-JUL-05
3 16-JUL-05
4 17-JUL-05
5 18-JUL-05
6 19-JUL-05
7 20-JUL-05
8 21-JUL-05
9 22-JUL-05
10 23-JUL-05
11 24-JUL-05
12 25-JUL-05
13 26-JUL-05
14 27-JUL-05
15 28-JUL-05
16 29-JUL-05
16 rows selected.
SQL> CREATE OR REPLACE PROCEDURE MYPROC(DAYS_TO_DELETE IN NUMBER) IS
2
3 BEGIN
4
5 DELETE FROM MYEXAM2 WHERE MYDATE<=DAYS_TO_DELETE;
6
7 END;
8 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERR
Errors for PROCEDURE MYPROC:
LINE/COL ERROR
-------- ---------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/33 PLS-00306: wrong number or types of arguments in call to '<='
i want to pass the number of days, and the data should be deleted
for ex, i call exec myproc(2), the only data that should
remain in the table is the data entered in 28th and 29th.
|
|
|
|
| Re: procedure to delete data based on date [message #130245 is a reply to message #130117] |
Thu, 28 July 2005 23:48 |
gautamvv
Messages: 254
Registered: June 2005
|
Senior Member |
|
|
got it !!
SQL> SELECT * FROM MYEXAM3;
COL1 MYDATE
--------- ---------
1 14-JUL-05
2 15-JUL-05
3 16-JUL-05
4 17-JUL-05
5 18-JUL-05
6 19-JUL-05
7 20-JUL-05
8 21-JUL-05
9 22-JUL-05
10 23-JUL-05
11 24-JUL-05
12 25-JUL-05
13 26-JUL-05
14 27-JUL-05
15 28-JUL-05
16 29-JUL-05
16 rows selected.
SQL> CREATE OR REPLACE PROCEDURE MYPROC(DAYS_TO_DELETE IN NUMBER) IS
2
3 BEGIN
4
5 DELETE FROM MYEXAM3 WHERE MYDATE<=sysdate-(DAYS_TO_DELETE);
6
7 END;
8 /
Procedure created.
SQL> EXECUTE MYPROC(6);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MYEXAM3;
COL1 MYDATE
--------- ---------
11 24-JUL-05
12 25-JUL-05
13 26-JUL-05
14 27-JUL-05
15 28-JUL-05
16 29-JUL-05
just a small modification was needed
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
