oracle常见问题总结

一、ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました

・存储过程中定义的变量值长度 < 实际的变量值长度，需要加大定义时候变量值的长度。
例如下面的情况，就会出现这种错误:

a varchar2(2)   -- 长度为2
a: = '123123'   -- 长度为6

（注: varchar2的长度上限是4000）

二、ORA-29284: 几行文件读取错误

ORA-06502: 読込み中に OS エラーが発生した

・① 文件中存在 * 或 - 符号
・② UTL_FILE.FOPEN(location in varchar2, filename in varchar2, open_mode in varchar2, [max_linesize] in binary_integer) return FILE_TYPE;
UTL_FILE.FOPEN有一个max_linesize参数。如果没有显式给出,Oracle提供的默认值是1024。如果超出这个数值会报错，应该指定一个更大的值，但必须是1到32767范围内的数字。

P_MAX_LINESIZE NUMBER := 32000;
WK_CSV VARCHAR2(4000); -- CSV文字列

INPUT_FILEHANDLE   :=   UTL_FILE.FOPEN(RTRIM(V_INPUTFILE_PATH), RTRIM(WK_INPUT_FILENAME),'R', P_MAX_LINESIZE);    --Inputファイル開く
OUTPUT_FILEHANDLE  :=   UTL_FILE.FOPEN(V_OUTPUTFILE_PATH, WK_OUTPUT_FILENAME,'W', P_MAX_LINESIZE);    --Outputファイル開く

三、NO_DATA_FOUND

① 读取csv时，出现 NO_DATA_FOUND 这类似于空指针异常，而这个意外情况只是没找到数据而已，加上对此异常的处理即可。

EXCEPTION
        WHEN no_data_found THEN  --ファイル読込完了した場合
          O_RETURN  := 0; -- 正常
          WK_CSV := NULL;

② 在select A into B 时，如果没有命中结果集，则当前的查询为空，pl/sql会将它认为是一个错误。可以在select into之前，先count下数量，>0的时候再去select A into B。
例:

        SELECT
              COUNT(0)
        INTO
              V_COUNT
        FROM
              table t
        WHERE t.itmename = 'tokyo';

        IF V_COUNT > 0 THEN
            SELECT
                  MIN(itemprice)
            INTO
                  V_MIN_ITEMPRICE
            FROM
                  table t
            WHERE t.itmename = 'tokyo';

四、ORDER BY排序问题

Oracle中如果使用order by对varchar2类型的字段进行排序，可能会出现错误。因为对varchar2类型进行排序的时候，首先比较第一位数字大小，然后再比较第二位数字大小，以此类推，因此可能会出现1 < 123 < 2 的情况。

解决方法：
・① CAST函数：
　 CAST函数是SQL标准中定义的通用函数，用于执行数据类型转换。它可以将一个表达式或列转换为指定的数据类型。

SELECT * FROM ITEM
ORDER  BY CAST(SEQNO AS INTEGER) ASC

・② TO_NUMBER函数：
　 TO_NUMBER函数是Oracle特定的函数，用于将字符串或其他数据类型转换为数值类型。

SELECT * FROM ITEM 
ORDER BY TO_NUMBER(SEQNO) ASC

・CAST和TO_NUMBER函数主要区别如下：

范围：CAST函数是SQL标准的一部分，因此在支持SQL标准的各种数据库管理系统中都可以使用。而TO_NUMBER函数是Oracle特定的函数，只能在Oracle数据库中使用。

用途：CAST函数可以执行通用的数据类型转换，可以将一个表达式或列转换为指定的数据类型，包括字符串、数值、日期等。TO_NUMBER函数专门用于将字符串或其他数据类型转换为数值类型。

格式掩码：TO_NUMBER函数可以使用格式掩码来指定输入字符串的格式，例如货币符号、千位分隔符等。CAST函数不支持格式掩码，只能执行简单的数据类型转换。

总的来说，CAST函数是一种通用的数据类型转换函数，适用于各种数据库管理系统，而TO_NUMBER函数是Oracle特定的函数，用于将字符串转换为数值类型，并支持格式掩码。

・格式掩码（Format Mask）是在Oracle数据库中用于指定字符串的格式的一种机制。它可以控制字符串的显示方式，例如日期、时间、货币、数值等。格式掩码由特定的字符组成，每个字符都代表一种格式元素或修饰符。

下面是一些常用的格式掩码字符及其含义：

• 9：表示一个数字位置，如果该位置没有数字，则显示为空格。
• 0：表示一个数字位置，如果该位置没有数字，则显示为0。
• .：小数点占位符，用于指定小数点的位置。
• ,：千位分隔符，用于将数字分组显示。
• L：表示一个字母位置，如果该位置没有字母，则显示为空格。
• $：货币符号位置，用于指定货币符号的位置。
• FM：格式修饰符，用于去除填充字符。

以下是一些示例使用格式掩码的情况：

• 999,999.99：将数字显示为千位分隔符分组的格式，最多显示两位小数。
• $999,999.99：将数字显示为货币格式，包括千位分隔符和两位小数。
• 99/99/9999：将数字显示为日期格式，使用斜杠分隔日、月、年。

格式掩码提供了一种灵活的方式来控制数据的显示格式，特别是在查询结果中或通过函数转换数据类型时。它允许将数据以特定的方式呈现给用户，符合其预期的格式要求。

五、DISTINCT 和 UNIQUE

在Oracle中DISTINCT和UNIQUE没有区别，二者为同义词，DISTINCT遵循ANSI标准，UNIQUE是Oracle特定的用法，从移植角度考虑，使用遵循ANSI标准的DISTINCT是一个更好的选择。

六、DISTINCT 和 NULL

DISTINCT将NULL值视为重复值。如果使用SELECT DISTINCT语句从具有多个NULL值的列中查询数据，则结果集只包含一个NULL值。