02-17-06, 20:49 #1Registered User
- Join Date
- Jul 2003
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, :
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
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)
cursor cat1_query is
SELECT ad_id, adv_company_id, pub_company_id,
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
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,
open cat1_query; ---(p_advertiser, p_publisher, p_start_date , p_end_date );
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;
if sql%notfound then raise no_data_found;
Does anybody know how to fix the problem?
02-17-06, 21:23 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
Here is a free clue.
The following are NOT dates
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.