Loading ...
Sorry, an error occurred while loading the content.

RE: PeopleSoft DBA Forum Query Returning Incomplete Results

Expand Messages
  • Timothy Herring
    There is an Oracle bug that returns inconsistent data-sets related to unnest_subquery PS recommended: *._unnest_subquery=FALSE ... From: the_dragon Draco
    Message 1 of 12 , May 11, 2012
    • 0 Attachment
      There is an Oracle bug that returns inconsistent data-sets related to unnest_subquery

      PS recommended: *._unnest_subquery=FALSE


      --- On Fri, 5/11/12, the_dragon Draco <ceprn@...> wrote:

      From: the_dragon Draco <ceprn@...>
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
      To: psftdba@yahoogroups.com
      Date: Friday, May 11, 2012, 4:47 AM

       

      I remember something like this from a couple of years ago - it required an Oracle patch, if I recall correctly.
       
      Could it be some kind of strange shared pool buffering?
       
      What results do you get if you use sql*plus?
       
      peace,
      clark 'the dragon' willis
       

      To: psftdba@yahoogroups.com
      From: rsalido@...
      Date: Thu, 10 May 2012 11:24:07 -0700
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

       
      This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
       
      MOST of the time I get 1200 rows back but there are instances where I get just one.
       
      So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
       
      As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
       
       We are scratching our heads.

      Follow me on twitter: @rsalido

      --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

      From: David Kurtz <david.kurtz@...>
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
      To: psftdba@yahoogroups.com
      Date: Thursday, May 10, 2012, 11:55 AM

       

      Is it possible that you have hit the maximum query size specified in the Application Server configuration?

       

      Note

      i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

      a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

      ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

       

       

       

      [PSAPPSRV]

      ;=========================================================================

      ; Settings for PSAPPSRV

      ;=========================================================================

      ; Max Fetch Size -- max result set size in KB for a SELECT query

      ; Default is 5000KB. Use 0 for no limit.

      Max Fetch Size=5000

      [PSQRYSRV]

      ;=========================================================================

      ; Settings for PSQRYSRV

      ;=========================================================================

      ; Max Fetch Size -- max result set size in KB for a SELECT query

      ; Default is 10000KB. Use 0 for no limit.

      Max Fetch Size=10000

       

       

       

      From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
      Sent: 10 May 2012 18:47
      To: psftdba@yahoogroups.com
      Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

       




      It's not.  Andy already mentioned PeopleSoft row level security.

      That's where I would start looking.

      Using PeopleSoft you must jump through this security hoop,

      where as straight SQL in toad or SqlPlus does not.

       

      __________________________________________________________
      James C. De Atley

       

      Mobile: (912) 253-0419

      From: "rsalido@..." <rsalido@...>
      To: psftdba@yahoogroups.com
      Sent: Thursday, May 10, 2012 1:43 PM
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

       

      Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

       

      Thanks!

       

      SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

        FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

        WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

           AND A.RECEIVER_ID = B.RECEIVER_ID

           AND C.VENDOR_ID = B.VENDOR_ID

           AND B.BUSINESS_UNIT = 'TMC01'

           AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

           AND A.PO_ID = D.PO_ID

           AND A.LINE_NBR = D.LINE_NBR

           AND A.SCHED_NBR = D.SCHED_NBR

           AND A.RECV_SHIP_STATUS <> 'X'

           AND D.CANCEL_STATUS <> 'X'

           AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

           AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

           AND A.PO_ID = E.PO_ID

           AND F.CATEGORY_ID = A.CATEGORY_ID

           AND F.EFFDT =

              (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

              WHERE F.SETID = F_ED.SETID

                AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                AND F_ED.EFFDT <= SYSDATE)

           AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

           AND A.PO_ID = G.PO_ID

           AND D.LINE_NBR = G.LINE_NBR)

           AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

           AND E.PO_ID = H.PO_ID

           AND H.COMMENT_CD IN ('RPT','CNT')

        GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

        ORDER BY 11, 15, 16, 17;

      Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


      From: Ashish Bhatt <ashish_bhatt@...>
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
      To: psftdba@yahoogroups.com, rsalido@...
      Date: Thursday, May 10, 2012, 11:25 AM

       

      Please copy paste the SQL in the mail.

      Thanks in advance.

      To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

      Hi,

         We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

          Can you think of anything that could be affecting the number of rows Query is returning?

       

      Thank you very much,

      Roberto.Follow me on twitter: @rsalido




    Your message has been successfully submitted and would be delivered to recipients shortly.