检查变量时Oracle类型错误的表达式 [英] oracle expression of wrong type while checking variable

问题描述

我正在检查列的长度和精度，如果精度高于数据库，则应该执行alter system.

I am doing a checking on the column length and precision , if the precision is higher than on the database then I should execute the alter system.

错误似乎在这里(COL_COUNT <> 0 AND TO_NUMBER(OLD_LENGTH) < 6,2 )

编辑

DECLARE COL_COUNT NUMBER;
OLD_LENGTH number(6);
 old_prec number(6);
BEGIN
COL_COUNT:= 0;
SELECT DATA_LENGTH, DATA_PRECISION INTO OLD_LENGTH , old_prec  FROM USER_TAB_COLS WHERE TABLE_NAME='EX_EMPLOYEE' AND COLUMN_NAME='ID';
SELECT COUNT (1) INTO COL_COUNT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EX_EMPLOYEE'AND COLUMN_NAME='ID';
IF (COL_COUNT <> 0 AND to_number(OLD_LENGTH) < 6.2 ) THEN
EXECUTE IMMEDIATE ('ALTER TABLE EX_EMPLOYEE MODIFY ID NUMERIC(6,2)');
END IF;
END;
/

推荐答案

这不是您应该比较data_length和data_precision的值的方式.将它们比较为适当的数字，不要将其转换为字符串:

This is not how you should compare the values for data_length and data_precision. Compare them as proper numbers, don't convert that to a string:

DECLARE 
  old_length NUMBER;
  old_prec   NUMBER;
BEGIN

  SELECT data_length, coalesce(data_precision,0)
     INTO old_length, old_prec 
  FROM user_tab_cols 
  WHERE table_name='EX_EMPLOYEE' 
    AND column_name='ID';

  IF (old_length <> 6 and old_prec <> 2) THEN
    EXECUTE IMMEDIATE ('ALTER TABLE EX_EMPLOYEE MODIFY ID NUMERIC(6,2)');
  END IF;

END;
/

请注意，第一次选择的SELECT COUNT(*) 之后是没有用的.如果没有这样的列，则第一个SELECT data_length, data_precision INTO将已经抛出ORA-01403: no data found异常，并且该代码将永远不会到达检查该列是否存在的检查.

Note that the SELECT COUNT(*) after the first select is useless. If there is no such column the first SELECT data_length, data_precision INTO will already throw a ORA-01403: no data found exception and the code will never reach the check if the column exists.

这篇关于检查变量时Oracle类型错误的表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！