Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: im getting divisor is equal to zero for the below proc

    create or replace PROCEDURE proc (in_run_id number default null) IS
    v_run_id NUMBER := in_run_id;
    v_sql varchar2(4000);
    v_sqlpt1 varchar2(4000);
    v_sqlpt2 VARCHAR2(4000);
    v_sqlpt3 varchar2(4000);
    v_sqlpt4 varchar2(4000);
    v_sqlpt5 varchar2(4000);
    type formulatx is table of ds_fios_bus_measures.formula_tx%type;
    v_formula_array formulatx;
    TYPE busmeacd IS TABLE OF ds_fios_base_bus_measures.bus_measure_cd%type;
    v_busmeacd_array busmeacd;
    type buskeyid is table of ds_fios_base_bus_measures.bus_measure_key_id%type;
    v_basebuskeyid_array buskeyid;
    v_buskeyid_array buskeyid;
    TYPE fmbuscd is table of ds_fios_bus_measures.bus_measure_cd%type;
    v_subfmcd fmbuscd;
    TYPE time_op is table of ds_fios_bus_measures.TIME_OP%type;
    v_timeop_array time_op;
    l_time_op ds_fios_bus_measures.TIME_OP%type;
    v_subfm formulatx;
    err_code varchar2(20);
    err_msg varchar2(400);
    BEGIN


    -- Inserts run status into u_run_status table
    util.ins_run_status('fios_bus_measures.proc_formul a','tmp_fios_fm_measures' ,null, 'Inprocess', v_run_id, 'Executing fios_bus_measures.proc_formula');


    v_sql := 'TRUNCATE TABLE tmp_fios_fm_measures';
    execute immediate v_sql;

    SELECT bus_measure_key_id,formula_tx, time_op BULK COLLECT INTO v_buskeyid_array,v_formula_array, v_timeop_array from ds_fios_bus_measures;

    -- For each formula, find the amounts of corresponding business measures and insert them as records into f_fios_bus_measures
    FOR fm in 1..v_buskeyid_array.count
    loop

    -- If the formula uses another formula, recursively expand it to lowest level
    v_formula_array(fm) := formula_expander(v_formula_array(fm));
    l_time_op := NVL(v_timeop_array(fm),'0');
    -- Find the corresponding intersections
    SELECT DISTINCT bus_measure_key_id, bus_measure_cd
    BULK COLLECT INTO v_basebuskeyid_array,v_busmeacd_array
    FROM
    ds_fios_base_bus_measures
    WHERE instr(v_formula_array(fm),'['||bus_measure_cd||']')> 0;

    v_sqlpt1 := v_formula_array(fm);
    v_sqlpt4 := v_formula_array(fm);
    v_sqlpt5 := v_formula_array(fm);
    v_sqlpt2 := '';
    v_sqlpt3 := '';
    FOR i in 1..v_basebuskeyid_array.count
    loop
    -- Get matching amounts
    v_sqlpt2 := v_sqlpt2 || 'decode(fact_key_id ,'||v_basebuskeyid_array(i)||',currentmonth ,0) as amt_'||v_basebuskeyid_array(i)||',
    decode(fact_key_id ,'||v_basebuskeyid_array(i)||',qtd ,0) as qtd_'||v_basebuskeyid_array(i)||',
    decode(fact_key_id ,'||v_basebuskeyid_array(i)||',ytd ,0) as ytd_'||v_basebuskeyid_array(i)||',';

    -- Get matching base business_measure_key_id
    v_sqlpt3 := v_sqlpt3 || v_basebuskeyid_array(i) ||', ';

    -- Replace base bus measures in formula with amounts
    if (instr(v_formula_array(fm),v_busmeacd_array(i)) > 0) then
    v_sqlpt1 := replace(v_sqlpt1,v_busmeacd_array(i),'sum(amt_'||v _basebuskeyid_array(i)||')');
    v_sqlpt4 := replace(v_sqlpt4,v_busmeacd_array(i),'sum(qtd_'||v _basebuskeyid_array(i)||')');
    v_sqlpt5 := replace(v_sqlpt5,v_busmeacd_array(i),'sum(ytd_'||v _basebuskeyid_array(i)||')');
    else
    v_sqlpt1 := replace(v_sqlpt1,v_busmeacd_array(i),'0');
    v_sqlpt4 := replace(v_sqlpt4,v_busmeacd_array(i),'0');
    v_sqlpt5 := replace(v_sqlpt5,v_busmeacd_array(i),'0');
    end if;

    end loop;

    --dbms_output.put_line (v_sqlpt1);
    --dbms_output.put_line (v_sqlpt4);
    --dbms_output.put_line (v_sqlpt5);

    -- Insert the formula records into f_fios_bus_measures
    v_sqlpt1 := replace(replace(v_sqlpt1,'[','('),']',')');
    v_sqlpt4 := replace(replace(v_sqlpt4,'[','('),']',')');
    v_sqlpt5 := replace(replace(v_sqlpt5,'[','('),']',')');
    v_sql := 'INSERT INTO tmp_fios_fm_measures(fact_key_id,period_id, scenario_key_id,company_key_id,currentmonth,qtd,yt d,time_op)'||
    'SELECT '||v_buskeyid_array(fm)||',period_id,scenario_key_ id, company_key_id, '||v_sqlpt1|| ',' ||v_sqlpt4||',' ||v_sqlpt5|| ',''' || l_time_op || '''
    FROM (SELECT '|| rtrim(trim(v_sqlpt2),',') ||',scenario_key_id,period_id,company_key_id FROM f_fios_bus_measures WHERE fact_key_id in ('|| rtrim(trim(v_sqlpt3),',')||')) GROUP BY period_id,scenario_key_id,company_key_id';


    EXECUTE IMMEDIATE v_sql;


    end loop;


    UPDATE u_run_status
    SET status = 'Completed', end_time = SYSDATE
    WHERE run_id = v_run_id;

    commit;
    EXCEPTION -- exception handlers begin


    WHEN OTHERS THEN -- handles all other errors
    ROLLBACK;

    err_code := sqlcode;
    err_msg := substr(SQLERRM, 1, 400);
    update u_run_status
    set status = 'ERROR',
    message = err_code ||':' ||err_msg,
    end_time = SYSDATE
    where run_id = v_run_id;

    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
    since we don't have your tables or data, we can't run, test, debug or improve your code.

    >im getting divisor is equal to zero for the below proc
    which line throws the error?
    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.

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    Quote Originally Posted by anacedent View Post
    since we don't have your tables or data, we can't run, test, debug or improve your code.

    >im getting divisor is equal to zero for the below proc
    which line throws the error?
    when i try to debug it the i guess the error may be happineing at the execute immediate.

    oracle shows the error at line 1 and at Riase statement in the excpetion block

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    for debugging remove, delete & eliminate EXCEPTION handler so you can see error line #
    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.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Also post what the sql(i.e. v_sql) that gets executed is:
    Code:
    v_sql := 'INSERT INTO tmp_fios_fm_measures(fact_key_id,period_id,  scenario_key_id,company_key_id,currentmonth,qtd,yt  d,time_op)'||
                     'SELECT  '||v_buskeyid_array(fm)||',period_id,scenario_key_  id, company_key_id,  '||v_sqlpt1|| ',' ||v_sqlpt4||',' ||v_sqlpt5|| ',''' || l_time_op || '''
                      FROM (SELECT '|| rtrim(trim(v_sqlpt2),',')  ||',scenario_key_id,period_id,company_key_id FROM f_fios_bus_measures  WHERE fact_key_id in ('|| rtrim(trim(v_sqlpt3),',')||')) GROUP BY  period_id,scenario_key_id,company_key_id';
    
    EXECUTE IMMEDIATE v_sql;

Posting Permissions

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