CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Tan
Tan

Reputation: 1583

Not able to resolve the error in PL/SQL function

I'm writing a stored function called MAKE_EMAIL that will accept input arguments for first name and lastname and will return a varchar2 value containing the email address in the form of first initial of the firstname followed by the full last name followed by @hpu.edu.

Ex: Calling:

select make_email('Edward','Souza')
from dual

... would return the single value:

'[email protected]'

Please consider the following code for a function:

Code:

create or replace function MAKE_EMAIL(lastname varchar2(10),firstname varchar2(10))
 return VARCHAR 
IS

  email VARCHAR2;

 BEGIN

 email := substr(lastname ,1,1)|| firstname || '@hpu.edu';
RETURN email;

END;
  /

After running above function, I'm getting some errors. Please find the errors below. Please let me know how can I remove these errors or anything wrong with above code?

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION MAKE_EMAIL:

LINE/COL ERROR
-----------------------------------------------------------------
1/38     PLS-00103: Encountered the symbol "(" when expecting one of the
     following:
     := . ) , @ % default character
     The symbol ":=" was substituted for "(" to continue.

1/61     PLS-00103: Encountered the symbol "(" when expecting one of the
     following:
     := . ) , @ % default character
     The symbol ":=" was substituted for "(" to continue.

Here is the link for error:

https://www.dropbox.com/s/gmjhbvie1c7dbcx/Error.png

Upvotes: 1

Views: 2715

Answers (2)

wittrup
wittrup

Reputation: 1565

create or replace function MAKE_EMAIL(lastname varchar2,firstname varchar2)
return VARCHAR2 
IS email VARCHAR2(28);
BEGIN
 email := substr(lastname ,1,1)|| firstname || '@hpu.edu';
RETURN email;
END;​
/

Upvotes: 0

Mat
Mat

Reputation: 206859

You cannot specify the size of a varchar2 parameter to a stored procedure or function. Just remove the two size specifications in your function's declaration and it should compile (assuming no other syntax errors).

On the other hand, the email variable does need a size constraint. So specify one when you declare that.

The max. size for varchar2 in PL/SQL is 32k, so should be more than enough for your use case. If you need to restrict that, do it in your function body.

Upvotes: 3

Related Questions