oracle apex 18.2 pl/sql function body returning sql query checking page item value
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am Creating a interacitve repor pl/sql function body returning sql query
I have a page item :P1_DIVISION_ID to pass as parameter as well as check item value is not null as show below
declare
lv_query varchar2(4000);
begin
IF :P1_DIVISION_ID IS NOT NULL THEN
select 'select DIVISION,
CUSTOMER_ID,
PARTY_NAME,
ACCOUNT_NUMBER,
ORG_ID,
OU_NAME,
AGING_1_30,
AGING_31_60,
AGING_61_90,
ABOVE_90,
CURRENT_BALANCE,
PAST_DUE,
WEBSITE_STATUS,
BLOCK_DATE,
BLOCK_REASON,
TOTAL_NUM_LOGIN,
CP_LAST_PAY_DT,
CP_LAST_AMT,
CP_LAST_PAY_MODE,
CP_AGE,
CP_STATUS,
CP_DATE,
CP_DEFF,
CP_UNBILL,
CP_PHONE,
CP_EMAIL,
CP_ACCT_MGR,
FU_ASSIGN,
CP_LTR_SENT_DATE,
CP_LTR_TYPE,
CP_COMMENTS,
COMMENTS
from XX_CUSTOMER_AGING_V WHERE DIVISION '||:P1_DIVISION_ID INTO lv_query
FROM DUAL;
END IF;
RETURN lv_query;
end;
BUT when i validate the query it show me the below errors
ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION
Suggestion required to handle the error
oracle-apex
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
add a comment |
0
I am Creating a interacitve repor pl/sql function body returning sql query
I have a page item :P1_DIVISION_ID to pass as parameter as well as check item value is not null as show below
declare
lv_query varchar2(4000);
begin
IF :P1_DIVISION_ID IS NOT NULL THEN
select 'select DIVISION,
CUSTOMER_ID,
PARTY_NAME,
ACCOUNT_NUMBER,
ORG_ID,
OU_NAME,
AGING_1_30,
AGING_31_60,
AGING_61_90,
ABOVE_90,
CURRENT_BALANCE,
PAST_DUE,
WEBSITE_STATUS,
BLOCK_DATE,
BLOCK_REASON,
TOTAL_NUM_LOGIN,
CP_LAST_PAY_DT,
CP_LAST_AMT,
CP_LAST_PAY_MODE,
CP_AGE,
CP_STATUS,
CP_DATE,
CP_DEFF,
CP_UNBILL,
CP_PHONE,
CP_EMAIL,
CP_ACCT_MGR,
FU_ASSIGN,
CP_LTR_SENT_DATE,
CP_LTR_TYPE,
CP_COMMENTS,
COMMENTS
from XX_CUSTOMER_AGING_V WHERE DIVISION '||:P1_DIVISION_ID INTO lv_query
FROM DUAL;
END IF;
RETURN lv_query;
end;
BUT when i validate the query it show me the below errors
ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION
Suggestion required to handle the error
oracle-apex
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
add a comment |
0
0
0
I am Creating a interacitve repor pl/sql function body returning sql query
I have a page item :P1_DIVISION_ID to pass as parameter as well as check item value is not null as show below
declare
lv_query varchar2(4000);
begin
IF :P1_DIVISION_ID IS NOT NULL THEN
select 'select DIVISION,
CUSTOMER_ID,
PARTY_NAME,
ACCOUNT_NUMBER,
ORG_ID,
OU_NAME,
AGING_1_30,
AGING_31_60,
AGING_61_90,
ABOVE_90,
CURRENT_BALANCE,
PAST_DUE,
WEBSITE_STATUS,
BLOCK_DATE,
BLOCK_REASON,
TOTAL_NUM_LOGIN,
CP_LAST_PAY_DT,
CP_LAST_AMT,
CP_LAST_PAY_MODE,
CP_AGE,
CP_STATUS,
CP_DATE,
CP_DEFF,
CP_UNBILL,
CP_PHONE,
CP_EMAIL,
CP_ACCT_MGR,
FU_ASSIGN,
CP_LTR_SENT_DATE,
CP_LTR_TYPE,
CP_COMMENTS,
COMMENTS
from XX_CUSTOMER_AGING_V WHERE DIVISION '||:P1_DIVISION_ID INTO lv_query
FROM DUAL;
END IF;
RETURN lv_query;
end;
BUT when i validate the query it show me the below errors
ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION
Suggestion required to handle the error
oracle-apex
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
I am Creating a interacitve repor pl/sql function body returning sql query
I have a page item :P1_DIVISION_ID to pass as parameter as well as check item value is not null as show below
declare
lv_query varchar2(4000);
begin
IF :P1_DIVISION_ID IS NOT NULL THEN
select 'select DIVISION,
CUSTOMER_ID,
PARTY_NAME,
ACCOUNT_NUMBER,
ORG_ID,
OU_NAME,
AGING_1_30,
AGING_31_60,
AGING_61_90,
ABOVE_90,
CURRENT_BALANCE,
PAST_DUE,
WEBSITE_STATUS,
BLOCK_DATE,
BLOCK_REASON,
TOTAL_NUM_LOGIN,
CP_LAST_PAY_DT,
CP_LAST_AMT,
CP_LAST_PAY_MODE,
CP_AGE,
CP_STATUS,
CP_DATE,
CP_DEFF,
CP_UNBILL,
CP_PHONE,
CP_EMAIL,
CP_ACCT_MGR,
FU_ASSIGN,
CP_LTR_SENT_DATE,
CP_LTR_TYPE,
CP_COMMENTS,
COMMENTS
from XX_CUSTOMER_AGING_V WHERE DIVISION '||:P1_DIVISION_ID INTO lv_query
FROM DUAL;
END IF;
RETURN lv_query;
end;
BUT when i validate the query it show me the below errors
ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION
Suggestion required to handle the error
oracle-apex
oracle-apex
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
edited Nov 24 '18 at 18:13
Mihai Chelaru
2,485101424
2,485101424
asked Nov 16 '18 at 21:44
user10266688user10266688
127
asked Nov 16 '18 at 21:44
user10266688user10266688
127
asked Nov 16 '18 at 21:44
user10266688user10266688
127
127
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
What is the query like when
P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.Don't do
select ... into lv_query from dual. Just assign the string to the variable.Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simpleSELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_IDwould suffice.
– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
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%2f53345834%2foracle-apex-18-2-pl-sql-function-body-returning-sql-query-checking-page-item-val%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
2
What is the query like when
P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.Don't do
select ... into lv_query from dual. Just assign the string to the variable.Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simpleSELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_IDwould suffice.
– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
add a comment |
2
What is the query like when
P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.Don't do
select ... into lv_query from dual. Just assign the string to the variable.Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simpleSELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_IDwould suffice.
– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
add a comment |
2
2
2
What is the query like when
P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.Don't do
select ... into lv_query from dual. Just assign the string to the variable.Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
What is the query like when
P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.Don't do
select ... into lv_query from dual. Just assign the string to the variable.Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
answered Nov 17 '18 at 16:42
Adrian PAdrian P
47148
47148
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simpleSELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_IDwould suffice.
– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
add a comment |
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simpleSELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_IDwould suffice.
– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
1
1
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simple
SELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_ID would suffice.– Littlefoot
Nov 17 '18 at 17:09
+1; there's nothing dynamic in that query, so - no point in a function that returns a query. A simple
SELECT whatever FROM XX_CUSTOMER_AGING_V WHERE DIVISION = :P1_DIVISION_ID would suffice.– Littlefoot
Nov 17 '18 at 17:09
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
I have 4 difference where clauses on the basis of 4 page items , i want to check if any of page item is not null the same query will be returned with some different where clause
– user10266688
Nov 17 '18 at 17:13
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
We don't see that level of detail in your question, so you are the best judge on whether the query by function is appropriate. I have rarely used this as a source since most use cases are met with a carefully crafted WHERE clause. If this is necessary, just make sure that the function always returns a valid SQL query.
– Adrian P
Nov 17 '18 at 23:47
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
And build your bind variables into the query string - don't concanate them, which transforms them into literals.
– Scott
Nov 20 '18 at 2:20
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%2f53345834%2foracle-apex-18-2-pl-sql-function-body-returning-sql-query-checking-page-item-val%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
