Oracle Apex 5.1 Dynamic SQL
how to Dynamic SQL before report to users in Apex 5.1?
my query is:
declare
q varchar2(4000);
begin
q := 'select * from tb1 t';
if :Param1 is not null then
q := q || ' where t.name = :Param1';
end if;
return q;
end;
:Param1 is optional for users.
I want if :Param1 is null then my query execute without any where clause.
otherwise set where clause in my query.
the point is my Apex is Version 5.1
Thanks
oracle plsql
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
add a comment |
how to Dynamic SQL before report to users in Apex 5.1?
my query is:
declare
q varchar2(4000);
begin
q := 'select * from tb1 t';
if :Param1 is not null then
q := q || ' where t.name = :Param1';
end if;
return q;
end;
:Param1 is optional for users.
I want if :Param1 is null then my query execute without any where clause.
otherwise set where clause in my query.
the point is my Apex is Version 5.1
Thanks
oracle plsql
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
add a comment |
how to Dynamic SQL before report to users in Apex 5.1?
my query is:
declare
q varchar2(4000);
begin
q := 'select * from tb1 t';
if :Param1 is not null then
q := q || ' where t.name = :Param1';
end if;
return q;
end;
:Param1 is optional for users.
I want if :Param1 is null then my query execute without any where clause.
otherwise set where clause in my query.
the point is my Apex is Version 5.1
Thanks
oracle plsql
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
how to Dynamic SQL before report to users in Apex 5.1?
my query is:
declare
q varchar2(4000);
begin
q := 'select * from tb1 t';
if :Param1 is not null then
q := q || ' where t.name = :Param1';
end if;
return q;
end;
:Param1 is optional for users.
I want if :Param1 is null then my query execute without any where clause.
otherwise set where clause in my query.
the point is my Apex is Version 5.1
Thanks
oracle plsql
oracle plsql
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
edited Jan 19 at 12:51
Barbaros Özhan
13k71632
13k71632
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
asked Jan 19 at 11:29
Mohammad TaleshiMohammad Taleshi
63
63
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If that query is used as a a source for a report region, then you don't have to use dynamic SQL, but a simple OR condition:
select *
from tb1 t
where (t.name = :param1 or :param1 is null)
The same would work elsewhere too.
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
add a comment |
you can use such a way below by using rowtype and type keywords for whole row and column values respectively :
SQL> set serveroutput on
SQL> declare
q varchar2(4000);
rt tb1%rowtype;
i_name tb1.name%type := 'Mohammad';
o_surname tb1.surname%type;
begin
q := 'select * from tb1 t';
if i_name is not null then
q := q || ' where ( t.name = :Param1 or :Param1 is null )';
end if;
execute immediate q into rt using i_name, i_name;
-- "i_name" appears twice because of ":Param1" appears twice in the string "q"
o_surname := rt.surname;
dbms_output.put_line(rt.surname);
end;
Taleshi
since there's only one parameter, then :Param1 is matched with i_name. If we had more than one parameter, then all should be matched in the appearance order of the bind parameters in the sql string with comma seperated variables such as i_name, .... in the using list.
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
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%2f54266616%2foracle-apex-5-1-dynamic-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If that query is used as a a source for a report region, then you don't have to use dynamic SQL, but a simple OR condition:
select *
from tb1 t
where (t.name = :param1 or :param1 is null)
The same would work elsewhere too.
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
add a comment |
If that query is used as a a source for a report region, then you don't have to use dynamic SQL, but a simple OR condition:
select *
from tb1 t
where (t.name = :param1 or :param1 is null)
The same would work elsewhere too.
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
add a comment |
If that query is used as a a source for a report region, then you don't have to use dynamic SQL, but a simple OR condition:
select *
from tb1 t
where (t.name = :param1 or :param1 is null)
The same would work elsewhere too.
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
If that query is used as a a source for a report region, then you don't have to use dynamic SQL, but a simple OR condition:
select *
from tb1 t
where (t.name = :param1 or :param1 is null)
The same would work elsewhere too.
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
answered Jan 19 at 12:00
LittlefootLittlefoot
21.8k71533
21.8k71533
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
add a comment |
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
I want if :Param1 is null then my query execute without any where clause. otherwise set where clause in my query.
– Mohammad Taleshi
Jan 19 at 14:09
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
That's exactly what such a WHERE clause does.
– Littlefoot
Jan 19 at 18:14
add a comment |
you can use such a way below by using rowtype and type keywords for whole row and column values respectively :
SQL> set serveroutput on
SQL> declare
q varchar2(4000);
rt tb1%rowtype;
i_name tb1.name%type := 'Mohammad';
o_surname tb1.surname%type;
begin
q := 'select * from tb1 t';
if i_name is not null then
q := q || ' where ( t.name = :Param1 or :Param1 is null )';
end if;
execute immediate q into rt using i_name, i_name;
-- "i_name" appears twice because of ":Param1" appears twice in the string "q"
o_surname := rt.surname;
dbms_output.put_line(rt.surname);
end;
Taleshi
since there's only one parameter, then :Param1 is matched with i_name. If we had more than one parameter, then all should be matched in the appearance order of the bind parameters in the sql string with comma seperated variables such as i_name, .... in the using list.
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
add a comment |
you can use such a way below by using rowtype and type keywords for whole row and column values respectively :
SQL> set serveroutput on
SQL> declare
q varchar2(4000);
rt tb1%rowtype;
i_name tb1.name%type := 'Mohammad';
o_surname tb1.surname%type;
begin
q := 'select * from tb1 t';
if i_name is not null then
q := q || ' where ( t.name = :Param1 or :Param1 is null )';
end if;
execute immediate q into rt using i_name, i_name;
-- "i_name" appears twice because of ":Param1" appears twice in the string "q"
o_surname := rt.surname;
dbms_output.put_line(rt.surname);
end;
Taleshi
since there's only one parameter, then :Param1 is matched with i_name. If we had more than one parameter, then all should be matched in the appearance order of the bind parameters in the sql string with comma seperated variables such as i_name, .... in the using list.
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
add a comment |
you can use such a way below by using rowtype and type keywords for whole row and column values respectively :
SQL> set serveroutput on
SQL> declare
q varchar2(4000);
rt tb1%rowtype;
i_name tb1.name%type := 'Mohammad';
o_surname tb1.surname%type;
begin
q := 'select * from tb1 t';
if i_name is not null then
q := q || ' where ( t.name = :Param1 or :Param1 is null )';
end if;
execute immediate q into rt using i_name, i_name;
-- "i_name" appears twice because of ":Param1" appears twice in the string "q"
o_surname := rt.surname;
dbms_output.put_line(rt.surname);
end;
Taleshi
since there's only one parameter, then :Param1 is matched with i_name. If we had more than one parameter, then all should be matched in the appearance order of the bind parameters in the sql string with comma seperated variables such as i_name, .... in the using list.
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
you can use such a way below by using rowtype and type keywords for whole row and column values respectively :
SQL> set serveroutput on
SQL> declare
q varchar2(4000);
rt tb1%rowtype;
i_name tb1.name%type := 'Mohammad';
o_surname tb1.surname%type;
begin
q := 'select * from tb1 t';
if i_name is not null then
q := q || ' where ( t.name = :Param1 or :Param1 is null )';
end if;
execute immediate q into rt using i_name, i_name;
-- "i_name" appears twice because of ":Param1" appears twice in the string "q"
o_surname := rt.surname;
dbms_output.put_line(rt.surname);
end;
Taleshi
since there's only one parameter, then :Param1 is matched with i_name. If we had more than one parameter, then all should be matched in the appearance order of the bind parameters in the sql string with comma seperated variables such as i_name, .... in the using list.
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
edited Jan 19 at 14:18
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
answered Jan 19 at 13:07
Barbaros ÖzhanBarbaros Özhan
13k71632
13k71632
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
add a comment |
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
I HAVE AN APEX PROBLEM...
– Mohammad Taleshi
Jan 19 at 13:15
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
@MohammadTaleshi ok, no need to shout, by the way seems more than a problem related with PL/SQL.
– Barbaros Özhan
Jan 19 at 13:20
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
It's not working. because the where clause finally check in query with or without null value.
– Mohammad Taleshi
Jan 19 at 14:09
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
@MohammadTaleshi Excuse me, now I've fixed.
– Barbaros Özhan
Jan 19 at 14:19
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
Generally in APEX you would not execute the query but let APEX run it to generate the report output.
– Jeffrey Kemp
Jan 21 at 6:57
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%2f54266616%2foracle-apex-5-1-dynamic-sql%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
