Home » SQL & PL/SQL » SQL & PL/SQL » START WITH/CONNECT BY returns fewer rows in 10g than 9i
START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #233340] Wed, 25 April 2007 14:14 Go to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
The SQL below returns a count of ten fewer when run against a 10g copy of our DB than it does against the 9i copy. Simple SQL to dump the table in each DB shows that the data matches. Did something break with this command in 10g? What syntax will work properly in both environments?

SELECT count(*)
  FROM SA.PROGRAM_LEVEL_LOCATION
 WHERE PRGRM_ID = 'E25'
   AND CAN_ASSIGN_WCODES_FLG = 1
START WITH LCTN_LEVEL = 1
       AND PRGRM_ID = 'E25'
       AND CAN_ASSIGN_WCODES_FLG = 1
CONNECT BY PARENT_LCTN_ID = prior LCTN_ID
       AND LCTN_LEVEL = (prior LCTN_LEVEL + 1)
       AND PRGRM_ID   = PRIOR PRGRM_ID
       AND SUB_PRGRM_ID = PRIOR SUB_PRGRM_ID
       AND CAN_ASSIGN_WCODES_FLG =1;


The table definition is:
   PRGRM_ID               NOT NULL   VARCHAR2(7)
   LCTN_LEVEL             NOT NULL   NUMBER(2)
   LCTN_ID                NOT NULL   VARCHAR2[8]
   PARENT_LCTN_ID                    VARCHAR2 [8]
   CAN_ASSIGN_WCODES_FLG             NUMBER(1)
   SUB_PRGRM_ID           NOT NULL   VARCHAR2[8]


In this specific case, the actual SQL (i.e. retrieving data, not just a count) should return rows with three distinct LCTN_LEVEL values (1,2,3). The missing ten rows are the level 3 rows where the LCTN_ID at level 3 is the same as levels 2 and 1.

[Mod-Edit: Added code tags]

[Updated on: Thu, 26 April 2007 00:54] by Moderator

Report message to a moderator

Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #233392 is a reply to message #233340] Thu, 26 April 2007 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add create table and insert statements so we can see the actual data.
So, not a select * from your table, but actual insert statements please.
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #236350 is a reply to message #233392] Wed, 09 May 2007 12:52 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
Sorry for the delay. Outlook sent your question to junk mail... Using Enterprise Manager, I did create like on the table. Here is the SQL:
CREATE TABLE "SA"."PROGRAM_LEVEL_LOCATION" ("PRGRM_ID" VARCHAR2(7 byte) NOT NULL,
"LCTN_LEVEL" NUMBER(2) NOT NULL, "LCTN_ID" VARCHAR2(8 byte)
NOT NULL, "PARENT_LCTN_ID" VARCHAR2(8 byte),
"CAN_ASSIGN_WCODES_FLG" NUMBER(1), "SUB_PRGRM_ID" VARCHAR2(8
byte) NOT NULL,
CONSTRAINT "PK_PROGRAM_LEVEL_LOCATION_1" PRIMARY
KEY("PRGRM_ID", "SUB_PRGRM_ID", "LCTN_LEVEL", "LCTN_ID")
USING INDEX
TABLESPACE "USERS"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)

TABLESPACE "D91A_PROD" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 88K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING

Here are insert statements for a subset of data that will reproduce the error:

insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0101',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0201',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0301',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0401',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0501',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0601',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0701',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0801',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0901',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'1001',null,1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0101','0101',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0201','0201',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0239','0201',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0301','0301',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0306','0301',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0401','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0414','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0436','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0501','0501',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0516','0501',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0528','0501',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0601','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0648','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0701','0701',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0801','0801',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0901','0901',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0916','0901',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'1001','1001',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0101','0101',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0126','0101',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0201','0201',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0206','0201',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0239','0239',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0301','0301',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0306','0306',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0328','0306',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0336','0306',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0339','0306',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0401','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0409','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0414','0414',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0416','0436',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0419','0436',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0426','0401',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0429','0414',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0436','0436',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0437','0436',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0414','0446',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0501','0501',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0512','0516',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0512','0516',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0516','0516',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0528','0528',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0536','0516',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0539','0528',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0546','0501',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0601','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0602','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0624','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0637','0648',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0648','0648',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0656','0648',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0659','0601',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0701','0701',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0726','0701',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0736','0701',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0801','0801',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0901','0901',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0908','0901',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0916','0916',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1001','1001',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1006','1001',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1016','1001',1,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0102','0101',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0110','0101',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0136','0101',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0143','0101',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0202','0201',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0216','0239',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0315','0301',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0344','0301',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0440','0437',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0443','0437',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0444','0429',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0450','0429',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0510','0516',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0533','0528',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0544','0528',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0550','0501',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0616','0601',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0641','0601',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0662','0648',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0670','0656',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0705','0701',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0805','0801',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0815','0801',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0820','0801',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0830','0801',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0835','0801',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0905','0901',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0920','0916',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0925','0901',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0930','0901',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0933','0901',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0943','0916',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0944','0901',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0945','0916',0,'(All)');
insert into SA.PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'1008','1001',0,'(All)');

Thanks for your help. We've also found out that setting the optimizer features initialization parameter to 9.2 causes the SQL to run properly.
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #236359 is a reply to message #236350] Wed, 09 May 2007 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which parameters? What are their values (before and after).

Btw, there are errors in your script. I got some "duplicate keys". Please fix it.
Also I don't have a user SA, I don't have a tablespace USERS, I don't have a tablespace D91A_PROD.

Regards
Michel

[Updated on: Wed, 09 May 2007 13:37]

Report message to a moderator

Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #237034 is a reply to message #236359] Fri, 11 May 2007 12:26 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
I don't think it matters what your schema or tablespace are, so you can change them. I don't understand why you're getting duplicate keys. There are no keys on the table. What constraints did you add to the table?

As for the parameter, it was only one, and it was the one that I mentioned - the optimizer features parameter. Specifically, optimizer_features_enable. We've set it globally, but you can test with it using:

SQL> connect as query owner

SQL> alter session set optimizer_features_enable='9.2.0';

then execute the query

[Updated on: Fri, 11 May 2007 12:29]

Report message to a moderator

Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #237040 is a reply to message #237034] Fri, 11 May 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't think it matters what your schema or tablespace are, so you can change them.

I agree it does not matter but why should I make the effort to modify your test to help you? Put one I can directly use.
Quote:
There are no keys on the table. What constraints did you add to the table?

None that there is not in your test case:
Quote:
CONSTRAINT "PK_PROGRAM_LEVEL_LOCATION_1" PRIMARY
KEY("PRGRM_ID", "SUB_PRGRM_ID", "LCTN_LEVEL", "LCTN_ID")

Quote:
As for the parameter, it was only one, and it was the one that I mentioned

You just say:
Quote:
setting the optimizer features initialization parameter

There are many optimizer features parameter, so it was important to mention which and above with which value you set it.
Now what is your Oracle version? With 4 decimals not just 10g.

Regards
Michel

Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #239928 is a reply to message #236350] Wed, 23 May 2007 12:24 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
Sorry for the confusion. The original "create" SQL I posted is just what the Enterprise Manager wizard generated for CREATE LIKE. I didn't realize that Enterprise Manager adds constraints that aren't on the original table. Drop the table and try this create statement:

CREATE TABLE PROGRAM_LEVEL_LOCATION (
"PRGRM_ID" VARCHAR2(7 byte) NOT NULL,
"LCTN_LEVEL" NUMBER(2) NOT NULL,
"LCTN_ID" VARCHAR2(8 byte) NOT NULL,
"PARENT_LCTN_ID" VARCHAR2(8 byte),
"CAN_ASSIGN_WCODES_FLG" NUMBER(1),
"SUB_PRGRM_ID" VARCHAR2(8 byte) NOT NULL))
STORAGE ( INITIAL 88K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
LOGGING;

I've omitted any reference to tablespaces or other things that might be specific to our database. I also used global search/replace to remove the SA. references from the insert statements. Hope that is sufficient.

insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0101',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0201',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0301',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0401',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0501',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0601',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0701',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0801',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'0901',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',1,'1001',null,1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0101','0101',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0201','0201',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0239','0201',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0301','0301',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0306','0301',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0401','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0414','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0436','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0501','0501',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0516','0501',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0528','0501',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0601','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0648','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0701','0701',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0801','0801',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0901','0901',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'0916','0901',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',2,'1001','1001',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0101','0101',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0126','0101',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0201','0201',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0206','0201',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0239','0239',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0301','0301',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0306','0306',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0328','0306',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0336','0306',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0339','0306',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0401','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0409','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0414','0414',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0416','0436',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0419','0436',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0426','0401',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0429','0414',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0436','0436',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0437','0436',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0414','0446',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0501','0501',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0512','0516',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0512','0516',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0516','0516',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0528','0528',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0536','0516',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0539','0528',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0546','0501',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0601','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0602','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0624','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0637','0648',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0648','0648',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0656','0648',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0659','0601',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0701','0701',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0726','0701',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0736','0701',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0801','0801',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0901','0901',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0908','0901',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'0916','0916',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1001','1001',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1006','1001',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',3,'1016','1001',1,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0102','0101',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0110','0101',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0136','0101',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0143','0101',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0202','0201',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0216','0239',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0315','0301',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0344','0301',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0440','0437',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0443','0437',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0444','0429',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0450','0429',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0510','0516',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0533','0528',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0544','0528',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0550','0501',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0616','0601',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0641','0601',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0662','0648',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0670','0656',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0705','0701',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0805','0801',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0815','0801',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0820','0801',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0830','0801',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0835','0801',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0905','0901',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0920','0916',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0925','0901',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0930','0901',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0933','0901',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0943','0916',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0944','0901',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'0945','0916',0,'(All)');
insert into PROGRAM_LEVEL_LOCATION (PRGRM_ID, LCTN_LEVEL, LCTN_ID, PARENT_LCTN_ID, CAN_ASSIGN_WCODES_FLG,SUB_PRGRM_ID) VALUES ('E25',4,'1008','1001',0,'(All)');

The optimizer feature parameter is "optimizer_features_enable" which was not set originally (when the SQL fails), but should be set to '9.2.0' in order to get the SQL to run properly.
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #239930 is a reply to message #239928] Wed, 23 May 2007 12:27 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
Forgot to include the Oracle version... Enterprise Manager shows it as 10.2.0.2.0
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #239961 is a reply to message #239930] Wed, 23 May 2007 14:18 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
More information from Oracle support... There is another initialization parameter that will also work around the issue:

SQL> alter session set "_OPTIMIZER_CONNECT_BY_COST_BASED"=FALSE;
SQL> run the problem query

I tested this and it does indeed cause the SQL to return the correct number of rows. I don't know, but it seems like this parameter would only affect CONNECT BY sql statements and therefore be preferable - since the remaining SQL can then take advantage of 10g performance improvements. Do you agree?

Below is a reference to other users having connect by related problems that use this initialization parameter as a workaround. Their bug it being fixed in 10.2.0.3 I don't know whether this bug will get fixed as a side effect of that fix. I also don't know when 10.2.0.3 is expected to be out. The posting is old (March 2006).

http://forums.oracle.com/forums/thread.jspa?threadID=369838
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #239973 is a reply to message #239961] Wed, 23 May 2007 15:22 Go to previous messageGo to next message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
Found this reference http://www.anysql.net/doc/bug10203.html, so it looks like 10.2.0.3 is available now. Am following up with our DBAs about why the new servers were set up as 10.2.0.2 instead of 10.2.0.3
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #240023 is a reply to message #239973] Wed, 23 May 2007 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10.2.0.3 is there since Dec 2006/Jan 2007, 10.2.0.4 should be there in a couple of weeks.

(I can't see the link you posted, it returns me an invalid link.)

Regards
Michel
Re: START WITH/CONNECT BY returns fewer rows in 10g than 9i [message #240333 is a reply to message #240023] Thu, 24 May 2007 11:29 Go to previous message
sarahsi
Messages: 8
Registered: February 2007
Junior Member
Thanks for the info on 10.2.0.3 and 10.2.0.4 I'm having some challenges getting 10.2.0.3 across to our DBA. Their comment to my request for it was "We applied the CPUApr2007 patch 5901881 for the oracle 10g version 10.2.0.2.0. This patch CPUApr2007 is a critical patch update, the oracle version will not change after applied it." sort of implying they've applied what they know to be the most recent patch and that I shouldn't expect the version to go to 10.2.0.3 ... not your problem.

Sorry about the link. My comma in making the sentence proper English got translated as being part of the URL. I didn't notice that when previwing. The link is http://www.anysql.net/doc/bug10203.html
Previous Topic: Trace all DML statements in a series of procedures
Next Topic: Using CONVERT US7ASCII to drop accents
Goto Forum:
  


Current Time: Fri Dec 09 02:03:12 CST 2016

Total time taken to generate the page: 0.07726 seconds