Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: Nested exceptions

    ---Compare data for specific advertiser and sku in source tables and destinaction table
    CREATE OR REPLACE
    PROCEDURE d_skuverify (p_advertiser IN NUMBER, p_sku IN VARCHAR2)
    IS

    v_test_name VARCHAR2(50) := 'D_SKUVERIFY'|| p_advertiser || p_sku;
    v_err VARCHAR2(500);

    v_adv d_sku.adv%type;
    v_sku d_sku.sku%type;
    v_item_name d_sku.item_name%type;
    v_catalog_id d_sku.catalog%type;
    v_catalog_name d_sku.catalog_name%type;
    v_category d_sku.category%type;
    v_skuitemname d_sku.item_name%type;
    v_skucatalog d_sku.catalog%type;
    v_skucatalogname d_sku.catalog_name%type;
    v_skucategory d_sku.category%type;

    v_started DATE := SYSDATE;

    BEGIN

    v_test_name := 'D_SKUVERIFY'|| p_advertiser || p_sku;

    SELECT item_name, catalog, catalog_name, category
    INTO v_skuitemname, v_skucatalog, v_skucatalogname, v_skucategory
    FROM d_sku
    WHERE adv = p_advertiser
    AND sku = p_sku;

    SELECT --+ PARALLEL(p,4) PARALLEL(ad,4)

    DISTINCT

    p.advertiser AS adv,

    p.sku AS sku,

    p.NAME AS item_name,

    p.ad AS catalog,

    p.advertisercategory AS category,

    ad.name as catalog_name

    INTO v_adv, v_sku, v_item_name, v_catalog_id, v_category,v_catalog_name

    FROM product@pc_source p, d_link ad

    WHERE p.ad = ad.id

    AND p.advertiser = p_advertiser

    AND p.sku = p_sku;


    assertEquals(v_test_name,'item_name',sysdate,v_ite m_name,v_skuitemname);
    assertEquals(v_test_name,'catalog_id',sysdate,v_ca talog_id,v_skucatalog);
    assertEquals(v_test_name,'catalog_name',sysdate,v_ catalog_name,v_skucatalogname);


    EXCEPTION
    WHEN TOO_MANY_ROWS THEN

    BEGIN

    SELECT --+ PARALLEL(p,4) PARALLEL(ad,4)
    DISTINCT
    p.advertiser AS adv, p.sku AS sku,
    p.NAME AS item_name, p.ad AS catalog,
    p.advertisercategory AS category,
    ad.name as catalog_name
    INTO v_adv, v_sku, v_item_name, v_catalog_id, v_category,v_catalog_name
    FROM product@pc_source p, d_link ad
    WHERE p.ad = ad.id
    AND p.advertiser =p_advertiser
    AND p.sku = p_sku
    AND p.ad = (SELECT MAX(ad) FROM product@pc_source WHERE advertiser = p_advertiser AND sku =p_sku);

    assertEquals(v_test_name,'item_name',sysdate,v_ite m_name,v_skuitemname);

    END;



    WHEN NO_DATA_FOUND THEN

    BEGIN


    SELECT --+ PARALLEL(item,4) PARALLEL(itemlist,4)
    DISTINCT
    advertiser_id AS adv,
    item_identifier AS sku,
    item.NAME AS item_name,
    itemlist.id AS catalog,
    itemlist.NAME AS catalog_name,
    'Not Found' AS category
    INTO v_adv, v_sku, v_item_name, v_catalog_id, v_catalog_name, v_category
    FROM item@cjo_source item, itemlist@cjo_source itemlist
    WHERE item.itemlist_id = itemlist.id (+)
    AND item_identifier = p_sku
    AND advertiser_id = p_advertiser;

    assertEquals(v_test_name,'item_name',sysdate,v_ite m_name,v_skuitemname);
    assertEquals(v_test_name,'catalog_id',sysdate,v_ca talog_id,v_skucatalog);
    assertEquals(v_test_name,'catalog_name',sysdate,v_ catalog_name,v_skucatalogname);


    WHEN OTHERS THEN
    v_err := SUBSTR(SQLCODE || ' - ' || SQLERRM,1,500);

    INSERT INTO dw_tests VALUES( SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL, SYSTIMESTAMP, 'N',v_err);

    COMMIT;

    RAISE;

    END;

    WHEN OTHERS THEN
    v_err := SQLCODE || ' - ' || SQLERRM;

    INSERT INTO dw_tests VALUES( SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL, SYSTIMESTAMP, 'N',v_err);

    COMMIT;

    RAISE;

    END;
    /


    SQL> show errors
    Errors for PROCEDURE D_SKUVERIFY:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    110/9 PLS-00103: Encountered the symbol "WHEN" when expecting one of
    the following:
    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << close current delete fetch lock insert
    open rollback savepoint set sql execute commit forall merge
    <a single-quoted SQL string> pipe
    <an alternatively-quoted SQL string>
    The symbol "exception" was substituted for "WHEN" to con



    Does anybody know how to fix this code?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It would be more obvious if you wrote it structured (or, perhaps you did, but didn't use the CODE tags?). Never mind; there are two errors: you miss the END keyword in line 121 (of my formatted procedure) and the last EXCEPTION handler shouldn't be here at all:
    Code:
    /* Formatted on 05/09/2006 22:25 (Formatter Plus v4.8.0) */
    
    
    CREATE OR REPLACE PROCEDURE d_skuverify (
       p_advertiser   IN   NUMBER,
       p_sku          IN   VARCHAR2
    )
    IS
       v_test_name        VARCHAR2 (50) := 'D_SKUVERIFY' || p_advertiser || p_sku;
       v_err              VARCHAR2 (500);
       v_adv              d_sku.adv%TYPE;
       v_sku              d_sku.sku%TYPE;
       v_item_name        d_sku.item_name%TYPE;
       v_catalog_id       d_sku.catalog%TYPE;
       v_catalog_name     d_sku.catalog_name%TYPE;
       v_category         d_sku.CATEGORY%TYPE;
       v_skuitemname      d_sku.item_name%TYPE;
       v_skucatalog       d_sku.catalog%TYPE;
       v_skucatalogname   d_sku.catalog_name%TYPE;
       v_skucategory      d_sku.CATEGORY%TYPE;
       v_started          DATE                      := SYSDATE;
    BEGIN
       v_test_name := 'D_SKUVERIFY' || p_advertiser || p_sku;
    
       SELECT item_name, catalog, catalog_name, CATEGORY
         INTO v_skuitemname, v_skucatalog, v_skucatalogname, v_skucategory
         FROM d_sku
        WHERE adv = p_advertiser AND sku = p_sku;
    
       SELECT          --+ PARALLEL(P,4) PARALLEL(AD,4)
              DISTINCT p.advertiser AS adv, p.sku AS sku, p.NAME AS item_name,
                       p.ad AS catalog, p.advertisercategory AS CATEGORY,
                       ad.NAME AS catalog_name
                  INTO v_adv, v_sku, v_item_name,
                       v_catalog_id, v_category,
                       v_catalog_name
                  FROM product@pc_source p, d_link ad
                 WHERE p.ad = ad.ID
                   AND p.advertiser = p_advertiser
                   AND p.sku = p_sku;
    
       assertequals (v_test_name, 'item_name', SYSDATE, v_item_name,
                     v_skuitemname);
       assertequals (v_test_name,
                     'catalog_id',
                     SYSDATE,
                     v_catalog_id,
                     v_skucatalog
                    );
       assertequals (v_test_name,
                     'catalog_name',
                     SYSDATE,
                     v_catalog_name,
                     v_skucatalogname
                    );
    EXCEPTION
       WHEN TOO_MANY_ROWS
       THEN
          BEGIN
             SELECT          --+ PARALLEL(P,4) PARALLEL(AD,4)
                    DISTINCT p.advertiser AS adv, p.sku AS sku,
                             p.NAME AS item_name, p.ad AS catalog,
                             p.advertisercategory AS CATEGORY,
                             ad.NAME AS catalog_name
                        INTO v_adv, v_sku,
                             v_item_name, v_catalog_id,
                             v_category,
                             v_catalog_name
                        FROM product@pc_source p, d_link ad
                       WHERE p.ad = ad.ID
                         AND p.advertiser = p_advertiser
                         AND p.sku = p_sku
                         AND p.ad =
                                (SELECT MAX (ad)
                                   FROM product@pc_source
                                  WHERE advertiser = p_advertiser AND sku = p_sku);
    
             assertequals (v_test_name,
                           'item_name',
                           SYSDATE,
                           v_item_name,
                           v_skuitemname
                          );
          END;
       WHEN NO_DATA_FOUND
       THEN
          BEGIN
             SELECT          --+ PARALLEL(ITEM,4) PARALLEL(ITEMLIST,4)
                    DISTINCT advertiser_id AS adv, item_identifier AS sku,
                             item.NAME AS item_name, itemlist.ID AS catalog,
                             itemlist.NAME AS catalog_name,
                             'Not Found' AS CATEGORY
                        INTO v_adv, v_sku,
                             v_item_name, v_catalog_id,
                             v_catalog_name,
                             v_category
                        FROM item@cjo_source item,
                             itemlist@cjo_source itemlist
                       WHERE item.itemlist_id = itemlist.ID(+)
                         AND item_identifier = p_sku
                         AND advertiser_id = p_advertiser;
    
             assertequals (v_test_name,
                           'item_name',
                           SYSDATE,
                           v_item_name,
                           v_skuitemname
                          );
             assertequals (v_test_name,
                           'catalog_id',
                           SYSDATE,
                           v_catalog_id,
                           v_skucatalog
                          );
             assertequals (v_test_name,
                           'catalog_name',
                           SYSDATE,
                           v_catalog_name,
                           v_skucatalogname
                          );
          END;                                                -- YOU MISS THIS END
       WHEN OTHERS
       THEN
          v_err := SUBSTR (SQLCODE || ' - ' || SQLERRM, 1, 500);
    
          INSERT INTO dw_tests
               VALUES (SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL,
                       SYSTIMESTAMP, 'N', v_err);
    
          COMMIT;
          RAISE;
    END;
    /* THE CODE BELOW SHOULD BE REMOVED
    EXCEPTION
    
       WHEN OTHERS THEN
          v_err := SQLCODE || ' - ' || SQLERRM;
    
          INSERT INTO dw_tests VALUES( SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL, SYSTIMESTAMP, 'N',v_err);
    
          COMMIT;
    
          RAISE;
    
    END;
    */
    /

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, did you, perhaps, have this in mind? You miss the EXCEPTION keyword in line 121, while END in 132nd line should be moved to line 143:
    Code:
    /* Formatted on 05/09/2006 22:30 (Formatter Plus v4.8.0) */
    
    
    CREATE OR REPLACE PROCEDURE d_skuverify (
       p_advertiser   IN   NUMBER,
       p_sku          IN   VARCHAR2
    )
    IS
       v_test_name        VARCHAR2 (50) := 'D_SKUVERIFY' || p_advertiser || p_sku;
       v_err              VARCHAR2 (500);
       v_adv              d_sku.adv%TYPE;
       v_sku              d_sku.sku%TYPE;
       v_item_name        d_sku.item_name%TYPE;
       v_catalog_id       d_sku.catalog%TYPE;
       v_catalog_name     d_sku.catalog_name%TYPE;
       v_category         d_sku.CATEGORY%TYPE;
       v_skuitemname      d_sku.item_name%TYPE;
       v_skucatalog       d_sku.catalog%TYPE;
       v_skucatalogname   d_sku.catalog_name%TYPE;
       v_skucategory      d_sku.CATEGORY%TYPE;
       v_started          DATE                      := SYSDATE;
    BEGIN
       v_test_name := 'D_SKUVERIFY' || p_advertiser || p_sku;
    
       SELECT item_name, catalog, catalog_name, CATEGORY
         INTO v_skuitemname, v_skucatalog, v_skucatalogname, v_skucategory
         FROM d_sku
        WHERE adv = p_advertiser AND sku = p_sku;
    
       SELECT          --+ PARALLEL(P,4) PARALLEL(AD,4)
              DISTINCT p.advertiser AS adv, p.sku AS sku, p.NAME AS item_name,
                       p.ad AS catalog, p.advertisercategory AS CATEGORY,
                       ad.NAME AS catalog_name
                  INTO v_adv, v_sku, v_item_name,
                       v_catalog_id, v_category,
                       v_catalog_name
                  FROM product@pc_source p, d_link ad
                 WHERE p.ad = ad.ID
                   AND p.advertiser = p_advertiser
                   AND p.sku = p_sku;
    
       assertequals (v_test_name, 'item_name', SYSDATE, v_item_name,
                     v_skuitemname);
       assertequals (v_test_name,
                     'catalog_id',
                     SYSDATE,
                     v_catalog_id,
                     v_skucatalog
                    );
       assertequals (v_test_name,
                     'catalog_name',
                     SYSDATE,
                     v_catalog_name,
                     v_skucatalogname
                    );
    EXCEPTION
       WHEN TOO_MANY_ROWS
       THEN
          BEGIN
             SELECT          --+ PARALLEL(P,4) PARALLEL(AD,4)
                    DISTINCT p.advertiser AS adv, p.sku AS sku,
                             p.NAME AS item_name, p.ad AS catalog,
                             p.advertisercategory AS CATEGORY,
                             ad.NAME AS catalog_name
                        INTO v_adv, v_sku,
                             v_item_name, v_catalog_id,
                             v_category,
                             v_catalog_name
                        FROM product@pc_source p, d_link ad
                       WHERE p.ad = ad.ID
                         AND p.advertiser = p_advertiser
                         AND p.sku = p_sku
                         AND p.ad =
                                (SELECT MAX (ad)
                                   FROM product@pc_source
                                  WHERE advertiser = p_advertiser AND sku = p_sku);
    
             assertequals (v_test_name,
                           'item_name',
                           SYSDATE,
                           v_item_name,
                           v_skuitemname
                          );
          END;
       WHEN NO_DATA_FOUND
       THEN
          BEGIN
             SELECT          --+ PARALLEL(ITEM,4) PARALLEL(ITEMLIST,4)
                    DISTINCT advertiser_id AS adv, item_identifier AS sku,
                             item.NAME AS item_name, itemlist.ID AS catalog,
                             itemlist.NAME AS catalog_name,
                             'Not Found' AS CATEGORY
                        INTO v_adv, v_sku,
                             v_item_name, v_catalog_id,
                             v_catalog_name,
                             v_category
                        FROM item@cjo_source item,
                             itemlist@cjo_source itemlist
                       WHERE item.itemlist_id = itemlist.ID(+)
                         AND item_identifier = p_sku
                         AND advertiser_id = p_advertiser;
    
             assertequals (v_test_name,
                           'item_name',
                           SYSDATE,
                           v_item_name,
                           v_skuitemname
                          );
             assertequals (v_test_name,
                           'catalog_id',
                           SYSDATE,
                           v_catalog_id,
                           v_skucatalog
                          );
             assertequals (v_test_name,
                           'catalog_name',
                           SYSDATE,
                           v_catalog_name,
                           v_skucatalogname
                          );
          EXCEPTION                                     -- you miss this EXCEPTION
             WHEN OTHERS
             THEN
                v_err := SUBSTR (SQLCODE || ' - ' || SQLERRM, 1, 500);
    
                INSERT INTO dw_tests
                     VALUES (SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL,
                             SYSTIMESTAMP, 'N', v_err);
    
                COMMIT;
                RAISE;
    -- END;                                             -- this END should be removed
             WHEN OTHERS
             THEN
                v_err := SQLCODE || ' - ' || SQLERRM;
    
                INSERT INTO dw_tests
                     VALUES (SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL,
                             SYSTIMESTAMP, 'N', v_err);
    
                COMMIT;
                RAISE;
          END;                                           -- this END should be added
    END;
    /

  4. #4
    Join Date
    Jul 2003
    Posts
    15
    Thank you very much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •