Oracle bind variables problem

From: Sanjay Nayar <sanjay_at_access.thegroup.net>
Date: 1995/10/23
Message-ID: <46h8ec$gdm_at_noc.thegroup.net>#1/1


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
Received on Mon Oct 23 1995 - 00:00:00 CET

Original text of this message