ORACLE: NO DATA FOUND -- but data exists

Question

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

Testing just the SELECT

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

This happily returns my value : '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)

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.

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:

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!

Solution 1

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.

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).

Solution 2

I noticed another issue for the same error. ERROR at line xx: ORA-01403: no data found ORA-06512: at line xx

select abc into var from table

If the query return no data, above error will throw.

Comments

[Paul Walker]

Good spot (and nice example). The only thing I would add to your conclusion would be to avoid explicitly declaring datatypes in PL/SQL where possible, declare it as: l t.a%TYPE

[Yair]

Thanks, this was happening to me in one of my triggers. My select into statement was null.