Oracle With Arithmetic Operators-CodePudding

How do i not allow use for arithmetic operators in specific columns in Oracle DWH?

Users mustn't this usage:

SELECT id, customername FROM customers WHERE id = 10 5

SELECT id, customername FROM customers WHERE id = '1' '5'

SELECT id, customername FROM customers WHERE id = '1' || '5'

Users must be use this style :

SELECT id, customername FROM customers WHERE id = 15

Thank you.

CodePudding user response：

Assuming your id column be an integer, you have to compare it against either an integer literal, integer column, or some other expression whose result evalautes to an integer. Your current version:

SELECT id, customername FROM customers WHERE id = '1' || '5'

can be made to work by casting to integer with TO_NUMBER():

SELECT id, customername FROM customers WHERE id = TO_NUMBER('1' || '5')

CodePudding user response：

Use bind parameters:

SELECT id, customername FROM customers WHERE id = :v_id;

Then the user can specify the value of the bind variable :v_id using whatever front-end interface you are using and it can only be a single value such as 15 or a single string '10 5' (which is a single scalar string value containing the characters 1 then 0 then then 5 and will not be evaluated as a numeric operation).