SQL ORACLE：未找到数据——但数据存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8401785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE: NO DATA FOUND -- but data exists
提问by Brennan Fuchs
Debugging a package procedure and am getting a no data found when there is in fact data.
调试一个包过程，当有实际数据时却发现没有数据。
Testing just the SELECT
只测试 SELECT
SELECT trim(trailing '/' from GL_SECURITY) as DUMMY
FROM b2k_user@b2k
WHERE sms_username = 'FUCHSB';
This happily returns my value : '23706*706'
这很高兴地返回了我的值：'23706*706'
As soon as i try to have this selected INTO i get a NO_DATA _FOUND error (commented out the error handling i put in)
一旦我尝试将这个选择 INTO，我就会收到一个 NO_DATA _FOUND 错误（注释掉我输入的错误处理）
set serveroutput on
DECLARE
p_BAS_user_name varchar2(20);
v_gl_inclusion varchar2(1000);
v_gl_exclusions varchar2(1000);
BEGIN
--inputs
p_BAS_user_name := 'FUCHSB';
dbms_output.put_line(p_BAS_user_name);
----- GOOD -----
--BEGIN
SELECT trim(trailing '/' from GL_SECURITY) as DUMMY
INTO v_gl_inclusion
FROM b2k_user@b2k
WHERE sms_username = p_BAS_user_name;
--EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- v_gl_inclusion := 'SUPER EFFING STUPID';
--END;
dbms_output.put_line(v_gl_inclusion);
END;
Error report:
ORA-01403: no data found
ORA-06512: at line 12
01403. 00000 - "no data found"
*Cause:
*Action:
FUCHSB
I can catch the error just fine except for the fact that based on the 1st query i know 100% there is a value for FUCHSB in the database.
我可以很好地捕获错误，除了基于第一个查询我知道 100% 数据库中有 FUCHSB 的值。
Any ideas.. I'm really starting to despise Oracle. Yes this query is being run over a datalink as seen in the 1st query the data is there.
任何想法.. 我真的开始鄙视甲骨文了。是的，此查询正在数据链路上运行，如第一个查询中所见，数据在那里。
Thanks
谢谢
SOLVED strange behavior in SQL developer caused me to overlook potential whitespace:
解决 SQL 开发人员中的奇怪行为导致我忽略了潜在的空格：
It looks as though SQL Developer when running the standalone select applies its own trimming comparator when doing the 'WHERE sms_username = p_BAS_user_name;' portion.. turns out when sitting in the package it does not.. bunch of white space was causing the issue.. still strange that it returns on the normal select. Thanks though!
看起来好像 SQL Developer 在运行独立选择时在执行“WHERE sms_username = p_BAS_user_name;”时应用了自己的修剪比较器 部分 .. 事实证明，当坐在包裹中时，它不会......一堆空白导致了这个问题......它在正常选择时返回仍然很奇怪。不过还是谢谢！
回答by Vincent Malgrat
I'm pretty sure I found the cause of this behaviour: I'm guessing that the column is actually of type CHAR and not VARCHAR2.
我很确定我找到了这种行为的原因：我猜测该列实际上是 CHAR 类型而不是 VARCHAR2 类型。
Consider the following:
考虑以下：
SQL> CREATE TABLE t (a CHAR(10));
Table created.
SQL> INSERT INTO t VALUES ('FUCHSB');
1 row created.
SQL> SELECT * FROM t WHERE a = 'FUCHSB';
A
----------
FUCHSB
SQL> DECLARE
2 l VARCHAR2(20) := 'FUCHSB';
3 BEGIN
4 SELECT a INTO l FROM t WHERE a = l;
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Conclusion:
结论：
- When working with the CHAR datatype, declare your PL/SQL variables as CHAR.
- When possible, prefer the VARCHAR2 datatype for table column definition. The CHAR datatype is just a bloated VARCHAR2 datatype and doesn't add any feature over the VARCHAR2 datatype (consuming more space/memory is not a feature).
- 使用 CHAR 数据类型时，将 PL/SQL 变量声明为 CHAR。
- 如果可能，最好为表列定义使用 VARCHAR2 数据类型。CHAR 数据类型只是一个臃肿的 VARCHAR2 数据类型，并没有在 VARCHAR2 数据类型上添加任何功能（消耗更多空间/内存不是一个功能）。
回答by MukeshKoshyM
I noticed another issue for the same error. ERROR at line xx: ORA-01403: no data found ORA-06512: at line xx
我注意到相同错误的另一个问题。第 xx 行错误：ORA-01403：未找到数据 ORA-06512：第 xx 行
select abc into var from table
If the query return no data, above error will throw.
如果查询没有返回数据，则会抛出上述错误。
