Oracle 9i Forms Developer Quick Notes

 

ORACLE FORMS DEVELOPER 10g:

BUILD INTERNET APPLICATIONS                        

Prepared by: Eng. Héba M. Noweir     

 

Oracle Forms Developer 10g: Build Internet Applications    •

CONTENTS

TOPIC

PAGE

Installing Oracle Developer Suite ……………………………………………... Oracle Forms Builder……………………………………………………………. Creating Non Database Items (Application Items) ………………………….. (A) Summary (B) Formula Master-Detail ……………………………………………………………………. Visual-Attributes ………………………………………………………………… List of Values LOV ……………………………………………………………… (A) Manually (B) Using LOV Wizard Editors ……………………………………………………………………………. Triggers …………………………………………………………………………… Check Box Display Item …………………………………………………………. LOV Display Item ………………………………………………………………… (A) Push Buttons (B) Display Items Types of Canvas …………………………………………………………………. (B) Stacked Canvas (C) Horizontal & Vertical Toolbars (D) Tab Canvas Database List …………………………………………………………………….. Assembled Task …………………………………………………………………. Tree ………………………………………………………………………………... Alert ………………………………………………………………………………... Multiple Form Activation ………………………………………………………… Important Task …………………………………………………………………… Solution of the Important Task ………………………………………………….. Parameter List ……………………………………………………………………. Program Units ……………………………………………………………………. Debug Mode ……………………………………………………………………… Object Groups ……………………………………………………………………. Object Libraries …………………………………………………………………... PL/SQL Libraries …………………………………………………………………. Menus ……………………………………………………………………………... Template Forms ………………………………………………………………….. Logon Forms ……………………………………………………………………… WebUtil ……………………………………………………………………………. Uploading BLOB Image …………………………………………………………. Iconic Buttons …………………………………………………………………….. Opening Forms in a separate window …………………………………………. Installing Java Beans …………………………………………………………….

1.1 1.2 2.1

Prepared by: Eng. Héba M. Noweir     

2.2 2.2 2.3 2.3 3.1 3.3 3.4 4.1

4.2 5.1 6.1 6.2 6.2 6.3 6.4 7.1 7.2 7.2 8.1 8.1 8.1 8.2 8.2 9.1 9.2 10.1 10.1 10.1 10.2

Oracle Forms Developer 10g: Build Internet Applications 

•

If a problem occurs with your Database:

Start Æ Oracle – OraDb10g_home Æ Configuration & Migration Tools Æ Database Configuration Assistant Æ Database Configuration Assistant Æ Delete a Database

•

Net Configuration Assistant:

Start Æ Oracle – DevSuiteHome Æ Configuration & Migration Tools Æ Net Configuration Assistant Æ Local Net Service Name Configuration Æ Add Æ Service Name: [orcl] Æ TCP Æ Host Name: [Your Computer Name] Æ Change Login Æ Username:[sys | hr] Æ [sys | hr] Æ Net Service Name: [Any Name]

•

Changing language: Run Æ Regedit Æ HKEY_LOCAL_MACHINE Æ SOFTWARE Æ ORACLE Find: NLS_LANG Æ Modify Æ Value data: AMERICAN_AMERICA.AR8MSWIN1256

•

Forms Builder Path:

Start Æ Oracle Developer Suite – DevSuiteHome Æ Forms Developer Æ Forms Builder

•

Note: Create a folder in the root directory, i.e. Path without any spaces to store your forms. Copy this path and paste it into the following file: [C]:\DevSuiteHome\Forms\Server\default.env (Open using Notepad and beside FORMS_PATH=[C]:\DevSuiteHome_1\forms; [HERE])

Oracle Forms Builder – [Object Navigator] (1) File Æ Connect Username: [hr]

Password: [hr]

Database: [orcl]

Note: You can also connect by clicking on the connect icon in the tools bar or by clicking Ctrl+j. (2) Some Useful Shortcuts: F2: Layout Editor (Canvas) F3: Object Navigator F4: Property Palette (3) Forms Tree: Each Module is a form; you can rename the module by double clicking on it (until it is highlighted with blue) and type the name.

Prepared by: Eng. Héba M. Noweir     

1‐1 

Oracle Forms Developer 10g: Build Internet Applications 

1‐2  (4) Creating Tabular (Table Shaped Form): Forms Module1 Data Blocks (Use the Data Block Wizard) Æ Table or View (5) To run the form; JInitiator must be installed. Start Æ Oracle Developer Suite – DevSuiteHome Æ Forms Developer Æ Start OC4J Instance (6) Each form you create will be saved under 2 different file extensions: .fmb: Modifiable files Extension .fmx: Executable files Extension (7) To open the Property Palette: a. Double Click on your target b. Right Click on it c. F4 When the target is highlighted with yellow Examples: Update Layout: Manually Show Scroll bar: Yes (8) Items Types: [From Property Palette] a. Radio Group (Label, Radio Button Value, Initial Value, Mapping of other values, Visible, Prompt Display Slide, Canvas, etc…) b. List Item (Elements in list, List Style, etc…) c. Check Box (Value When Checked, Value When Unchecked, Check Box Mapping of Other Values, etc…)

• •

(9) Right Click on Module (Form Name) from Object Navigator: Data Block Wizard Layout Wizard (10)Title Bar from Object Navigator: Layout Æ Size Components Æto compare fields (11)To Run the form: Program Æ Run Form [Title Bar] Run Form [Button from Tool bar] (12)On the Layout view: Canvas, View port, Frame (13)To hide an item from your canvas: F4 (Property Palette) Æ Canvas: Null

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications  (14) To hide Data under * Æ F4 on item Æ Conceal Data: Yes

Prepared by: Eng. Héba M. Noweir     

1‐3 

Oracle Forms Developer 10g: Build Internet Applications 

•

To create a non-database item (Application Item): (A) SUMMARY ¾ From the layout view Display item (F4) Æ Property Palette General Item Type: Display Item Data Type: Number Calculation Calculation mode: Summary Summary Function: [Choose your Function] Summarized Block: [Choose Block] Summarized Item: [Choose the Item you want to summarize] Records: Number of Items Displayed: 1 Database Database Item: No ¾ Then from the Object Navigator view Æ the property palette of the block Query All Records: Yes From Data Block ¾ TASK: Make a summary for SALARY in EMPLOYEES block

(B) FORMULA ¾ From the layout view Display item (F4) Æ Property Palette General Item Type: Display Item Data Type: Number Calculation Calculation mode: Formula Calculation Formula: [Write your Formula] Records Number of Items Displayed: [As needed] Database Database Item: No ¾ Then from the Object Navigator view Æ the property palette of the block Query All Records: Yes ¾ Note: Don’t forget to add (:) for Bind Variables in your Formula TASK: Make a Formula to calculate the Total Salary after commissions, Then, Make a summary for the this previous Total Salary item Hint: You can make use of [: SALARY + (: SALARY * NVL (:COMMISSION_PCT, 0))]

Prepared by: Eng. Héba M. Noweir     

2‐1 

Oracle Forms Developer 10g: Build Internet Applications 

•

2‐2 

MASTER-DETAIL: Create a new form Æ Data Block: Departments Create a new Data Block (Tabular) on the same Data Block you’ve just created (Employees) Data Block Wizard: Create Relationship (It automatically detects the Foreign Key) Join Condition: Employees.Department_id=Departments.Department_id Note: In the Object Navigator, you have to make sure that Master is displayed first ¾ You can also create the relationship manually: From the Object Navigator Æ Relations of the Master Block Detail Block: EMPLOYEES Join Condition: Employees.Department_id=Departments.Department_id ¾ TASK: Create a Master-Detail as Follows: Master Detail Detail Detail Detail

Regions Countries Locations Departments Employees

Form Form Form Form Tabular

1 Record 1 Record 1 Record 1 Record 10 Records

Note: Make sure the Join Condition is Correct

•

VISUAL ATTRIBUTES: Create a form Æ From the Object Navigator Æ Visual Attributes Æ Create Visual Attribute F4 On the item Æ Visual Attributes Visual Attributes Group: [Choose the one you’ve just created] Records Current Record Visual Attribute Group: [Choose the one you’ve just created]

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

2‐3  •

LIST OF VALUES ‘LOVs’: (A) MANUALLY (1) Create Form (Ex. Employees) (2) Create Record Group Æ Enter your Query Statement Ex. (Select Employee_ID, Last_name From Employees) (3) Create LOV (Manually) Æ Property Palette: Functional Record Group: [Choose the Record Group you’ve just created] Column Mapping Properties: [Type the column names and select a return value for each or for some of them “Make sure you return the appropriate value for the column”] (4) Go to the Column of the Return value in the Object Navigator (Ex. Manager_id) Æ Property Palette List of Values (LOV) List of Values: [Select the LOV you’ve just created] Note: If you want to hide a column in your form, choose its width to be ‘0’ (B) USING LOV WIZARD (1) Create Form (Ex. Employees) (2) Create Record Group Æ Enter your Query Statement Ex. (Select Department_ID, Department_name From Departments) (3) Create LOV (Using Wizard) Æ Existing Record Group [Choose your Record Group] Æ Modify Existing Record Group Æ Enter your Query Æ … Æ Choose Appropriate Return Values For Columns

¾

•

Note: Using LOV Wizard, the list of values of the return column is updated automatically. TASK: Location from Locations, Form Employees [Mgrs, Depts]

EDITORS: Create a form Æ From the Object Navigator Æ Editors Æ Create Editor F4 On the item Æ Editors Editor: [Choose the one you’ve just created]

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

•

3‐1 

TRIGGERS

CODE

TYPE SCOPE

Trigger Type: Defines the specific event that will cause the trigger to fire. Trigger Code: The body of PL/SQL that defines the actions of the trigger. Trigger Scope: The level in a form module at which the trigger is defined.

•

Trigger Type: Î Î Î Î Î Î

¾

¾

PrePostWhenOnKeyUser-named

TASK: Form Level Trigger (1) Create a Form (Ex. Employees) (2) Layout view Æ Create a Push-Button on the Canvas Æ F4 [Customize your Button] (3) Object Navigator Æ On the Form Æ Smart Triggers Æ WHEN-NEW-FORM-INSTANCE (4) PL/SQL code: BEGIN MESSAGE ('WELCOME TO EMPLOYEES FORM'); MESSAGE ('WELCOME TO EMPLOYEES FORM'); END; (5) Compile PL/SQL (6) RUN TASK: Block Level, Record Level, Item Level (Apply it on Button WHEN-BUTTONPRESSED) Triggers • Repeat the same steps as the previous Form Level Trigger.

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

Notes: (1) HELP Æ Ctrl+H (2) Don’t forget that each item in the Form is a Bind Variable, so you have to use (:) Ex. If you want to display a dynamic message: MESSAGE (‘Welcome to Employees’ || : Employees.Last_Name) (3) There are some Triggers that are no longer used in 10g but you may find them in HELP to serve for backward compatibility. (4) Advice: Try to put objects which are not related to tables or items in a separate Data Block (Ex. Control). (5) We wrote the Message 2 times in the PL/SQL Block, because the 1st time it appears in Consol and the 2nd time it is displayed for the user in the Form. ¾

TASK: Create a WHEN-VALIDATE-ITEM Trigger on Salary (1) Create Form (Employees) (2) Smart Triggers Æ WHEN-VALIDATE-ITEM (3) PL/SQL Code: BEGIN IF :EMPLOYEES.SALARY > 24000 THEN MESSAGE ('THIS IS AN INVALID SALARY'); MESSAGE ('THIS IS AN INVALID SALARY'); RAISE FORM_TRIGGER_FAILURE; END IF; END;

¾

TASK: Create a WHEN-VALIDATE-ITEM Trigger on Last_Name (1) Create Form (Employees) (2) Create Smart Triggers Æ WHEN-VALIDATE-ITEM (3) PL/SQL Code: BEGIN

BEGIN FOR i IN 1..LENGTH(:Last_Name) LOOP IF SUBSTR (:LAST_NAME,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN MESSAGE ('YOU CANNOT ENTER A NAME INCLUDING NUMBERS'); MESSAGE ('YOU CANNOT ENTER A NAME INCLUDING NUMBERS'); RAISE FORM_TRIGGER_FAILURE; END IF; END LOOP; END;

Prepared by: Eng. Héba M. Noweir     

FOR i IN 0..9 LOOP IF INSTR (:LAST_NAME,i) > 0 THEN MESSAGE ('YOU CANNOT ENTER A NAME INCLUDING NUMBERS'); MESSAGE ('YOU CANNOT ENTER A NAME INCLUDING NUMBERS'); RAISE FORM_TRIGGER_FAILURE; END IF; END LOOP; END;

3‐2 

Oracle Forms Developer 10g: Build Internet Applications 

¾ TASK: Create a WHEN-VALIDATE-ITEM Trigger on EMAIL (1) Create Form (Employees) (2) Create Smart Triggers Æ WHEN-VALIDATE-ITEM (3) PL/SQL Code: BEGIN IF (:EMAIL) LIKE '%@%.%' THEN -- Checks How many Letters after the Dot IF LENGTH (SUBSTR (:EMAIL,INSTR(:EMAIL,'.'))) > 4 THEN MESSAGE ('CHECK THE NUMBER OF LETTERS AFTER THE DOT'); MESSAGE ('CHECK THE NUMBER OF LETTERS AFTER THE DOT'); RAISE FORM_TRIGGER_FAILURE; -- Checks if the EMAIL starts with @ ELSIF (: EMAIL) LIKE ‘@%' Or (: EMAIL) LIKE '%@' THEN MESSAGE ('@ SYMBOL IS NOT IN ITS APPROPRIATE PLACE'); MESSAGE ('@ SYMBOL IS NOT IN ITS APPROPRIATE PLACE'); RAISE FORM_TRIGGER_FAILURE; END IF; ELSE MESSAGE ('THIS IS AN APPROPRIATE EMAIL FORMAT'); MESSAGE ('THIS IS AN APPROPRIATE EMAIL FORMAT'); RAISE FORM_TRIGGER_FAILURE; END IF; END;

•

CHECKBOX_DISPLAY_ITEM (1) (2) (3) (4)

Create Form (Employees) Military_Status (Active) Item Type: Check Box Create a Display Item [Name it: U_Active] beside Check Box Create POST-QUERY Trigger PL/SQL Code: --SHOW MILITARY STATUS 'ACTIVE'=Y OR 'INACTIVE'='N' DECLARE V_ACTIVE VARCHAR2 (1); BEGIN SELECT MILITARY_STATUS INTO V_ACTIVE FROM EMPLOYEES WHERE EMPLOYEE_ID=:EMPLOYEE_ID; IF V_ACTIVE='Y' THEN: U_ACTIVE:='ACTIVE'; ELSE : U_ACTIVE:='INACTIVE';

Prepared by: Eng. Héba M. Noweir     

3‐3 

Oracle Forms Developer 10g: Build Internet Applications  END IF;

3‐4 

END; •

LOV_DISPLAY_ITEM (A) Push-Buttons HELP Æ SHOW_LOV Built In (1) (2) (3) (4)

Create Form (Employees) Make a list of Values LOV on Manager_id and Department_id Create 2 Push Buttons from Layout view beside Department_id and Manager_id Create WHEN-BUTTON-PRESSED Trigger on the buttons you’ve just created PL/SQL Code:

DECLARE V_LOV_DEPT BOOLEAN; BEGIN V_LOV_DEPT:=SHOW_LOV('LOV_DEPT'); END;

DECLARE V_LOV_MGR BOOLEAN; BEGIN V_LOV_MGR:=SHOW_LOV('LOV_MGR'); END;

Note: V_LOV_DEPT, V_LOV_MGR are the names of Push-Buttons LOV_DEPT, LOV_MGR are the names of LOVs

(B) Display-Items (1) Create Form (Departments) (2) Create 2 Display Items from Layout view beside Department_id and Manager_id (F4 Æ Data Base Items: No) (3) Make a list of Values LOV on Manager_id and Department_id (4) Return the values of Department_name & Employee_id into the Display Items (5) Create WHEN-VALIDATE-ITEM Trigger on the Department_id & Manager_id Columns from Object Navigator PL/SQL Code: BEGIN SELECT DEPARTMENT_NAME INTO: U_DEPT FROM DEPARTMENTS WHERE DEPARTMENT_ID=:DEPARTMENT_ID; SELECT LAST_NAME INTO: U_MGR FROM EMPLOYEES WHERE EMPLOYEE_ID=:MANAGER_ID; END; (6) Create a POST-QUERY Trigger on Departments Data Block PL/SQL Code: BEGIN SELECT LAST_NAME INTO: U_MGR FROM EMPLOYEES WHERE EMPLOYEE_ID=:MANAGER_ID; SELECT DEPARTMENT_NAME INTO: U_DEPT FROM DEPARTMENTS WHERE DEPARTMENT_ID =:DEPARTMENT_ID;

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications  EXCEPTION WHEN OTHERS THEN NULL; END;

Prepared by: Eng. Héba M. Noweir     

3‐5 

Oracle Forms Developer 10g: Build Internet Applications 

•

Types of Canvas: (A) (B) (C) (D)

Content Stacked Toolbar Tab

(B) STACKED CANVAS: - Displayed on top of a content canvas - Shares a window with a content canvas - Usually smaller than the content canvas in the same window Note: Make sure that the Stacked Canvas is below the Content Canvas in the Object Navigator Example: (1) Create a Form - Content Canvas (Departments) (2) Create a Button for Manager_id (3) Create a new Data Block (Employees) – No Relations, New Canvas – Type: Stacked (4) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: BEGIN --Use One_Time_Where Function to Set_Block_Property SET_BLOCK_PROPERTY ('EMPLOYEES',ONETIME_WHERE, ‘EMPLOYEE_ID=:DEPARTMENTS.MANAGER_ID'); GO_BLOCK('EMPLOYEES'); EXECUTE_QUERY; --Use Show_View Function to view the Stacked Canvas SHOW_VIEW ('STACKED'); END; (5) Create a Button to exit the Employees Canvas (6) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: BEGIN GO_BLOCK('DEPARTMENTS'); HIDE_VIEW('STACKED'); END; (C) HORIZONTAL & VERTICAL TOOLBARS: (1) Create Form (Employees) on a Content Canvas (2) Create a new Data Block (Control) manually to display the push buttons on a new Canvas (Canvas Type: Toolbar) (3) Use the Layout Editor to resize the Toolbar Canvas Note: Make sure that Mouse Navigate: No & Keyboard Navigate: No for any functionality used in Toolbar Canvas.

Prepared by: Eng. Héba M. Noweir     

4‐1 

Oracle Forms Developer 10g: Build Internet Applications 

4‐2  (D) TAB CANVAS: - Displayed on top of a content canvas - Enables you to organize and display related information on separate tabs - Consists of one or more Tab Pages Example: (1) Create a new Form (Employees) – Canvas Type: Content (2) Create a new Canvas from Object Navigator Æ F4 - Canvas Type: Tab (3) Notice that as soon as you create a Tab Canvas, Tab pages are displayed (4) Modify in the Tab Pages [Add, Select items to be displayed, etc…] (5) For each item in the Content Canvas Æ F4 and choose its Canvas and tab page

•

Data Base List: (1) Create a new Form (Departments) (2) Create a List Item from Layout view beside Manager_id - F4 Æ Data Base Item: No Æ Elments in List: Last_Name (3) Create a new Record Group (Select to_char(employee_id) ID, last_name from employees) (4) To fill the List with values: Create a WHEN-NEW-BLOCK-INSTANCE Trigger PL/SQL Code: DECLARE V_RG_MGR NUMBER; BEGIN --POPULATE GROUP V_RG_MGR :=POPULATE_GROUP ('RG_MGR'); --POPULATE LIST (i.e FILL THE LIST) AND RELATE IT TO RECORD GROUP POPULATE_LIST ('LIST_MGR','RG_MGR'); END; (5) Create WHEN-LIST-CHANGED Trigger on List Item PL/SQL Code: BEGIN :MANAGER_ID:=:LIST_MGR; END;

Notes: (1) In the Data Base List, all queried columns must have the same data type (2) In Query Statement, Columns queried first are first retrieved (3) To make the list item from database: Make Manager_id Column the List_Item Go to List_Item from layout Æ F4 Database item: Yes Populate_list (‘MANAGER_ID’,’RG_MGR’); ¾ Task: Make Manager_id Æ Data Base List

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

5‐1 

Create a Master-Detail Form: DEPARTMENTS-EMPLOYEES

DEPARTMENTS

(1) Layout Style: Form (2) Location_ID: Database List-Item Æ Cities (3) Manager_ID: LOV Button (4) Create a Button beside Manager_ID that opens a STACKED Canvas containing Manager’s personal information (5) Create a Button to close the Canvas

X

EMPLOYEES

(1) Layout Style: Tabular in TAB Canvas (2) Gender: Radio Group Item (3) Active (Military Status): Check Box Item (4) Marital Status: List Item

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

•

TREE: A Unique Data Block has to be created for the Tree (1) Create a new Form [TREE] (2) Create a Data Block manually [TREE_BLOCK] (3) From Layout View, Create a Hierarchical Tree [MY_TREE] (4) Create a Record Group [RG_TREE] Select 1, Level, Last_Name, Null, To_char (Employee_id) ID From Employees Connect by prior Employee_id = Manager_id Start with Manager_id is Null Mode of TREE: -1: Collapsed 0: Level 1 1: Expanded Pseudo-Column: Level (May also take values 1, 2, 3,..) The Column that appears to the user: Last_Name Instead of putting icons: Null The value in Database MUST be of the same data type as the Column that appears to the user [Last_Name]: Employee_id (5) Go to MY_TREE From Object Navigator Æ F4 Record Group: [Choose the Record Group you’ve just created] RG_TREE (6) Create a WHEN-NEW-FORM-INSTANCE Trigger on Data Block/Form TREE PL/SQL Code: DECLARE V_RG_TREE NUMBER; BEGIN V_RG_TREE:=POPULATE_GROUP('RG_TREE'); --POPULATE TREE ON BLOCK LEVEL FTREE.POPULATE_TREE('MY_TREE'); END; ¾ Further Practice: (7) Create a new Data Block (Employees) No Relations (8) Create a WHEN-TREE-NODE-SELECTED on MY_TREE PL/SQL Code: DECLARE V_EMP_ID VARCHAR2(50); BEGIN V_EMP_ID:=FTREE.GET_TREE_NODE_PROPERTY ('MY_TREE',:SYSTEM.TRIGGER_NODE,FTREE.NODE_VALUE); SET_BLOCK_PROPERTY ('EMPLOYEES',ONETIME_WHERE,'EMPLOYEE_ID='||V_EMP_ID); GO_BLOCK('EMPLOYEES'); EXECUTE_QUERY;

Prepared by: Eng. Héba M. Noweir     

6‐1 

Oracle Forms Developer 10g: Build Internet Applications  END;

•

ALERT: (1) Create a new Form [ALERT] (2) Create a Data Block [DEPARTMENTS] – Tabular (3) Create Alert from Object Navigator [MY_ALERT] ÆF4 Title: [Display a title] Message: [Display your Message] Alert Style: [Choose your style] Button Label: [Choose Label] Default Alert Button: [Choose your default Button] (4) Create a WHEN-WINDOW-CLOSED Trigger on Form Level PL/SQL Code: DECLARE V_ALERT NUMBER; BEGIN V_ALERT:=SHOW_ALERT('MY_ALERT'); IF V_ALERT=ALERT_BUTTON1 THEN EXIT_FORM(DO_COMMIT); ELSIF V_ALERT=ALERT_BUTTON2 THEN EXIT_FORM(NO_COMMIT); GO_BLOCK('DEPARTMENTS'); END IF; END;

•

MULTIPLE FORM ACTIVATION: To open a Form from another one, we have 4 procedures: (1) OPEN_FORM Built-In Navigates between Forms (2) CALL_FORM Built-In Opens the new Form on the old one (3) GO_FORM Built-In If the Form is already opened, go to Form; and if it is not yet opened, open it (4) NEW_FORM Built-In Opens the new Form and closes the old one Note: Go_Form is the only Procedure that takes the Module_Name The 3 other procedures take the File_name

Prepared by: Eng. Héba M. Noweir     

6‐2 

Oracle Forms Developer 10g: Build Internet Applications 

Example: (1) Create a new Form (Employees) (2) Create another Form (Calling_Departments) – Data Block (Departments) (3) Create a Button (B_Call_Dept) on Employees Form (4) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: BEGIN :GLOBAL.DEPT_ID:=:EMPLOYEES.DEPARTMENT_ID; IF ID_NULL(FIND_FORM('CALLING_DEPARTMENTS')) THEN OPEN_FORM('CALLING_DEPARTMENTS'); ELSE GO_FORM ('CALLING_DEPARTMENTS'); END IF; END; (5) Create a WHEN-NEW-FORM-INSTANCE Trigger on Calling_Departments Form PL/SQL Code: BEGIN SET_BLOCK_PROPERTY('DEPARTMENTS',ONETIME_WHERE,'DEP ARTMENT_ID='||:GLOBAL.DEPT_ID); GO_BLOCK('DEPARTMENTS'); EXECUTE_QUERY; END; Note: Don’t forget to save the 2 Forms, so that you can use their File_Names in the code. We use Global Variables to open a Form from another Form. ¾

Task: [V.I.P] (1) Create a Table [Forms] (Form_Name Primary_Key, Module_Name, Parent_Form Foreign_Key, Description) (2) Create a TREE (Description Appears & takes its value from Form_Name in Database) Hint: Select 1, Level, Description, Null, Form_Name From Forms Connect by prior Form_Name =Parent _Form Start with Parent_Form is Null (3) Create a Form as Follows: TREE

Form_Name Module_Name Parent_Form

Prepared by: Eng. Héba M. Noweir     

LOV

Description Refresh

6‐3 

Oracle Forms Developer 10g: Build Internet Applications 

•

Task [V.I.P] Solution: (1) Create Table Forms (Form_name varchar2(50) primary key, module_name varchar2(50), parent_form varchar2(50), description varchar2(200), constraint FK_Form_parent foreign key (parent_form) references forms (form_name));

(2) (3) (4) (5)

Create a new Form (FORMS_TREE) – Datablock: (Forms) Create a new Datablock manually: (Tree) From Layout View, create a Hierarchical tree (MY_TREE) Create a Record Group (RG_TREE) SELECT 1, Level, DESCRIPTION, NULL, FORM_NAME FROM FORMS CONNECT BY PRIOR FORM_NAME=PARENT_FORM START WITH PARENT_FORM IS NULL;

F4 on MY_TREE Æ Choose RG_TREE (6) Create a LOV Button (B_LOV) beside Parent_Form Create Record Group (RG_FORMS) SELECT DESCRIPTION, FORM_NAME FROM FORMS

Create LOV using LOV Wizard based on the existing Record Group (RG_FORMS), LOV Column: Form_Name, Return Value: FORMS.PARENT_FORM Create a When-Button-Pressed Trigger on B_LOV PL/SQL Code: DECLARE V_LOV BOOLEAN; BEGIN V_LOV:=SHOW_LOV('LOV_PARENT'); END;

(7) Create a Button (B_Refresh), Keyboard Navigable: No, Mouse Navigable: No Create a When-Button-Pressed Trigger PL/SQL Code: DECLARE V_RG_TREE NUMBER; BEGIN COMMIT_FORM; V_RG_TREE:=POPULATE_GROUP('RG_TREE'); FTREE.POPULATE_TREE('MY_TREE'); END;

(8) Create a When-New-Form-Instance Trigger PL/SQL Code: DECLARE V_RG_TREE NUMBER; BEGIN V_RG_TREE:=POPULATE_GROUP('RG_TREE'); FTREE.POPULATE_TREE('MY_TREE'); END;

(9) Create a When-Tree-Node-Activated Trigger PL/SQL Code: DECLARE V_NODE_VALUE VARCHAR2(50); BEGIN V_NODE_VALUE:=FTREE.GET_TREE_NODE_PROPERTY('MY_TREE',:SYSTEM.TRIG GER_NODE, FTREE.NODE_VALUE); OPEN_FORM('V_NODE_VALUE'); END;

Prepared by: Eng. Héba M. Noweir     

6‐4 

Oracle Forms Developer 10g: Build Internet Applications 

•

PARAMETER LIST: (1) Create a new Form (PAR_EMP) – Data Block (Employees) (2) Create another Form (PAR_DEPT) – Data Block (Departments) (3) Create a Button on Employees Block beside Department_id to Call the PAR_DEPT Form (4) Create Parameter on PAR_DEPT [P_DEPT] From Object Navigator F4 Æ Parameter Data Type: Number (5) Save both Forms (6) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: DECLARE PL_ID PARAMLIST; PL_NAME VARCHAR2(10) :='TEMP'; BEGIN PL_ID:=GET_PARAMETER_LIST(PL_NAME); --IF THE PARAMETER LIST DOESN'T EXIST THEN CREATE A NEW ONE IF ID_NULL(PL_ID) THEN PL_ID:=CREATE_PARAMETER_LIST(PL_NAME); --IF THE PARAMETER LIST ALREADY EXISTS THEN DELETE THE PARAMETERS FROM IT ELSE DELETE_PARAMETER(PL_ID,'P_DEPT'); END IF; --ADD P_DEPT PARAMETER TO PARAMETER LIST ADD_PARAMETER(PL_ID,'P_DEPT',TEXT_PARAMETER,:EMPLOYEE S.DEPARTMENT_ID); --OPEN THE FORM OPEN_FORM('PAR_DEPT',ACTIVATE,NO_SESSION,PL_ID); END; (7) Create a WHEN-NEW-FORM-INSTANCE Trigger on PAR_DEPT Form PL/SQL Code: BEGIN IF :PARAMETER.P_DEPT IS NOT NULL THEN SET_BLOCK_PROPERTY ('DEPARTMENTS',ONETIME_WHERE,'DEPARTMENT_ID='||:PARAMETER.P _DEPT); GO_BLOCK('DEPARTMENTS'); EXECUTE_QUERY; END IF; END; (8) Save both Forms Æ RUN

Prepared by: Eng. Héba M. Noweir     

7‐1 

Oracle Forms Developer 10g: Build Internet Applications 

7‐2  •

PROGRAM UNITS: (1) Create a new Form [PROGRAM_UNITS] – Data Block (Employees) (2) Create a Program Unit from Object Navigator Æ Procedure: Raise_Salary PL/SQL Code: PROCEDURE RAISE_SALARY (EMP_ID NUMBER) IS BEGIN UPDATE EMPLOYEES SET SALARY=SALARY+1000 WHERE EMPLOYEE_ID=EMP_ID; END; (3) Create a Button beside Salary on the Form (4) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: BEGIN RAISE_SALARY (:EMPLOYEE_ID); END;

•

DEBUG MODE: (1) Create a new Form [DEBUG_MODE] – Data Block (Employees) (2) Create a Button beside Salary (3) Create a WHEN-BUTTON-PRESSED Trigger PL/SQL Code: BEGIN --I WANT TO INCREASE THE SALARY OF THE CURRENT EMPLOYEE • SELECT SALARY+1000 INTO :SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=:EMPLOYEE_ID; /* I WANT TO MAKE THE MANAGER OF THIS EMPLOYEE HIS MANAGER'S MANAGER TO MAKE THE EMPLOYEE AND HIS DIRECT MANAGER ON THE SAME LEVEL AN ERROR WILL OCCUR WHEN THE MANAGER_ID OF THE MANAGER IS NULL, SO WE NEED TO DEBUG THE FORM */

•

SELECT MANAGER_ID INTO :MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=:MANAGER_ID; COMMIT_FORM; END;

Prepared by: Eng. Héba M. Noweir     

Oracle Forms Developer 10g: Build Internet Applications 

Note: The above code would probably cause a BUG F5 (Insert/Remove Breakpoint ) RUN from Debug Pause Step into/Step out [Trace the Program] After detecting the BUG, we can handle it Example: EXCEPTION WHEN NO_DATA_FOUND THEN MESSAGE('THIS MANAGER HAS NO MANAGERS');

Prepared by: Eng. Héba M. Noweir     

7‐3 

Oracle Forms Developer 10g: Build Internet Applications 

•

OBJECT GROUPS: Illustrative Example: (1) Create a new Form [DEPT_OG] – Data Block (Departments) (2) Create an Alert (3) Create a When-New-Form-Instance BEGIN SET_WINDOW_PROPERTY ('WINDOW1',WINDOW_STATE,'MAXIMIZE'); END; (4) Create a LOV for Locations (5) From the Object Navigator, Create an Object Group (6) Drag and Drop all what we have previously created [Window, Canvas, Trigger, Alert, LOV] into Object Group (7) Create a new form (8) Drag and Drop the Object Group of the 1st Form into this new Form Note: Subclass: If you change in the parent Object Group, all children will be changed

•

OBJECT LIBRARIES: (1) Create Object Library from Object Navigator MUST be saved (2) You can Drag & Drop any Object into Object Library

•

PL/SQL LIBRARIES: Illustrative Example: (1) Create a PL/SQL Library from Object Navigator and save it under any name (2) Create Program Units Æ Procedure [PROC_COMMIT] PROCEDURE PROC_COMMIT IS BEGIN COMMIT_FORM; END; (3) Compile & Save (4) Create a new Library Æ Program Units Æ Procedure [PROC_2] PROCEDURE PROC_2 IS BEGIN PROC_COMMIT; END; (5) Attach the 1st Library because the new one depends on it

Prepared by: Eng. Héba M. Noweir     

8‐1 

Oracle Forms Developer 10g: Build Internet Applications 

•

MENUS: (1) Create Menu from Object Navigator (2) Create Items (3) Investigate the Property Palette and Use the Help to find codes ¾ TASK: Create a Menu like Oracle Forms Menu

•

TEMPLATE FORMS: Illustrative Example: (1) Create a new Form (2) Create a new Data Block [CONTROL] manually on a Horizontal Toolbar Canvas (3) Add the Following Buttons: PL/SQL Code: a. Create_record; Æ ADD b. Delete_record; Æ DELETE c. Commit_form; Æ SAVE d. First_record; Æ FIRST e. Previous_record; Æ PREVIOUS f. Next_record; Æ NEXT g. Last_record; Æ LAST h. Enter_query; Æ ENTER i. Execute_query; Æ EXECUTE j. Clear_block; Æ CLEAR k. Exit_form; Æ EXIT Note: Investigate F4 Æ Tooltip (4) Create a Form Level Trigger (When-New-Form-Instance) PL/SQL Code: BEGIN SET_WINDOW_PROPERTY('WINDOW1',WINDOW_STATE,MAXIMIZE); END; (5) Create an Alert (6) Form Æ F4 Æ Menu Module [Choose the one you created in the last exercise] (7) Save this Template Form (8) Create an Object Group [OG_TEMPLATE] and Add everything you created in the previous steps (9) Create a new Form [BASED_ON_TEMPLATE] – Data Block (Departments) (10) Drag & Drop Object Group (11)SET_WINDOW_PROPERTY('WINDOW_NAME',TITLE,:SYSTEM.CURRENT_F ORM);

Prepared by: Eng. Héba M. Noweir     

8‐2 

Oracle Forms Developer 10g: Build Internet Applications  •

LOGON FORMS: (1) In iSQLplus Environment, Create the following table: Create Table USERS (USER_NAME VARCHAR2(20) PRIMARY KEY, NAME VARCHAR2(100), PASSWORD VARCHAR2(30), SECURITY_LEVEL VARCHAR2(1)); (2) Create a new Form [USERS_TAB] – Data Block (USERS) (3) Security_Level: Static List_item Level A Æ A Level B Æ B Level C Æ C (4) Password: Conceal Data (5) Create a new Data Block (Control) manually on a new Canvas (6) Create 2 Text Items (Non Database Items): Username Password U_USER U_PASS (7) Create 2 Buttons [B_LOGON] & [B_EXIT] (8) Create a When-Button-Pressed Trigger on B_EXIT PL/SQL Code: BEGIN EXIT_FORM; END; (9) Create a When-Button-Pressed Trigger on B_LOGON PL/SQL Code: DECLARE V_COUNT NUMBER; V_PASS VARCHAR2(50); BEGIN SELECT COUNT(*) INTO V_COUNT FROM USERS WHERE UPPER(USER_NAME)=UPPER(:U_USER); IF V_COUNT=1 THEN SELECT PASSWORD INTO V_PASS FROM USERS WHERE UPPER(USER_NAME)=UPPER(:U_USER); IF V_PASS=:U_PASS THEN MESSAGE ('WELCOME ||:U_USER'); NEW_FORM('30_USERS'); ELSE MESSAGE ('INVALID USERNAME OR PASSWORD'); CLEAR_BLOCK; END IF; ELSE MESSAGE ('INVALID USERNAME OR PASSWORD'); MESSAGE ('INVALID USERNAME OR PASSWORD'); CLEAR_BLOCK; END IF; END;

Prepared by: Eng. Héba M. Noweir   

9‐1 

Oracle Forms Developer 10g: Build Internet Applications 

9‐2  •

WEBUTIL: -

-

-

¾

WebUtil is a free extensible utility that enables you to interact with the client machine Although WebUtil uses Java classes, you code in PL/SQL You integrate WebUtil into a form by attaching its PL/SQL library and using an object group from its object library; then you can use its functions after the form has started and while it is running With WebUtil, you can do the following on the client machine: open a file dialog box, read and write image or text files, execute operating system commands, perform OLE automation, and obtain information about the client machine To install WebUtil, Follow the instruction of the word file downloaded from the Internet

TASK: (1) Create Form – Data Block (Control) manually (2) Create an image in the Canvas F4 Æ Data Base Item: No (3) Create Button [B_image] – When-Button-Pressed-Trigger PL/SQL Code: DECLARE V_FILE VARCHAR2(500):= CLIENT GET_FILE_NAME('','','GIF FILES| *.GIF|JPEG FILES|*.JPG|ALL FILES|*.*','SELECT IMAGE TO UPLOADED',OPEN_FILE,TRUE); V_IMAGE ITEM:=FIND_ITEM('EMPS.IMAGE'); BEGIN CLIENT_IMAGE.READ_IMAGE_FILE(V_FILE_NAME,'',V_IMAGE); END; (4) Image Æ F4 Sizing Style: Adjust Data Base Item: No Mouse Navigate: No Keyboard Navigate: No

Prepared by: Eng. Héba M. Noweir   

Oracle Forms Developer 10g: Build Internet Applications 

•

UPLOADING BLOB IMAGE: (1) Create Table Emps as select employee_id, last_name from employees where department_id=30; (2) Alter Table Emps add (image BLOB) [I am supposed to fill this table with images] (3) Forms Builder: a. Create Form – Data Block (Emps) b. Upload-image-to-table c. Drag & Drop Object Group from Object Library d. Attach PL/SQL Library (WebUtil.pll) – SubClass WebUtil.olb e. Canvas of WebUtil [Invisible] (4) Create Button [B_image] – When-Button-Pressed-Trigger PL/SQL Code: DECLARE V_FILE VARCHAR2(500):= CLIENT GET_FILE_NAME('','','GIF FILES| *.GIF|JPEG FILES|*.JPG|ALL FILES|*.*','SELECT IMAGE TO UPLOADED',OPEN_FILE,TRUE); V_IMAGE ITEM:=FIND_ITEM('EMPS.IMAGE'); BEGIN CLIENT_IMAGE.READ_IMAGE_FILE(V_FILE_NAME,'',V_IMAGE); END; (5) Image Æ F4 Sizing Style: Adjust Data Base Item: No Mouse Navigate: No Keyboard Navigate: No

•

ICONIC BUTTONS: (1) Edit Oracle_DevSuiteHome/Forms/Server/formsweb.cfg [imageBase=CodeBase] ‘This step is done once’ (2) Put the gif files you want to use in your Forms in Oracle_DevSuiteHome/Forms/Java (3) Create a button on a canvas F4 Æ Iconic: Yes Icon Filename: [simply write the name of the file without extensions]

•

OPENING FORMS IN SEPARATE WINDOWS: Edit: Oracle_DevSuiteHome Æ FormsÆ ServerÆ formsweb.cfg separateFrame=true colorScheme=teal,blaf,red,blue width=750 height=600 background= lookAndFeel=Oracle logo=

Prepared by: Eng. Héba M. Noweir     

10‐1

Oracle Forms Developer 10g: Build Internet Applications 

•

10‐2 INSTALLING JAVA BEANS: Assuming that you have your JBeans in a folder called “Demos_Java” 1st Example: [Rollover Button] (1) Demos_Java Æ Classes Æ Copy all jar files and paste them in Oracle_DevSuiteHome/Forms/Java (2) Copy the name of jar files and append it to: [archive_jini] Oracle_DevSuiteHome/Forms/Server/formsweb.cfg (3) Copy the full path of jar file Oracle_DevSuiteHome\forms\java\ rolloverbutton.jar And append it to: [CLASSPATH=] Oracle_DevSuiteHome/Forms/Server/default.env (4) Create a button on a canvas F4 Æ Label: [ROLLOVER]exit | [ROLLOVER]add | [ROLLOVER]help Implementation Class: [Bring the name of the class & replace each / by .] oracle.forms.demos.enhancedItems. RolloverButton 2nd Example: [calendarpjc] (1) Demos_Java Æ Classes Æ Copy all jar files and paste them in Oracle_DevSuiteHome/Forms/Java (2) Copy the name of jar file and append it to: [archive_jini] Oracle_DevSuiteHome/Forms/Server/formsweb.cfg (3) Copy the full path of jar file Oracle_DevSuiteHome\forms\java\ calendar.jar And append it to: [CLASSPATH=] Oracle_DevSuiteHome/Forms/Server/default.env (4) Demos_Java\lib Æ Copy all files and paste them in MyForms Folder (5) Open all olb files from Forms Builder (6) Open Calenderj90 Form Æ Run Form [Don’t Forget to attach Libraries] (7) Create a new Form – Data Block (Employees) (8) Drag & Drop When-New-Form-Instance Trigger from Calendar Block into the new Form (Copy) (9) Repeat step (8) for PJC Block (Copy) (10) Repeat step (8) for Program Units (Copy) (11) Delete Datefield from PJC Data Block (12) Edit the Trigger in PJC Data Block – Replace :PJC.DATEFIELD By :EMPLOYEES.HIRE_DATE (13) Keep only the last 2 comments in the Form-Level When-New-Form-Instance ¾ Try FileUpload JBean Note: If there are pll files in the folder put them into MyForms Any pll file must be compiled

Prepared by: Eng. Héba M. Noweir