Error when comparing values in Oracle

Question

I write simple procedure.

DECLARE
  connection_id LINE.CONNECTION_ID%TYPE := 11009;
  tmp_integer INTEGER;
BEGIN
  SELECT COUNT(*) INTO tmp_integer FROM LINE WHERE LINE.CONNECTION_ID = 11009;
  DBMS_OUTPUT.PUT_LINE(connection_id);
  DBMS_OUTPUT.PUT_LINE(tmp_integer);
END;

Result of the launch:

11009
3

It is good result. I have only 3 rows where CONNECTION_ID is 11009. After modification:

DECLARE
  connection_id LINE.CONNECTION_ID%TYPE := 11009;
  tmp_integer INTEGER;
BEGIN
  SELECT COUNT(*) INTO tmp_integer FROM LINE WHERE LINE.CONNECTION_ID = connection_id;
  DBMS_OUTPUT.PUT_LINE(connection_id);
  DBMS_OUTPUT.PUT_LINE(tmp_integer);
END;

But in this case I gain strange result:

11009
30997

Where is the mistake?

Answer 1

Try changing the name of your PL/SQL variable to be different from the column name in the table, e.g. v_connection_id.