Oracle sequence number error on store procedure error
I have this query working fine when I test it
INSERT INTO some_table VALUES (EXAMPLE.SEQ_EXAMPLE_ID.NEXTVAL, 'TEST', 'TEST');
But when I try to use the same query inside a stored procedure I got a
ORA-00001: unique constraint (EXAMPLE.PK_SOME_TABLE) violated
If I select from some_table using the sequence number generated, there's nothing returned.
I already checked the constrains on the table SELECT * FROM all_indexes WHERE TABLE_NAME = 'some_table'; and everything seems to be fine.
-- edited
We solved the problem re creating the sequence, still not sure what the main problem was, but deleting and creating the sequence again solved the issue.
oracle stored-procedures plsql oracle12c
asked Nov 15 '18 at 15:43
user3541246user3541246
13
add a comment |
I have this query working fine when I test it
INSERT INTO some_table VALUES (EXAMPLE.SEQ_EXAMPLE_ID.NEXTVAL, 'TEST', 'TEST');
But when I try to use the same query inside a stored procedure I got a
ORA-00001: unique constraint (EXAMPLE.PK_SOME_TABLE) violated
If I select from some_table using the sequence number generated, there's nothing returned.
I already checked the constrains on the table SELECT * FROM all_indexes WHERE TABLE_NAME = 'some_table'; and everything seems to be fine.
-- edited
We solved the problem re creating the sequence, still not sure what the main problem was, but deleting and creating the sequence again solved the issue.
oracle stored-procedures plsql oracle12c
asked Nov 15 '18 at 15:43
user3541246user3541246
13
6
Presumably you meannextvalrather than justnext. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.
– APC
Nov 15 '18 at 16:33
add a comment |
I have this query working fine when I test it
INSERT INTO some_table VALUES (EXAMPLE.SEQ_EXAMPLE_ID.NEXTVAL, 'TEST', 'TEST');
But when I try to use the same query inside a stored procedure I got a
ORA-00001: unique constraint (EXAMPLE.PK_SOME_TABLE) violated
If I select from some_table using the sequence number generated, there's nothing returned.
I already checked the constrains on the table SELECT * FROM all_indexes WHERE TABLE_NAME = 'some_table'; and everything seems to be fine.
-- edited
We solved the problem re creating the sequence, still not sure what the main problem was, but deleting and creating the sequence again solved the issue.
oracle stored-procedures plsql oracle12c
asked Nov 15 '18 at 15:43
user3541246user3541246
13
I have this query working fine when I test it
INSERT INTO some_table VALUES (EXAMPLE.SEQ_EXAMPLE_ID.NEXTVAL, 'TEST', 'TEST');
But when I try to use the same query inside a stored procedure I got a
ORA-00001: unique constraint (EXAMPLE.PK_SOME_TABLE) violated
If I select from some_table using the sequence number generated, there's nothing returned.
I already checked the constrains on the table SELECT * FROM all_indexes WHERE TABLE_NAME = 'some_table'; and everything seems to be fine.
-- edited
We solved the problem re creating the sequence, still not sure what the main problem was, but deleting and creating the sequence again solved the issue.
oracle stored-procedures plsql oracle12c
oracle stored-procedures plsql oracle12c
asked Nov 15 '18 at 15:43
user3541246user3541246
13
asked Nov 15 '18 at 15:43
user3541246user3541246
13
edited Nov 27 '18 at 12:04
user3541246
asked Nov 15 '18 at 15:43
user3541246user3541246
13
asked Nov 15 '18 at 15:43
user3541246user3541246
13
asked Nov 15 '18 at 15:43
user3541246user3541246
13
13
6
Presumably you meannextvalrather than justnext. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.
– APC
Nov 15 '18 at 16:33
add a comment |
6
Presumably you meannextvalrather than justnext. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.
– APC
Nov 15 '18 at 16:33
6
6
Presumably you mean
nextval rather than just next. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.– APC
Nov 15 '18 at 16:33
Presumably you mean
nextval rather than just next. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.– APC
Nov 15 '18 at 16:33
add a comment |
1 Answer
1
active
oldest
votes
There are only two operation you can do it with Sequence. Next is not a valid statement.
- To find the next value ( sequence.NEXTVAL )
- To find the current value ( sequence.CURRVAL )
Once the sequence is created, next value should be called at least once before calling current value. To read further about sequence, please click the below link.
https://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
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%2f53323005%2foracle-sequence-number-error-on-store-procedure-error%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
There are only two operation you can do it with Sequence. Next is not a valid statement.
- To find the next value ( sequence.NEXTVAL )
- To find the current value ( sequence.CURRVAL )
Once the sequence is created, next value should be called at least once before calling current value. To read further about sequence, please click the below link.
https://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
add a comment |
There are only two operation you can do it with Sequence. Next is not a valid statement.
- To find the next value ( sequence.NEXTVAL )
- To find the current value ( sequence.CURRVAL )
Once the sequence is created, next value should be called at least once before calling current value. To read further about sequence, please click the below link.
https://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
add a comment |
There are only two operation you can do it with Sequence. Next is not a valid statement.
- To find the next value ( sequence.NEXTVAL )
- To find the current value ( sequence.CURRVAL )
Once the sequence is created, next value should be called at least once before calling current value. To read further about sequence, please click the below link.
https://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
There are only two operation you can do it with Sequence. Next is not a valid statement.
- To find the next value ( sequence.NEXTVAL )
- To find the current value ( sequence.CURRVAL )
Once the sequence is created, next value should be called at least once before calling current value. To read further about sequence, please click the below link.
https://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
answered Nov 26 '18 at 9:31
siva balanarayanansiva balanarayanan
215
215
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%2f53323005%2foracle-sequence-number-error-on-store-procedure-error%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
6
Presumably you mean
nextvalrather than justnext. Beyond that there's nothing we can suggest unless you show us the whole procedure, or at least post a reproducible test case. Because this should work, which means there's a problem in how you've written the procedure: we can't spot the problem without seeing the source.– APC
Nov 15 '18 at 16:33