How does one evaluate an expression stored as a VARCHAR 2 in a table?
I am attempting to write a single SQL statement that will select three columns of data from a table and based on the value of a string in one column perform a calculation using the other two columns. Here is an example. TableX contains three columns. For simplicity sake let's say Col1 and Col2 are Number(5) and Col3 is a VARCHAR2(25).
TableX
Col1 Col2 Col3
5 7 Col1+Col2
10 3 Col1*Col2
20 5 Col1/Col2
I would like to write an SQL statement like 'select Col1, Col2, evaluate(Col3) as "Eval" from TableX' and get the following result set back.
Col1 Col2 Eval
5 7 12
10 3 30
20 5 4
Is there an Oracle provided function like the hypothetical evaluate() provided above that will do this?
One potential solution would be to write a stored procedure that accepts the three columns as parameters and then performs an execute immediate and returns the result. However, I would like a more dynamic utility so I don't have to be resticted to a preset number of columns (3 in this example).
Any ideas would be greatly appreciated.
Thanks,
Matt