Re: Same query, different results

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 19 Apr 2007 19:56:14 +0200
Message-ID: <4627AD3E.8080102@arcor.de>

Thorsten Jens schrieb:

> On Apr 19, 11:27 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On 19.04.2007 10:41, Thorsten Jens wrote:
>>
>>
>>
>>> 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.
>> Do you happen to have a column pdoknr in that table?

> 
> No, all columns start with the zvz_tnswhdw_ prefix.
> 
> Thorsten
> 
>

Is your column by any chance of datatype CHAR ? Then it could be the possible reason:

SQL> CREATE TABLE a AS SELECT CAST('hello' AS CHAR(10)) text FROM dual;

Table created.

SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> declare

   2 res varchar2(100);
   3 x varchar2(100);
   4 begin
   5 x:='hello';
   6 BEGIN
   7 select 'selected with literal successful' into res from a where text='hello';

   8 dbms_output.put_line(res);
   9 EXCEPTION
  10 WHEN OTHERS THEN
  11 dbms_output.put_line('selected with literal failed');   12 res:=SQLERRM;
  13 dbms_output.put_line(res);
  14 END;
  15
  16 BEGIN
  17 select 'selected with padded variable successful' into res from a where text=rpad(x,10,' ');
  18 dbms_output.put_line(res);
  19 EXCEPTION
  20 WHEN OTHERS THEN
  21 dbms_output.put_line('selected with padded variable failed');   22 res:=SQLERRM;
  23 dbms_output.put_line(res);
  24 END;
  25
  26 BEGIN
  27 select 'selected with variable successful' into res from a where text=x;
  28 dbms_output.put_line(res);
  29 EXCEPTION
  30 WHEN OTHERS THEN
  31 dbms_output.put_line('selected with variable failed');   32 res:=SQLERRM;
  33 dbms_output.put_line(res);
  34 END;
  35 end;
  36 /
selected with literal successful
selected with padded variable successful selected with variable failed
ORA-01403: no data found

PL/SQL procedure successfully completed.

Best regards

Maxim Received on Thu Apr 19 2007 - 12:56:14 CDT