Oracle PL/SQL Query With Dynamic Parameters in Where Clause

Question

I'm trying to write a dynamic query that could have a different amount of parameters of different type. The only issue I'm having is handling if the value is a string therefore needing single quotes around it. I am using the value of a field called key_ref_ to determine what my where clause will look like. Some examples are:

LINE_NO=1^ORDER_NO=P6002277^RECEIPT_NO=1^RELEASE_NO=1^

PART_NO=221091^PART_REV=R02^

At the moment I am replacing the '^' with ' and ' like this:

REPLACE( key_ref_, '^' ,' and ' );

Then I'm trying to create the dynamic query like this:

EXECUTE IMMEDIATE
'select '||column_name_||' into column_ from '||base_table_||' where '||
 key_ref_ || 'rownum = 1';

This won't work in cases where the value is not a number.

Also I only added "rownum = 1" to handle the extra 'and' at the end instead of removing the last occurence.

Not very clear what exactly your issue is. Can you please post some examples. — XING
– XING, Commented Oct 19, 2018 at 9:16
You should not do this that way. As there are plenty possibilities for sql injection and you have to write a parser for your conditions. Better use a user defined datatype to define the conditions and then use DBMS_SQL instead of EXECUTE IMMEDIATE and bind the parameters to your query... — Radagast81
– Radagast81, Commented Oct 19, 2018 at 9:47
I won't need to worry about sql injection as there is no user input, it's just part of a background job but I will look into using DBMS_SQL instead — Niall
– Niall, Commented Oct 19, 2018 at 10:20
Ok then it isn't that bad, but binding the parameters is allways useful, as string literals in the query lead to different entries in the database cache. Which will influence overall system performance counterproductive... — Radagast81
– Radagast81, Commented Oct 19, 2018 at 11:03
@Radagast81 why do you think DBMS_SQL would be better than EXECUTE IMMEDIATE? NB, in newer Oracle releases (i.e. >=12.1) you can also use user defined datatypes in EXECUTE IMMEDIATE. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 19, 2018 at 15:47

Chaitanya Kotha · Accepted Answer · 2018-10-19 09:19:24Z

1

If the input will not have the tild symbol(~) then you can try the below code. if the input has tild, you can replace it with some other value which should not be there in input

considering the input provided in the example..

LINE_NO=1^ORDER_NO=P6002277^RECEIPT_NO=1^RELEASE_NO=1^PART_NO=221091^PART_REV=R02^

use the below code

replace(replace(replace('LINE_NO=1^ORDER_NO=P6002277^RECEIPT_NO=1^RELEASE_NO=1^PART_NO=221091^PART_REV=R02^','^','~ and '),'=','=~'),'~',q'[']')

and the result would be

LINE_NO='1' and ORDER_NO='P6002277' and RECEIPT_NO='1' and RELEASE_NO='1' and PART_NO='221091' and PART_REV='R02' and

System will type cast the number fields so, there would not be any issue.

answered Oct 19, 2018 at 9:19

Chaitanya Kotha

4762 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Niall Over a year ago

Thank you that worked. What is the purpose of the '~' ?

Wernfried Domscheit Over a year ago

I think ~ is just a random character which intends to not occur in your dynamic string.

Collectives™ on Stack Overflow

Oracle PL/SQL Query With Dynamic Parameters in Where Clause

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related