-- Creates a report (as a text file) of various properties of all blocks and items in a form. -- Use as is, or paste in an Excel sheet PROCEDURE Form_list_items (p_tabular_format IN BOOLEAN := FALSE) -- Parameter p_tabular_format: -- * FALSE for headers between block info; -- * TRUE for no headers between, for use of the output in Excel-sheets etc. IS /* This utility is meant for development and QA, certainly NOT for use in production !!! See below for further comments */ -- © Original code by Jan-Hendrik van Heusden, Transfer Solutions, Leerdam, Netherlands -- Using / altering of this code is allowed; please keep the above line in your text, -- and specify any changes below /* CHANGE HISTORY: WHEN (dd-mm-yyyy) WHO WHAT ----------------- ------------ ----------------------------------------------- 01-06-2003 JHvHeusden Initial creation for Forms 5, 6, 6i; See below for use in Forms 4.5 */ /* Forms Versions || Use in Forms 5, 6, 6i. For use in 4.5, a few properties are renamed (DISPLAYED || instead of VISIBLE || || Purpose || Creates a text file (use as is, or paste in Excel) with a report of all blocks || and items in a form. || May be useful especially: || * in complicated forms || * for quality checks, i.e. check format masks on numbers, dates etc. || || Use: || Add the following statement to your WHEN-NEW-FORM-INSTANCE trigger (create one if || necessary): || Form_list_items(FALSE); -- report with headers and info for each block || or || Form_list_items(TRUE); -- tabular output, ie if output is pasted in Excel || || Run your form, and get your report from the file system. || || TAKE CARE: --> Once you got your report, remove are comment out the added line!! || || Again, the utility is meant for development and QA, certainly NOT for use in || production !!! || || Changes: || * Change output format || * Items may be added; variables have to be added for the values and the column width || Data length and format string are candidates. || * Pathname is hardcoded; change if necessary. || || Robustness: || Not much care is given to exception handling of file IO, since the utility should || be used by developers, not in production. || If any file IO errors occur, check path and filename, and check your OS-privileges || on the specified path || *************************************************************************************/ -- Hardcoded path + name of output-file; alter if necessary l_temp_filename VARCHAR2(255) := 'C:\temp\'||:SYSTEM.current_form||'_fmb_blk_items.txt'; l_tabular BOOLEAN := NVL(p_tabular_format, FALSE) ; -- Constants below specify the column width for the various columns -- The values are pretty small (maybe too small in certain cases), but -- in most cases the output is more readable with not too large values -- Ajust the values if necessary. -- If you want to find out which width you need for a certain property, use -- the "l_width_..."-variabeles (BELOW); uncomment the code that writes this -- information to file -- Ajusts the widths below, according to the found values lc_width_block_name CONSTANT INTEGER := 18; lc_width_data_target_type CONSTANT INTEGER := 7; lc_width_data_target_name CONSTANT INTEGER := 20; lc_width_item_name CONSTANT INTEGER := 32; lc_width_col_name CONSTANT INTEGER := 44; lc_width_visible CONSTANT INTEGER := 7; lc_width_item_type CONSTANT INTEGER := 14; lc_width_fmt_mask CONSTANT INTEGER := 22; lc_width_datatype CONSTANT INTEGER := 10; lc_width_canvas CONSTANT INTEGER := 24; lc_width_prompt CONSTANT INTEGER := 28; lc_width_hint_text CONSTANT INTEGER := 6; -- This latter one for the header only, not for the hint itself. -- Always use the rightmost column for this property so that it does not -- ruin the layout of your report -- Block variables l_first_block VARCHAR2(100) := get_form_property(:SYSTEM.current_form, first_block); l_last_block VARCHAR2(100) := get_form_property(:SYSTEM.current_form, last_block); l_cur_block VARCHAR2(100); l_is_last_block BOOLEAN := FALSE; -- Item variable l_first_item VARCHAR2(100); l_last_item VARCHAR2(100); l_cur_item VARCHAR2(100); l_is_last_item BOOLEAN := FALSE; -- variabele for Text_IO l_tempfile text_io.file_type; -- Counters l_block_count INTEGER := 0; l_items_in_block INTEGER := 0; l_item_count INTEGER := 0; -- Variables for block-properties l_data_target_type VARCHAR2(100); l_data_target_name VARCHAR2(100); -- Variables for item-properties l_col_name VARCHAR2(100); l_visible VARCHAR2(30); l_item_type VARCHAR2(30); l_datatype VARCHAR2(30); l_fmt_mask VARCHAR2(100); l_canvas VARCHAR2(100); l_prompt VARCHAR2(200); l_hint_text VARCHAR2(2000); /* */ -- Enable (uncomment) code below to find column widths (max. field lengths) l_width_block_name INTEGER := 0; l_width_data_target_type INTEGER := 0; l_width_data_target_name INTEGER := 0; l_width_item_name INTEGER := 0; l_width_col_name INTEGER := 0; l_width_fmt_mask INTEGER := 0; l_width_visible INTEGER := 0; l_width_item_type INTEGER := 0; l_width_datatype INTEGER := 0; l_width_canvas INTEGER := 0; l_width_prompt INTEGER := 0; l_width_hint_text INTEGER := 0; /* */ l_str VARCHAR2(2000); l_items_hdr_text VARCHAR2(2000) := RPAD('Block' , lc_width_block_name) ||RPAD('Item' , lc_width_item_name ) ||RPAD('Disp.?' , lc_width_visible ) ||RPAD('Item type' , lc_width_item_type ) ||RPAD('Column' , lc_width_col_name ) ||RPAD('Datatype' , lc_width_datatype ) ||RPAD('Format Msk' , lc_width_fmt_mask ) ||RPAD('Canvas' , lc_width_canvas ) ||RPAD('Prompt' , lc_width_prompt ) ||RPAD('Hint' , lc_width_hint_text ) ; l_items_hdr_2nd VARCHAR2(2000) := RPAD('*', lc_width_block_name-1, '*')||' ' ||RPAD('*', lc_width_item_name -1, '*')||' ' ||RPAD('*', lc_width_visible -1, '*')||' ' ||RPAD('*', lc_width_item_type -1, '*')||' ' ||RPAD('*', lc_width_col_name -1, '*')||' ' ||RPAD('*', lc_width_datatype -1, '*')||' ' ||RPAD('*', lc_width_fmt_mask -1, '*')||' ' ||RPAD('*', lc_width_canvas -1, '*')||' ' ||RPAD('*', lc_width_prompt -1, '*')||' ' ||RPAD('*', lc_width_hint_text -1, '*')||' ' ; BEGIN message('Writing item info...', no_acknowledge); synchronize; l_tempfile := text_io.fopen(l_temp_filename, 'W'); -- For output in table format (ie Excel) than only 1 report header IF l_tabular THEN -- Write header for item info l_str := l_items_hdr_text; text_io.put_line(l_tempfile, l_str); l_str := l_items_hdr_2nd; text_io.put_line(l_tempfile, l_str); END IF; -- Loop through blocks l_cur_block := l_first_block; WHILE NOT l_is_last_block LOOP l_block_count := l_block_count + 1; l_items_in_block := 0; -- Get type, name and datasource of the block (NONE, TABLE, STORED PROCEDURE, ...) l_data_target_type := get_block_property(l_cur_block, dml_data_target_type); IF l_data_target_type = 'TABLE' -- NAME of datatarget with tables only THEN l_data_target_name := get_block_property(l_cur_block, dml_data_target_name); ELSE l_data_target_name := ''; END IF; /**/ -- Get property lengths of block properties l_width_block_name := GREATEST(l_width_block_name , NVL(LENGTH(l_cur_block) , 0)); l_width_data_target_type := GREATEST(l_width_data_target_type, NVL(LENGTH(l_data_target_type), 0)); l_width_data_target_name := GREATEST(l_width_data_target_name, NVL(LENGTH(l_data_target_name), 0)); /**/ -- If not table format, than header and block info for each block IF NOT l_tabular THEN -- Header + block info schrijven text_io.put_line(l_tempfile, '***************************************************'); text_io.put_line(l_tempfile, 'Block: '||l_cur_block); -- Info m.b.t. data-target van het block schrijven text_io.put_line( l_tempfile , ' Data target: '||l_data_target_type||' ' ||l_data_target_name ); text_io.put_line(l_tempfile, '***************************************************'); -- Write Header for items l_str := l_items_hdr_text; text_io.put_line(l_tempfile, l_str); l_str := l_items_hdr_2nd; text_io.put_line(l_tempfile, l_str); END IF; -- Get first and last items l_first_item := get_block_property(l_cur_block, first_item); l_last_item := get_block_property(l_cur_block, last_item); -- Loop through the items l_is_last_block := ( NVL(l_cur_block, l_last_block) = l_last_block ); l_cur_item := l_first_item; l_is_last_item := FALSE; WHILE NOT l_is_last_item LOOP message('Block: '||l_cur_block||' Item: '||l_cur_item, no_acknowledge); synchronize; l_items_in_block := l_items_in_block + 1; l_item_count := l_item_count + 1; l_is_last_item := ( NVL(l_cur_item, l_last_item) = l_last_item ); -- Displayed / non displayed? Format output l_visible := REPLACE(REPLACE(get_item_property(l_cur_block||'.'||l_cur_item, visible) , 'TRUE', 'Disp. ' ) , 'FALSE', ' ' ); -- Get other item properties l_item_type := get_item_property(l_cur_block||'.'||l_cur_item, item_type); IF l_item_type IN ('LIST', 'TEXT ITEM', 'DISPLAY ITEM', 'RADIO GROUP', 'CHECKBOX') THEN IF l_item_type = 'TEXT ITEM' THEN l_fmt_mask := get_item_property(l_cur_block||'.'||l_cur_item, format_mask); ELSE l_fmt_mask := ''; END IF; l_datatype := get_item_property(l_cur_block||'.'||l_cur_item, datatype); IF NVL(l_data_target_type, 'NONE') = 'TABLE' THEN -- Get tablename.columnname in l_col_name l_col_name := l_data_target_name||'.'||get_item_property(l_cur_block||'.'||l_cur_item, column_name); -- If l_col_name is empty, then the value ends with a period "."; get rid of it IF SUBSTR(l_col_name, LENGTH(l_col_name)) = '.' THEN l_col_name := SUBSTR(l_col_name, 1, LENGTH(l_col_name)-1); END IF; END IF; ELSE l_datatype := ''; l_col_name := ''; END IF; l_canvas := get_item_property(l_cur_block||'.'||l_cur_item, item_canvas); l_prompt := get_item_property(l_cur_block||'.'||l_cur_item, prompt_text); l_hint_text := get_item_property(l_cur_block||'.'||l_cur_item, hint_text); -- Format output string item info l_str := TRANSLATE( RPAD(l_cur_block , lc_width_block_name) ||RPAD(l_cur_item , lc_width_item_name ) ||RPAD(NVL(l_visible , ' '), lc_width_visible ) ||RPAD(NVL(l_item_type, ' '), lc_width_item_type ) ||RPAD(NVL(l_col_name , ' '), lc_width_col_name ) ||RPAD(NVL(l_datatype , ' '), lc_width_datatype ) ||RPAD(NVL(l_fmt_mask , ' '), lc_width_fmt_mask ) ||RPAD(NVL(l_canvas , ' '), lc_width_canvas ) ||RPAD(NVL(l_prompt , ' '), lc_width_prompt ) ||l_hint_text , CHR(10)||CHR(13) , ' ' ) ; -- Write to file text_io.put_line(l_tempfile, l_str); -- Get next item l_cur_item := get_item_property(l_cur_block||'.'||l_cur_item, nextitem); /* */ -- Get item property lengths l_width_item_name := GREATEST(l_width_item_name, NVL(LENGTH(l_cur_item) , 0)); l_width_col_name := GREATEST(l_width_col_name , NVL(LENGTH(l_col_name) , 0)); l_width_visible := GREATEST(l_width_visible , NVL(LENGTH(l_visible) , 0)); l_width_item_type := GREATEST(l_width_item_type, NVL(LENGTH(l_item_type) , 0)); l_width_datatype := GREATEST(l_width_datatype , NVL(LENGTH(l_datatype) , 0)); l_width_fmt_mask := GREATEST(l_width_fmt_mask , NVL(LENGTH(l_fmt_mask) , 0)); l_width_canvas := GREATEST(l_width_canvas , NVL(LENGTH(l_canvas) , 0)); l_width_prompt := GREATEST(l_width_prompt , NVL(LENGTH(l_prompt) , 0)); l_width_hint_text := GREATEST(l_width_hint_text, NVL(LENGTH(l_hint_text) , 0)); /* */ END LOOP; -- End of item loop -- If not tabular, write block info IF NOT l_tabular THEN -- Item count within block text_io.new_line(l_tempfile); text_io.put_line(l_tempfile, 'Block '||l_cur_block||': '||l_items_in_block||' items'); text_io.new_line(l_tempfile); text_io.new_line(l_tempfile); END IF; text_io.new_line(l_tempfile); -- Get next block l_cur_block := get_block_property(l_cur_block, nextblock); END LOOP; IF NOT l_tabular THEN -- Write footer (counts of blocks, items) text_io.new_line(l_tempfile); text_io.new_line(l_tempfile); text_io.new_line(l_tempfile); text_io.put_line(l_tempfile, '***************************************'); text_io.put_line(l_tempfile, 'END OF REPORT'); text_io.put_line(l_tempfile, 'Blocks: '||l_block_count||' Items: '||l_item_count); text_io.put_line(l_tempfile, '***************************************'); text_io.new_line(l_tempfile); END IF; /* */ --/* -- Enable (uncomment) code below to output max lengths of item property values -- Display length of block properties text_io.put_line(l_tempfile,'Max. length of block property values:'); text_io.put_line(l_tempfile,'------------------------------------ '); text_io.put_line(l_tempfile,'l_width_block_name '||l_width_block_name ); text_io.put_line(l_tempfile,'l_width_data_target_type '||l_width_data_target_type); text_io.put_line(l_tempfile,'l_width_data_target_name '||l_width_data_target_name); -- Display length of item properties text_io.new_line(l_tempfile); text_io.put_line(l_tempfile,'Max. length of item property values:'); text_io.put_line(l_tempfile,'----------------------------------- '); text_io.put_line(l_tempfile,'l_width_item_name '||l_width_item_name ); text_io.put_line(l_tempfile,'l_width_col_name '||l_width_col_name ); text_io.put_line(l_tempfile,'l_width_visible '||l_width_visible ); text_io.put_line(l_tempfile,'l_width_item_type '||l_width_item_type ); text_io.put_line(l_tempfile,'l_width_datatype '||l_width_datatype ); text_io.put_line(l_tempfile,'l_width_fmt_mask '||l_width_fmt_mask ); text_io.put_line(l_tempfile,'l_width_canvas '||l_width_canvas ); text_io.put_line(l_tempfile,'l_width_prompt '||l_width_prompt ); text_io.put_line(l_tempfile,'l_width_hint_text '||l_width_hint_text ); --*/ /* */ text_io.fclose(l_tempfile); -- Message: we 're ready message('Item info written to file '||l_temp_filename); message('Item info written to file '||l_temp_filename, no_acknowledge); synchronize; EXCEPTION WHEN OTHERS THEN -- If necessary, close file IF text_io.is_open(l_tempfile) THEN text_io.fclose(l_tempfile); END IF; -- Show error in "alert" message( 'Error in execution of procedure "Form_list_items" (Form: ' ||:SYSTEM.current_form||' Block: '||l_cur_block||' Item: '||l_cur_item||')' ); message( 'Error in execution of procedure "Form_list_items" (Form: ' ||:SYSTEM.current_form||' Block: '||l_cur_block||' Item: '||l_cur_item||')' , no_acknowledge ); synchronize; -- re-raise RAISE; END;