| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Need Help from Dynamic Sql Gurus
Good Morning,
We have a large table that we keep trimmed by archiving to a second table. Basically, twice a week, I move all records that were finalized 90 days ago or more. The archive table started getting huge, so I created twelve tables (1 for each month), and wrote the package below, which includes dynamic sql, to handle the archiving. The package takes all records that have been finalized 90 days or more and copies them to the archive table for the month that each record was initiated. Everything works fine except for the values of some date columns in the receiving table. The tables consist of 30 columns, 4 of which are date fields (in order): call_date (date/time record initiated), proc_flag (date/time record began processing), rept_flag (date/time record finalized processing), comm_cycle (always null). For some reason, the first 2 date fields always get assigned the value of the third date field. For example, if a record has the following values in the main table - call_date of '13-MAY-99 12:00:00', proc_flag of '15-AUG-99 00:00:01', and rept_flag of '17-AUG-99 13:00:00' - when it gets to the archive table, all three of these date fields will be '17-AUG-99 13:00:00'. All other columns, which consist of varchar2, number, integer, and char, are all correct. From my troubleshooting, I suspect the problem is happening during the dbms_sql.bind_array process. I have checked the value of my 'place holder' arrays after dbms_sql.column_value executes, and after dbms_sql.bind_array executes, and it is always correct. Unfortunately, I am not sure how to check the value of my bind array variables as they are assigned. Finally, there are on errors in any of the trace or log files, and we are running Oracle 8.0.5.1.0 for NT on Dec Alpha. Below is the body from my package. I appreciate any help anyone can give me. Thanks in advance.
Joe Long
AS
PROCEDURE move_recs (tab_name IN varchar2, dt_month IN varchar2) IS
cursor_sel integer;
cursor_ins integer;
sel_ret_rows number;
ins_ret_rows number;
fetch_ret_rows number;
sismas_tab dbms_sql.number_table;
rasite_tab dbms_sql.number_table;
racust_tab dbms_sql.number_table;
ani_tab dbms_sql.varchar2_table;
raosta_tab dbms_sql.varchar2_table;
call_date_tab dbms_sql.date_table;
radur_tab dbms_sql.number_table;
tni_tab dbms_sql.varchar2_table;
rastat_tab dbms_sql.varchar2_table;
bni_tab dbms_sql.varchar2_table;
rarev_tab dbms_sql.number_table;
comm_dstrb_tab dbms_sql.number_table;
comm_sbscrbr_tab dbms_sql.number_table;
rasich_tab dbms_sql.number_table;
ratmch_tab dbms_sql.varchar2_table;
rameth_tab dbms_sql.varchar2_table;
raseq_tab dbms_sql.varchar2_table;
ratypo_tab dbms_sql.varchar2_table;
ratp6_tab dbms_sql.varchar2_table;
raclso_tab dbms_sql.varchar2_table;
racond_tab dbms_sql.varchar2_table;
rarge_tab dbms_sql.varchar2_table;
rip_rap_tab dbms_sql.number_table;
rept_flag_tab dbms_sql.date_table;
bill_flag_tab dbms_sql.varchar2_table;
proc_flag_tab dbms_sql.date_table;
comm_cycle_tab dbms_sql.date_table;
raduro_tab dbms_sql.number_table;
cti_flat_fee_tab dbms_sql.number_table;
comm_rev_tab dbms_sql.number_table;
indx number := 1;
indx_ctr integer;
BEGIN
cursor_sel := dbms_sql.open_cursor;
dbms_sql.parse(cursor_sel, ' SELECT * from co_comm' ||
' WHERE trunc(rept_flag) <=
trunc(sysdate - 90)' ||
' AND to_char(trunc(call_date), ''mon'')
= :dt_month', dbms_sql.native);
dbms_sql.bind_variable(cursor_sel, ':dt_month', dt_month);
dbms_sql.define_array(cursor_sel, 1, sismas_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 2, rasite_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 3, racust_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 4, ani_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 5, raosta_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 6, call_date_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 7, radur_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 8, tni_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 9, rastat_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 10, bni_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 11, rarev_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 12, comm_dstrb_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 13, comm_sbscrbr_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 14, rasich_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 15, ratmch_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 16, rameth_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 17, raseq_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 18, ratypo_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 19, ratp6_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 20, raclso_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 21, racond_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 22, rarge_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 23, rip_rap_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 24, rept_flag_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 25, bill_flag_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 26, proc_flag_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 27, comm_cycle_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 28, raduro_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 29, cti_flat_fee_tab, 1000, indx);
dbms_sql.define_array(cursor_sel, 30, comm_rev_tab, 1000, indx);
sel_ret_rows := dbms_sql.execute(cursor_sel);
loop
fetch_ret_rows := dbms_sql.fetch_rows(cursor_sel);
indx_ctr := dbms_sql.last_row_count;
dbms_sql.column_value(cursor_sel, 1, sismas_tab);
dbms_sql.column_value(cursor_sel, 2, rasite_tab);
dbms_sql.column_value(cursor_sel, 3, racust_tab);
dbms_sql.column_value(cursor_sel, 4, ani_tab);
dbms_sql.column_value(cursor_sel, 5, raosta_tab);
dbms_sql.column_value(cursor_sel, 6, call_date_tab);
dbms_sql.column_value(cursor_sel, 7, radur_tab);
dbms_sql.column_value(cursor_sel, 8, tni_tab);
dbms_sql.column_value(cursor_sel, 9, rastat_tab);
dbms_sql.column_value(cursor_sel, 10, bni_tab);
dbms_sql.column_value(cursor_sel, 11, rarev_tab);
dbms_sql.column_value(cursor_sel, 12, comm_dstrb_tab);
dbms_sql.column_value(cursor_sel, 13, comm_sbscrbr_tab);
dbms_sql.column_value(cursor_sel, 14, rasich_tab);
dbms_sql.column_value(cursor_sel, 15, ratmch_tab);
dbms_sql.column_value(cursor_sel, 16, rameth_tab);
dbms_sql.column_value(cursor_sel, 17, raseq_tab);
dbms_sql.column_value(cursor_sel, 18, ratypo_tab);
dbms_sql.column_value(cursor_sel, 19, ratp6_tab);
dbms_sql.column_value(cursor_sel, 20, raclso_tab);
dbms_sql.column_value(cursor_sel, 21, racond_tab);
dbms_sql.column_value(cursor_sel, 22, rarge_tab);
dbms_sql.column_value(cursor_sel, 23, rip_rap_tab);
dbms_sql.column_value(cursor_sel, 24, rept_flag_tab);
dbms_sql.column_value(cursor_sel, 25, bill_flag_tab);
dbms_sql.column_value(cursor_sel, 26, proc_flag_tab);
dbms_sql.column_value(cursor_sel, 27, comm_cycle_tab);
dbms_sql.column_value(cursor_sel, 28, raduro_tab);
dbms_sql.column_value(cursor_sel, 29, cti_flat_fee_tab);
dbms_sql.column_value(cursor_sel, 30, comm_rev_tab);
exit when fetch_ret_rows != 1000;
end loop;
cursor_ins := dbms_sql.open_cursor;
dbms_sql.parse(cursor_ins, 'INSERT INTO '||tab_name|| ' VALUES
(:bv_sismas, :bv_rasite, ' ||
'bv_racust, :bv_ani, :bv_raosta, :bv_call_date,
:bv_radur, :bv_tni, ' ||
':bv_rastat, :bv_bni, :bv_rarev, :bv_comm_dstrb,
:bv_comm_sbscrbr, ' ||
':bv_rasich, :bv_ratmch, :bv_rameth, :bv_raseq,
:bv_ratypo, :bv_ratp6, ' ||
':bv_raclso, :bv_racond, :bv_rarge, :bv_rip_rap,
:bv_rept_flag, ' ||
':bv_bill_flag, :bv_proc_flag, :bv_comm_cycle,
:bv_raduro, ' ||
':bv_cti_flat_fee, :bv_comm_rev)', dbms_sql.native);
dbms_sql.bind_array(cursor_ins, ':bv_sismas',
sismas_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rasite',
rasite_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_racust',
racust_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_ani',
ani_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_raosta',
raosta_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_call_date',
call_date_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_radur',
radur_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_tni',
tni_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rastat',
rastat_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_bni',
bni_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rarev',
rarev_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_comm_dstrb',
comm_dstrb_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_comm_sbscrbr',
comm_sbscrbr_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rasich',
rasich_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_ratmch',
ratmch_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rameth',
rameth_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_raseq',
raseq_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_ratypo',
ratypo_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_ratp6',
ratp6_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_raclso',
raclso_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_racond',
racond_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rarge',
rarge_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rip_rap',
rip_rap_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_rept_flag',
rept_flag_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_bill_flag',
bill_flag_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_proc_flag',
proc_flag_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_comm_cycle',
comm_cycle_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_raduro',
raduro_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_cti_flat_fee',
cti_flat_fee_tab, 1, indx_ctr);
dbms_sql.bind_array(cursor_ins, ':bv_comm_rev',
comm_rev_tab, 1, indx_ctr);
ins_ret_rows := dbms_sql.execute(cursor_ins);
dbms_sql.close_cursor(cursor_ins);
dbms_sql.close_cursor(cursor_sel);
commit;
END; PROCEDURE arch_recs IS
CURSOR get_call_dt_month IS
SELECT distinct to_char(trunc(call_date), 'mon')
FROM co_comm
WHERE trunc(rept_flag) <= trunc(sysdate - 90);
call_dt_mon varchar2(3);
table_name varchar2(16);
BEGIN
OPEN get_call_dt_month;
LOOP
BEGIN
FETCH get_call_dt_month into call_dt_mon;
table_name := 'co_comm_arch_'||call_dt_mon;
EXIT when get_call_dt_month%NOTFOUND;
move_recs(table_name, call_dt_mon);
END;
END LOOP;
 |
 |