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

    Unanswered: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    I am getting the following error message when I try to execute procedure bellow. Does anybody know how to fix the issue?

    Thanks,

    Katya

    SQL> execute d_skuverify(1803223, '15X173');
    BEGIN d_skuverify(1803223, '15X173'); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "CJORPT.D_SKUVERIFY", line 85
    ORA-01403: no data found
    ORA-06512: at line 1


    Here is the procedure

    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 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);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    -- If the corresponding information was not found, then compare inbound
    -- variables to NULL.
    assertEquals(v_test_name,'item_name',sysdate,'not found',v_skuitemname);
    assertEquals(v_test_name,'catalog_id',sysdate,0,v_ skucatalog);
    assertEquals(v_test_name,'catalog_name',sysdate, 'not found', 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;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does anybody know how to fix the issue?
    Do not try to assign a non-numeric character to a NUMBER field.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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