Creating a user defined function in oracle SQL called lastnamefirst
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
add a comment |
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
add a comment |
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
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
sql oracle
asked Nov 13 '18 at 19:04
J.Griff2J.Griff2
61
61
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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 noDECLARE
keyword at all, and datatype requires length (such asVARCHAR2(30)
)
CONCAT
accepts only two arguments; use a concatenation operator, double pipe||
instead- there's no
DEFINE
in PL/SQL
add a comment |
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.
add a comment |
Using DECLARE
where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLARE
isn'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;
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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 noDECLARE
keyword at all, and datatype requires length (such asVARCHAR2(30)
)
CONCAT
accepts only two arguments; use a concatenation operator, double pipe||
instead- there's no
DEFINE
in PL/SQL
add a comment |
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 noDECLARE
keyword at all, and datatype requires length (such asVARCHAR2(30)
)
CONCAT
accepts only two arguments; use a concatenation operator, double pipe||
instead- there's no
DEFINE
in PL/SQL
add a comment |
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 noDECLARE
keyword at all, and datatype requires length (such asVARCHAR2(30)
)
CONCAT
accepts only two arguments; use a concatenation operator, double pipe||
instead- there's no
DEFINE
in PL/SQL
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 noDECLARE
keyword at all, and datatype requires length (such asVARCHAR2(30)
)
CONCAT
accepts only two arguments; use a concatenation operator, double pipe||
instead- there's no
DEFINE
in PL/SQL
answered Nov 13 '18 at 19:09
LittlefootLittlefoot
21.1k71433
21.1k71433
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Nov 13 '18 at 19:10
Gordon LinoffGordon Linoff
765k35297401
765k35297401
add a comment |
add a comment |
Using DECLARE
where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLARE
isn'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;
add a comment |
Using DECLARE
where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLARE
isn'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;
add a comment |
Using DECLARE
where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLARE
isn'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;
Using DECLARE
where you have is essentially starting a new code block, which is leading to the error you see. In your code, the DECLARE
isn'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;
answered Nov 13 '18 at 19:10
GriffeyDogGriffeyDog
7,00431628
7,00431628
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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