Can I parse string to SQL code

Question

I am trying to create a materialized view that requires slightly different filters between prod, dev, qa.

We have a variables table that stores random ids and I'm trying to find a way to store something like this in my variables table:

       prod_filter_values =  "(D.DEFID = 123 AND D.ATTRID IN (2, 3, 4)) OR
        (D.DEFID = 3112 AND D.ATTRID IN (3, 30, 34, 23, 4)) OR
        (D.DEFID = 379 AND D.ATTRID IN (3, 5, 8)) OR
        (D.DEFID = 3076 AND D.ATTRID = 5);"

Then I'd do something like select * from variables_table where EVAL(prod_filter_values)

Is it possible?

LoztInSpace · Accepted Answer · 2018-02-22 23:38:53Z

3

Yes you can as other answers have explained. However a better way would be to have this data driven - simply create tables in your various environments that have the corresponding magic numbers and join to that as required.

A second way is to have different views for the different environments with the numbers hard-coded there.

Anything that avoids building strings is going to be better for several reasons including having code in one place, stable code, no security/injection problems, no parse overhead.

answered Feb 22, 2018 at 23:38

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

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

Comments

Z . · Accepted Answer · 2018-02-22 23:24:47Z

0

Yes. Lookup dynamic SQL:

https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#LNPLS01102

something like this:

EXECUTE IMMEDIATE 'select * from vars_table where ' || prod_filter_values;

edited Feb 22, 2018 at 23:24

answered Feb 22, 2018 at 23:08

Z .

12.8k2 gold badges34 silver badges59 bronze badges

3 Comments

Alex Poole Over a year ago

You can bind variables, not the entire where clause. You'd have to concatenate into a single string instead.

Z . Over a year ago

better like this?

Alex Poole Over a year ago

Oracle uses || for string concatenation, not +. You also have to select into something, or open a ref cursor instead, depending on what will be done with the results.

Collectives™ on Stack Overflow

Can I parse string to SQL code

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related