Nrthugu

I am using oracle SQL developper.

I am trying to create a function that will accept two parameters (first and last name) and return them as one variable, with the last name showing up first. Here is my function.

CREATE OR REPLACE FUNCTION LASTNAMEFIRST

(

varFirstName IN VARCHAR2,

varLastName IN VARCHAR2

)

RETURN VARCHAR2 AS 

BEGIN



DECLARE varFullName VARCHAR2;



DEFINE varFullName := CONCAT(varLastName,' ' ,varFirstName);



  RETURN varFullName;

END LASTNAMEFIRST; 

I am receiving an error on the semicolon at 'end lastnamefirst' "syntax error"
I keep trying to change small things and that same error just shows up in different places whenever I change things. What am I doing wrong?

Wrong syntax. Should be

SQL> create or replace function lastnamefirst

  2    (varfirstname in varchar2,

  3     varlastname in varchar2)

  4  return varchar2

  5  as

  6  begin

  7    return varlastname||' '||varfirstname;

  8  end;

  9  /



Function created.



SQL> select lastnamefirst('Little', 'Foot') result from dual;



RESULT

------------------------------

Foot Little



SQL>

What's wrong with your code?

• you don't DECLARE within the body; if you do, there's no DECLARE keyword at all, and datatype requires length (such as VARCHAR2(30))


• 
  CONCAT accepts only two arguments; use a concatenation operator, double pipe || instead


• there's no DEFINE in PL/SQL

I would expect the Oracle syntax to look more like:

CREATE OR REPLACE FUNCTION LASTNAMEFIRST (

   in_FirstName IN VARCHAR2,

   in_LastName IN VARCHAR2

)

RETURN VARCHAR2 AS

    v_FullName varchar2(4000);

BEGIN

    v_FullName := in_LastName || ' ' || in_FirstName;



    RETURN v_FullName;

END;  --  LASTNAMEFIRST; 

This can of course be simplified (say by not using a local variable), but it follows the logic of your code.

Using DECLARE where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLAREisn't necessary if you move the variable declaration prior to the BEGIN. DEFINE is also invalid. Something like this should work:

CREATE OR REPLACE FUNCTION LASTNAMEFIRST

(

varFirstName IN VARCHAR2,

varLastName IN VARCHAR2

)

RETURN VARCHAR2 AS 

   varFullName VARCHAR2(100);

BEGIN

  varFullName := varLastName || ' ' || varFirstName;

  RETURN varFullName;

END LASTNAMEFIRST;

This could be simplified further by removing the variable declaration completely:

CREATE OR REPLACE FUNCTION LASTNAMEFIRST

(

varFirstName IN VARCHAR2,

varLastName IN VARCHAR2

)

RETURN VARCHAR2 AS 

BEGIN

  RETURN varLastName || ' ' || varFirstName;

END LASTNAMEFIRST;