Re: Forms3.0 Inserts w/in Procedure

mike sullivan (sullivan_at_sytex.com) wrote:
> Environ: Forms 3.0.16, RDBMS 6.0.36 on Interactive Unix 3.0
 

> I've got a simple procedure:
> PROCEDURE TEST (code NUMBER) IS
> BEGIN
> INSERT INTO TABLE1 (FIELD1,FIELD2,FIELD3,CODE)
> SELECT FIELD1,FIELD2,FIELD3,CODE
> FROM TABLE
> WHERE CODE = code;
> END;
 
> Problem: When I run this procedure, ALL the records in TABLE are copied
> to TABLE1, NOT just WHERE CODE=code. If I change 'code' in the where
> clause to :BLOCK1.CODE (I basically call this procecure with the
> syntax TEST(:BLOCK1.CODE) ), it works! If I put a MESSAGE to display the
> value of 'code' in this procedure, the correct value is displayed!
 

> Why will one version work and not the other?
Because code is a field in the table: the where clause is being seen as  WHERE TABLE.CODE = TABLE.code
and so everything is altered.

There is a warning in the PL/SQL manual to avoid using variables with the same name as your columns. This is why!

--
  _________________________   __________________________________________
 /  Tommy Wareing          \ /  In the beginning, there was The Bomb    \
|  p0070621_at_brookes.ac.uk   X   And The Bomb said "Let there be Light!"  |
 \  0865-483389            / \     - The Bomb, Dark Star                /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~