Re: PL/SQL bug?

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 Received on Wed Oct 23 1996 - 00:00:00 CEST