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

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.

CodePudding user response：

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.

CodePudding user response：

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;
/

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 parsed, but not executed.