Question:

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

<a href="https://i.stack.imgur.com/LE8V7.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/LE8V7.png" data-original="https://i.stack.imgur.com/LE8V7.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

You have several issues:

<ul><li>you have a trailing semicolon on the create ... line;</li> <li>you are using a substitution variable (denoted by &) - which you are being prompted for;</li> <li>you are using int instead of number for the argument types (not really an error, but using native types is usuually better);</li> <li>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););</li> <li>you are adding the two numbers together with +, and putting the result of that (i.e. 2029) into a string variable;</li> <li>you're using that string variable - if you ignore the & - as a number, not a date, but then trunc won't work;</li> <li>if you have the first day of the month already then trunc is pointless anyway.</li> </ul>

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.

<hr />

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

You want to call startDate variable so remove ampersand sign,

& is s a sign <a href="https://www.experts-exchange.com/questions/21305720/How-to-prompt-user-for-input-in-plsql-procedure.html" rel="nofollow">for prompting user's input</a>. Change your line in code to:

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

&value is a user entered parameter

</blockquote>