CODEWITHSUNDEEP
oracle-databaseplsql
Niall
Niall

Reputation: 518

Oracle PL/SQL Query With Dynamic Parameters in Where Clause

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.

Upvotes: 0

Views: 539

Answers (1)

Chaitanya Kotha
Chaitanya Kotha

Reputation: 476

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.

Upvotes: 1

Related Questions