ORACLE:找不到数据-但是数据存在 [英] ORACLE: NO DATA FOUND -- but data exists

问题描述

调试打包过程，发现实际上没有数据.

Debugging a package procedure and am getting a no data found when there is in fact data.

仅测试SELECT

SELECT trim(trailing '/' from GL_SECURITY) as DUMMY 
FROM b2k_user@b2k
WHERE sms_username = 'FUCHSB';

很高兴返回我的值:'23706 * 706'

This happily returns my value : '23706*706'

我一经尝试将其选择为INTO，就会收到NO_DATA _FOUND错误 (评论了我输入的错误处理)

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)

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

我可以很好地捕获该错误，除了基于第一个查询的事实我知道100％的数据库中有一个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.

任何想法..我真的开始鄙视Oracle.是的，此查询正在数据链接上运行，如第一个查询中所示，数据在那里.

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.

谢谢

解决了SQL开发人员中的奇怪行为，使我忽略了潜在的空格:

SOLVED strange behavior in SQL developer caused me to overlook potential whitespace:

当运行独立选择"时，SQL Developer看起来在执行"WHERE sms_username = p_BAS_user_name;"时会应用其自身的修整比较器.部分..当坐在包装盒中时，结果并没有..一堆空白引起了问题..仍然很奇怪，它在正常选择时返回.不过谢谢！

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!

推荐答案

我很确定我找到了造成这种现象的原因:我猜测该列实际上是CHAR类型，而不是VARCHAR2.

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.

请考虑以下内容:

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

结论:

• 使用CHAR数据类型时，将PL/SQL变量声明为CHAR.
• 在可能的情况下，最好使用VARCHAR2数据类型来定义表列. CHAR数据类型只是膨胀的VARCHAR2数据类型，不会在VARCHAR2数据类型之上添加任何功能(消耗更多空间/内存不是功能).

这篇关于ORACLE:找不到数据-但是数据存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！