difficulties creating a pl/sql procedure

Question

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: ;

The code you've posted doesn't have 36 lines, so it isn't clear what the error is referring to. If it's part of a bigger script do you actually have a trailing semicolon after the `END`, and suitable statement separators - like a `/` on a line on its own - between this and the next thing you do in the script? — Alex Poole, Jul 09 '19 at 13:56
Unless you intend to create to create a never ending recursive procedure the line "HK.PURGE_LAN_DOTS;" needs to be removed. I believe what your looking for is "END PURGE_LAN_DOTS; (no schema name). Further using TIMESTAMP an a column name is extremely poor practice as that term is an Oracle defined type. Using as column name, while allowed, leads to very hard to find errors. — Belayer, Jul 09 '19 at 16:10

score 1 · Answer 1 · answered Jul 09 '19 at 15:46

Probably just typos. This version ought to work (as long as HK has access to LAN.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 the END statement, when you probably intended to use END 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 just END;).

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;

END PURGE_LAN_DOTS;
/

Alex Poole · Answer 2 · 2019-07-09T16:21:12.747

The end of the code you posted has this:

  end loop;
  HK.PURGE_LAN_DOTS;
END

The END is missing a semicolon; but the previous line calls this procedure, which would cause infinite recursion (which Oracle would kill eventually).

I think you've corrupted this while posting and you actually have:

  end loop;
END HK.PURGE_LAN_DOTS;

and now the error makes sense (although the report line and columns numbers don't quite).

Although the create statement takes an optional schema prefix:

create or replace procedure HK.PURGE_LAN_DOTS

the matching END does not. The overall statement can sort of be thought of as a mix of SQL and PL/SQL - not to the same extent that a trigger is, but here the effect is similar. The statement really does several things - it creates a procedure-type object called PURGE_LAN_DOTS under that schema, and compiles and stores the PL/SQL part of the code with the same name. The HK. bit is not part of the object name, and is not relevant to the PL/SQL engine - and the END is pure PL/SQL. If you look at the all_source view you'll see that the stored source will be PROCEDURE PURGE_LAN_DOTS AS ... without either the create or replace or the HK. prefix - the owner will be set to HK, though.

So, the END should only reference the PL/SQL object name, and cannot have a schema prefix:

create or replace procedure HK.PURGE_LAN_DOTS
...
  end loop;
END PURGE_LAN_DOTS;

Not related, but:

deleteline timestamp := current_timestamp - 365;

will cause the current_timestamp value to be converted to a date to have 365 days subtracted, and that will then be converted back to a timestamp; which is more conversion than necessary, and loses the fractional seconds. You probably don't really care about that in this scenario, but sometimes it matters. To avoid both you could do either of these:

deleteline timestamp := current_timestamp - interval '365' day;
deleteline timestamp := current_timestamp - 365 * interval '1' day;

Once you switch to an interval you might be tempted to change that to current_timestamp - interval '1' year, but that will error with ORA-01839 if you run it on February 29th...

Also make sure you really do want current_timestamp and not systimestamp.

difficulties creating a pl/sql procedure

2 Answers2