Creating a user defined function in oracle SQL called lastnamefirst












0















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?










share|improve this question



























    0















    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?










    share|improve this question

























      0












      0








      0








      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?










      share|improve this question














      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?







      sql oracle






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 13 '18 at 19:04









      J.Griff2J.Griff2

      61




      61
























          3 Answers
          3






          active

          oldest

          votes


















          0














          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






          share|improve this answer































            0














            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.






            share|improve this answer































              0














              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;





              share|improve this answer























                Your Answer






                StackExchange.ifUsing("editor", function () {
                StackExchange.using("externalEditor", function () {
                StackExchange.using("snippets", function () {
                StackExchange.snippets.init();
                });
                });
                }, "code-snippets");

                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "1"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287884%2fcreating-a-user-defined-function-in-oracle-sql-called-lastnamefirst%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                3 Answers
                3






                active

                oldest

                votes








                3 Answers
                3






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                0














                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






                share|improve this answer




























                  0














                  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






                  share|improve this answer


























                    0












                    0








                    0







                    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






                    share|improve this answer













                    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







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Nov 13 '18 at 19:09









                    LittlefootLittlefoot

                    21.1k71433




                    21.1k71433

























                        0














                        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.






                        share|improve this answer




























                          0














                          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.






                          share|improve this answer


























                            0












                            0








                            0







                            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.






                            share|improve this answer













                            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.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Nov 13 '18 at 19:10









                            Gordon LinoffGordon Linoff

                            765k35297401




                            765k35297401























                                0














                                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;





                                share|improve this answer




























                                  0














                                  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;





                                  share|improve this answer


























                                    0












                                    0








                                    0







                                    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;





                                    share|improve this answer













                                    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;






                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Nov 13 '18 at 19:10









                                    GriffeyDogGriffeyDog

                                    7,00431628




                                    7,00431628






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Stack Overflow!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287884%2fcreating-a-user-defined-function-in-oracle-sql-called-lastnamefirst%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Bressuire

                                        Vorschmack

                                        Quarantine