My problem is that we have an application where the end user can introduce a formula, wich will eventually turned into a dynamic sql. The idea is to return a null if the expression encounters a zero-divide expression.

Our options are
- Use a function to wrap around the expression that would catch the zero-divide exception. I don't know how to solve this.

- Parse the expressions the user can introduce. This is difficult to do.


Do you know a way to implement the first solution ?
Something I could use as follows :
SELECT myfunc(C1/C2),myfunc(C3+(C4/C5))... FROM T2


Thank you so much for this and for your books, they have been very helpful.

Cheers
Gabriel

"My problem is that we have an application where the end user can introduce a formula, wich will eventually turned into a dynamic sql"

If I understand that correctly you are taking the user input (a formula) and including it directly in your SQL query. Sounds like a huge SQL injection security hole to me.

If you aren't parsing the input how do you know the user will always input valid column names?

What we have here is a denormalized table of objects and counters. The table T have the following columns:
OBJ_ID
C01
C02
C03

In the user interface (web based), the user can create, at any time, a derived counter by defining a formula that can be C01/C02 or (C01+C03)/C02/C01 for example. Those formulas will be stored in some table and resulting of that, an engine will create some dynamic sql which will look like this :

SELECT C01/C02, (C01+C03)/C02/C01 FROM T

as we can see, if C02 or C01 for example are equal to zero the sql statement will fail with zero_divide exception

I cannot predict the formula the user will enter in the system that's why I am not able to create a view with the decodes and so. Another option would be to "force" the user to enter the formula "C01/C02" in the following format "decode( c2, 0, to_number(null), c1/c2 )" which is not user friendly and forces the user to know SQL language.
Specially if the formula is more complex like (C01+C03)/C02/C01 for example.

The botom line is that maybe the engine has to parse the string the user enters and replace it with the protected version of it.

This will be executed periodically since this is a near real time system used for supervision.

Is there another way to solve this ? By catching the zero_devide exception for example ... that's why I came up with the function example...

Sorry I forgot to sign the previous Review

Hi,

Let me explain my case in details:
- I had a similar problem with the user who can input the formula using specific user interface that parse the user input in order to let enter only valid column names, operators etc.
- Formulas entered by the user are then executed by the application engine using dynamic SQL. Our parser allows C1/C2 formulas and we had zero_divide exception at the execution time.

There is a two ways to solve this problem:
1. Your users must use decode function in such cases (that was our choice, decode is finally not too dificult to learn)
2. You must integrate parsing engine in order to automatically add decode in your formulas.

I regret that Oracle cannot return ROWID of the record raising an exception in order to easy identify the record with the problem.

you do understand the SECURITY implications of such a thing? yes?  you really do right?  You never seem to mention it -- it is pretty big, very popular, and way easy to exploit.  


you can easily write a plsql function

ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select rownum-1, rownum-2, rownum-3
  3  from all_users where rownum <= 5;
 
5 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function eval( p_rowid in rowid, p_tname in varchar2, p_function in varchar2 ) return number
  2  as
  3          l_return number;
  4          zero_divide exception;
  5          pragma exception_init( zero_divide, -1476 );
  6  begin
  7          execute immediate
  8          'select ' || p_function || ' from ' || p_tname || ' where rowid = :x'
  9          into l_return using p_rowid;
 10
 11          return l_return;
 12  exception
 13          when zero_divide
 14          then
 15                  return null;
 16  end;
 17  /
 
Function created.
 
ops$tkyte@ORA9IR2> select t.*, eval(t.rowid, 't', 'x/y+z' ) from t;
 
         X          Y          Z EVAL(T.ROWID,'T','X/Y+Z')
---------- ---------- ---------- -------------------------
         0         -1         -2                        -2
         1          0         -1
         2          1          0                         2
         3          2          1                       2.5
         4          3          2                3.33333333
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant execute on eval to scott;
 
Grant succeeded.
 
<b>caveat emptor</b>


ops$tkyte@ORA9IR2> @connect scott/tiger
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR2> set termout on
scott@ORA9IR2>
scott@ORA9IR2> select count(*) from dba_users;
select count(*) from dba_users
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
scott@ORA9IR2> select ops$tkyte.eval( rowid, 'dual', '(select count(*) from dba_users)' ) from dual;
 
OPS$TKYTE.EVAL(ROWID,'DUAL','(SELECTCOUNT(*)FROMDBA_USERS)')
------------------------------------------------------------
                                                          24
 
 

The objective of our application looks like the same as yours Mladen.

The only "feature" I was looking for here was a function like "NVL" that, instead of testing "NULL" values would test the zero_divide exception.

Something like
FUNC ( expr1 , expr2 )

Where the function FUNC would return 'expr2' in case 'expr1' raised a zero_divide exception.

if REGEXP_LIKE('2+df',
'^[-+]?([a-z][a-z0-9_]*|[0-9]+)' ||
'([-+*/]([a-z][a-z0-9_]*|[0-9]+))*$', 'i') then
... run_query ...

Security issue aside (as if), you'll also be opening up the potential for horrendously badly written subqueries to be entered and potentially kill performance.

e.g. rather than "c1/c2" user types "(select count(*) from reallybigunindexedtable bt where c1 > bt.col1)"

My the point was that the regexp would only match safe expressions and therefor could be used to make your "eval" (evil?) function safe.

(Would be nice if Oracles regexp-implementation was Perl compatible)

I am receiving an ora-1476 error, but it only seems to affect the WHERE clause of the statement.

SELECT (hrs.value/div.value)
,....
FROM ...
WHERE ...
AND (hrs.value/div.value) <> paf.normal_hours

fails with ora-1476: divisor is equal to zero

Using DECODE in the WHERE clause:

SELECT (hrs.value/div.value)
,....
FROM ...
WHERE ...
AND (hrs.value/DECODE(div.value,0,1,div.value))
<> paf.normal_hours

works OK.

I also checked the query runs OK, after changing the <> to =, just to make sure that the row causing the problem wasn't being excluded.

So (hrs.value/div.value) seems to work OK in the select but not in the where clause. Is there an explaination for this?

div is a table used as a lookup and only contains a few rows and value is never set to zero. The value column is a varchar2.

Hello Tom,

I am trying to excute a set of INSERT/UPDATE statements (more than 50k) through SQLPLUS. Before commiting the operation, I want to know how many statements have failed. Is there a way to do this in sqlplus. If this number is > zero, I will ROLLBACK the operation.

Thanks

hi
i want divide A/B into result and i want manage that if B=0 then result=0

note: i want use only DECODE function

for example
decode(A/B , ? , 0 , A/B)

Yes decode(b,0,null,a/b) will avoid the divide by zero. But what should you do when b is actually a calculated field potentially with its own zero-divide bugs as in:

(1+a+b)/(1+c+d/e)

Solution 1: Encapsulate each division operation in its own decode as in: decode(e,0,null,decode(1+c+d/e,0,null,(1+a+b)/(1+c+d/e)))

Why is this not really a solution? Suppose your boss decides the new equation should be (1+a+b+f/g)/(1+c+d/(e+f/g)) and that this needs to occur in multiple queries in the database, not to mention that it is now extremely difficult to read and understand the code.

Solution 2: Use inner select queries to name the numerators and denominators. This makes the code more readable.

Why this isn't really a solution: A 100 line query (80 lines of which are column declarations) becomes a 500 line query 400 lines of which are duplicated query names from the previous selects.

Solution 3: Write a function to calculate the value.

This is the best solution so far -- but why does everything in Oracle have to be so difficult? A function is also completely unsuitable for dynamic code and ad-hoc queries. I'm seriously considering writing a function d(x number, y number) which returns x/y just to avoid this inane limitation in Oracle.

What the solution should be: Introduce an option/hint to the parser

Select /*divide_by_zero_is_null*/

which tells the Oracle to convert all divide by zeros into null for this query.

In some cases its better to get some data instead of getting none at all.

An example of a language where divide by zero is not a problem:
Oracle SQL
In particular: ratio_to_report()

I'm not saying that all divisions by 0's should be converted to nulls but that the developer should be given the choice and ability to easily avoid them. I would be pissed if my paycheck was 0 because rogue divide by zero nulled it out, but there are instances where division by zero is not a problem -- or better yet has no solution [How does one calculate percent growth of something that started at 0?]. To have to manually wrap every single division statement that might lead to an error is a waste or time. One cannot always validate that the data will not lead to such a division especially in group aggregations. Ratio_to_report recognizes this and wraps the division so that it doesn't cause ORA-01476. For everything else we have to explicitely wrap it ourselves. This leads to additional sub-selects and difficult to maintain code. This is furhter complicated by the "deprecation" of decode -- Which given how useful it is probably won't occur before the "Big Crunch" or "Heat Death" of the Universe -- "case d when 0 the null else n/d end" in every query in the system is going to be fun, thank god I will be dead before that happens (I only expect to be alive for another 80 years).

We have exceptions. So lets use them.
Set a flag which tells the engine to ignore XXX exception and apply the natural corrective action (with the understanding that developer is giving up something in the process).

I understand why Oracle can't take a key violation and drop the conflicting row when doing block level inserts. Fair enough. Its the price we pay for using a robust transactional database.

So if your answer were: "The results of Select are calculated at the block level and there is no way to determine the problem row so all rows would have to be returned with null which is unacceptable." That would at least be a start. But I doubt that is the case and ratio_to_report suggests that it is possible to trap the division at some point. It seems that somewhere in the Oracle kernel either
a) a thread is trapping the CPU NaN exception, clearing it and passing back the Oracle divide by 0 exception.
or
b) a thread is validating the data before passing the division to the CPU.
or
c) the ratio_to_report aggregate is checking the division before evaluating

Applying the (c) type solution across the board could have performance implications so I can understand why it might not be implemented.

That still leaves (a) or (b): why can't a flag be set on that thread telling it to correct the divide by zero, and continue?

My only guess is that they just didn't think of this when coding the engine and the required changes would have to be made across multiple architectures and in very critical code segments and as a result simply cannot be fixed without major rewritting of code which could potentially seriously affect system stability and as a result wouldn't see production for years.

And if thats the case I can accept that, every program has bugs and design oversights, mine more than most. But admitting to the error is much better than saying "Fix it yourself" "Oracle is doing the right thing" "What did you expect to happen" which is what I see a lot of on AskTom.