Oracle SQL Procedure - Bad Bind Variable in REGEX
0
I have an Oracle SQL procedure which is giving Bad bind variable 'VNAME1' error on compilation. The idea is to pass the value of item.tempid2 in the REGEX query.
The procedure below:
DECLARE
anyexists varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||substr(last_name) as
tempid2, user_id, Custom_Unique_Name FROM PV_USERS;
vname1 varchar(20);
exec vname1:= NULL;
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
vname1:= item.tempid2;
SELECT COUNT(*) INTO anyexists from (SELECT
Custom_Unique_Name from PV_USERS
WHERE REGEXP_LIKE (Custom_Unique_Name, '^'|| :vname1 ||
'[^A-Za-z][0-9]*')
AND user_id <> item.user_id);
END LOOP;
END;
sql regex oracle
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
add a comment |
0
I have an Oracle SQL procedure which is giving Bad bind variable 'VNAME1' error on compilation. The idea is to pass the value of item.tempid2 in the REGEX query.
The procedure below:
DECLARE
anyexists varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||substr(last_name) as
tempid2, user_id, Custom_Unique_Name FROM PV_USERS;
vname1 varchar(20);
exec vname1:= NULL;
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
vname1:= item.tempid2;
SELECT COUNT(*) INTO anyexists from (SELECT
Custom_Unique_Name from PV_USERS
WHERE REGEXP_LIKE (Custom_Unique_Name, '^'|| :vname1 ||
'[^A-Za-z][0-9]*')
AND user_id <> item.user_id);
END LOOP;
END;
sql regex oracle
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
3
thevnamevariable is already a bind variable, so there is no need for the:in front of it in theregexp_like.
– Boneist
Nov 20 '18 at 12:51
add a comment |
0
0
0
I have an Oracle SQL procedure which is giving Bad bind variable 'VNAME1' error on compilation. The idea is to pass the value of item.tempid2 in the REGEX query.
The procedure below:
DECLARE
anyexists varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||substr(last_name) as
tempid2, user_id, Custom_Unique_Name FROM PV_USERS;
vname1 varchar(20);
exec vname1:= NULL;
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
vname1:= item.tempid2;
SELECT COUNT(*) INTO anyexists from (SELECT
Custom_Unique_Name from PV_USERS
WHERE REGEXP_LIKE (Custom_Unique_Name, '^'|| :vname1 ||
'[^A-Za-z][0-9]*')
AND user_id <> item.user_id);
END LOOP;
END;
sql regex oracle
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
I have an Oracle SQL procedure which is giving Bad bind variable 'VNAME1' error on compilation. The idea is to pass the value of item.tempid2 in the REGEX query.
The procedure below:
DECLARE
anyexists varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||substr(last_name) as
tempid2, user_id, Custom_Unique_Name FROM PV_USERS;
vname1 varchar(20);
exec vname1:= NULL;
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
vname1:= item.tempid2;
SELECT COUNT(*) INTO anyexists from (SELECT
Custom_Unique_Name from PV_USERS
WHERE REGEXP_LIKE (Custom_Unique_Name, '^'|| :vname1 ||
'[^A-Za-z][0-9]*')
AND user_id <> item.user_id);
END LOOP;
END;
sql regex oracle
sql regex oracle
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
asked Nov 20 '18 at 12:44
Gaurav KabraGaurav Kabra
5919
5919
3
thevnamevariable is already a bind variable, so there is no need for the:in front of it in theregexp_like.
– Boneist
Nov 20 '18 at 12:51
add a comment |
3
thevnamevariable is already a bind variable, so there is no need for the:in front of it in theregexp_like.
– Boneist
Nov 20 '18 at 12:51
3
3
the
vname variable is already a bind variable, so there is no need for the : in front of it in the regexp_like.– Boneist
Nov 20 '18 at 12:51
the
vname variable is already a bind variable, so there is no need for the : in front of it in the regexp_like.– Boneist
Nov 20 '18 at 12:51
add a comment |
1 Answer
1
active
oldest
votes
-1
You should add a USING vname1 clause at the end of your SELECT statement.
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
1
That would only be true if the select statement was dynamic sql being run viaexecute immediate. It's not, it's being run as a static statement.
– Boneist
Nov 20 '18 at 15:21
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
});
}
});
draft saved
draft discarded
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%2f53393274%2foracle-sql-procedure-bad-bind-variable-in-regex%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
-1
You should add a USING vname1 clause at the end of your SELECT statement.
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
1
That would only be true if the select statement was dynamic sql being run viaexecute immediate. It's not, it's being run as a static statement.
– Boneist
Nov 20 '18 at 15:21
add a comment |
-1
You should add a USING vname1 clause at the end of your SELECT statement.
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
1
That would only be true if the select statement was dynamic sql being run viaexecute immediate. It's not, it's being run as a static statement.
– Boneist
Nov 20 '18 at 15:21
add a comment |
-1
-1
-1
You should add a USING vname1 clause at the end of your SELECT statement.
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
You should add a USING vname1 clause at the end of your SELECT statement.
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
answered Nov 20 '18 at 12:55
Hellmar BeckerHellmar Becker
1,830513
1,830513
1
That would only be true if the select statement was dynamic sql being run viaexecute immediate. It's not, it's being run as a static statement.
– Boneist
Nov 20 '18 at 15:21
add a comment |
1
That would only be true if the select statement was dynamic sql being run viaexecute immediate. It's not, it's being run as a static statement.
– Boneist
Nov 20 '18 at 15:21
1
1
That would only be true if the select statement was dynamic sql being run via
execute immediate. It's not, it's being run as a static statement.– Boneist
Nov 20 '18 at 15:21
That would only be true if the select statement was dynamic sql being run via
execute immediate. It's not, it's being run as a static statement.– Boneist
Nov 20 '18 at 15:21
add a comment |
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
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%2f53393274%2foracle-sql-procedure-bad-bind-variable-in-regex%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
3
the
vnamevariable is already a bind variable, so there is no need for the:in front of it in theregexp_like.– Boneist
Nov 20 '18 at 12:51