1 Reply Latest reply: Aug 22, 2011 12:12 PM by Steve.Clamage-Oracle RSS

    select count(*) on sql statement returning zero when a matching row exists.

      Our account has an ANSI C application that checks for the existence a row on an Oracle table(s) by using the following SQL:

      int iCount = 0;

      EXEC SQL
      SELECT count(rownum) INTO :iCount
      FROM sys.all_tab_columns
      WHERE table_name IN
      (SELECT table_name FROM
      WHERE upper(synonym_name) = upper(:szDestTable))
      AND upper(column_name) = upper(:szColumnName)
      AND owner = 'DBAUSER';

      The bind variables szDestTable and szColumnName are populated with values parsed from columns on another database table. This application is executed through out the day. Occasionally, the application will report a zero in the iCount when there should be a match. I have verified the szDestTable and szColumnName are populated with the correct values which would find a match and they are correct. To make matters even stranger, the application will parse the same input values and find a match (as it should). At some point during the day, and it can be at any time, the application will NOT find a match on the same file, same values. Every subsequent execution of this application will not find a match on the same values. Once the database is brought down and started up in the evening for its normal backups, the application will find a match again on the same values. This problem does not occur every day. I could be a week or a week and a half between incidents.

      I printed the contents of the sqlca.sqqlerrm.sqlerrmc field to a log file. The sqlca.sqlerrm.sqlerrmc field reported an ORA-1405 bind variable was null when the iCount was reporting a zero. When I compiled this application, I set the Proc*C flag to UNSAFE_NULLS=yes since there are other bind variable in the application that can be NULL and that is ok.

      The above SQL is compiled into the C application using the Proc*C compiler. It is compiled using the Oracle libraries. The application is executed against an Oracle database. The database and application are executed on an HP/Unix 11.31 platform.

      This problem did not start occurring until our account went from Oracle 10.2 to Oracle 11.2. Recently, I have changed the SQL to perform a “SELECT COUNT(rownum)” instead of the “SELECT COUNT(*)”. I compiled the application and executed the new application with the SELECT COUNT(rownum) against the same database where the same application with the SELECT COUNT(*) was failing to find a row that actually existed. The application with the SELECT COUNT(rownum) found the matching row as it should have. The new application has been executing in production for about 10 days now without any problems against ten various Oracle 11.2 databases.

      Why would SELECT COUNT(*) and SELECT COUNT(rownum) be any different?