PL/SQL function to check if data in column is numeric

Question

I have few tables which have varchar as data type for column but contains numeric values, I wrote a pl/sql function for the same...but not working as intended.

create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
   RETURN NUMBER
AS
BEGIN
   EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name);
   RETURN 1;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;

for a column which does not contain numeric value, the function should return 0, but the function is returning 1 every time despite column containing numeric values.

OldProgrammer · Accepted Answer · 2022-12-20 18:01:34Z

3

You have to return the value from the select into a bind variable, otherwise, no exception will be thrown:

    create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
       RETURN NUMBER
    AS
    val NUMBER:
    BEGIN
       EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name) 
  into val;
       RETURN 1;
    EXCEPTION
       WHEN OTHERS
       THEN
          RETURN 0;
    END;

I would also recommend fixing your tables to use proper data types.

edited Dec 20, 2022 at 18:01

answered Dec 20, 2022 at 17:56

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

1 Comment

Alex Poole Over a year ago

You probably need to change the query to get MAX(..) or something or it'll get too-many-rows, unless the table only has one row. It's unclear whether it should return 1 if the table is empty - arguably the answer is 'unknown'...

Alex Poole · Accepted Answer · 2022-12-21 17:21:06Z

As an alternative to catching an exception, in recent versions of Oracle (since 12c) you can use the validate_conversion() function, which returns 1 for success and zero for failure:

create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
   RETURN NUMBER
AS
   l_result NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MIN(VALIDATE_CONVERSION(' ||column_name|| ' AS NUMBER)) from ' ||table_name
   INTO l_result;
   RETURN l_result;
END;
/

fiddle

I've added MIN() around it so that if any value in the column cannot be converted then the overall result will be 0; if all values can then it will be 1. I have omitted the NVL() because if there are null values you can't really say if those are numeric or not, and it wasn't doing anything in your version anyway; but if you want to treat a column with no values at all as numeric or not numeric then you can do NVL(MAX(...), 0) - but with no data the answer is really 'unknown', so just returning null might be appropriate, as long as the caller knows what that means.

And as OldProgrammer said, you need an INTO clause - not having one is why your version isn't ever throwing an exception. The documentation says:

Note: If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.

The statement is still parsed, but the query is not executed - so it would report a syntax error (and return zero for all rows because of when others in your version), but it won't access the table data or try to convert any values, hence it not complaining about non-numeric values when you run your code.

Collectives™ on Stack Overflow

PL/SQL function to check if data in column is numeric

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related