Problems debugging a cursor.

[rmchung]

So I've been trying to tackle this procedure and I just don't see where the error is coming from. The error I get is "Numeric or value error: character to number conversion error". I'm a SQL Server guy and this was originally in SQL Server, but this other project I'm on is having me convert this stuff to Oracle. I've gotten pretty far so far, but this one has got me baffled.

CREATE OR REPLACE PROCEDURE TSAR116RepTransThreshold
is
v_TestNum varchar2(10):='116';
v_ThreshDateMin varchar2(20):='01-JAN-2004';
v_ThreshDateMax varchar2(20):='31-MAR-2005';
v_ThreshRollDays number:=7;
v_ThreshAmt number:=9000;
v_ThreshPCT number:=0.10;
v_sql varchar2(4000);
v_commitCount integer(4):=0;
type ref_cursor is ref cursor;
cur_tables ref_cursor;
v_curr_acctID dda_trans.acct_id%type;
v_curr_transDT dda_trans.trans_dt%type;
v_curr_transAmt dda_trans.trans_amt%type;
v_curr_transID dda_trans.trans_id%type;
v_start_acctID dda_trans.acct_id%type;
v_start_transDT dda_trans.trans_dt%type;
v_start_transAmt dda_trans.trans_amt%type;
v_start_transID dda_trans.trans_id%type;
v_counter integer(4):=0;

BEGIN

v_sql := 'SELECT acct_id, trans_dt, trans_amt, trans_id ' ||
'FROM dda_trans ' ||
'WHERE trans_amt between ' || v_ThreshAmt - (v_ThreshPCT * v_ThreshAmt) || ' AND ' ||
v_ThreshAmt + (v_ThreshPCT * v_ThreshAmt) || ' ' ||
'AND exclude is NULL ' ||
'AND trans_dt between ''' || v_ThreshDateMin ||
''' AND ''' || v_ThreshDateMax || '' ||
'ORDER BY acct_id, trans_dt';

open cur_tables for v_sql;

fetch cur_tables into v_start_acctID, v_start_transDT, v_start_transAmt, v_start_transID;

IF cur_tables%FOUND then
fetch cur_tables into v_curr_acctID, v_curr_transDT, v_curr_transAmt, v_curr_transID;
else
close cur_tables;
end if;

v_Counter:= 0;

LOOP

EXIT WHEN CUR_TABLES%NOTFOUND;

IF v_start_acctID = v_curr_acctID AND (v_curr_transDT - v_start_transDT <= v_ThreshRollDays) THEN
execute immediate
'INSERT INTO AnalysisTransResults(Test_ID, DateRun, Acct_ID, Trans_Amt, Trans_ID)
VALUES(v_TestNum, SYSDATE, v_Curr_AcctID, v_Curr_TransAmt, v_Curr_TransID)';

v_Counter:=v_Counter + 1;
-- end if
ELSE

IF v_Counter > 0 THEN
execute immediate
'INSERT INTO AnalysisTransResults(Test_ID, DateRun, Acct_ID, Trans_Amt, Trans_ID)
VALUES(v_TestNum, SYSDATE, v_Start_AcctID, v_Start_TransAmt, v_Start_TransID)';
v_Counter:= 0;
END IF;

IF cur_tables%NOTFOUND THEN
fetch cur_tables into v_start_acctID, v_start_transDT, v_start_transAmt, v_start_transID;
ELSE
CLOSE cur_tables;
END IF;

IF cur_tables%NOTFOUND THEN
fetch cur_tables into v_curr_acctID, v_curr_transDT, v_curr_transAmt, v_curr_transID;
ELSE
CLOSE cur_tables;
END IF;

END IF; -- end else

fetch cur_tables into v_curr_acctID, v_curr_transDT, v_curr_transAmt, v_curr_transID;

END LOOP;

close cur_tables;
END SAR116RepTransThreshold;
/

 

[BJCooperIT]

Start by making your dates date formats instead of character strings :

  v_ThreshDateMin date := 
    to_char('01-JAN-2004','DD-MON-YYYY');
  v_ThreshDateMax date := 
    to_char('31-MAR-2005','DD-MON-YYYY');

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Thargy]

Two things,

can you post the create table statements which you use for the underlying tables, and a couple of lines of sanitised (dummy) data, so that I can avoid irrelevant compilation errors.

Second, in the section

IF v_start_acctID = v_curr_acctID AND (v_curr_transDT - v_start_transDT <= v_ThreshRollDays) THEN
     execute immediate
    'INSERT INTO AnalysisTransResults(Test_ID, DateRun, Acct_ID, Trans_Amt, Trans_ID)
    VALUES(v_TestNum, SYSDATE, v_Curr_AcctID, v_Curr_TransAmt, v_Curr_TransID)';

     v_Counter:=v_Counter + 1;
     -- end if

you use execute immediate on a string. This isn't needed.
Just state

IF v_start_acctID = v_curr_acctID AND (v_curr_transDT - v_start_transDT <= v_ThreshRollDays) THEN
  INSERT INTO AnalysisTransResults(Test_ID, DateRun, Acct_ID, Trans_Amt, Trans_ID)
    VALUES(v_TestNum, SYSDATE, v_Curr_AcctID, v_Curr_TransAmt, v_Curr_TransID);

     v_Counter:=v_Counter + 1;
     -- end if

Oracle's very good at using sql within pl/sql you see

Regards

Tharg

Grinding away at things Oracular