This discussion is archived
3 Replies Latest reply: Jun 5, 2013 4:41 PM by alwu RSS

Oracle 11g - Problem in referring ROWNUM in the SQL

739891 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Hi,

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

    Installation

    Hope it helps,

    Zhe Wu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points