SQL command skips where clause when in a package

Question

I have a problem with my plsql code that it skips the where clause if it's in a package and gives me the wrong result. I made a small test that you can run. Needs to enable DBMS output in SQL Developer and run sequentially, one command/block at a time. The Oracle DB is version 12C.

create table tmp_bounding_box  (
  id number,
  x number(14,10),
  y number(14,10)
);

insert into tmp_bounding_box (id,x,y) values(1,0.0,0.0);
insert into tmp_bounding_box (id,x,y) values(1,0.0,4.0);
insert into tmp_bounding_box (id,x,y) values(1,4.0,4.0);
insert into tmp_bounding_box (id,x,y) values(1,4.0,0.0);
insert into tmp_bounding_box (id,x,y) values(2,1.0,1.0);
insert into tmp_bounding_box (id,x,y) values(2,1.0,3.0);
insert into tmp_bounding_box (id,x,y) values(2,3.0,3.0);
insert into tmp_bounding_box (id,x,y) values(2,3.0,1.0);
commit;

declare
    MAX_X number(14,10) := 0;
    MIN_X number(14,10) := 0;
    MAX_Y number(14,10) := 0;
    MIN_Y number(14,10) := 0;
    log_msg varchar2(4000);
begin  
    select max(x), min(x), max(y), min(y) into MAX_X, MIN_X, MAX_Y, MIN_Y 
    from tmp_bounding_box where id in ( 2 );

    log_msg := 'Bounding box: max_x<'||MAX_X||'> min_x<'||MIN_X||'> max_y<'||MAX_Y||'> min_y<'||MIN_Y||'>';
    dbms_output.put_line(log_msg);
end;

create or replace PACKAGE bounding_box_pck as 
    procedure GetBoundingBox( id number );
end bounding_box_pck;

create or replace PACKAGE BODY bounding_box_pck as
    procedure GetBoundingBox( id number ) is 
    MAX_X number(14,10) := 0;
    MIN_X number(14,10) := 0;
    MAX_Y number(14,10) := 0;
    MIN_Y number(14,10) := 0;
    log_msg varchar2(4000);
begin
    select max(x), min(x), max(y), min(y) into MAX_X, MIN_X, MAX_Y, MIN_Y 
    from tmp_bounding_box where id in ( id );

    log_msg := 'Bounding box: max_x<'||MAX_X||'> min_x<'||MIN_X||'> max_y<'||MAX_Y||'> min_y<'||MIN_Y||'>';
    dbms_output.put_line(log_msg);

  end GetBoundingBox;
end bounding_box_pck;

begin
    bounding_box_pck.GetBoundingBox(2);
end;

First pl/sql block returns:

Bounding box: max_x<3> min_x<1> max_y<3> min_y<1>

Second pl/sql block returns:

Bounding box: max_x<4> min_x<0> max_y<4> min_y<0>

I have been trying to find out why this happens but with no result.

user_1118978 · Answer

In your second procedure

select max(x), min(x), max(y), min(y) into MAX_X, MIN_X, MAX_Y, MIN_Y 
    from tmp_bounding_box where id in ( id );

this query compares id column by itself in the where clause. It is not using your variable. consider to change your variable name to v_id

select max(x), min(x), max(y), min(y) into MAX_X, MIN_X, MAX_Y, MIN_Y 
    from tmp_bounding_box where id in ( v_id );

SQL command skips where clause when in a package

1 Answers