Issue in creating oracle function

问题:

I have created a function which need input as month(11) and year(2018) and it will return the first Sunday of the next month.

Create or replace function get_effectivedate (par_Month in int,Par_Year in int);

Return date

Is 

    startDate varchar;
    edate date;

    begin
    startDate := par_Month+Par_Year;
    edate     := select  next_day(add_months(trunc(startDate, 'MM'), 1), 'Sunday') as EffectiveDate from dual; 

     return edate;
     end;

This function is prompting me while running and it is throwing some errors on compilation.

Error:

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

Function GET_EFFECTIVEDATE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

Function GET_EFFECTIVEDATE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

回答1:

You have several issues:

• you have a trailing semicolon on the create ... line;
• you are using a substitution variable (denoted by &) - which you are being prompted for;
• you are using int instead of number for the argument types (not really an error, but using native types is usuually better);
• your startdate local variable is declared as varchar instead of varchar2, which works but is discouraged, but either way you have to specify a laximum length for the string (i.e. startdate varchar2(6););
• you are adding the two numbers together with +, and putting the result of that (i.e. 2029) into a string variable;
• you're using that string variable - if you ignore the & - as a number, not a date, but then trunc won't work;
• if you have the first day of the month already then trunc is pointless anyway.

You can do this quite simply as:

create or replace function get_effectivedate (par_month in number, par_year in number)
return date
is 
begin
  return next_day(
    add_months(
      -- converts the two number to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM'),
      1),
    'Sunday');
end;
/

then call it as:

select get_effectivedate(11, 2018) from dual;

GET_EFFECT
----------
2018-12-02

or for all months this year via a CTE:

with cte (year, month) as (
  select 2018, level from dual connect by level <= 12
)
select year, month, get_effectivedate(month, year)
from cte;

      YEAR      MONTH GET_EFFECT
---------- ---------- ----------
      2018          1 2018-02-04
      2018          2 2018-03-04
      2018          3 2018-04-08
      2018          4 2018-05-06
      2018          5 2018-06-03
      2018          6 2018-07-08
      2018          7 2018-08-05
      2018          8 2018-09-02
      2018          9 2018-10-07
      2018         10 2018-11-04
      2018         11 2018-12-02
      2018         12 2019-01-06

Converting both of the arguments to strings explicitly lets you control leading zeros, so you can be sure that passing in say 1 and 234 will convert to strings '01' and '0234', so when they are concatenated they form '023401', to match the format model.

If you don't specify a day number explicitly then to_date() gives you the first day of the month.

---

The function above gets the second Sunday if the first of the month is itself a Sunday, which you said in comments is what you actually want. (Notice 2018-07-08). If you did actually just want the first Sunday with no further adjustment you do either:

  return next_day(
    add_months(
      -- converts the two numbers to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM'),
      1) - 1, -- find first day of next month, then go back one day to last day of this month
    'Sunday'); -- find next Sunday from that day

or more simply:

  return next_day(
    last_day(
      -- converts the two numbers to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM')
      ), -- find last day of this month
    'Sunday'); -- find next Sunday from that day

either of which givesL

      YEAR      MONTH GET_EFFECT
---------- ---------- ----------
      2018          1 2018-02-04
      2018          2 2018-03-04
      2018          3 2018-04-01
      2018          4 2018-05-06
      2018          5 2018-06-03
      2018          6 2018-07-01
      2018          7 2018-08-05
      2018          8 2018-09-02
      2018          9 2018-10-07
      2018         10 2018-11-04
      2018         11 2018-12-02
      2018         12 2019-01-06

回答2:

You want to call startDate variable so remove ampersand sign,

& is s a sign for prompting user's input. Change your line in code to:

edate     := select  next_day(add_months(trunc(startDate, 'MM'), 1), 'Sunday') as EffectiveDate from dual; 

&value is a user entered parameter