-- Create a storage process for batch SMS dump and transfer sent SMS messages to the SMS History Table
Create or replace procedure PUB_SHORTMSG_ARCH_BAT
As
MSG_ID pub_shortmsg_td.shotmsg_id % type;
Cursor cur_msg is
Select shotmsg_id from pub_shortmsg_td a where a. send_flag! = 0;
Cur_rec cur_msg % rowtype;
Begin
Open cur_msg;
Loop
Fetch cur_msg into cur_rec;
Exit when cur_msg % notfound;
MSG_ID: = cur_rec.shotmsg_id;
Insert into his_pub_shortmsg_td (select * from pub_shortmsg_td where shotmsg_id = MSG_ID );
Delete pub_shortmsg_td where shotmsg_id = MSG_ID;
COMMIT;
End loop;
Close cur_msg;
End PUB_SHORTMSG_ARCH_BAT;
-- Create a job and execute the historical dump of sent messages at every day
VARIABLE job NUMBER;
Begin
Sys. dbms_job.submit (job =>: job,
What => 'pub _ SHORTMSG_ARCH_BAT ();',
Next_date => to_date ('30-08-2008 01:30:05 ', 'dd-mm-yyyy hh24: mi: ss '),
Interval => 'sysdate + 1 ');
Commit;
End;
/
