3 Replies Latest reply: Jun 5, 2013 6:41 PM by alwu-Oracle RSS

    Oracle 11g - Problem in referring ROWNUM in the SQL

    739891
      Hello All,

      We are facing a strange problem with Oracle 11g (11.2.0.1.0).
      When we issue a query which refers the rownum, it returns a invalid record ( which is not exists in the table).
      The same sql is working fine once we analyze the table
      Note: The same sql is working fine with oracle 10g (Before analyze also).
      -----

      The script to reproduce the issue:

      DROP TABLE BusinessEntities;
      CREATE TABLE BusinessEntities
      (
      business_entity_id VARCHAR2(25) PRIMARY KEY,
      business_entity_name VARCHAR2(50) NOT NULL ,
      owner_id VARCHAR2(25) ,
      statutory_detail_id NUMBER ,
      address_id NUMBER NOT NULL
      );

      DROP TABLE BusEntityRoles;
      CREATE TABLE BusEntityRoles
      (
      business_entity_id VARCHAR2(25) NOT NULL,
      role_id VARCHAR2(10) NOT NULL,
      PRIMARY KEY (business_entity_id, role_id)
      );

      INSERT
      INTO businessentities ( business_entity_id , business_entity_name, owner_id , statutory_detail_id , address_id)
      VALUES
      ( 'OWNER', 'OWNER Corporation Ltd', NULL , 1, 1 );

      INSERT
      INTO businessentities ( business_entity_id , business_entity_name, owner_id , statutory_detail_id , address_id)
      VALUES
      ( 'ALL_IN_ALL', 'ALL IN ALL Corporation Ltd', 'OWNER' , 2, 2 );

      INSERT INTO busentityroles(business_entity_id, role_id) VALUES ('TEST' , 'OWNER');
      INSERT INTO busentityroles (business_entity_id,role_id) VALUES ('TEST','VENDOR');
      INSERT INTO busentityroles(business_entity_id, role_id) VALUES ('ALL_IN_ALL' , 'VENDOR');

      SELECT *
      FROM
      (SELECT raw_sql_.business_entity_id, raw_sql_.business_entity_name, raw_sql_.owner_id, raw_sql_.address_id,
      rownum raw_rnum_
      FROM
      (SELECT *
      FROM BusinessEntities
      WHERE (business_entity_id IN
      (SELECT business_entity_id
      FROM BusinessEntities
      WHERE business_entity_id = 'OWNER'
      OR owner_id = 'ALL_IN_ALL'
      AND business_entity_id NOT IN
      (SELECT business_entity_id FROM BusEntityRoles
      )
      ))
      ORDER BY business_entity_id ASC
      ) raw_sql_
      WHERE rownum <= 5
      )
      WHERE raw_rnum_ > 0;
      --------

      OUTPUT Before Analyzing

      BUSINESS_ENTITY_ID: OWNER
      BUSINESS_ENTITY_NAME: NULL
      OWNER_ID: OWNER
      ADDRESS_ID: NULL
      RAW_RNUM_: 1

      Note: There is no record in the table with the value business_entity_id as 'OWNER' and OWNER_ID as 'OWNER' and the address_id as NULL
      ---------

      OUTPUT : After analyzed the table Using the below mentioned command

      ANALYZE TABLE "BUSENTITYSUPPLYCHAINROLES" ESTIMATE STATISTICS
      ANALYZE TABLE "BUSINESSENTITIES" ESTIMATE STATISTICS

      BUSINESS_ENTITY_ID: OWNER
      BUSINESS_ENTITY_NAME: OWNER Corporation Ltd
      OWNER_ID: NULL
      ADDRESS_ID: 1
      RAW_RNUM_: 1

      --------

      Any clue why Oracle 11g is behaving like this.
        • 1. Re: Oracle 11g - Problem in referring ROWNUM in the SQL
          715399
          Hi,

          Posting your question in the SQL forum will have a higher chance of getting answered, as the issue does not seem related to semantic technologies:
          SQL and PL/SQL

          For what it's worth, I did confirm your query's interesting behavior on 11.2.0.1.

          Thanks,
          Vlad
          • 2. Oracle 11G installation issue on Widnows 7 (64)
            427407
            I'm trying to install Oracle 11G enterprise on windows 7, and keep getting this error

            "The specified home name was not found in the registry" even though it is there. Here is the logs info


            SubKey = software\oracle\OracleMTSRecoveryService\Setup\Current Version
            Value = Home
            INFO: Query Returned: SOFTWARE\ORACLE\KEY_OraDb11g_home1
            INFO: Calling Query areasQueries10.2.0.1.0 getAllOracleHomeNames
            INFO: Query Returned: OraClient11g_home1,OraDb11g_home1
            INFO: Calling Query rgsQueries10.1.0.3.0 getKeyFromHomeName
            OracleHomeName = OraClient11g_home1
            INFO:
            Query Exception: HomeNameNotFoundException
            Query Exception Class: class oracle.sysman.oii.oiil.OiilQueryException
            INFO: Adding ExitStatus STOP_INSTALL to the exit status set
            INFO: Finding the most appropriate exit status for the current application
            INFO: Exit Status is -4
            INFO: Shutdown Oracle Database 11g Release 2 Installer
            INFO: Unloading Setup Driver

            Please let me know what could be the issue?
            Thanks
            • 3. Re: Oracle 11G installation issue on Widnows 7 (64)
              alwu-Oracle
              Hi,

              You may want to post your question to the forum dedicated to Oracle installation.

              Database Installation

              Hope it helps,

              Zhe Wu