# Oracle Create formula for dynamic SQL to prevent divide by zero

Solution for Oracle Create formula for dynamic SQL to prevent divide by zero
is Given Below:

I have a table with formula column that have formula to fetch to dynamic SQL LIKE `n5/n14+n3`

My problem is when formula have divide, I should try convert the formula to prevent error `divide by zero`.

My current idea is convert formula to `CASE` when it have divine, for e.g `n5/n14+n3` to `CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END`,

but I can convert only in case formula have only 1 divide, and cannot convert in case formula have > 1 divide ( e.g `n5/n4+n3/n2`, or formula have divide by an expression (e.g `n5/(n4+n3)`.

Could anyone have solution for that?

``````WITH tmp AS
(
SELECT 'n5/n14+n3' AS formula FROM dual UNION ALL
SELECT 'n5/n14+n3/n1-n2' AS formula FROM dual UNION ALL
SELECT 'n8/(n17*n6)-n5/n4+n3/n1-n2' AS formula FROM dual UNION ALL
SELECT 'n5/(n14+n3*n2)-n1' AS formula FROM dual
)
SELECT
formula,
CASE
WHEN formula NOT LIKE '%/%' OR REPLACE(formula, ' ', '') LIKE '%/(%' OR (LENGTH(formula) - LENGTH(REPLACE(formula, "https://stackoverflow.com/", ''))) > 1 THEN  formula
ELSE 'CASE WHEN ' || SUBSTR(REGEXP_SUBSTR(REPLACE(formula, ' ', ''), '/(nd+)'), 2) || ' = 0 THEN 0 ELSE ' || formula || ' END'
END AS formula1,
'CASE WHEN ' || SUBSTR(REGEXP_SUBSTR(REPLACE(formula, ' ', ''), '/(nd+)'), 2) || ' = 0 THEN 0 ELSE ' || formula || ' END' AS formula2
FROM tmp t;
``````

Current result is not expected output (only line 1 resolve divide by zero error):

``````formula                     formula1                                    formula2
n5/n14+n3                   CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END
n5/n14+n3/n1-n2             n5/n14+n3/n1-n2                             CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3/n1-n2 END
n8/(n17*n6)-n5/n4+n3/n1-n2  n8/(n17*n6)-n5/n4+n3/n1-n2                  CASE WHEN n4 = 0 THEN 0 ELSE n8/(n17*n6)-n5/n4+n3/n1-n2 END
n5/(n14+n3*n2)-n1           n5/(n14+n3*n2)-n1                           CASE WHEN  = 0 THEN 0 ELSE n5/(n14+n3*n2)-n1 END

``````

You can use `NULLIF()` to prevent an error. The result will be `NULL` instead:

``````(
SELECT 'n5/nullif(n14, 0)+n3' AS formula FROM dual UNION ALL
SELECT 'n5/nullif(n14, 0)+n3/nullif(n1, 0)-n2' AS formula FROM dual UNION ALL
SELECT 'n8/nullif(n17*n6, 0)-n5/nullif(n4, 0)+n3/nullif(n1, 0)-n2' AS formula FROM dual UNION ALL
SELECT 'n5/nullif((n14+n3*n2, 0)-n1' AS formula FROM dual
)
``````

• Write a parser in Java to parse your expression (or use an existing parser).
• Write a static Java function to convert the abstract syntax tree generated by the parser to your required output and wrap each denominator in the division with a `NULLIF` or `CASE` expression to prevent division-by-zero exceptions being raised.
• Load it into the database using the `loadjava` utility.
• Write a PL/SQL function to wrap the Java function.
• Use the PL/SQL function to transform your dynamic code into something safer.

Thanks to Mr @Linoff’s suggestion, I do use “ to fetch and convert formula using `NULLIF` like this

``````WITH tmp AS
(
SELECT 'n5/n14+n3' AS formula FROM dual UNION ALL
SELECT 'n5/n14+n3/n1-n2' AS formula FROM dual UNION ALL
SELECT 'n8/(n17*n6)-n5/n4+n3/n1-n2' AS formula FROM dual UNION ALL
SELECT 'n5/(n14+n3*n2)-n1' AS formula FROM dual
)
SELECT formula,
REGEXP_REPLACE(REPLACE(formula, ' ', ''), '/(nd+)|/(([^)]*))', '/NULLIF(12,0)') AS formula1
FROM tmp;
``````

Result:

``````formula                     formula1
n5/n14+n3                   n5/NULLIF(n14,0)+n3
n5/n14+n3/n1-n2             n5/NULLIF(n14,0)+n3/NULLIF(n1,0)-n2
n8/(n17*n6)-n5/n4+n3/n1-n2  n8/NULLIF((n17*n6),0)-n5/NULLIF(n4,0)+n3/NULLIF(n1,0)-n2
n5/(n14+n3*n2)-n1           n5/NULLIF((n14+n3*n2),0)-n1

``````