Home » SQL & PL/SQL » SQL & PL/SQL » SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does (Ora Enterprise 11.1.0.7.0 on WinServer 2008 R2)
SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570368] Thu, 08 November 2012 06:35 Go to next message
geomac
Messages: 8
Registered: November 2012
Location: Germany
Junior Member
Hi all, my OS is WinServer 2008 R2 64 bit and i have 2 ORACLE instances....

an Ora Enterprise 11.1.0.7.0 on port 1521 and
Ora XE 10.2.0.1.0 on port 1522

so i created a table TEST_E with named constraints and added some data

xxxxxxxxxxxxxxxxxxxxx

-- create tables
create table TEST_E (
ID number (19,0) not null,
primary key (ID)
);
create table TEST_HJ (
PID number(19,0) not null,
CID number(19,0) not null,
primary key (PID, CID),
constraint FK_TEST_PID foreign key (PID) references TEST_E,
constraint FK_TEST_CID foreign key (CID) references TEST_E
);

-- fill data
insert into TEST_E values (1);
insert into TEST_E values (2);
insert into TEST_HJ (PID, CID) values (1, 2);

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
- no i´d like so set this queries but unfortunately the last query gets no result.... the named 2nd and 3rd succeed..
- i have no idea why 2nd query succeeds when have added just a column in select (p.ID)

-- query data (expected result: one row containing the value 1 in the ID column)
-- does NOT work (empty result set)
SELECT t.ID FROM TEST_E t left join TEST_HJ j on t.ID = j.CID left join TEST_E p on j.PID = p.ID where p.ID is null;

-- does work
SELECT t.ID, p.ID FROM TEST_E t left join TEST_HJ j on t.ID = j.CID left join TEST_E p on j.PID = p.ID where p.ID is null;
-- does work too
SELECT t.id FROM TEST_E t left join TEST_HJ j on t.ID = j.CID where j.PID is null;

this skripts are used to drop the tables

-- cleanup
drop table TEST_HJ cascade constraints;
drop table TEST_E cascade constraints;

=> SUMMING UP: problems occures only in ORA 11 Enterprise, in ora 10 and 11 XE its working
=> seems like a bug in database but also in enterprise 11.2 its not working....

does anybody have some hints or further documentation.. TIA Carsten

Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570369 is a reply to message #570368] Thu, 08 November 2012 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SUMMING UP: problems occures only in ORA 11 Enterprise


Which 11? Give the version with 4 decimals.

Anyway, if you find a wrong result your only chance is to raise a SR to Oracle.
Either it is a known and fix problem and you'll have the solution, either you will have to wait for a new patch. In all cases, only Oracle can give you the solution.

Note that the result is correct in 11.2.0.3.

Regards
Michel
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570370 is a reply to message #570369] Thu, 08 November 2012 07:14 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 08 November 2012 12:50

Which 11? Give the version with 4 decimals.


geomac wrote on Thu, 08 November 2012 12:35
Hi all, my OS is WinServer 2008 R2 64 bit and i have 2 ORACLE instances....

an Ora Enterprise 11.1.0.7.0


Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570372 is a reply to message #570370] Thu, 08 November 2012 07:21 Go to previous messageGo to next message
geomac
Messages: 8
Registered: November 2012
Location: Germany
Junior Member
my ORA Enterpise is 11.1.0.7.0 and 11.2.0.1.
so it should work in 11.2.0.3. Thanks .. i´ll try carsten
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570375 is a reply to message #570370] Thu, 08 November 2012 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Thu, 08 November 2012 14:14
Michel Cadot wrote on Thu, 08 November 2012 12:50

Which 11? Give the version with 4 decimals.


geomac wrote on Thu, 08 November 2012 12:35
Hi all, my OS is WinServer 2008 R2 64 bit and i have 2 ORACLE instances....

an Ora Enterprise 11.1.0.7.0




Yes but:

Quote:
seems like a bug in database but also in enterprise 11.2 its not working....


So?

Regards
Michel
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570388 is a reply to message #570375] Thu, 08 November 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/B25329_01/doc/license.102/b25456/toc.htm#BABHFGHA
"Any use of the Oracle Database Express Edition is subject to the following limitations; 1. Express Edition is limited to a single instance on any server;"

it appears that you are in direct violation of XE License
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570394 is a reply to message #570388] Thu, 08 November 2012 10:08 Go to previous messageGo to next message
geomac
Messages: 8
Registered: November 2012
Location: Germany
Junior Member
- i tried ora enterprise on different maschines... so 11.1.0.7 and 11.2.0.1 both delivered no result after query
- so it should work with 11.2.0.3 .. so the XE instance was only for test purpose and will vanish (be de-installed)
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570397 is a reply to message #570368] Thu, 08 November 2012 10:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2067
Registered: January 2010
Senior Member
Bug:

SQL> select  *
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT t.ID FROM TEST_E t left join TEST_HJ j on t.ID = j.CID left join TEST_E p on j.PID = p.ID where p.ID is null;

no rows selected

SQL> SELECT * FROM TEST_E t left join TEST_HJ j on t.ID = j.CID left join TEST_E p on j.PID = p.ID where p.ID is null;

        ID        PID        CID         ID
---------- ---------- ---------- ----------
         1


Workaround:

WITH t AS (
           SELECT  /*+ NO_MERGE */
                   t.ID t_id,
                   j.pid,
                   j.cid,
                   p.id p_id
             FROM      TEST_E t
                   left join
                       TEST_HJ j
                     on t.ID = j.CID
                   left join
                       TEST_E p
                     on j.PID = p.ID
             WHERE p.id IS NULL
          )
SELECT  t_id
  FROM  t
/

      T_ID
----------
         1

SQL> 


SY.
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #570424 is a reply to message #570397] Fri, 09 November 2012 03:54 Go to previous messageGo to next message
geomac
Messages: 8
Registered: November 2012
Location: Germany
Junior Member
hi all, thx for workaround select.. nevertheless i can confirm bug is fixed in 11.2.0.3.0 => so this thread can be closed... thanks to all
Re: SQL gets not result in Ora Enterprise 11.1.0.7.0 but in 10 and 11 XE it does [message #608177 is a reply to message #570424] Mon, 17 February 2014 03:41 Go to previous message
geomac
Messages: 8
Registered: November 2012
Location: Germany
Junior Member
Hi all, the "bug" named above is fixed in ORA 11.2.0.3.0 but in oracle XE 11.2 its still present.... is there any fix / patch solving this issue also in 11 XE R2 ?? Please give me hints.. thanks in advance
Previous Topic: FROM WHERE TO DOWNLOAD UTL_TCP, UTL_SMTP , UTL_MAIL PACKAGES
Next Topic: FLASHBACK QUERY
Goto Forum:
  


Current Time: Thu Nov 27 16:26:49 CST 2014

Total time taken to generate the page: 0.11951 seconds