Advanced

1
Advanced Oracle Forms
Scott Hollows
2
Goals

• Learn advanced Oracle Forms techniques
• Scotts freebies
• Build your own
• Tips and Traps

3
Content

• Debuginator
• Dynamic Code
• Loop
• Show Hidden Fields
• Sort
• Query sort everything lookup data
• Export
• Tree Trace

4
Availability and Licence

• Forms Versions
• Forms 6i to 10gr2
• Master version 6i
• Verified 6i to 10gr2
• MS-Windows client-server
• Web Forms
• Source
• Full Source Code
• Creative Commons Licence

5
Debuginator
6
Debuginator

• Show form info
• System Variables
• Application Properties
• Form Properties
• Block Properties
• Item Properties
• Item Value
• Displayed Value
• Stored Value
• Does not show
• Form Parameters
• Global Variables
• Package Variables

7
Debuginator
Search
Search All
Help Text
8
Debuginator

• Design
• Single package
• Easy to Install
• Copy package into menu
• or Copy package to your library
• or Attach the library to your form or menu
• Can be called from
• Menu (no changes to the form)

9
File Architecture

• Menu
• Copy the procedure into your menu, or PLL library
• Add a DEBUG or SUPPORT or ADMIN menu
• Call procedure from the form or menu

PLL Library
PLL Library
Menu
Menu
Form
Form
Form
10
Dynamic Code
11
Dynamic Forms Code

• Name of object is unknown
• Reuse Code
• Issues
• Get / Set
• Name_In
• Copy
• Find Object / Object Id
• Database Dynamic Code out of scope
• EXECUTE IMMEDIATE
• REF CURSORS

12
Consider every possibility

• differnet coordinate system
• inches
• pixels
• pointsColor blindness done use yellow to
  highlight

13
Menu and Library Code

• Code in Menu and Library cant see the form
  directly
• Cant get / set values using
• BLOCK.ITEM_NAME
• PARAMETER.NAME
• SYSTEM.NAME
• Instead use
• NAME_IN get value. returns a string
• COPY- set value

14
Direct Method

• Get Value
• EMP.SALARY X
• PARAMETER.LEVEL X
• SYSTEM.MESSAGE_LEVEL 25
• Set Value
• EMP.SALARY X
• PARAMETER.LEVEL X
• SYSTEM.MESSAGE_LEVEL 25

15
Indirect Method

• Get Value
• NAME_IN (EMP.SALARY)
• NAME_IN ( PARAMETER.LEVEL)
• NAME_IN (SYSTEM.MESSAGE_LEVEL)
• Set Value
• COPY (ABC, EMP.SALARY)
• COPY(ABC, PARAMETER.LEVEL)
• COPY(25, SYSTEM.MESSAGE_LEVEL)

16
Indirect Method

• Within loop code, the block and item name are
  defined in variables
• Get Value
• NAME_IN (v_item_name)
• Set Value
• COPY (ABC, v_item_name)

17
Loop
18
Loop

• Find all objects
• Do something with them
• Pseudocode
• Find first object
• Loop Get next object
• Stop when no more
• End Loop
• Different technique depending on the object

19
Loop Through Objects

• Open Forms
• Blocks
• All blocks
• Block relations
• Child blocks of a specific block
• Items
• Items in a specific block
• Items in the current block
• Every item in all blocks (every item in the form)
• Records in a block

20
More Complex Loops

• Loop Through
• All canvases
• All windows
• Difficult
• no way to get first and next window / canvas
• Achieve by looping through items, copy unique
  canvas and window names into record group
• Cache results in package record group so dont
  have to redo this expensive and slow operation

21
Loop Issues

• Need to process every item may result in errors
• Cant get or set a property that does not apply
  to the item type

Runtime error if item is a button "FRM-41042 no
such property"
IF GET_ITEM_PROPERTY ( v_item_id ,UPDATE_ALLO
WED )THEN ltetcgt
22
Loop Issues

• Need to process every item may result in errors
• Item may not be on a canvascant change visible
  property

Runtime error if item is not on a
canvas "FRM-41042 no such property"
IF SET_ITEM_PROPERTY ( v_item_id ,VISIBLE ,P
ROPERTY_TRUE )THEN ltetcgt
23
Loop Issues

• Solution 1
• ON-ERROR and ON-MESSAGE triggers
• Too messy to code for this situation, dont do
  this

24
Loop Issues

• Solution 2
• Suppress error messagesdoes not suppress many
  loop errors

v_orig_message_level varchar2(10)
SYSTEM.MESSAGE_LEVEL BEGIN --
suppress errors SYSTEM.MESSAGE_LEVEL
25ltyour code heregt -- restore message level
SYSTEM.MESSAGE_LEVEL v_orig_message_level END

25
Loop Issues

• Solution 3
• Conditional code based on item type

IF get_item_property (v_item_id, visible)
'FALSE -- avoid FRM-41014
item is not on a canvas and get_item_property
(v_item_id, canvas_name) is not null THEN
set_item_property (v_item_id, visible,
property_true) set_item_property
(v_item_id, enabled, property_true)
-- avoid "FRM-41042 no such
property" if get_item_property
(v_item_id, item_type) not in (
'BUTTON, 'CHART ITEM, ,'DISPLAY ITEM,
'IMAGE ,'OLE OBJECT,'RADIO
GROUP,,'USER AREA ,'VBX CONTROL' )
then set_item_property (v_item_id, enabled,
property_true)
26
Show Hidden Fields
27
Show Hidden Fields

• Mainly for hidden Foreign Key columns
• Loop through all items in the form
• If hidden, make field visible

28
Show Hidden Fields
Put the hidden field anywhere on the screen
29
Call Procedure From

• Menu
• Copy the procedure into your menu, or PLL library
• Add a DEBUG or SUPPORT or ADMIN menu
• Call procedure from the form or menu

PLL Library
PLL Library
Menu
Menu
Form
Form
Form
30
Sort
3 12 156 245 890 1024 1900 40990 4591320
31
Sort Demo

• Sort any base table item
• Activate via
• Popup Menu (right mouse button)or Menu
• or Double click on item promptor Toolbar
  Button

32
User Defined Block Sorting

• Issues
• Funky code needed to detect item when double
  click on item prompt (item mouse trigger does not
  fire)
• Non base table items are not sortable
• See Updatable View Blocks for workaround
• Items are sorted based on database value, not
  displayed value
• UPDATE Fixed for list items in latest build

33
Sort Requirements

• Determine requirements
• Easy
• Sort by single column only
• Difficult
• Sort by multiple columns
• Sort by functions
• Sort by sub-queries

34
User Interface - Visual Indicator

• Need visual indication of block order column
• Change item prompt
• Ascending or descending item prompts
• Employee Name () Employee Name (-)
• Employee Name () Employee Name (-)
• Employee Name () Employee Name (v)

35
Updatable and Queryable Descriptor Items
36
Descriptor Items

• Data Model
• Goal
• Display descriptor columns from lookup table
• Queryable
• Sortable

37

• Block data source needs to use a join query

SELECT E. ,D.DEPARTMENT_NAME FROM
EMPLOYEES E
,DEPARTMENTS D WHERE E.DEPARTMENT_ID
D.DEPARTMENT_ID
38

• Options
• 1) Create a view based on the query then
  base the block on the view
• or 2) Base the block on the query

39
Verify the view is updatable

• Verify that the view is updatable

SELECT from USER_UPDATABLE_COLUMNS where
table_name MY_VIEW_NAME'
INSERTABLE OWNER TABLE_NAME
COLUMN_NAME UPDATABLE DELETABLE

HR EMPLOYEES_EXT_V EMPLOYEE_ID
YES YES YES HR EMPLOYEES_EXT_V
FIRST_NAME YES YES YES HR
EMPLOYEES_EXT_V LAST_NAME YES YES
YES HR EMPLOYEES_EXT_V EMAIL YES
YES YES HR EMPLOYEES_EXT_V PHONE_NUMBER
YES YES YES HR EMPLOYEES_EXT_V
HIRE_DATE YES YES YES HR
EMPLOYEES_EXT_V JOB_ID YES YES
YES HR EMPLOYEES_EXT_V SALARY YES
YES YES HR EMPLOYEES_EXT_V
COMMISSION_PCT YES YES YES HR
EMPLOYEES_EXT_V MANAGER_ID YES YES
YES HR EMPLOYEES_EXT_V DEPARTMENT_ID YES
YES YES HR EMPLOYEES_EXT_V DEPT_NAME
NO NO NO
40
Does Not Work yet

• Need to change various properties
• Block
• Enforce Primary Key property Yes
• Primary Key Item (EMPLOYEE_ID)
• Primary Key property Yes

41
Data Entry Populate Descriptor Item
WHEN-VALIDATE-ITEM on Foreign Key Item
(Department Id)
PROCEDURE populate_department_name IS BEGIN
EMP.DEPARTMENT_NAME '' -- clear descriptor
item value in case no matches SELECT
D.department_name into
EMP.DEPARTMENT_NAME from departments D
where D.department_id EMP.DEPARTMENT_ID E
XCEPTION WHEN NO_DATA_FOUND THEN message
('ERROR could not locate a department matching
this value') WHEN TOO_MANY_ROWS THEN
message ('ERROR more than one department
matches this value') WHEN OTHERS THEN
message ('ERROR ' sqlerrm) END
42
Data Source

• View Block Data Source View Name
• Query Block Data Source (SELECT )
• From

43
Export
44
Export Requirements

• "The purpose of a application is not to store
  information, it's purpose is to get the
  information out."
• Export on Steroids - DesignAssist

45
Poor Mans Export

• Do it yourself
• With the help from Scotts loop code

PAYPALACCEPTED
46
Export - Requirements

• Requirements / Design Decisions
• What to include ?
• Blocks
• Single block only (Easiest to do !)
• All blocks
• Current block and all child blocks
• Include Hidden Fields ?
• Include password protected fields ?
• Show database value or displayed text ?(For List
  Items, Checkboxes, Radio Groups)

47
Export - Format

• Output Format
• Tab separated
• CSV
• HTML (HTML Table)
• XML

48
Export - Pseudocode

• Loop through all records in the block
• Loop through all items in the record
• process item value
• (write item value to output)
• (write record text to output)
• Display output

Scotts Loop Code
Write Output.
View Output
49
Export Write Output

• Various methods to write output
• Copy to clipboard
• go_item ('BLOCK.ITEM')
• select_all
• copy_region
• Forms TEXT_IO package
• Writes to file on Forms Server
• WEB_UTIL (download free from Oracle)
• CLIENT_TEXT_IO package
• Insert into to Database Table
• Write to file on database server
• UTL_FILE package can write to file on database
  server

50
Export - View Output

• Forms 6 client/server
• HOST (excel ltfilenamegt'), NO_SCREEN)
• HOST ('start excel ltfilenamegt'), NO_SCREEN)
• DDE.app_begin ( p_program_name
• ' ' p_file_name, DDE.APP_MODE_NORMAL
  )
• DDE.app_begin (p_file_name,DDE.APP_MODE_NORMAL)

51
Export - View Output

• Web Forms 6 to 10gr21) Forms WEB package
• WEB.show_document (v_file_name, '_SELF')
• WEB.show_document (v_file_name, '_BLANK')
• 2) WEB_UTIL add-on
• Free from Oracle
• WEB_UTIL.CLIENT_HOST (p_local_file_name)

52
Tree Tracing
53
Sequential Tracing

• Sequential tracing is a chronological dump.
• No dependency info

PROC 1 start PROC 2 start PROC 3 start PROC 3
end PROC 2 end PROC 1 end PROC 4 start PROC 4
end PROC 5 start PROC 5 end
54
Indented Output
PROC 1 start PROC 2 start PROC 3 start PROC
3 end PROC 2 end PROC 1 endPROC 4 start PROC 4
end PROC 5 start PROC 5 end
Can determine dependencies executed as a
natural firing sequence or executed as a result
of being called
55
How to achieve ?

• Store level counter in package
• Inc / Dec within each procedure / function

Procedure PROC_1 is begin v_level v_level
1 -- write to log file with indent based on
level LOG (PROC_1, v_level) ltyour code
goes heregt v_level v_level - 1 ltwrite to
log file with indent based on levelgt end
56
Improved Code

• Trace routines should do all the work.
• Package TRACE_PKG is
• Procedure.TRACE ( p_source_1 in varchar2
• p_source_2 in varchar2
• p_trace_message in varchar2
• p_level_adjust in number default 0 )
  isbegin
• p_trace_level p_trace_level
  nvl(p_level_adjust,0)-- write trace line with
  indent based on travel level

57
Be Paranoid

• Error Handling
• Trace level can get screwed up
• Early exit from routine
• Exceptions
• Start / end trace points not included
• Reset trace level to 0 if too small or large
• If level lt1 reset to 0
• If level gt 100 reset to 0

58
Tips
59
Global Variables

• Coding Standards - Dont use because uses too
  much memory
• Stored in array, with fixed size elements
• Old standard 20 years old. Less critical now
• Use where appropriate
• Erase when no longer need
• When finished using
• When Exit form
• Dont write global variable manager

Avoid caching last resort, better to tune
60
Cursor Restore Position

• -- save current cursor location
• DECLARE
• v_original_item varchar2 (70)
  name_in('SYSTEM.cursor_item' )
• v_original_record number (10)
  name_in('SYSTEM.cursor_record')
• begin
• -- do your stuff here
• -- return to the original cursor location
• go_item (v_original_item) -- return
  to the original block
• go_record (v_original_record) -- return to
  the original record
• go_item (v_original_item) -- return
  to the original item
• end

61

• Before This Presentation

62

• After This Presentation

63
Goals

• Learn advanced Oracle Forms techniques
• Scotts freebies
• Build your own
• Tips and Traps