how to get values from report corresponding to check boxes which are checked in oracle apex -
after getting report sql commands in apex, want store data of variables sid,serial# corresponding check boxes checked. after have execute alter system kill session 'sid,serial#' in apex itself
hi there i'll show approach used skillbuilders on this tutorial, based on collections
the approach consist on 4 steps, idea make report on collection rather set of tables. i'll make description of steps suggest see video on link above specific details on code.
first need create collection (and named it) information want show on report, make string query , bind variables may (or may not) need. here's template collection, keep in mind template needs adjusted scenario. in case interest items #47 , #50, status field (47) , check box field(50).
declare l_collection_name apex_collections.collection_name%type; l_bind_names apex_application_global.vc_arr2; l_bind_values apex_application_global.vc_arr2; l_query varchar2(32767); begin l_collection_name := 'replace_name'; if apex_collection.collection_exists(l_collection_name) apex_collection.delete_collection( p_collection_name => l_collection_name ); end if; --query initialize collection --do not reorder columns later break values in tabular form (reorder in report) l_query := 'select col_1, ' --c001 => f01 || ' col_2, ' --c002 => f02 || ' col_3, ' --c003 => f03 || ' col_4, ' --c004 => f04 || ' col_5, ' --c005 => f05 || ' null, ' --c006 => f06 || ' null, ' --c007 => f07 || ' null, ' --c008 => f08 || ' null, ' --c009 => f09 || ' null, ' --c010 => f10 || ' null, ' --c011 => f11 || ' null, ' --c012 => f12 || ' null, ' --c013 => f13 || ' null, ' --c014 => f14 || ' null, ' --c015 => f15 || ' null, ' --c016 => f16 || ' null, ' --c017 => f17 || ' null, ' --c018 => f18 || ' null, ' --c019 => f19 || ' null, ' --c020 => f20 || ' null, ' --c021 => f21 || ' null, ' --c022 => f22 || ' null, ' --c023 => f23 || ' null, ' --c024 => f24 || ' null, ' --c025 => f25 || ' null, ' --c026 => f26 || ' null, ' --c027 => f27 || ' null, ' --c028 => f28 || ' null, ' --c029 => f29 || ' null, ' --c030 => f30 || ' null, ' --c031 => f31 || ' null, ' --c032 => f32 || ' null, ' --c033 => f33 || ' null, ' --c034 => f34 || ' null, ' --c035 => f35 || ' null, ' --c036 => f36 || ' null, ' --c037 => f37 || ' null, ' --c038 => f38 || ' null, ' --c039 => f39 || ' null, ' --c040 => f40 || ' null, ' --c041 => f41 || ' null, ' --c042 => f42 || ' null, ' --c043 => f43 || ' null, ' --c044 => f44 || ' null, ' --c045 => f45 || ' null, ' --c046 => f46 || ' ''o'', ' --c047 (for record status) || ' wwv_flow_item.md5(col_1, col_2, col_3, col_4, col_5) ' --c048 (for optimistic locking) --c049 (not used in collection/reserevered seq_id array) --c050 (not used in collection/reservered delete checkbox array) || 'from some_table ' || 'where some_col = :pxx_item_name'; l_bind_names(1) := 'pxx_item_name'; l_bind_values(1) := v('pxx_item_name'); apex_collection.create_collection_from_query_b( p_collection_name => l_collection_name, p_query => l_query, p_names => l_bind_names, p_values => l_bind_values ); if :request = 'add' apex_collection.add_member( p_collection_name => l_collection_name ); end if; end; step two once collection has been created can make report of querying directly collection, , using apex_item package you'll make fields proper items, notice fifth item checkbox linked 50th field on collection. once again, code below it's example:
select apex_item.hidden(47,c047,null,'f47_'|| '#rownum#') || apex_item.hidden(48,c048,null,'f48_'|| '#rownum#') || apex_item.hidden(49,seq_id,null,'f49_'|| '#rownum#') || apex_item.hidden(1,c001,null,'f01_'|| '#rownum#') || apex_item.checkbox( 50, seq_id, null, case when c047 = 'd' seq_id end, ':', 'f50_' || '#rownum#' ) delete_checkbox, apex_item.text( 2, c002, 20, 50, null, 'f02_' || '#rownum#' ) col_2, apex_item.text( 3, c003, 20, 50, null, 'f03_' || '#rownum#' ) col_3, apex_item.select_list_from_lov_xl( 4, nvl(c004, '-1'), 'named_lov', null, 'yes', '-1', '- select value -', 'f04_' || '#rownum#' ) col_4, apex_item.select_list_from_lov( 5, nvl(c005, '-1'), 'named_lov', null, 'yes', '-1', '- select value -', 'f05_' || '#rownum#' ) col_5 apex_collections collection_name = 'replace_name' order c003 step three so, have collection displaying items modified in page, will changes posted collection?...nope, need update collection first, have code, here it's dealing updates, row creation , row deletion.
declare l_collection_name apex_collections.collection_name%type; l_original_md5 varchar2(32); l_latest_md5 varchar2(32); begin l_collection_name := 'replace_name'; x in 1 .. apex_application.g_f49.count loop if apex_application.g_f01(x) not null --id exists, check see if record updated select c048 l_original_md5 apex_collections collection_name = l_collection_name , seq_id = apex_application.g_f49(x); l_latest_md5 := wwv_flow_item.md5( apex_application.g_f01(x), apex_application.g_f02(x), apex_application.g_f03(x), apex_application.g_f04(x), apex_application.g_f05(x) ); if l_original_md5 != l_latest_md5 apex_collection.update_member( p_collection_name => l_collection_name, p_seq => apex_application.g_f49(x), p_c001 => apex_application.g_f01(x), p_c002 => apex_application.g_f02(x), p_c003 => apex_application.g_f03(x), p_c004 => apex_application.g_f04(x), p_c005 => apex_application.g_f05(x), p_c047 => 'u', p_c048 => apex_application.g_f48(x) ); end if; else --id not exist, must new record apex_collection.update_member( p_collection_name => l_collection_name, p_seq => apex_application.g_f49(x), p_c001 => apex_application.g_f01(x), p_c002 => apex_application.g_f02(x), p_c003 => apex_application.g_f03(x), p_c004 => apex_application.g_f04(x), p_c005 => apex_application.g_f05(x), p_c047 => 'n', p_c048 => apex_application.g_f48(x) ); end if; end loop; if :request = 'delete' x in 1 .. apex_application.g_f50.count loop apex_collection.update_member_attribute( p_collection_name => l_collection_name, p_seq => apex_application.g_f50(x), p_attr_number => '47', p_attr_value => 'd' ); end loop; end if; end; finally step four here update table/s based on changes made collection. using 47th , 50th fields know ones should updated, deleted, or created:
declare l_table_md5 varchar2(32); l_collection_name apex_collections.collection_name%type; l_del_count pls_integer := 0; l_upd_count pls_integer := 0; l_ins_count pls_integer := 0; l_success_message varchar2(32767); cursor op_lock_check_cur ( p_id in number ) select wwv_flow_item.md5(col_1, col_2, col_3, col_4, col_5) some_table col_1 = op_lock_check_cur.p_id update; begin l_collection_name := 'replace_name'; x in ( select * apex_collections collection_name = l_collection_name , c047 in ('n','u','d') ) loop if x.c047 = 'n' insert some_table( col_1, col_2, col_3, col_4, col_5 ) values ( some_table_seq.nextval, x.c002, x.c003, x.c004, x.c005 ); l_ins_count := l_ins_count + 1; elsif x.c047 = 'u' --optimistic lock check --md5 should identical collection initialization proc open op_lock_check_cur(x.c001); fetch op_lock_check_cur l_table_md5; -- compare checksum values , if different, raise error. if l_table_md5 != x.c048 raise_application_error( -20001, 'current version of data in database has changed ' || 'since user initiated update process.' ); end if; update some_table set col_2 = x.c002, col_3 = x.c003, col_4 = x.c004, col_5 = x.c005 current of op_lock_check_cur; close op_lock_check_cur; l_upd_count := l_upd_count + 1; elsif x.c047 = 'd' delete some_table col_1 = x.c001; l_del_count := l_del_count + 1; end if; end loop; apex_collection.delete_collection(p_collection_name => l_collection_name); l_success_message := l_ins_count || ' rows inserted, ' || l_upd_count || ' rows updated, ' || l_del_count || ' rows deleted'; :px_success_message := l_success_message; end; that all, once again tutorial tip toe, there's lot of info can't post in here.
disclaimer: i'm not skillbuilders representative, don't own rights on video , i'm not promoting skillbuilders web page in way. i'm programmer found tutorial useful.
Comments
Post a Comment