Re: ORA-03123

TR wrote:
> Hi
> I want to execute the following sql in a data source in Cognos Decision
> stream.
>
> SELECT
> A.CALENDAR_DATE,
> B.PROD_ID,
> D.ITEM_ID,
> D.TYPE,
> C.ITEM_LEVEL,
> COUNT(D.PROD) TOTAL_PRODUCTS
> FROM
> CALENDAR_BY_YEAR A,
> PRODUCT_DETAIL B,
> ITEM_DETAIL C,
> STORE_DETAIL D,
> STORE_TYPE_DETAIL E
> WHERE
> A.CAL_DATE = E.CAL_DATE AND
> B.STORE_ID = E.STORE_ID AND
> C.ITEM_LEVEL = E.ITEM_LEVEL AND
> D.PRODUCT_ID = E.PRODUCT_ID
> GROUP BY
> A.CALENDAR_DATE,
> B.PROD_ID,
> D.ITEM_ID,
> D.TYPE,
> C.ITEM_LEVEL
>
> When I execute it I am getting the following errors in Decision stream
> and also in Oracle:
> EndDataStream returned with 1 message
> 1. ERROR
> DS-DBMS-E400: UDA driver reported the following on connection 'SOURCE':
>
> DMS-E-GENERAL, A general exception has occurred during operation
> 'rollback transaction'.
> ORA-03123: operation would block
>
> DMS-E-GENERAL, A general exception has occurred during operation
> 'detach database'.
> ORA-03127: no new operations allowed until the active operation ends
>
> I think there is nothing wrong with the query. But I want to know why
> am I getting this error and how can I correct it? Can some plz help me
> out?
>
> Thank you.
>
> TR.

Take it back to:

SELECT A.CALENDAR_DATE, COUNT(D.PROD) TOTAL_PRODUCTS FROM CALENDAR_BY_YEAR A, STORE_DETAIL D, STORE_TYPE_DETAIL E WHERE A.CAL_DATE = E.CAL_DATE
AND D.PRODUCT_ID = E.PRODUCT_ID
GROUP BY A.CALENDAR_DATE; and start adding back pieces until it breaks.

Make sure you don't have a Cartesian product given joins such as the one on CAL_DATE.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)