Same query, different results

From: Thorsten Jens <thojens_at_gmx.de>
Date: 19 Apr 2007 01:41:12 -0700
Message-ID: <1176972072.086254.290950@e65g2000hsc.googlegroups.com>

Hi,

take these two PL/SQL snippets:

 declare
   pznr_tnsw VARCHAR2(200);
   pdoknr VARCHAR2(200) := 'D1.4655-00-00-00.00-00';  begin

     SELECT zvz_tnswhdw_zchnr
      INTO pznr_tnsw
      FROM pp_info.zvz_tnswhdw
     WHERE zvz_tnswhdw_doknr = pdoknr AND
           zvz_tnswhdw_auf = '51398';

 end;

vs.

 declare
   pznr_tnsw VARCHAR2(200);
   pdoknr VARCHAR2(200) := 'D1.4655-00-00-00.00-00';  begin

     SELECT zvz_tnswhdw_zchnr
      INTO pznr_tnsw
      FROM pp_info.zvz_tnswhdw
     WHERE zvz_tnswhdw_doknr = 'D1.4655-00-00-00.00-00' AND
           zvz_tnswhdw_auf = '51398';

 end;

The only difference is that in #1 the string to compare comes from a variable, while in #2 it is specified directly.

Running #1 leads to ORA-01403, "no data found" -- while #2 leads to ORA-01422, "exact fetch returns more than requested number of rows". Why that? Am I doing something wrong here? There is more than one matching row, so I was expecting #1 to return ORA-01422 as well.

Oracle 9.2.0.7.0 EE on Solaris 64bit.

Thanks,
Thorsten Received on Thu Apr 19 2007 - 03:41:12 CDT