Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Same query, different results

Re: Same query, different results

From: Thorsten Jens <thojens_at_gmx.de>
Date: 19 Apr 2007 22:41:54 -0700
Message-ID: <1177047714.785754.128710@y80g2000hsf.googlegroups.com>


On Apr 19, 7:56 pm, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> 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.
>
>
> Is your column by any chance of datatype CHAR ?
> Then it could be the possible reason: [...]

That was it, indeed. I changed the variables from varchar2 to use table.column%type, which works!

Thank you,
Thorsten Received on Fri Apr 20 2007 - 00:41:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US