Oracle Forms Recipes

By Vinish Kapoor Developing Oracle Applications since 2001

 

Contents

Map Columns From Different Tables and Create Insert and Update Statements in Oracle Forms

Checking For User Permissions Before Updating or Inserting The Records in Oracle Forms

An Example of Pre-Query and Post-Query Triggers in Oracle Forms With Using Display_Item to Highlight Dynamically

Displaying Modal Window Messages in Oracle Forms Using Alerts

Get_File_Name Usage in Oracle Forms 6i

Creating, Stopping, Re-Starting and Deleting a Timer in Oracle Forms

Writing Text File From A Tabular Block In Oracle Forms

Populating Tabular Data Block Manually Using Cursor in Oracle Forms

If Value Exists Then Query Else Allow Create New in Oracle Forms An Example

Trigger Execution Sequence Of Oracle Forms

Why And When To Use Pre-Update and Pre-Insert Triggers In Oracle Forms

Some Useful Property Settings Explained Of Oracle Forms

FRM-10001, FRM-10002, FRM-10003 Oracle Form Builder Error Solution

Example of Get_File_Name Function in Oracle Forms

Create Timer Example To Show Image Presentation in Oracle Forms

See also: Create timer to display clock in Oracle Forms

Moving From Top To Bottom in Detailed Block in Oracle Forms

Date Picker Calendar For Oracle Forms 6i

Download Form and required PLL

How To Use

Determining Current Block and Current Item in Oracle Forms

SYSTEM.CURSOR_BLOCK

SYSTEM.CURSOR_ITEM

View Oracle Developer Handbook at Amazon.com

Adding Value To Combo List at Runtime in Oracle Forms

Using GET_GROUP_SELECTION For Record Groups in Oracle Forms

Oracle Form Data Entry Sample

Using User-Named Triggers in Oracle Forms

Giving Data Backup Option in Oracle Forms 6i

An Example of On-Error Trigger in Oracle Forms

Shifting List Item Values From One List To Another In Oracle Forms

Using SYSTEM.MOUSE_ITEM In Oracle Forms

Using Post_Query Trigger in Oracle Forms

Pre-Update and Pre-Insert Trigger Examples For Oracle Forms

Pre-Insert trigger

Handling Tab Pages in Oracle Forms

How To PLAY_SOUND in Oracle Forms

Using Find_Alert and Show_Alert in Oracle Forms

How To Use RUN_PRODUCT In Oracle Forms

How To Use FETCH_RECORDS In Oracle Forms

Writing On-Error Trigger In Oracle Forms

How To Use DBLink In Oracle Forms 6i

Solution - 1

Solution - 2

Highlighting Text Item On Entry In Oracle Forms

Steps to highlight current item

Changing Icon File Of Push Button At Runtime In Oracle Forms 6i

Set Icon_File property in When-Mouse-Enter trigger

Displaying Window In Center In Oracle Forms 6i

Populating Tree Item With Record Group In Oracle Forms

Formatting Excel File Using Ole2 In Oracle Forms

Adding List Item Element At Runtime In Oracle Forms

Populating Display Item Value On Query In Oracle

Creating Custom Login Screen In Oracle Forms 10g

Writing Text Files On The Client in Oracle Forms 10g

Reading An Image File Into Forms From Client In Oracle Forms 10g

Number To Indian Rupee Words in Oracle Forms / Reports

Creating Object Library OLB in Oracle D2k Form

Creating Excel File in Oracle Forms

How To Tune or Test PLSQL Code Performance in Oracle D2k Forms

Creating Dynamic LOV in Oracle D2k Forms

Using GET_APPLICATION_PROPERTY in Oracle D2k Forms

Upload Files To FTP in Oracle Forms D2k

 

 

 

 

 

 

Map Columns From Different Tables and Create Insert and Update Statements in Oracle Forms

 

This is one of my most needed tool to create Insert and Update statements using select or alias from different tables where column mapping is difficult.

 

What this utility actually does is, suppose you have two table with too many columns and you want to update or insert in one table from another, you know column names but you want some visual interface so that your task can be easier. This tool provide the same facility to create insert or update statement by visually mapping fields from two tables.

 

The utility is created using Oracle Forms and can be used easily. Free download it from Tablemap.fmx

 

Below is the screen shots of this tool:

 

 

 

 

Checking For User Permissions Before Updating or Inserting The Records in Oracle Forms

 

Suppose you want to check the user permissions on inserting or updating the records in Oracle Forms, then you can use Pre-Insert and Pre-Update triggers for that particular data block to check whether user is having proper permission or not.

 

The example is given for HR schema and the following demo table is created for this:

 

Create Table User_Permissions (User_Name varchar2(50) Primary Key,

Can_Insert Varchar2(1) default 'N',

Can_Update Varchar2(1) default 'N')

 

Insert into User_Permissions values ('FORMUSER1', 'Y', 'N');

 

Commit;

 

Below is the screen shot for the examle:

 

 

You can download this form from the following link: Pre-Update-Insert.fmb

 

The following is the code written in Pre-Update trigger to check the permissions from the database:

 

Declare

n_allow number;

begin

Select 1 into n_allow

   from hr.user_permissions

   where user_name = 'FORMUSER1'

   and can_update = 'Y';

   --- all is well if no exception raised else stop in exception area

   exception

     when others then

        message('You have no permission to update the record.');

        message('You have no permission to update the record.');

        raise form_trigger_failure;

end;

 

The following is the code written in Pre-Insert trigger to check the permissions from the database on Insert:

 

Declare

n_allow number;

begin

Select 1 into n_allow

   from hr.user_permissions

   where user_name = 'FORMUSER1'

   and can_insert = 'Y';

   --- all is well if no exception raised else stop in exception area

   exception

     when others then

        message('You have no permission to insert the record.');

        message('You have no permission to insert the record.');

        raise form_trigger_failure;

      

end;

 

The code is written for Save button:

 

Commit_form;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

An Example of Pre-Query and Post-Query Triggers in Oracle Forms With Using Display_Item to Highlight Dynamically

 

Example is given for Pre-Query and Post-Query triggers in Oracle Forms, with using Display_Item built-in in Post-Query triggers to highlight fields dynamically.

 

This is the screen shot below for this example:

 

 

You can also download this form from the following link: Query.fmb

 

The example is based on HR schema departments table. In this example Department No. and Execute Query push button is in upper block named "Ctrl" block and below block is the departments block. User will be asked to enter department no. in above block and then to click on Execute Query button to filter the records below. The filtration is handled in Pre-Query trigger and after execution of query the Manager Name and Salary will be populated in Post-Query trigger by dynamically highlighting the Manager Name using Display_Item built-in.

 

The following code written in Pre-Query trigger of Departments block to filter the records:

 

if :ctrl.deptno is not null then

  -- set default_where property of the block to the ctrl block item to filter the records before query

  set_block_property('departments', default_where, 'department_id = :ctrl.deptno');

end if;

 

The following code written in Post-Query trigger of Departments block to populate non-database item fields and dynamically highlighting the Manager Name field:

 

begin

 select first_name, salary into :departments.empname, :departments.sal

   from hr.employees where employee_id = :departments.manager_id;

   -- highlight as per your criteria

   if :sal >= 10000 then

                  -- create highlight visual attribute with color of your choice to highlight

      display_item('departments.empname', 'highlight');

   else

                 -- create default1 visual attribute to restore to normal view

      display_item('departments.empname', 'default1');

   end if;

exception

 when others then

   null;

end;

 

The following code written in When-Button-Pressed trigger of Execute Query push button in Ctrl block to execute query in Departments block:

 

go_block('departments');

set_block_property('departments', default_where, '');

execute_query;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Displaying Modal Window Messages in Oracle Forms Using Alerts

 

You can display modal windows in Oracle Forms to display normal messages, error message or asking for confirmation eg. on deleting a record or saving a record etc.

 

These modal window messages can be shown using Alert option in Oracle forms.

 

This is the screen shot below for this example:

 

 

You can download this form from the following link: Modal_Msgt.fmb

 

For this example I have created three alerts with the following names:

 

1. Good_Msg

2. Error_Msg

3. Ask_Alert

 

The following code is written for "Show Good Message" button to display a normal message, you can use this code in any PLSQL block:

 

Declare

-- create a numeric variable to hold show_alert return value

nalertbutton number;

Begin

-- set the message for alert

set_alert_property('good_msg', alert_message_text, 'Records saved successfully.');

-- after below statement the execution will hold till you click on ok.. becuase it is an modal window

nalertbutton := show_alert('good_msg');

:alertblock.result := 'That was a good message.';

-- after this you can perform any task...

End;

 

The following code is written for "Show Error Message" button to display an Error message:

 

Declare

-- create a numeric variable to hold show_alert return value

nalertbutton number;

Begin

-- set the message for alert

set_alert_property('error_msg', alert_message_text, 'An error occurred.');

-- after below statement the execution will hold till you click on ok.. becuase it is an modal window

nalertbutton := show_alert('error_msg');

:alertblock.result := 'That was an ERROR message.';

-- after this you can perform any task...

End;

 

The following code is written for "Ask Confirmation" button to ask for a confirmation:

 

Declare

-- create a numeric variable to hold show_alert return value

nalertbutton number;

Begin

-- set the message for alert

set_alert_property('ask_alert', alert_message_text, 'Confirm Yes or No?');

-- after below statement the execution will hold till you click on ok.. becuase it is an modal window

nalertbutton := show_alert('ask_alert');