trying to create a procedure but i keep getting an error. here is the code
create or replace procedure HK.PURGE_LAN_DOTS
AS
batchsize number := 15000;
deleteline timestamp := current_timestamp - 365;
counter number := 0;
BEGIN
loop
DELETE FROM LAN.DOTS
WHERE rownum <= batchsize
AND TIMESTAMP < deleteline;
COMMIT;
counter:=counter + SQL%rowcount;
exit when batchsize > SQL%rowcount;
end loop;
HK.PURGE_LAN_DOTS;
END
this is the error iam getting
Error(36,12): PLS-00103: Encountered the symbol "." when expecting one of the following: ;
Probably just typos. This version ought to work (as long as
HK
has access toLAN.DOTS
).PL/SQL scripts are treated differently from standalone SQL in that they require both the trailing semicolon as well as a forward slash on a new line.
Slash vs. semicolon semantics in SQL*Plus is a bit odd; here's another question asked about precisely this: When do I need to use a semicolon vs a slash in Oracle SQL?
Finally, you have included
HK.PURGE_LAN_DOTS;
before theEND
statement, when you probably intended to useEND PURGE_LAN_DOTS;
. Note that you can't include the schema name in this closing tag, and the named closing tag is entirely optional (you can use justEND;
).