74680

Issue in creating oracle function

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>

Answer1:

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

Answer2:

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>

Recommend

  • SQL: Show average and min/max within standard deviations
  • Printing in IE8 Has @href contents inline
  • How can I organize this data into the objects I want with LINQ?
  • How to wait till webViewDidFinishLoad gets completed
  • How can I use scipy optimization to find the minimum chi-squared for 3 parameters and a list of data
  • How can I merge my files when the folder structure has changed using Borland StarTeam?
  • how to get username into sql trigger when multiple users signed on from asp membership
  • Cannot save model when using ember render helper
  • Knockout custom binding handler
  • Filter strings with regex before casting to numeric
  • Using Sax parsing to edit and write XML in VB6
  • RxJava debounce by arbitrary value
  • pyodbc doesn't report sql server error
  • D3 get axis values on zoom event
  • C: Incompatible pointer type initializing
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Groovy: Unexpected token “:”
  • Custom validator control occupying space even though display set to dynamic
  • JSON response opens as a file, but I can't access it with JavaScript
  • How to create a file in java without a extension
  • Django: Count of Group Elements
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Nant, Vault & Windows Integrated Authentication
  • PHP - How to update data to MySQL when click a radio button
  • jQuery show() function is not executed in Safari if submit handler returns true
  • Counter field in MS Access, how to generate?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Weird JavaScript statement, what does it mean?
  • How do you troubleshoot character encoding problems?
  • How to get next/previous record number?
  • How to format a variable of double type
  • PHP: When would you need the self:: keyword?
  • Acquiring multiple attributes from .xml file in c#
  • How to get Windows thread pool to call class member function?
  • apache spark aggregate function using min value
  • Is there any way to bind data to data.frame by some index?
  • reshape alternating columns in less time and using less memory
  • How to Embed XSL into XML
  • How can I use threading to 'tick' a timer to be accessed by other threads?