Re: PL/SQL bug?

Eric Byrnes wrote:
>
> Alex Ma wrote:
> >
> > The following sample procedure takes in an input parameter P_NAME
> > and inside the procedure I use the P_NAME in the SELECT statement:
> >
> > procedure XXX(P_NAME in varchar2) is
> > l_field number(10);
> > begin
> > SELECT field
> > INTO l_field
> > FROM table
> > WHERE table.NAME = P_NAME
> > end;
> >
> > The above code would compile but returns wrong result. In fact it will
> > raise an exception of no_data_found.
>
> I have used this kind of PL/SQL procedure and variable usage ad nauseum
> and have never had any problems. However, I've had interesting things
> happen in certain types of data conversions which PL/SQL makes. A little
> more info might help clear the mystery:
> What are the data types and table definitions involved (i.e. for
> table.NAME, l_name)?
> What version of Oracle is in use?
> What type of data is being passed to P_NAME? Does it have nulls? What
> is the type of the input variable, and where does it come from?
>
> I suspect from the limited information that this may be type-conversion
> related: i.e. implicit conversions between different data types which
> change the intent of what you are doing.
>
> Eric

Also you can try :

procedure XXX(P_NAME in table.NAME%TYPE) is l_field table.field%TYPE;
 begin

    SELECT field
    INTO l_field
    FROM table
    WHERE table.NAME = P_NAME
 end;

Slava. Received on Thu Oct 24 1996 - 00:00:00 CEST