-- The issue
This following query works fine on Toad and PowerBuilder yet when it is run
on SQLPlus I get: "ORA-03113: end-of-file on communication channel"

When I remove the distinct from the first line (capitalized below) it runs
fine on SQLPlus.

Is there some flag I need to set in SQLPlus to get this to work
properly?


-- The query in question:
SELECT DISTINCT t2.USER_GROUP_ID nn
FROM scott.T_XR_CUST t2
,(SELECT distinct t2.USER_ID
FROM scott.T_XR_CUST_ORDER t2
WHERE rownum <= 27526) t8
WHERE t2.USER_ID = t8.USER_ID
;


-- The tables I am selecting from
create table scott.t_xr_cust
(cust_id number(10,0) NOT NULL,
,user_group_id number(10,0) NOT NULL
);
Primary key is cust_id.

create table scott.t_xr_cust_order
(cust_id number(10,0) NOT NULL,
,order_id number(10,0) NOT NULL
);
Primary key is cust_id, order_id.
Foreign key constraint exists on the cust_id column and tr_xr_cust.cust_id.


-- Environment
OS: Running Windows XP (Home Edition) on a Toshiba A25-S207 laptop.

SQL*Plus: Release 9.2.0.1.0 - Production

Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

My guess will be you simply run out of memory. Oracle selects all records
and then tried to select only unique.
I am not really experienced with Oracle Personal so, sorry cannot give you
advice on that. but regular Oracle does have parameters how memory to use
( even few of them) into init file

Would you try something different query. On tables you gave it will be
something like

SELECT DISTINCT t2.USER_GROUP_ID nn
FROM scott.T_XR_CUST t2 left outer join scott.T_XR_CUST_ORDER t8 on
(t2.USER_ID = t8.USER_ID)
WHERE t8.USER_ID is not null

or ,which could work for you even better,

SELECT DISTINCT t2.USER_GROUP_ID nn
FROM scott.T_XR_CUST t2
WHERE EXISTS ( SELECT 1 FROM scott.T_XR_CUST_ORDER t8 on WHERE t8.USER_ID =
t2.USER_ID)

"BobG" <robert.goulart (AT) sagacityus (DOT) com> wrote