This discussion is archived
6 Replies Latest reply: May 14, 2012 8:06 AM by user16854 RSS

PL/SQL Error in workflow

user16854 Newbie
Currently Being Moderated
Sorry - I have also posted a similar question to this in the Workflow form:
Workflow Customisation Error

But wanted to ask here too because this is related to PL/SQL too, so I'm not sure where the query best sits.

I have a customised workflow, and it is falling over here:
-- ####
  l_progress := '15 Got This Far';
  IF (l_po_wf_debug = 'Y') THEN
     PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
  END IF;
-- ####
 
          -- get attribute value
          -- cannot use GetItemAttrText because if a PO 
          -- is sent for approval twice, it will generate 2 different item_keys
         SELECT DISTINCT wiav.text_value
                    INTO l_x_contract
                FROM apps.wf_item_attribute_values wiav
                   , apps.wf_item_attributes wia
                   , apps.wf_items wi
               WHERE wiav.item_type = wia.item_type
                 AND wi.item_type = wiav.item_type
                 AND wi.item_key = wiav.item_key
                 AND wiav.NAME = wia.NAME
                 AND wiav.text_value IS NOT NULL
                 AND wiav.item_type = 'POAPPRV'
                 AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                 AND wi.user_key = TO_CHAR(l_this_doc_num);
 
-- ####
  l_progress := '16 Got This Far';
  IF (l_po_wf_debug = 'Y') THEN
     PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
  END IF;
-- ####
When I check the po_wf_debug table, I can see there the '15 Got This Far' line is showing, but it's breaking before it gets to the next '16 Got This Far' section.

I'm assuming the problem is with the SQL statement I've used to try to set the l_x_contract variable.

I have tried to hard code values instead, to see if that avoids the error - e.g.
SELECT DISTINCT '50079161' -- wiav.text_value
                    INTO l_x_contract
                FROM apps.wf_item_attribute_values wiav
                   , apps.wf_item_attributes wia
                   , apps.wf_items wi
               WHERE wiav.item_type = wia.item_type
                 AND wi.item_type = wiav.item_type
                 AND wi.item_key = wiav.item_key
                 AND wiav.NAME = wia.NAME
                 AND wiav.text_value IS NOT NULL
                 AND wiav.item_type = 'POAPPRV'
                 AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                 -- AND wi.user_key = TO_CHAR(l_this_doc_num)
                 AND wi.user_key = '50079161';
But it's still erroring just the same.

No doubt I am making huge and stupid mistakes, but any pointers would be much appreciated.

Thanks
  • 1. Re: PL/SQL Error in workflow
    indra budiantho Expert
    Currently Being Moderated
    Use exception:
    Exception
    When others then DBMS_output.put_line(sqlerrm);
  • 2. Re: PL/SQL Error in workflow
    Hoek Guru
    Currently Being Moderated
    That's really a bad advice. OP shouldn't be swallowing exceptions...

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537#153527300346542578
  • 3. Re: PL/SQL Error in workflow
    Hoek Guru
    Currently Being Moderated
    Please post the full error message you're getting.
  • 4. Re: PL/SQL Error in workflow
    user16854 Newbie
    Currently Being Moderated
    Hi,

    Sorry for the slow reply. I have been doing some more testing.

    Thanks for your answers so far.

    The error is trapped via dealing with exceptions.

    e.g. extract of that bit from the package:
    ....
    
    EXCEPTION
      WHEN OTHERS THEN
      
    -- ##################
      l_progress := 'X_TEST.MyWork: OTHERS: ' || sqlerrm;
      IF (l_po_wf_debug = 'Y') THEN
         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
      END IF;
    -- ##################
    For an error example, the debug output is:
    X_TEST.MyWork: OTHERS: ORA-01403: no data found
    So presumably my problem is that this bit of code:
    SELECT DISTINCT wiav.text_value
                        INTO l_xccc_contract
                    FROM apps.wf_item_attribute_values wiav
                       , apps.wf_item_attributes wia
                       , apps.wf_items wi
                   WHERE wiav.item_type = wia.item_type
                     AND wi.item_type = wiav.item_type
                     AND wi.item_key = wiav.item_key
                     AND wiav.NAME = wia.NAME
                     AND wiav.text_value IS NOT NULL
                     AND wiav.item_type = 'POAPPRV'
                     AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                     AND wi.user_key = TO_CHAR(l_this_doc_num);
    i.e. when it returns nothing, the code errors.

    I tried an NVL, via:
    SELECT DISTINCT NVL(wiav.text_value,'test')
                        INTO l_xccc_contract
                    FROM apps.wf_item_attribute_values wiav
                       , apps.wf_item_attributes wia
                       , apps.wf_items wi
                   WHERE wiav.item_type = wia.item_type
                     AND wi.item_type = wiav.item_type
                     AND wi.item_key = wiav.item_key
                     AND wiav.NAME = wia.NAME
                     AND wiav.text_value IS NOT NULL
                     AND wiav.item_type = 'POAPPRV'
                     AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                     AND wi.user_key = TO_CHAR(l_this_doc_num);
    But that returns nothing either (i.e. it says "no rows returned" in TOAD instead of returning 'test') - e.g. if I run it direct in TOAD:
    SELECT DISTINCT NVL(wiav.text_value,'test')
    --           INTO l_xccc_contract
               FROM apps.wf_item_attribute_values wiav
                  , apps.wf_item_attributes wia
                  , apps.wf_items wi
              WHERE wiav.item_type = wia.item_type
                AND wi.item_type = wiav.item_type
                AND wi.item_key = wiav.item_key
                AND wiav.NAME = wia.NAME
                AND wiav.text_value IS NOT NULL
                AND wiav.item_type = 'POAPPRV'
                AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                AND wi.user_key = '1111';
    At the risk of invoking the wrath of others viewing this, and I realise I am being lazy and useless here, but is there any way I can get an output from this, or even set 'l_xccc_contract' to a set value if the SQL returns nothing?

    Sorry for asking, but I am v. stuck.

    Any advice much appreciated.

    Thanks
  • 5. Re: PL/SQL Error in workflow
    BluShadow Guru Moderator
    Currently Being Moderated
    If you're expecting a NO_DATA_FOUND exception you can wrap your SQL in another BEGIN... EXCEPTION... END; block to specifically capture that exception and handle it.

    {thread:id=697262}
  • 6. Re: PL/SQL Error in workflow
    user16854 Newbie
    Currently Being Moderated
    Thanks very much for your advice.

    Using that advice, I have changed this:
             SELECT DISTINCT wiav.text_value
                    INTO l_x_contract
                FROM apps.wf_item_attribute_values wiav
                   , apps.wf_item_attributes wia
                   , apps.wf_items wi
                   , po.po_headers_all pha
                   WHERE wiav.item_type = wia.item_type
                 AND wi.item_type = wiav.item_type
                 AND wi.item_key = wiav.item_key
                 AND wiav.NAME = wia.NAME
                 AND wi.user_key = pha.segment1
                 AND wiav.text_value IS NOT NULL
                 AND wiav.item_type = 'POAPPRV'
                 AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                 AND wi.user_key = TO_CHAR(l_this_doc_num);
    To this:
    DECLARE
        l_x_contract VARCHAR2(100);
    BEGIN
    SELECT DISTINCT wiav.text_value
           INTO l_x_contract
       FROM apps.wf_item_attribute_values wiav
          , apps.wf_item_attributes wia
          , apps.wf_items wi
          , po.po_headers_all pha
          WHERE wiav.item_type = wia.item_type
        AND wi.item_type = wiav.item_type
        AND wi.item_key = wiav.item_key
        AND wiav.NAME = wia.NAME
        AND wi.user_key = pha.segment1
        AND wiav.text_value IS NOT NULL
        AND wiav.item_type = 'POAPPRV'
        AND wia.NAME = 'X_CONTRACT_IN_PLACE'
        AND wi.user_key = TO_CHAR(l_this_doc_num);
    EXCEPTION
     WHEN no_data_found THEN     
         l_x_contract := 'OLD';
         INSERT INTO XX.XTMP(l_id, l_name, l_value) VALUES (APPS.XJRSEQ.NEXTVAL, 'l_x_contract no_data_found', l_x_contract);
         COMMIT;
    END;
    
    IF l_x_contract = 'N' THEN
         l_supplier_flagged := FALSE;
    END IF;
    
    IF l_x_contract = 'Y' THEN
         l_supplier_flagged := TRUE;             
    END IF;
    
    IF l_x_contract = 'OLD' THEN
         l_supplier_flagged := l_supplier_flagged;                  
    END IF;
    
    INSERT INTO XX.XTMP(l_id, l_name, l_value) VALUES (APPS.XJRSEQ.NEXTVAL, 'l_x_contract end', l_x_contract);
    COMMIT;
    That now avoids the error without a problem, so thank you for your advice.

    However, when I do a SELECT FROM XX.XTMP*, I see:
    l_id  | l_name                       | l_value
    -----------------------------------------------
    1     | l_x_contract no_data_found   | OLD
    2     | l_x_contract END             | 
    i.e. it knows it's OLD to start with, but by the end it doesn't know what it is any more, so the if statement looking for 'l_x_contract' = 'OLD' does not fire.

    Does that fact that l_x_contract's value is set inside a BEGIN block mean the code outside of that does not know the value of l_x_contract?

    Any advice much appreciated.

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points