Results 1 to 2 of 2

Thread: Ora-01858

  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Question Unanswered: Ora-01858

    Getting the following error when I try to call procedure:

    SQL> execute data_valid (807, 512, '07-jul-05', '08-jul-05', :P_PUBLISHER_COMMISSION, :P_ADVERTISER
    _COMMISSION, :P_ADVERTISER_SALE_AMOUNT, :P_PUBLISHER_SALE_AMOUNT, :P_SALES_TOTAL, :P_CLICKS_TOTAL, :
    P_LEADS_TOTAL);
    BEGIN data_valid (807, 512, '07-jul-05', '08-jul-05', :P_PUBLISHER_COMMISSION, :P_ADVERTISER_COMMISS

    *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "CJ.DATA_VALID", line 23
    ORA-06512: at "CJ.DATA_VALID", line 54
    ORA-06512: at line 1


    Here is the code:

    ---To find uncorrected number of impressions, clicks, sales , and leads data for advertiser and publisher in cjo_action_txn table for specific event date

    spool data_validation.txt

    SET SERVEROUTPUT ON SIZE 1000000;

    set verify off

    create or replace procedure data_valid
    (p_advertiser IN cjo_action_txn.adv_company_id%type,
    p_publisher IN cjo_action_txn.pub_company_id%type,
    p_start_date IN date,
    p_end_date IN date,
    p_publisher_commission OUT cjo_action_txn.pub_comm_pub_curr%type,
    p_advertiser_commission OUT cjo_action_txn.adv_comm_adv_curr%type,
    p_advertiser_sale_amount OUT cjo_action_txn.sale_amount_adv_curr%type,
    p_publisher_sale_amount OUT cjo_action_txn.sale_amount_pub_curr%type,
    p_sales_total OUT NUMBER,
    p_clicks_total OUT NUMBER,
    p_leads_total OUT NUMBER)


    IS


    --declare


    cursor cat1_query is

    SELECT ad_id, adv_company_id, pub_company_id,
    website_id,

    TRUNC(event_date) trunc_edate,
    SUM(DECODE(actiontrackertype, 'sim_lead', 1, 'item_lead', 1, 0)) AS lead_count,
    sum(DECODE(actiontrackertype, 'sim_sale', 1, 'item_sale', 1, 0)) as sale_count,
    SUM(decode(actiontrackertype, 'click', num_actions, 0)) as click_count,
    (SUM(-adv_comm_adv_curr) - SUM(cj_fee_adv_curr)) as comm_adv_curr,
    SUM(pub_comm_pub_curr) AS comm_pub_curr,
    SUM(cj_fee_adv_curr) AS cj_fee_adv_curr,
    SUM(cj_fee_usd) AS cj_fee_usd,
    SUM(pub_comm_usd) AS comm_pub_usd,
    SUM(sale_amount_adv_curr) AS sale_amount_adv_curr,
    SUM(sale_amount_pub_curr) AS sale_amount_pub_curr
    FROM CJO_ACTION_TXN CJOAT
    WHERE
    cjoat.adv_company_id = p_advertiser
    AND cjoat.pub_company_id = p_publisher
    AND cjoat.event_date >= 'p_start_date'
    and cjoat.event_date < 'p_end_date'
    AND cjoat.adjustmentcode = 0
    AND actiontrackertype NOT IN ('perf_inc','bonus')
    GROUP BY ad_id, adv_company_id, pub_company_id,
    website_id,
    TRUNC(event_date);


    v1_query cat1_query%rowtype;


    BEGIN
    open cat1_query; ---(p_advertiser, p_publisher, p_start_date , p_end_date );


    LOOP

    FETCH cat1_query INTO V1_query;


    p_publisher_commission := v1_query.comm_pub_curr;
    p_advertiser_commission := v1_query.comm_adv_curr;
    p_advertiser_sale_amount := v1_query.sale_amount_adv_curr;
    p_publisher_sale_amount := v1_query.sale_amount_pub_curr;
    p_sales_total := v1_query.sale_count;
    p_clicks_total := v1_query.click_count;
    p_leads_total := v1_query.lead_count;


    EXIT WHEN cat1_query%NOTFOUND;


    END LOOP;



    if sql%notfound then raise no_data_found;

    end if;


    CLOSE cat1_query;

    end data_valid;




    /


    Does anybody know how to fix the problem?

    Thanks,

    Katya

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Here is a free clue.
    The following are NOT dates
    '07-jul-05', '08-jul-05',
    they are text strings.
    You are foolish to rely on default data type conversion.
    Use TO_DATE!
    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
  •