Fill cursor with data using IDs returned by another cursor
0
I'm trying to fill a cursor in a stored procedure with records from a table using ids return by a different stored procedure in a separate cursor.
My code looks something like this (I simplified it a lot for the sake of clarity):
PROCEDURE GetAll(DataCur OUT RefCur)
AS
IDsCursor RefCur;
ids refs;
BEGIN
GetAllRefs(IDsCursor);
loop
fetch IDsCursor
bulk collect into ids limit 999999999;
exit when IDsCursor%NOTFOUND;
end loop;
close IDsCursor;
open DataCur for
select
tbl.*
from
table(ids) ids
inner join myobjects tbl on
tbl.itemid = ids.itemid;
END;
where refs is a custom table type.
The weird thing is that this works when I have a small number of ids but when the number increases (70k+ in my case, so not a lot) I get the following error:
My questions are:
- Is this really a communication issue or I am doing something wrong? I never had this problem before.
- Is there another way of achieving what I need? I need to be able to reuse the
GetAllRefsstored procedure in multiple places, but how can I do that without using an actual table?
oracle toad
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
add a comment |
0
I'm trying to fill a cursor in a stored procedure with records from a table using ids return by a different stored procedure in a separate cursor.
My code looks something like this (I simplified it a lot for the sake of clarity):
PROCEDURE GetAll(DataCur OUT RefCur)
AS
IDsCursor RefCur;
ids refs;
BEGIN
GetAllRefs(IDsCursor);
loop
fetch IDsCursor
bulk collect into ids limit 999999999;
exit when IDsCursor%NOTFOUND;
end loop;
close IDsCursor;
open DataCur for
select
tbl.*
from
table(ids) ids
inner join myobjects tbl on
tbl.itemid = ids.itemid;
END;
where refs is a custom table type.
The weird thing is that this works when I have a small number of ids but when the number increases (70k+ in my case, so not a lot) I get the following error:
My questions are:
- Is this really a communication issue or I am doing something wrong? I never had this problem before.
- Is there another way of achieving what I need? I need to be able to reuse the
GetAllRefsstored procedure in multiple places, but how can I do that without using an actual table?
oracle toad
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; theidswould be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)
– Alex Poole
Nov 14 '18 at 9:42
add a comment |
0
0
0
I'm trying to fill a cursor in a stored procedure with records from a table using ids return by a different stored procedure in a separate cursor.
My code looks something like this (I simplified it a lot for the sake of clarity):
PROCEDURE GetAll(DataCur OUT RefCur)
AS
IDsCursor RefCur;
ids refs;
BEGIN
GetAllRefs(IDsCursor);
loop
fetch IDsCursor
bulk collect into ids limit 999999999;
exit when IDsCursor%NOTFOUND;
end loop;
close IDsCursor;
open DataCur for
select
tbl.*
from
table(ids) ids
inner join myobjects tbl on
tbl.itemid = ids.itemid;
END;
where refs is a custom table type.
The weird thing is that this works when I have a small number of ids but when the number increases (70k+ in my case, so not a lot) I get the following error:
My questions are:
- Is this really a communication issue or I am doing something wrong? I never had this problem before.
- Is there another way of achieving what I need? I need to be able to reuse the
GetAllRefsstored procedure in multiple places, but how can I do that without using an actual table?
oracle toad
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
I'm trying to fill a cursor in a stored procedure with records from a table using ids return by a different stored procedure in a separate cursor.
My code looks something like this (I simplified it a lot for the sake of clarity):
PROCEDURE GetAll(DataCur OUT RefCur)
AS
IDsCursor RefCur;
ids refs;
BEGIN
GetAllRefs(IDsCursor);
loop
fetch IDsCursor
bulk collect into ids limit 999999999;
exit when IDsCursor%NOTFOUND;
end loop;
close IDsCursor;
open DataCur for
select
tbl.*
from
table(ids) ids
inner join myobjects tbl on
tbl.itemid = ids.itemid;
END;
where refs is a custom table type.
The weird thing is that this works when I have a small number of ids but when the number increases (70k+ in my case, so not a lot) I get the following error:
My questions are:
- Is this really a communication issue or I am doing something wrong? I never had this problem before.
- Is there another way of achieving what I need? I need to be able to reuse the
GetAllRefsstored procedure in multiple places, but how can I do that without using an actual table?
oracle toad
oracle toad
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
asked Nov 14 '18 at 9:11
SmartDevSmartDev
2,50011018
2,50011018
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; theidswould be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)
– Alex Poole
Nov 14 '18 at 9:42
add a comment |
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; theidswould be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)
– Alex Poole
Nov 14 '18 at 9:42
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; the
ids would be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)– Alex Poole
Nov 14 '18 at 9:42
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; the
ids would be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)– Alex Poole
Nov 14 '18 at 9:42
add a comment |
0
active
oldest
votes
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%2f53296538%2ffill-cursor-with-data-using-ids-returned-by-another-cursor%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53296538%2ffill-cursor-with-data-using-ids-returned-by-another-cursor%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
The error usually means a back-end process died, so you can check the alert log on the database server for more information. It might be useful to include the type definistions, but what you're doing doesn't seem unreasonable. (Although your bulk collect limit inside the loop is a bit odd as - if you had more than 999999999 results and went round the loop a second time, you'd lose the first set of results; the
idswould be replaced, not added to. And if you'll never reach that why have the loop at all? Probably not relevant to the error though.)– Alex Poole
Nov 14 '18 at 9:42