Re: Oracle bind variables problem

sanjay_at_access.thegroup.net (Sanjay Nayar) wrote:

You didn't describe the structure of VERSION_CONTROL for us but I am going to assume that the column subsystem_name is defined as CHAR(X) in the database. When comparing VARCHAR2(Y) to CHAR(X), the database will promote the VARCHAR2(Y) to CHAR(Y) -- note y is the current length of the varchar, NOT the maxmimum length of the varchar -- . So your string

'DAWIA MIS SYSTEM ENGINE'
is being compared to
'DAWIA MIS SYSTEM ENGINE ' And the varchar2(y) turned into a char(y) is not the same.

On the other hand, the character string constant 'DAWIA MIS SYSTEM ENGINE' has no type originally and so is promoted to what it is being compared to so,

'DAWIA MIS SYSTEM ENGINE'
gets turned into
'DAWIA MIS SYSTEM ENGINE '
which does compare to
'DAWIA MIS SYSTEM ENGINE ' Either-

change the datatype in the database to varchar2 and update the column to rtrim the trailing blanks.

change the plsql code to:
declare

   my_version number;
   my_name VERSION_CONTROL.SUBSYSTEM_NAME%TYPE; begin

   my_name := 'DAWIA MIS SYSTEM ENGINE';

or change the query to one of
select max(subsystem_version)

      into my_version from version_control
      where rtrim(subsystem_name) = my_name; -- WARNING no indexes used

   select max(subsystem_version)

        into my_version from version_control
        where subsystem_name = rpad( my_name, length(subsystem_name) );

If the column is not a char in the database, then I don't know.

If it is char in the database, then this behaviour is to be expected (and mandated by ANSI actually) and is not limited to pl/sql. It will happen in sql (compare a varchar2 column to a char column and you will see the same issue), pl/sql (as you have seen), and pro*c when binding varchar host vars to be compared to char database fields.

>Hi,
 

>We are experiencing a problem here that Oracle Support has been
>unable to provide much help with. It would be great if I could
>get some pointers from someone in this group. For that help I
>will be extermely grateful.
 

>The problem has to do with the use of bind variables in a PL/SQL
>program. The following is a simple test program:
 

>drop table my_temp;
>create table my_temp
>(
> name varchar2(30),
> version number(4,1)
>);
 

>declare
> my_version number;
> my_name varchar2(30);
>begin
> my_name := 'DAWIA MIS SYSTEM ENGINE';
> select max(subsystem_version)
> into my_version from version_control
> where subsystem_name = my_name;
 

> insert into my_temp values (my_name, my_version);
>end;
>.
>/
 

>"my_name" has been set to a string constant before it is used
>in the select statement. However, the select does not get any value
>from the table "version_control" to put into the variable
>"my_version".
 

>On the other hand, if I use the string constant instead of the variable
>"my_name", as in the sample program below:
 

>drop table my_temp;
>create table my_temp
>(
> name varchar2(30),
> version number(4,1)
>);
 

>declare
> my_version number;
>begin
> select max(subsystem_version)
> into my_version from version_control
> where subsystem_name = 'DAWIA MIS SYSTEM ENGINE';
 

> insert into my_temp values ('DAWIA MIS SYSTEM ENGINE', my_version);
>end;
>.
>/
 

>I get the desired result. That is, the correct value gets put into the
>variable "my_version".

>Can anyone give me an idea of what is going on?
 

>Much thanks,
 

>Sanjay
 

>--
 

>Sanjay Nayar |e-mail: sanjay_at_access.thegroup.net
>Systems Analyst
>DynCorp-EENSP
>B-K Dynamics Operation

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Oct 24 1995 - 00:00:00 CET