Wrong count(*) in pl/sql procedure

Question

Oracle 11g Express Edition 11.2.0

Table "RIGHT" data (contains only 1 row with name = Test):

Id | Name

1 Test

2 New2

3 New14

...

SELECT COUNT(*) FROM RIGHT WHERE name = 'Test';

Result = 1

I have procedure in package TEST:

    create or replace
    PACKAGE BODY TEST
    AS
    PROCEDURE FIND_RIGHT(rightName IN VARCHAR2)
    IS
      countrows       NUMBER;
    BEGIN
      SELECT COUNT(*) INTO countrows FROM RIGHT WHERE name = rightName;

    /* DEBUG POINT !!! in this point countrows = 212 !!!! */
    ...
    END;
    END TEST;

And run it from other procedure:

    DECLARE
      rightName VARCHAR2(200);
    BEGIN
      rightName := 'Test';
      TEST.FIND_RIGHT(rightName);
    END;

I run debugger (in Oracle SQL Developer) and debug point after select. I see countrows = 212.

Why countrows != 1 ???

UPDATED: All transaction is commited. Open only 1 session (from SQL Developer). Table Rights has 3 indexes (table is big, I don't write all colums in the post). Procedures have many input params (custom objects), but I drop extra information.

UPDATED #2: I change code to

    create or replace
    PACKAGE BODY TEST
    AS
    PROCEDURE FIND_RIGHT(rightName IN VARCHAR2)
    IS
      countrows       NUMBER;
      testVar       VARCHAR2(200);
    BEGIN
      testVar := 'Test';
      SELECT COUNT(*) INTO countrows FROM RIGHT WHERE name = testVar;

    /* in this point countrows = 1  */
    ...
    END;
    END TEST;

did you run the count() and the proc call in the same sql developer window? Also do you have any uncommitted transactions (as the window that ran the pl/sql call may have inserted rows but not committed them). best to test both in the same session via sqlplus and show the output. — DazzaL
Yes. I call proc in sql developer window. All transactions is commited. Open only 1 session. — Andrey.Pushin
and if you try the same from a single SQLplus session instead? can you show the output of that from the sqlplus prompt please? — DazzaL
Do you perhaps have multiple tables named 'RIGHT' in the same database but in different schemas? You may want to try fully qualifying the tables being accessed, i.e. use SCHEMA_NAME.TABLE_NAME. — Bob Jarvis - Reinstate Monica
I believe that RIGHT is a reserved word - can you change the name of the table (or create another one for test purposes) and try it with the new table name? — Bob Jarvis - Reinstate Monica

kirotnes kirotnes · Accepted Answer · 2013-04-04T19:48:22

I guess your table contains a column called "rightName"?

if this is the case your orginal query would compare the "name" and "rightName" columns instead of using the procedure argument.

Try changing the argument name.

Wrong count(*) in pl/sql procedure

1 Answers