Oracle-Sqldeveloper function: compilation error in "END my_function;" statement

Question

So i'm debugging a sql function with the compilation error:

Error(16,1): PLS-00103: Encountered the symbol "/"

..which is occurring at the very end of the function:

create or replace
function total_weekdays(fromdate in date, todate in date)
  return number
  as totaldays number := 0;
  dates number := 0;
BEGIN
  select to_number(count(dates)) into totaldays from
  (select to_char(fromdate + level -1, 'dd/mm/YYYY DY') as dates
    from dual connect by level <= todate - fromdate +1
  minus
  select to_char(ho_date, 'dd/mm/YYYY DY') as dates
    from FS.fs_holiday
    ) where not regexp_like(dates,'SUN|SAT');
return totaldays;
END;
/

Prompt *** RPT_PACK_GEN_INV_MOVEMENT Package ***
CREATE OR REPLACE PACKAGE REPORTS_CLIENT."RPT_PACK_GEN_INV_MOVEMENT" AS

  Procedure GEN_INV_MOVEMENT(pv_RunDate_i DATE default null);
  Procedure RE_GEN_INV_MOVEMENT(pv_RunDate_i DATE default null);
  Procedure GEN_INV_MOVEMENT_SUMMARY(pv_RunDate_i DATE default null);
  Procedure GEN_Opening(pv_RunDate_i DATE);

end RPT_PACK_GEN_INV_MOVEMENT;

I think, if you are using ; then you don't need /, it's either of the two. — Ferdinand Gaspar, Aug 10 '17 at 19:23
Is this in a SQL worksheet or in the object viewer? If the latter then remove the trailing slash. — Alex Poole, Aug 10 '17 at 19:24
You check this link for the explanation https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql — Ferdinand Gaspar, Aug 10 '17 at 19:33
Thanks for answering! I've tried removing both ";" and "/", and I'm still getting the error. That being said, I did not provide enough context in my original post. I'm putting the full block in the answers section — jsatlien, Aug 10 '17 at 19:37
anyways, when I remove the "/", I get the same error, but replace with 'Encountered the symbol "Prompt"'. However, removing both the "/" AND the rest of the block beneath it causes this to compile without error. Still scratching my head as to why — jsatlien, Aug 10 '17 at 19:47
In a SQL worksheet your modified code still compiles OK (again, removing the schemas that I don't have). Removing both the slash and last semicolon would give you a different error. If you're running the whole thing in a worksheet as a script then you need to the slash to mark the end of the function. I'm 99% sure you're doing this in the object viewer instead of in a worksheet. You can't define multiple objects (or use prompt) in the object viewer. — Alex Poole, Aug 10 '17 at 19:47
Yeah sorry I never answered that, but yes it's in the object viewer — jsatlien, Aug 10 '17 at 19:55
@AlexPoole ahh yeah I see what you mean about multiple objects. I guess the best solution would just be to redefine that second object elsewhere eh? — jsatlien, Aug 10 '17 at 19:58
I already added a bit about that to my answer. I usually use a script in a worksheet, but you can create objects through the wizards too. — Alex Poole, Aug 10 '17 at 19:58

Alex Poole · Answer 1 · 2017-08-10T19:58:00.300

What you've shown compiles OK in a worksheet (without the FS. schema prefix for the demo):

create table fs_holiday(ho_date date);

create or replace
function total_weekdays(fromdate in date, todate in date)
  return number
  as totaldays number := 0;
  dates number := 0;
BEGIN
  select to_number(count(dates)) into totaldays from
  (select to_char(fromdate + level -1, 'dd/mm/YYYY DY') as dates
  from dual connect by level <= todate - fromdate +1
minus
 select to_char(ho_date, 'dd/mm/YYYY DY') as dates
 from fs_holiday
 ) where not regexp_like(dates,'SUN|SAT');
 return totaldays;
END;
/

Function TOTAL_WEEKDAYS compiled

If you open the function in the object viewer and click on the SQL tab then it is shown without the trailing slash.

Adding the slash back in and then compiling does then give the error you're seeing,

Error(15,1): PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.

In the object viewer context you should not have the slash. In the worksheet, as in SQL*Plus, SQL Developer:

... treats PL/SQL subprograms in the same manner as SQL commands, except that a semicolon (;) or a blank line does not terminate and execute a block. Terminate PL/SQL subprograms by entering a period (.) by itself on a new line. You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.

So in the worksheet the slash is necessary to delimit the end of the (named) block and identify the entirety of the create statement.

In the object viewer you are only seeing the code for that object, so there is no need to have that slash delimiter, and everything in the SQL window is treated as part of the object source code.

Similarly if you look in the user_source data dictionary view, there is no line stored for the slash.

select line, text from user_source where name = 'TOTAL_WEEKDAYS' order by line;

      LINE TEXT
---------- --------------------------------------------------------------------------------
         1 function total_weekdays(fromdate in date, todate in date)
         2   return number
         3   as totaldays number := 0;
         4   dates number := 0;
         5 BEGIN
         6   select to_number(count(dates)) into totaldays from
         7   (select to_char(fromdate + level -1, 'dd/mm/YYYY DY') as dates
         8   from dual connect by level <= todate - fromdate +1
         9 minus
        10  select to_char(ho_date, 'dd/mm/YYYY DY') as dates
        11  from fs_holiday
        12  ) where not regexp_like(dates,'SUN|SAT');
        13  return totaldays;
        14 END;

14 rows selected.

It is part of the client's statement handling, not part of the function.

If you are in the object viewer then you can't create a second object in the same SQL window. That is the source code for the object you are viewing.

If you want to create multiple objects at once in a script then use a worksheet (right-click your connection and choose 'Open SQL Worksheet'), put all your code in, and then run as a script (F5). If you want to use the object view then expand your connection, right-click on the 'Packages' option, and choose 'New Package'.

Read more about managing objects and the SQL Worksheet.

I found the root of my problem. The database script in which that function and package were defined was missing a blank line between that last "/" and the Prompt/package defined below it, which was causing that second block to be attached to the first function object. After running that script again, the 'total_weekdays' object now looks like it's defined correctly. Thanks again for your time/effort/assistance! — jsatlien, Aug 10 '17 at 20:26
@jsatlien - the combined code you posted already had a blank line. Creating both in the object viewer gets the same error with or without the blank line,. Running as a script in the worksheet works with or without the blank line. That is not the root of your problem. You were trying to use the object viewer's SQL window incorrectly. I think you re-ran the script in the worksheet, and anything else you changed wasn't relevant. Anyway, glad it worked out in the end. — Alex Poole, Aug 11 '17 at 08:26

Oracle-Sqldeveloper function: compilation error in "END my_function;" statement

1 Answers1