sql - How can I pass dynamic cursor into temporary table? -
my temporary table is-
begin stmt := create local temporary table fmo_app.dynamicsql(engine_family_code varchar2(30 byte), contract_name varchar2(200 byte) not null enable, contract_seq_id number(9, 0) not null enable, usage_month varchar2(10 byte), invoice_num varchar2(14 byte) not null, invoice_amt number(15, 2), invoice_billing_date date, created_date date, billing_invoice_type varchar2(255 byte), efh number(11, 3), ec number(9, 0), cancelled_invoice_num varchar2(14 byte), restored_esn varchar2(12 byte), payment_term_text varchar2(60 byte), recon_invoice_num varchar2(1 byte), recon_period varchar2(1 byte), payment_due_date date, contract_code varchar2(4 byte) not null enable, product_line_code varchar2(20 byte) not null enable, payment_status varchar2(50 byte), rn number, count number) on commit delete rows; execute immediate stmt; insert fmo_app.dynamicsql (cur_result); --is correct ? end;
the dynamic cursor -
open cur v_sql; loop fetch cur cur_result; exit when cur%notfound; --v_sql dynamic query. --cur ref_cur. --cur_result dynamicsql%rowtype;
i need use temporary table store values cursor , use later update table.
below complete code.
begin usage_month_parameters := usage_month_array(); open cur v_sql; loop fetch cur cur_result; exit when cur%notfound; begin stmt := create local temporary table fmo_app.dynamicsql(engine_family_code varchar2(30 byte), contract_name varchar2(200 byte) not null enable, contract_seq_id number(9, 0) not null enable, usage_month varchar2(10 byte), invoice_num varchar2(14 byte) not null, invoice_amt number(15, 2), invoice_billing_date date, created_date date, billing_invoice_type varchar2(255 byte), efh number(11, 3), ec number(9, 0), cancelled_invoice_num varchar2(14 byte), restored_esn varchar2(12 byte), payment_term_text varchar2(60 byte), recon_invoice_num varchar2(1 byte), recon_period varchar2(1 byte), payment_due_date date, contract_code varchar2(4 byte) not null enable, product_line_code varchar2(20 byte) not null enable, payment_status varchar2(50 byte), rn number, count number) on commit delete rows; execute immediate stmt; insert fmo_app.dynamicsql (cur_result); end; begin select fi.billing_invoice_type, mi.tag_type v_inv_type, v_tag_type fmo_op2_invoice fi, fmo_op2_manual_invoice_items mi fi.invoice_num = mi.invoice_num; if upper(v_inv_type) = 'm' , upper(v_tag_type) = 'p' select count(distinct mi.item_date) item_date_count fmo_op2_manual_invoice_items mi mi.invoice_num = cur_result.invoice_num; if item_date_count = 1 select distinct mi.item_date v_item_date fmo_op2_manual_invoice_items mi mi.invoice_num = cur_result.invoice_num; select to_char(v_item_date, 'yyyy - mm - dd') var_usage_month dual; elsif item_date_count > 1 var_usage_month := to_char('multiple'); else var_usage_month := to_char(cur_result.usage_month, 'yyyy - mm - dd'); end if; begin v_usage_month_arr.extend; v_usage_month_arr(var_num) := usage_month_value_obj(cur_result.engine_family_code, cur_result.contract_name, cur_result.contract_seq_id, var_usage_month, cur_result.invoice_num, cur_result.invoice_amt, cur_result.invoice_billing_date, cur_result.created_date, cur_result.billing_invoice_type, cur_result.efh, cur_result.ec, cur_result.cancelled_invoice_num, cur_result.restored_esn, cur_result.payment_term_text, cur_result.recon_invoice_num, cur_result.recon_period, cur_result.payment_due_date, cur_result.contract_code, cur_result.product_line_code, cur_result.payment_status ); end; else var_usage_month := to_char(cur_result.usage_month, 'yyyy - mm - dd'); end if; end; end loop; close cur; open p_out_contract_data select cast(v_usage_month_arr fmo_op2_manual_table_type) dual; commit;
your understanding of temporary tables in oracle wrong. need create once, outside of stored code (procedures, functions, packages):
create temporary table my_temporary_table (...) on commit delete rows;
after that, can use everywhere need. fill data use insert ... select
statement:
procedure my_proc (...) ... begin insert my_temporary_table (...) select ... source_table; end;
table cleared after commit.
Comments
Post a Comment