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

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -