We had this same problem last year. The answer was to
upgrade to 8.1.7.2 which came with its own set of bugs
which required we go to 8.1.7.3.
We had run for months in production before we ran into
the bug. Once we hit it, there was no way to avoid
it. The only option was to upgrade.
I don't recall all of the specifics any more, but if
it is important, I can check with the developer. In
our environment, the same query run with SQL Navigator
and run through a batch job returned a different
result set.
- "Aponte, Tony" <AponteT_at_hsn.net> wrote:
> We had a similar issue, although it always resulted
> in an ORA-600. It was with descending indexes as
> defined in the Peopletools repository. We had to
> set _IGNORE_DESC_IN_INDEX=TRUE (and
> _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT=1 to comply
> with the Certification Requirements) and recreate
> the development databases. For Production, PS
> Support gave the OK to recreate the indexes without
> having to redo the upgrade.
>
> HTH
> Tony Aponte
>
> -----Original Message-----
> Sent: Monday, June 10, 2002 1:48 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Accounts payable. But this is not a Peoplesoft
> problem, but an Oracle one. I tried the same query
> against another Peoplesoft instance, and it ran
> fine. So there's something more than the view
> involved here. It's in Oracle Support's court now.
>
> I must be living right. It's failing in development
> and working in production.
>
> Ian
>
> -----Original Message-----
> Sent: Sunday, June 09, 2002 7:13 PM
> To: Multiple recipients of list ORACLE-L
>
>
> what modules, if you dont mind me asking, i'm at a
> site where we're
> going to implement HR, Financials And EPM soon.
>
> thanks, joe
>
>
> MacGregor, Ian A. wrote:
>
> >Yep sure is.
> >
> >Ian
> >
> >
> >-----Original Message-----
> >Sent: Sunday, June 09, 2002 5:43 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Ian, is that peopleslop?
> >
> >joe
> >
> >
> >MacGregor, Ian A. wrote:
> >
> >>SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE
> ROWNUM < 10;
> >>
> >>VOUCHER_
> >>--------
> >>00003394
> >>00003395
> >>00003396
> >>00003397
> >>00003398
> >>00003399
> >>00003400
> >>00003401
> >>00003402
> >>
>
>>------------------------------------------------------------------------
> >>set feedback on
> >>
> >> 1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE VOUCHER_ID = '00003394'
> >>SQL> /
> >>VOUCHER_
> >>--------
> >>
> >>
> >>1 row selected.
>
>>----------------------------------------------------------------------------------
> >>Zounds !!! Select dump(voucher_id) shows that
> Oracle is returning a null here.
> >>
> >>Here a function is used to force the query to do
> full tablescans
> >>
> >>1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE CONCAT(VOUCHER_ID,'A') =
> CONCAT('00003394', 'A')
> >>SQL> /
> >>
> >>VOUCHER_
> >>--------
> >>00003394
>
>>-------------------------------------------------------------------------------------
> >>I get the expected results if I force full table
> scans.
> >>
> >>I looked at the explain plan for the failing query
> and rebuilt the ps_voucher index. This
> >>did not change its erroneous results. I rebuilt
> the view itself again to no avail. A check on
> Metalink revealed bug 1852163. Although this bug's
> conditions were different from the one above, their
> were some similarities. One workaround for bug
> 1852163
> >>
> >>alter session set "_complex_view_merging" = true;
> >>
> >>I tried this and the original query still gave
> improper results.
>
>>-----------------------------------------------------------------------------------------------
> >>All queries against the component tables of the
> view work fine.
>
>>------------------------------------------------------------------------------------------
> >>The view text is
> >>
> >>CREATE VIEW SYSADM.PS_VCHR_MM_VW
> >>AS
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
>
>>-------------------------------------------------------------------------------------
> >>If I run the select statement outside of the view
> and tack on the 'voucher_id = ' clause
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
> >>and a.voucher_id = '00003394'
> >>/
> >>
> >>I get the expected results. The query plan
> matches the one for the failing statement.
>
>>----------------------------------------------------------------------------------------------
> >>If I select more than voucher_id from the view
> with the 'voucher_id = ' predicate
> >>the other fields are projected correctly, but
> returns voucher_id as null.
>
>>===========================================================================================
> >>
> >>Ian MacGregor
> >>Stanford Linear Accelerator Center
> >>ian_at_slac.stanford.edu
> >>
> >>
> >>
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Joe Testa
> INET: jtesta_at_dmc-it.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
=== message truncated ===
Pete Barnett
Lead Database Administrator
The Regence Group
pnbarne_at_regence.com
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Peter Barnett
INET: regdba_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Jun 10 2002 - 13:43:41 CDT
