FJTable Oracle Forms tool 1.4

Home page

Purpose

What’s up in this version ?

Objects provided with this tool

How it works ?

How the user can invoke the FJTable options panel ?

Some screen shots samples

Installation steps

1. Purpose

This tool allows multi-lines blocks to look like a Java Swing Jtable.

In the Forms application, when the cursor is located in a multi-lines block, user can perform the following actions:

q Swap columns

q Increase / decrease the size of each column

q Sort the block on every based column (ASC/DESC)

This tool has been tested with the following configurations:

· Forms 9i and Database 9i

· Forms 10g and Database 10g

Note:

If you still use a previous version, do not forget you can download (for test) more recent versions from OTN.

This is the non-persistent version.

To see the version that allows users to store/retrieve settings from the database, click here

2. What’s up in this version ?

Bug correction

q Correct move of columns when items are not stacked.

(keep the space between items)

q Migrated form for previous version (6i and previous).

Someone tell me that, when a previous form (6i) is migrated, the database COLUMN_NAME property is left blank.

A parameter has been added (PM$INITIAL_VERSION) to indicate the initial (before any migration) version.

The default is : 9.

If form comes from a migration, indicate in this parameter a number less than 9.

q NEXT and PREVIOUS PL/SQL table column have been resized to Varchar2(61) to store full block_name.item_name property.

q When the Options panel is invoked from a display item, cancel button do not hide the panel. Go_Item() have been replaced by Go_Block().

Evolutions

q Disable blocks that contain RADIO GROUP items.

The Move_Item() procedure need to know the X_POS and WIDTH of each item.

To get these properties on a RADIO GROUP, we must indicate the Option button’s Name into the Radio Group.

Unfortunately, there is no built-in that gives the list of option buttons in a radio group.

So, it is not possible to move option buttons.

A message is displayed in the status bar when the Init() procedure find a Radio Group item, and the FJTable option panel cannot be displayed for this block.

q Best location of FJPanel option window.

(The FJTable panel option is allways completely displayed)

3. Objects provided with this tool

A PL/SQL library wich contains one package ( fjtable.pll )
An object library wich contains one object group ( obj_fjtable.olb )

4. How it works ?

The PL/SQL library’s package contains procedures and functions to initialize global variables at startup of the form and performs the FJTable actions at runtime.

Summary of the PKG_Jtable package functions:

q Init

-- Init blocks informations at startup --

Procedure Init

(

PC$Can_Resize_Window Varchar2 DEFAULT 'O',

PC$Can_Resize_Canvas Varchar2 DEFAULT 'O',

PN$Initial_Version Pls_Integer DEFAULT 9

) ;

This procedure loops through every block of the current form.

For each multi-line block, it stores information in the PL/SQL global tables.

It must be called at startup of the form in the When-New-Form-Instance trigger.

PC$Can_Resize_Window defines if the user can increase the size of the window (when he increases the size of a column).

PC$Can_Resize_Canvas defines if the user can increase the size of the canevas (when he increases the size of a column).

PN$Initial_Version indicates the original (before any migration) form version. (enter one master digit; e.g. 6 )

By default, these arguments contain the values of the three Form parameters:

· :PARAMETER.PM$RESIZEWINDOW

· :PARAMETER.PM$RESIZECANVAS

· :PARAMETER.PM$INITIAL_VERSION

Value must be ‘O’ (default) if you want to enable the user to increase the window or canvas size

You can use these parameters in NEW_FORM(), OPEN_FORM() and CALL_FORM() built-ins.

Note:

The size of window/canvas can be increased only for Content or Stacked canvas.

Tab page canvas cannot be increased.

q Get_Id_Block

-- Get the block number in collection --

Function Get_Id_Block ( PC$Blk_Name In Varchar2 ) RETURN PLS_INTEGER ;

All information are stored in global package PL/SQL tables.

Since actual Forms PL/SQL engine does not recognize the INDEX BY VARCHAR2() nested table syntax, all the collections use the INDEX BY BINARY_INTEGER syntax. So, we need the ID of each object in the collections to perform direct access features.

PC$Blk_Name is the current block name

q Get_Id_Item

-- Get the item number in collection --

Function Get_Id_Item ( PN$Blk_Id In Pls_Integer, PC$Item_Name In Varchar2 )

RETURN PLS_INTEGER ;

PN$Blk_Id is the ID of the current block

PC$Item_Name is the item name

q Move_Item

-- Move item --

Procedure Move_Item

(

PN$Blk_Id In Pls_Integer,

PN$Item_Id In Pls_Integer,

PC$Direction In Varchar2

) ;

This procedure is called by the BT_MV_RIGHT and BT_MV_LEFT BL_JFMENU’s block buttons.

PN$Blk_Id is the current block ID

PN$Item_Id is the current item ID

PC$Direction can take one of the following values:

· ‘R’ to move the column on the right

· ‘L’ to move the column on the left

Note:

Current block and item IDs are defined in the When-Mouse-DoubleClick forms level trigger.

q Resize_Item

-- Resize item --

Procedure Resize_Item

(

PN$Blk_Id In Pls_Integer,

PN$Item_Id In Pls_Integer,

PC$Operation In Varchar2

) ;

This procedure is called by the BT_INCREASE and BT_DECREASE BL_JFMENU’s block buttons.

PN$Blk_Id is the current block ID

PN$Item_Id is the current item ID

PC$Operation can take one of the following values:

· ‘+’ increase by one character the size of the selected column

· ‘-’ decrease by one character the size of the selected column

What happens to the window/canvas size ?

When a user increases the size of a column, subsequent columns are moved to the right.

When the new size is smaller than the canvas size, there are the followings possibilities:

· The canvas is a Tab page canvas

Nothing. The function returns

· You disabled the increase window/canvas feature

Nothing. The function returns

· You enabled the increase window/canvas feature

The window/canvas size is increased to fit the new size of the block

q Set_Order_By

-- Set order by --

Procedure Set_Order_By( PC$Orientation In Varchar2 DEFAULT 'ASC' ) ;

If the selected item has a database correspondance, this procedure does the following actions:

· Set the ORDER BY block’s clause

· Perform an EXECUTE_QUERY

PC$Orientation is ‘ASC’ or ‘DESC’

This procedure is called by the BT_SORT_ASC and BT_SORT_DESC BL_JFMENU’s block buttons.

q Get_Id_Canvas

-- Get the Canvas number --

Function Get_Id_Canvas

(

PC$Cv_name In Varchar2,

PC$TabCan In Varchar2

) Return Pls_Integer ;

This function returns the internal canvas ID in the PL/SQL table.

q Trace

-- Output PL/SQL table on table --

---------------------------------------

-- need the F_TRACE table --

-- and the F_TRACE stored procedure --

Procedure Trace ;

This procedure allows the developer to output the contents of the PL/SQL nested tables on the TRACE Oracle’s table.

If you do not want to install the TRACE table and F_TRACE stored procedure, remove this procedure from the package specification and body.

5. How the user can invoke the FJTable options panel ?

When the cursor is located in a multi-lines block, users can invoke the FJTable options panel by a double-click in any column of the block. If a KEYMODIFIER is specified, users must press this keyboard key at the same time they double-click.

If the double-click functionality is already in use in your form, you can add a KEYMODIFIER to be sure there will not be any interference with your own code.

The KEYMODIFIER is read from the Forms parameter : :PARAMETER.PM$SHIFTSTATUS

The possible values can be:

· Shift+

· Caps Lock+

· Control+ (default)

· Alt+

· Command+

· Super+

· Hyper+

If you do not want to enable a KEYMODIFIER, set the parameter value to nothing.

Some screen shots samples:

In this sample, the user wants to increase the size of the Job column.

He (Ctrl+) double-cliks on the Job column...

... then presses the Increase size (+) button as many times as required.

Note:

As long as the options panel is displayed, the user can perform operations on the selected column.

When he wants to return to the block, he presses the Cancel button.

Now, he wants to move the Sal column just after the Job column.

He (Ctrl+) double-clicks the Sal column and presses two times the << Move to left button.

Finally, he wants to sort the block on the Ename column.

He (Ctrl+) double-clicks on the Ename column and presses the Set Order by ASC button.

Installation steps:

Download the FJTable.zip file

Unzip the FJTable.zip file

If you want to create the TRACE table and the F_TRACE stored procedure, then run the f_trace.sql script

Compile the fjtable.pll library

8. Test with your own form

Open your form module
Attach the fjtable.pll library
Open the obj_fjtable.olb objects library
Drag the GRP_FJTABLE_xxx object group to your Object group node (click the REFERENCE option)

(Depending on the coordinate system currently used in your form, drag the corresponding Object group)

· GRP_FJTABLE_PIXEL

· GRP_FJTABLE_CM

· GRP_FJTABLE_INCH

· GRP_FJTABLE_POINT

· GRP_FJTABLE_CHAR

Move the BL_JFMENU block to the end of the blocks list in the object navigator

Move the CV_JFMENU canvas to the end of the canvases list in the object navigator

Edit the When-New-Form-Instance trigger to add the call of the init procedure:

PKG_JTABLE.Init

(

PC$Can_Resize_Window => :PARAMETER.PM$RESIZEWINDOW,

PC$Can_Resize_Canvas => :PARAMETER.PM$RESIZECANVAS,

PN$Initial_Version => :PARAMETER.PM$INITIAL_VERSION

) ;

Compile all and run the form.

9. Special settings

If you already use a When-Mouse-Doubleclick block or item trigger:

· Set its Execution Hierarchy Property property to : fire after parent

· Be sure that the PM$SHIFTSTATUS parameter contains a key modifier.

10. Translation strings

You can translate some strings into your own language.

The strings that can be translated are label of the JFTable option panel and four messages displayed in the status bar when the resizing of canvas or window is not allowed, or when the user want to sort on an item that has no corresponding database column.

The translation strings are stored in the PKG_JTABLE package specifications.

11. Known issues

· As it is not possible (with the actual Forms version) to modify at run time the properties of graphic items like Frames, do not use them with your multi-lines blocks.

When the user increases the size of columns, this automatically increases the size of the canvas but the frame does not !

One solution is to replace the frame with a single straight line at the top of the block.

· Items order in the object navigator

Items in the object navigator must be in order by X_POS

e.g.

-- Correct setting --

ITEM NAME X_POS

ITEM_1 50

ITEM_2 100

ITEM_3 140

-- Incorrect setting --

ITEM NAME X_POS

ITEM_1 50

ITEM_2 140 --> in the object navigator, drag this item after ITEM_3

ITEM_3 100

· For blocks that do not have scrollbar, a FRM-99999 message is displayed in the status bar.

Because the move of the scrollbar is the last action performed by the Move_Item() procedure, all seem to be right.

I think the problem is with the Get_Block_Property( …, BLOCKSCROLLBAR_X_POS ) built-in when no scrollbar exists for the block.

If your form contains multi-lines blocks with no scrollbar defined, modify your existing ON-ERROR form-level trigger or add it:

Declare

LN$ErrCode Number := ERROR_CODE ;

LC$ErrType VArchar(3) := ERROR_TYPE;

LC$ErrText Varchar2(256) := ERROR_TEXT ;

Begin

If LN$ErrCode = 99999 and LC$ErrText like '%1413%' Then

-- Error on Get_Block_Property( ..., BLOCKSCROLLBAR_X_POS ) --

-- If Block does not have Scrollbar defined --

Null ;

Else

Bell ;

Message( LC$ErrType || '-' || TO_CHAR(LN$ErrCode) || ': ' || LC$ErrText);

Raise form_trigger_failure ;

End if ;

End ;

· Because it is not possible to retrieve Option buttons from a Radio Group, this item type cannot be moved/resized.

When a bloc contains a Radio Group item, the FJTable tool Panel cannot be invoked.

12. Need your feedback

I have tried this tool with a certain amount of forms and it seems to go right.

However, with your own forms, you might get bugs.

This version does not take into account the SHARE_LIBRARY_DATA calling form’s functionality.

Please, send me your feedback by email at “f.degrelle@free.fr”

Annex : The F_TRACE procedure

This procedure allows to output on the screen (DBMS_OUTPUT) or in a table (INSERT) strings of 1 to 32767 characters.

It is an autonomous procedure.

Syntax of the procedure:

procedure F_TRACE ( PC$Message in VARCHAR2, PC$Output in VARCHAR2 DEFAULT 'S' )

PC$Message is the string you want to output

PC$Output can take one of the following values:

· ‘S’ (default) output on the screen with the DBMS_OUTPUT package

· != ‘S’ output on the TRACE table

If you want to install the TRACE table and the F_TRACE procedure, run the f_trace.sql script provided in the .zip file