Resources » Articles/Knowledge Sharing » Placement Papers


Posted Date: 11-Mar-2008  Last Updated:   Category: Placement Papers    
Author: Member Level: Gold    Points: 5

1 What are the different modules of FORMS 4.5 ?
Form, Menu and Library modules.
2 What are the differences between FORMS 3.0 and FORMS 4.5 ?
1] Mode Character GUI
2] System Variables 20 42
3] Session Single Multiple
4] Blocks, Canvas 255 No limit
5] Triggers 75 121
6] Items types - 10
7] Relation Design Time Run Time
8] Inheritance Not Available Available
9] Property Class - DO - - DO -
10] Object Groups - DO - - DO -
11] Library - DO - - DO -
12] Attached Libraries - DO - - DO -
13] Visual Attributes - DO - - DO -
3 What is the maximum limit for creating BLOCKS and CANVAS in FORMS 4.5 ?
4 In a MULTI RECORD BLOCK can we have an item displayed once ?
Yes, By setting the ITEMS DISPLAYED property for the respective item in the Properties Window.
5 After creation of the block can we create a BUTTON PALETTE ?
6 What are the different types of triggers that will be created for MASTER DELETES properties such as a] ISOLATED, b] NON - ISOLATED, c] CASCADING ?

Master Deletes Property Resulting Triggers

NON - ISOLATED On-Check-Delete-Master

CASCADING On-Clear-Details

ISOLATED On-Clear-Details
7 What is the difference between creating a MASTER-DETAIL relationship in NEW BLOCK window and creating relations after creation of the blocks ?
When you create the relation in the NEW BLOCK window, Oracle forms also alters the properties of the FOREIGN KEY items(s) in the DETAIL BLOCK by doing the following:
• Setting the CANVAS property to NULL to make the item a NULL CANVAS item.
• Setting the following properties to FALSE
• Sequencing items in the Navigator such that the FOREIGN KEYITEMS are last in the block’s navigation sequence.
• Boiler plate text label for the FOREIGN KEY ITEM is not created.
8 How to set RELATION properties Dynamically ?
Using the following properties:
SET_RELATION_PROPERTY(relation_id, property, value);
SET_RELATION_PROPERTY(relation_name, property, value);
9 How many types of ITEMS are there in FORMS 4.5 ?
10 items ( Text Item, Display Item, List Item, Image Item, Chart Item, VBX, OLE, Check Box, Radio Button, Push Button ).
10 What are the different types of LIST ITEMS and the triggers associated with them ?
1] Pop list, 2] Text list, 3] Combo box.
1] When-List-Activated
2] When-List-Changed
11 Can we change the color of the PUSH BUTTON ?
Note: When we make the ICONIC property of the PUSH BUTTON as TRUE, then we can change the Color of the button. But adding text to the button is not possible.
12 What is the significance of the OTHER VALUES property in CHECK BOX ?
You can specify that a CHECK BOX can handle the OTHER VALUES property in one of the following ways:
• Reject Other Values as NOT ALLOWED.
• Display Other Values as the CHECKED STATE
• Display Other Values as UNCHECKED STATE
13 What is a RADIO GROUP ? Is dummy radio group necessary ? What is the associated trigger ? How to DISABLE a radio button dynamically ?
A RADIO GROUP is an interface control that displays a fixed number of options that are MUTUALLY EXCLUSIVE. Each option is represented by an individual radio button.
Yes. When-Radio-Changed Trigger.
Use Set_Radio_Button_Property to DISABLE a radio button.
14 What is the difference between TEXT ITEM & DISPLAY ITEM ?
Navigable TRUE FALSE
Memory More Less
What is the differnce between SINGLE LINE TEXT ITEM and MULTILINE TEXT ITEM ?
Scroll Bar NO YES
Wrap Style NO YES
Secure YES NO
Auto Skip YES NO
Format Mask YES NO
16 How to invoke LOV dynamically ?
Using SHOW_LOV built-in
SHOW_LOV(lov_id, x, y);
SHOW_LOV(lov_name, x, y);
17 What does LOV FOR VALIDATION property do when it is set to TRUE ?
When LOV for Validation is True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs:

• If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally.

• If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to
automatically reduce the list.
18 What is the difference between LIST_VALUES and SHOW_LOV ?
LIST_VALUES displays the list of values for the current item, as long as the input focus is in a text item
that has an attached LOV. The list of values remains displayed until the operator dismisses the LOV or
selects a value.

By default, LIST_VALUES uses the NO_RESTRICT parameter. This parameter causes Oracle Forms
not to use the automatic search and complete feature. If you use the RESTRICT parameter, Oracle
Forms uses the automatic search and complete feature.

Displays a list of values (LOV) window at the given coordinates, and returns TRUE if the operator selects a value from the list, and FALSE if the operator Cancels and dismisses the list.

Note: You must attach a LIST_VALUES built-in to the text item. But it is not necessary to attach SHOW_LOV to the text item.
19 What is a RECORD GROUP ? Different types of record groups ? How to create QUERY RECORD GROUP Dynamically ?
A RECORD GROUP is an Internal Oracle Forms data structure that has a column/row frame work similar to a database table. TYPES: QUERY, NON-QUERY AND STATIC RECORD GROUP.
For creating QUERY RECORD GROUP dynamically use CREATE_GROUP_FROM_QUERY(recordgroup_name, query);
20 How many no. of columns that a RECORD GROUP can have ?
The total no. of columns should not exceed 64K.
21 What does POPULATE_GROUP return when query suceeds ?
NUMBER i.e 0 When it suceeds.
22 How to change an LOV from one record group to another record group ?
SET_LOV_PROPERTY(lov_id, property, value);
SET_LOV_PROPERTY(lov_name, property, value);
SET_LOV_PROPERTY(lov_id, property, x, y);
SET_LOV_PROPERTY(lov_name, property, x, y);
Ex : Set_LOV_Property(lov_id,GROUP_NAME,'NEW_GROUP_NAME');
23 What are different types of CANVASES ?
The Raise on Entry property of canvas-views determines how stacking order is affected by navigation to items on those views:
• When Raise on Entry is False, Oracle Forms raises the canvas-view only if the target item is hidden behind another canvas-view in that same window.
• When Raise on Entry is True, Oracle Forms raises the canvas-view to the front of the window whenever the operator or the application navigates to an item on that view. Be careful about
setting Raise on Entry to True for a content canvas-view; Because a content view occupies the
entire content area of its window, it will always obscure any stacked canvas-views when it is
raised to the front of the view stack.
25 What is a CONTENT VIEW ?
A CONTENT CANVAS VIEW is the “BASE” view that occupies the entire CONTENT pane of the window on which it is placed.
What is the difference between SHOW_VIEW and REPLACE_CONTENT_VIEW ?
SHOW_VIEW(view_id); SHOW_VIEW(view_name);
REPLACE_CONTENT_VIEW(window_id, view_id);
REPLACE_CONTENT_VIEW(window_name, view_id);
REPLACE_CONTENT_VIEW(window_id, view_name);
REPLACE_CONTENT_VIEW(window_name, view_name);

SHOW_VIEW displays the view at the specified display co-ordinates, whereas REPLACE_CONTENT_VIEW displays the view at the location of the prevoiusly displayed content view. The advantage is REPLACE_CONTENT_VIEW will not obscure any stacked view displayed before calling REPLACE_CONTENT_VIEW.
27 What are the different types of WINDOWS ?
28 How to display a WINDOW programmatically ?
Use SHOW_WINDOW, SET_WINDOW_PROPERTY to display window programmatically.
SHOW_WINDOW(window_id, x, y);
SHOW_WINDOW(window_name, x, y);
SET_WINDOW_PROPERTY(window_id, property, value);
SET_WINDOW_PROPERTY(window_id, property, x);
SET_WINDOW_PROPERTY(window_id, property, x, y);
SET_WINDOW_PROPERTY(window_name, property, value);
SET_WINDOW_PROPERTY(window_name, property, x);
SET_WINDOW_PROPERTY(window_name, property, x, y);
29 To which type of window REMOVE ON EXIT property is meaningful ?
DOCUMENT WINDOW ( Modeless Window)
30 To which type of window SCROLL BAR doesn’t apply ?
DIALOG WINDOW ( Modal Window )
31 How to SCROLL a window dynamically ?
SCROLL_VIEW(view_id, x, y);
SCROLL_VIEW(view_name, x, y);
SET_VIEW_PROPERTY(view_id, property, value);
SET_VIEW_PROPERTY(view_id, property, x, y);
SET_VIEW_PROPERTY(view_name, property, value);
SET_VIEW_PROPERTY(view_name, property, x, y);
32 How to RESIZE a Window ?
RESIZE_WINDOW(window_id, width, height);
RESIZE_WINDOW(window_name, width, height);
SET_VIEW_PROPERTY(view_id, property, value);
SET_VIEW_PROPERTY(view_id, property, x, y);
SET_VIEW_PROPERTY(view_name, property, value);
SET_VIEW_PROPERTY(view_name, property, x, y);
33 How to increase the SIZE of the window dynamically ?
SET_WINDOW_PROPERTY(window_id, property, value);
SET_WINDOW_PROPERTY(window_id, property, x);
SET_WINDOW_PROPERTY(window_id, property, x, y);
SET_WINDOW_PROPERTY(window_name, property, value);
SET_WINDOW_PROPERTY(window_name, property, x);
SET_WINDOW_PROPERTY(window_name, property, x, y);
34 What are the window event triggers ?
35 What is a CONSOL WINDOW ? On what window it will be displayed ? Can we change the consol window at run time ?
A CONSOL WINDOW includes the STATUS LINE and MESSAGE LINE and is displayed at the bottom of the window to which it is attached.
On MS-WINDOWS the CONSOL WINDOW is always displayed on the MDI application window and cannot be displayed on individual document or dialog windows in the form.
36 Define a] PROPERTY CLASS , b] VISUAL ATTRIBUTES ? What are the differences between property class and visual attributes ?
A PROPERTY CLASS is a named object that contains a list of properties and their settings.
A VISUAL ATTRIBUTE is a separate object that defines a set of visual attribute settings.
Property classes are similar to named visual attributes, but there are important differences you should
be aware of:
• Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.
• You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically.
• When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are
37 How to change a VISUAL ATTRIBUTE dynamically ?
38 If I change a property settings of a field which is attached to a visual attribute what will happen ?
The VISUAL ATTRIBUTE name in the properties window for that item will become CUSTOM.
39 What is a LIBRARY ?
A library is a collection of subprograms, including user-named procedures, functions and packages.

Libraries provide a convenient means of storing client-side program units and sharing them among
multiple applications. Once you create a library, you can attach it to any other form, menu, or library module. Then, you can call library program units from triggers, menu item commands, and user-named routines you write in the modules to which you have attached the library.

The same library can be attached to multiple forms and menus. Conversely, a single form or menu can
have more than one attached library. Libraries can also be attached to other libraries. When a library attaches another library, program units in the first library can reference program units in the attached library.
Libraries support dynamic loading --that is, a library's program units are loaded into an application only
when needed. This can significantly reduce the runtime memory requirements of an application.
40 What is the difference between PROGRAM UNITS and ATTACHED LIBRARIES ?
A User-named subprogram is a named PL/SQL function or procedure that you write in a form , menu or library module.
- A user -named subprogram defined in a form module can be called only from triggers and other user-named subprograms in the same module.
- A user-named subprogram defined in the menu module can be called only from menu item commands and startup code in that menu module.
- A user-named subprogram defined in a library module can be called from any trigger or menu item command, provided that the library is attached to the form or menu module.
41 What type of references can I use in ATTACHED LIBRARIES ?
Use NAME_IN sub program to refer to the values of bind variables and use COPY sub program procedure to set values for the bind variables.
Ex: NAME_IN(‘block_name.item_name’), NAME_IN(‘GLOBAL.variablel_name’),
NAME_IN(‘SYSTEM. variable _name).
COPY(‘’28876’,’’), COPY(‘standard’,’GLOBAL.variable_name’)
42 What are the different types of MENU ?
43 How to attach a menu to a form ?
In the forms set the MENU MODULE PROPERTY to the respective menu file name.
44 How to replace MENU’s DYNAMICALLY ?
REPLACE_MENU(menu_module_name, menu_type);
REPLACE_MENU(menu_module_name, menu_type,
starting_menu_name );
REPLACE_MENU(menu_module_name, menu_type,
starting_menu, group_name);
REPLACE_MENU(menu_module_name, menu_type,
starting_menu, group_name, use_file);
45 What are all the different types of MENU ITEMS ?
46 In which platform BACKROUND MENU is supported ?
47 What are the different types of codes that we write in MENUS ?
48 What are the way of referencing you will be using while writing the codes in menus ?
Use NAME_IN and COPY for referencing objects in menu commands.
49 What is an ALERT ? How to change an alert message dynamically ? What type of an window is an alert ? What are the maximum no. of characters that an alert have ?
An ALERT is a MODAL WINDOW that displays a message notifying the operator of some application condition.
Use SET_ALERT_PROPERTY to change the alert message.
Syntax: SET_ALERT_PROPERTY(alert_id/name,ALERT_MESSAGE_TEXT,’Good choice’).
ALERT is a MODAL WINDOW. Maximum no. of characters allowed in alert is 200.
50 What is FREEZE / UNFREEZE ?
Toggles properties window synchronization On and Off. When FREEZE is Off ( the Default ), the property list is updated to display the properties of objects you select in the Navigator and Windows. When FREEZE is On, the property list is pinned and does not get update, allowing you to compare it to other property lists.
51 What do you mean by COPYING and REFERENCING ?
COPYING creates a new and separate instance of the object in the target module.
REFERENCING creates a new object that maintains a link to the source object
52 When you COPY a REFERENCED object what will be the resultant object ?
COPYING a REFERENCED object creates a new and separate instance of the referenced OBJECT.
The resultant is a REFERNCED object.
53 How to reuse the PL/SQL codes ?
For reusing PL/SQL codes we have to either REFERENCE or COPY the PL/SQL codes defined in the SOURCE object.
Go for Program Units ( Procedures & Functions ) - Right Answer. ( Libraries )
54 How to use DDL statements in the FORMS 4.5 ?
Use FORMS_DDL( Statemnt)
Statement Any string expression up to 32K:
• a literal
• an expression or a variable representing the text of a block of dynamically created
PL/SQL code.
• a DML statement or
• a DDL statement
55 How many SYSTEM VARIABLES are there in FORMS 4.5 ?
42 System Variables.
56 What does the SYSTEM_MOUSE_BUTTON_PRESSED variable do ?
SYSTEM.MOUSE_BUTTON_PRESSED indicates the number of the button that was clicked. Mouse
button support is limited to buttons 1 and 2 (left or middle) on a three button mouse. The value is always a character string.
57 What is the SYSTEM VARIABLE that is used to determine the CURRENT MODE ?
SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string.
58 What are the various categories of triggers ? Explain each in detail ?
1] Block-processing-triggers, 2] Interface-event-triggers, 3] Master-detail-triggers,4] Message-handling-triggers,5] Navigational-triggers,6] Query-time-triggers,7] Transactional-triggers,8] Validational-triggers.
59 What are different types of EDITORS ?
60 What is the difference between EDIT_TEXTITEM and SHOW_EDITOR ?
Invokes the Runform item editor for the current text item and puts the form in Edit mode.
EDIT_TEXTITEM(x, y, width, height);
• The input focus must be in a text item.
• The Width must be at least wide enough to display the buttons at the bottom of the editor
Displays USER-NAMED editor at the specified display co-ordinates.
SHOW_EDITOR(editor_id, message_in, message_out, result);
SHOW_EDITOR(editor_id, message_in, x, y, message_out, result);
SHOW_EDITOR(editor_name, message_in, message_out, result);
SHOW_EDITOR(editor_name, message_in, x, y, message_out, result);
The result parameter is a BOOLEAN IN OUT parameter. If the operator accepts the editor, SHOW_EDITOR sets result to TRUE, and sets message_out to the current text string. If the operator cancels the editor, SHOW_EDITOR sets the result to FALSE and set the message_out to NULL.
When an editor is displayed at run time, Oracle forms is in EDIT MODE, and no triggers fire until the operator returns to the Normal mode by dismising the editor.
61 What are the triggers that are valid in ENTER_QUERY mode ?
The following triggers are valid in enter_query mode.
1] Key-*
2] On-error
3] On-message
4] When-triggers except
62 What does the property called SECURE ? What will happen when it is set to true ?
Used to hide the value stored in a single-line text item by designating the item as secure. For example, a password text item can be made secure so that operator input does not echo to the screen.
63 What do you mean by OWNERSHIP view and VISUAL view ?
Ownership View In the ownership view, all form objects are visible, and the display hierarchy
corresponds to the Oracle Forms object ownership hierarchy: form--block--item. Items and relations are owned by blocks; blocks are owned by forms; triggers can be owned by forms, blocks, or items; all
other form objects (windows, editors, record groups, etc.) are owned by forms.

Visual View In the Visual view, only windows, canvas-views, and items are displayed. The Visual
view hierarchy corresponds to the hierarchy of objects in a form window: window--canvas-view--item.
Items are assigned to canvas-views; canvas-views are assigned to windows. Each window can have
multiple canvas-views, and there can be multiple items on a single canvas-view.
64 How to change the DEFAULT_WHERE clause and ORDER BY clause dynamically ?
SET_BLOCK_PROPERTY(block_id, property, value);
SET_BLOCK_PROPERTY(block_name, property, value);
65 How to modify the default NAVIGATION sequence ?
SET_BLOCK_PROPERTY(block_id, property, value);
SET_BLOCK_PROPERTY(block_name, property, value);
66 If I have a PRIMARY and FOREIGN KEY in the table and If I On the CONSTRAINT option what trigger will be created ?
67 If it is a COMPOSITE KEY what type of trigger that will be created ?
1] KEY-DELREC [ Block Level ]
2] WHEN-VALIDATE-RECORD [ Record Level ]
68 If I have a PRIMARY KEY in the table and while creating a block and If I On the constraint property what trigger will be created ?
1] KEY-DELREC [ Block Level ]
2] WHEN-VALIDATE-ITEM [ Item Level - Dept No ]
69 If you are using PROCEDURES that are created in a] LIBRARY, b] DATABASE, c] FORM level, Which will be advantageous ?
70 What are the properties of the ITEMS that you can validate without writting triggers ?
Datatype, Required, Fixed Legth, Maximum Length, Range Low Value / High Value.
71 Explain in detail about DEBUGGER ?
72 What are the objects that I cannot create in the object navigator ?
Boiler Plate Graphics Objects - Text, Line, Square, Oval, Freeform etc.,
73 Give a live example where you can use non-query record group ?
a] When you want to dynamically sent the data from Forms to Reports.
b] When you want to validate a column to maintain Integrity Constraint at Form level. ( i.e before commit.)
74 What are the values that a WHERE clause can reference in LOV ?
a] Item Names, b] parameter, c] Global Variables.
75 What are the types of items that are always CONTROL items ?
Text Item, List Item, Image Item, Check Box, Push Button.
76 What are the different types of CUSTOM ITEMS and the Triggers associated with them ?
77 What are the differences between FORMS 3.0 & FORMS 4.5 which regards to MASTER - DETAIL relationship: Explain all the properties of MASTER - DETAIL in detail ?

Reports 2.5 Questions & Answers
What are the different styles of Reports ?
Tabular, Form. Form Letter, Master/Detail, Matrix, Mail Label
Name the SYSTEM VARIABLES that are available in Reports 2.5 ?
11 System Parameters.
a] Backround, b] Copies, c] Currency d] Decimal e] Desination Format, f] Destination Name
g] Destination type, h] Mode, i] Orientation, j] Print job and k] Thousands.
Name the Objects that are created Under the Data Model Node in Reports 2.5 ?
9 Objects come under Data Model Editor node.
System parameters, User Parameters, Queries, Groups, Database Columns, Formula Columns, Placeholder Columns, Summary Columns and Data Links.
Name the Objects that are created Under the Layout Editor Node in Reports 2.5 ?
4 Objects come under the Layout Editor node.
Header, Trailer, Body and Margin.
Name the Objects that are created Under the Parameter Form in Reports 2.5 ?
4 Objects come under the Parameter Form editor node in reports 2.5.
Fields, graphics Boiler Plate, text Boiler Plate and Image boiler Plate.
Name the Objects that are created Under the Reports trigger Node in Reports 2.5 ?
Before Parameter Form, After Parameter Form, Before Repor Trigger, Between Pages and After Report Trigger.

How many groups are required for a MATRIX REPORT ?
4 Groups.
What are the steps in creating a PAGEWISE SUMMMARY COLUMN ?
An External query is an Oracle Report Module that is an ANSI - standard SQL SELECT statement that can be used in more than one report or Oracle Product.
Through which object you are establishing LINKS BETWEEN GROUPS ?
Data Link Object - It establishes a relationship between the data of two queries.
a] A formula is a computation performed on a single record spanning one or more columns.
b] A Place holder is a “dummy” column for which you can set the Data type & value via PL/SQL or a
user exit. They are useful when you want to selectively populate a column wih a value.
c] A summary is a computation that is performed on all records of a single column.
What is an ANCHOR ?
Anchors determine the relative position of objects in the report output by attaching one layout object, called the child, to a second called the parent.
What is a PARAMETER ? Different types of parameter ? Trigger associated wih the Parameter ?
Parameters are Report variables to which users can assign values at runtime.
a] Bind Parameter [ : ], b] Lexical Parameter [ & ].
1] Before Parameter form
2] After Parameter form
Can we use DDL statements in Reports ? If so how ?
Yes. Using SRW.DO_SQL Package.
What is a DRILLDOWN Report ?
What is the trigger associated with PUSH BUTTON ?
Action Trigger.
What are the different types of REPORT TRIGGERS ? List out the sequence in which they fire ?
Can I use DML statements in FORMAT trigger ?
What is the significance of GROUP FILTER trigger ?
With this we can restrict the Rows retrieved from the Database when the query is executed.
How to control the no of records retrieved ?
Can we refer to column values in REPORT triggers ?
Yes - Check.
In the table I am having a value for ENAME as ‘ALLEN’ But I want it to be printed as ‘XXX’ in the previewer screen, printer. Is it possible ? If so how ?

I want 3 records to be displayed for each page What should I do ?
For achieving this you have to set the RECORDS DISPLAYED PRE PAGE property to 3.
How to integrate REPORTS 2.5 wih FORMS 4.5 ? How to pass parameters from FORMS to REPORTS ?
With the help of RECORD GROUPS.

1. What is a View ? Why is it required to define a View ?
A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
Advantage: 1. Security 2. Complex query can be replaced.

2. Can we create a View without a table ?
Yes, Using the FORCE option in the CREATE VIEW syntax.
Ex: CREATE FORCE VIEW view_name as SELECT column name,columnname..
FROM table_name;

3. What is the difference between a SYNONYM and a VIEW ?
A SYNONYM is a name assigned to a table or view that may thereafter be used to refer it. If you access to another user’s table, you may create a synonym for it and refer to it by the synonym alone, without entering the user’s name as a qualifier.
A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

Difference: A View can be based on MULTIPLE Tables whereas a SYNONYM is based on a single object only.

4. What is SNAPSHOT ? What is a SNAPSHOT LOG ?
A SNAPSHOT is a means of creating a local copy of remote data. A snapshot can be used to replicate all or part of a single table, or to replicate the result of a query against multiple tables. The refreshes of the replicated data can be done automatically by the database ( at time intervals you specify ) or manually.Snapshot Log is the table associated with the Master Table of the Snap shot.

5. What is a DATABASE trigger ? What is a DATABASE Procedure ?
A DATABASE TRIGGER is a stored procedure associated with a table that ORACLE7 automatically executes on one or more specified events (BEFORE or AFTER an INSERT,UPDATE or DELETE) affecting the table. Triggers can execute for the table as a whole or for each affected row in the table.
A PACKAGED PROCEDURE is a built-in PL/SQL procedure that is available in all forms. Each packaged procedure executes a SQL*FORMS function, such as moving to a field or executing a query.

6. How to show MESSAGES in PROCEDURES for debugging purposes ?
DBMS_OUTPUT_PACKAGE allows you to use 3 debugging functions within your package. You must use “SET SERVER OUTPUT ON” before executing the procedure object you will be debugging.
PUT - Puts multiple O/P’s on same line.
PUT_LINE Puts each O/P on a separate line.
NEW_LINE Used with PUT; Signals the end of current O/P line.

7. What is the difference between DATABASE trigger and DATABASE procedure ?
DATABASE triggers are executed automatically in response to specific events. But the DATABASE procedures are to be explicitly invoked to execute the code contained in them.

8. What is a CURSOR ?
A work area in memory where ORACLE stores the current SQL statement. For a query , the area in memory also includes column headings and one row retrieved by the SELECT statement.

9. What are the attributes of IMPLICIT CURSOR ?






10. Can we pass a PARAMETER to CURSOR ? What is SQL%ROWCOUNT ?
We can pass parameter to CURSOR. Eg: OPEN CUSOR(‘VASAN’).
SQL%ROWCOUNT is used to count the number of rows returned by an SQL DML statement.It will return zero if the DML statement doesn’t return any row.

11. How to write a SQL statement that should have a best RESPONSE TIME ?
Use the ___________________ in the optimizer hint inorder to obtain a best response time. Use “FIRST_ROW” - Cost based Optimizer Hint.

12. What are OPTIMIZER HINTS ?
Specifies a hint string that Oracle Forms passes on to the RDBMS optimizer when constructing queries. Using the optimizer can improve the performance of database transactions.

13. What is the difference between %TYPE and %rowtype ?
%TYPE provides the datatype of a varible,constant or column. It is useful when you declare a variable that refers to a database column in the table.
%ROWTYPE attribute is based on a record variable that has the same structure as a row in a table or view or as a row fetched from a cursor.

14. Can we define structure like objects in PL/SQL ?
[ If the structure is what we define in ‘C’ then we can create objects of type structure using RECORD variable available in PL/SQL. ]

Yes, Using the PL/SQL tables. PL/SQL tables are temporary array like objects used in a PL/SQL block. PL/SQL tables can have one column and a primary key. The column data type can belong to any scalar data type, but the primary key must only belong to the type binary_integer.

15. Can we use a funtion inside an INSERT statement ?

16. What is TRUNCATE table ?
TRUNCATE table is a DDL command used to remove all the rows from the specified table or cluster instantly. Eg: TRUNCATE TABLE table_name;
Advantage over DELETING:
a] It is a DDL statement and generates NO ROLLBACK information.
b] Doesn’t fire the tables DELETE TRIGGER.
c] Truncating the master table of a snapshot doesn’t record any changes in the tables snapshot log.
d] It’s more convinient than dropping and recreating the table.
e] D/R invalidates the table’s dependent objects than truncating the object.
f] D/R requires you to REGRANT the privileges on the table while truncating doesn’t.
g] D/R requires you to RECREATE the INDEXES, INTEGRITY CONSTRAINTS, TRIGGERS and STORAGE PARAMETER while truncating doesn’t.

17. What is ROWID ? What are its components ?
ROWID is the logical address of a row, and it is unique within the database.The ROWID is broken into three sections: left,middle,, and right (corresponding to 00001F20,000C, AND 0001, just shown). The numbering is in hexadecimal notation.

The left section is the block in the file, the middle is the row sequence number within the block(numbering starts with 0, not 1), and the right is the file number within the database. Note that the file numbers are uniquewithin the whole database. The tablespace they are in is not relevant to the ROWID.

ROWID can be selected, or used in a where clause, but cannot be changed by an insert, update, or delete. However it can changeif the table it is in is exported and imported.

18. What is the differnce between REPLACE and TRASLATE ?
Syntax : REPLACE(string,if,then)
REPLACE replaces a character or characters in a string with 0 or more characters, if is a character or characters. Everytime it appears in a string, it is by the contents of then.

Syntax: TRANSLATE(string,if,then)
TRANSLATE looks at each character in string, and then checks if to see if that character is there, if it is, TRANSLATE notes the position in if where it found the character, and then looks the same position in then. Whatever character it finds there it substitutes the character in string

19. What is a LEVEL ?
LEVEL is a pseudo column, used with CONNECT BY. It is equal to 1 for a root, 2 for a child of root, 3 for a child of a child of a root and so on.

20. What is anonymous block in PL/SQL ?
The text of an Oracle Forms trigger is an anonymous PL/SQL block. It consists of
three sections :
• A declaration of variables, constants,cursors and exceptions which is optional.
• A section of executable statements.
• A section of exception handlers, which is optional.
--- declarartive statements ( optional )
--- executable statements ( required )
--- exception handlers ( optional )

21. Name any ORACLE defined EXCEPTION ?

22. Can we define our OWN EXCEPTION ? How to raise it ?
In the DECLARATION part define a variable of type exception. In the excecution part call the exception using RAISE exception_name. In the exception part handle the exception using WHEN exception_name.

23. What is a PRAGMA ?
It is a directive to the COMPILER, rather than a piece of executable code. Eventhough it appears in the program, it is not executable. It gives instructions to the compiler.

24. Difference between CHAR and VARCHAR2 ?
CHAR(size) - It is a fixed length character data, size characters long. It is padded with BLANKS ON RIGHT to the full length of size. DEFAULT - 1 bytes, MAXIMUM - 255 bytes.
VARCHAR2(size) - It is a varable length char string having a maximum of size bytes.
MAXIMUM - 2000 bytes.

25. What is a CURSOR FOR LOOP ?
The CURSOR FOR LOOP lets you implicitly OPEN a cursor, FETCH each row returned by the query associated with the cursor and CLOSE the cursor when all rows have been processed.

26. What are the possible CONSTRAINTS defined on a TABLE ?

PL/SQL is the language used for both client-side Oracle forms applications and server-side database triggers and stored procedures and there is a PL/SQl engine in both Oracle forms Runform and the Oracle7 Server.
This means that you can take advantage of application patitioning to execute application code on either the client or the server.
Application partitioning allows you to optimize performance and resource usage by storing and executing procedures either locally or at the server, which makes the most sense for your particular application and configuration.

28. Difference between a STORED PROCEDURE and a STORED FUNCTION ?
Unlike procedures, FUNCTIONS returns a VALUE to the caller. This value is returned thro’ the RETURN command/keyword within the function.
Functions don’t use the IN, OUT | IN OUT arguments, which are available for PROCEDURES.

Use EXECUTE Procedure_name command.

30. How to TRAP ERRORS in procedures ?
Use SHOW_ERRORS. this will display all the errors associated with the most recently created procedural object. This command will check the VIEW_ERRORS data dictionary for the ERRORS associated with the most recent compilation attempt for that procedural object.
SHOW_ERRORS will display the LINE and COLUMN NO. for each error, as well as the text of the error message. Eg: SELECT LINE, POSITION,TEXT FROM USER_ERRORS WHERE
NAME = ‘balance_check’ AND

NOTE: We can use ALL_ERRORS & DBA_ERRORS to view errors.

TRAPPING ERORS: DBMS_OUTPUT package allows you to use 3 debugging functions within your package. You must set ‘SERVER OUTPUT ON’ before executing the procedure object you will be debugging.
PUT - Puts multiple o/p’s on same line.
PUT_LINE - Puts each o/p on a separate line.
NEW_LINE - Used with PUT; Signals the END of current o/p line.

31. When do we get a MUTATING ERROR ?
This happens with TRIGGERS. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of VIEWS or TEMPORARY TABLES so the database is selecting from one while updating the other.

Use the DIABLE option in CREATE TABLE or ALTER TABLE or using

NOTE : For diabling REFERENTIAL INTEGRITY we have to include CASCADE option.

33. How to know what all CONSTRAINTS are present in a table ?
Using the USER_CONSTRAINTS view we can get the type of constaints declared on a table.
Use ALL_CONSTRAINTS to list the constraints on all of the tables that the user have access. DBA_CONSTRAINTS lists all of the constraints in the database.

34. What is MASTER - DETAIL relationship ? Can we write a master-detail relationship programs
without using the setings at design time. If so how ?
It is an association between TWO BASE TABLE blocks - a MASTER block and a DETAIL block. The relationship between the blocks reflects a PRIMARY KEY - FOREIGN KEY relationship between the tables on which the blocks are based.
Yes. Using the SET_RELATION property.

35. What does BUFFER RECORDS option and ARRAY SIZE parameter ?
ARRAY SIZE - Specifies the minimum no. of records that get fetched each time forms goes to the database.
BUFFER RECORDS - Specifies the minimum no of records that should be placed in memory when records are fetched from the database. Even if you specify a low value of 3, the minimum per form is slightly over 300.

36. During VALIDATION WHAT CHECKS are done with respective to FIELDS / ITEMS ?
1] Data type, 2] Maximum length, 3] Fixed length, 4] Required and
5] Range Low value / Range High value.

37. What is the difference between PRIMARY KEY and UNIQUE KEY ?
The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed.
In case of PRIMARY KEY columns entry of duplicate as well as NULL value is restricted.

38. What is the DIFFERENCE between PRE-QUERY and POST-QUERY ?
PRE-QUERY fires ONLY ONCE during EXECUTE-QUERY or COUNT-QUERY processing, just before Oracle Forms constructs and issues the SELECT statement to identify rows that match the query criteria.
POST-QUERY fires each time for records placed on the blocks list of records.

39. When do you use ON-DATABASE-RECORD triigger ?
Use an ON-DATABASE-RECORD to perform an action every time a record is first marked as an INSERT or UPDATE.
This trigger fires, as soon as Oracle Forms determines thro’ validation that the record should be processed by the next post or commit as an INSERT or UPDATE

40. What are RESTRICTED PACKAGED PROCEDURES ? Why are they restricted from using ?
Any PACKAGED PROCEDURE that affects the basic functions of SQL*FORMS is a RESRICTED PACKAGED PROCEDURE. You should use restricted packaged procedure only in KEY-TRIGGERS, USER-NAMED TRIGGERS that are invoked by KEY-TRIGGERS, and ON_NEW_FIELD_INSTANCE triggers. You should not use restricted packaged procedures in any of the following types of triggers.
• On-error,On-Database-Record,On-delete,On-insert,On-Lock,
• On-Message,On-New-Record,On-Remove-record,On-Update,
• On-Validate-Field, and On-validate-Record triggers.
• Post-Change triggers.
• Pre- and Post- Field, Pre- and Post- Record, Pre- and Post-Block, Pre- and Post-Form triggers.
• Pre- and Post-Query triggers.
• Pre- and Post-Insert, Pre- and Post-Update, Pre- and Post-Delete, Pre- and Post-Commit triggers.
• User-Named triggers that are invoked by any of the above triggers.

IMPLICIT CURSORS are automatically opened by issuing a SELECT statement. But the EXPLICIT cursors are to be opened using OPEN, fetching is done using FETCH and closing using CLOSE.

42. What is the difference between ROWID and ROWNUM ?
ROWID is the logical address of the row, whereas ROWNUM returns the sequence no. in which the row was retrieved when first feched from a table.

43. What is the RESULT of the statement ?
Result : 0, No rows will be selected.

44. How do you evaluate performance ?
Using SQL TRACE. It is an utility that can monitor and report on database performance when one or more queries are run against the database.
It is used to gather statistics when running the query (i.e) reports on CPU time spent on the query, the total no. of rows processed and statistics related to parsing and cache performance.

45. What will EXPLAIN PLAN give ?
It is an utility that shows how Oracle will access data for a given query. Use EXPLAIN PLAN to determine the effective way to write queries and decide whether to INDEX CERTAIN COLUMNS or TO USE CLUSTERS.
It shows :
1] The type of query processed; SELECT, INSERT,UPDATE or DELETE.
2] The cost assigned by the COST BASED OPTIMIZER if it is in use.
3] The steps that are necessary to return the data.
4] The internal operations that were performed for each step.
5] The object accessed for each step.

46. How do you analyse TKPROF ?
TKPROF filename.tra O/P file EXPLAIN = USR/PWD0

47. what parameter variables to be set to use TKPROF ?

48. How many types of lockings are there ?
5 types of locks.
To lock is to temporarily restrict other user’s access to data. The restriction is placed on such data is called “a lock”. The modes are SHARE, SHARE UPDATE,EXCLUSIVE,ROW SHARE AND ROW EXCLUSIVE. Not all locks can be acquired in all modes.

49. What is a SHARE LOCK ?
A SHARE lock is one that permits other users to query data, but not to change it.

50. What is a SHARE UPDATE LOCK ?
A SHARE UPDATE lock is one that permits other users to both query and lock data.

51. What is an EXCLUSIVE LOCK ?
An EXCLUSIVE LOCK is one that permits other users to query data, but not to change it. It differs from the SHARE lock because it does not permit another user to place any type of lock on the same data; several users may place SHARE locks on the same data at the same time.

With a ROW SHARE or SHARE UPDATE lock, no users can lock the whole table for exclusive access, allowing concurrent access for all users to the table. The two types of locks are synonymous, and SHARE UPDATE exists for compatibility with previous versions of ORACLE.
ROW EXCLUSIVE locks are similar to ROW SHARE but they prohibit shared locking, so only one user user may access the table at the same time.

53. What is a DEAD LOCK ?
A DEAD lock is a rare situation in which two or more user processes of a database cannot complete their tansactions.This occurs because each process is holding a resource that the other process requires (such as a row in a table) in order to complete.Although these situations occur rarely, ORACLE detects and resolves deadlocks by rolling back the work of one of the processes.

54. How do you analyse which resources has locked for what ?

55. How to kill a SESSION ?

56. What are USER_EXITS ?
It is an utility in SQL*FORMS for making use of HOST 3 GL languages for the purpose like ONLINE PRINTING etc.

57. When will you use the trigger WHEN-NEW-FORM-INSTANCE ?
At FORMS STARTUP Oracle navigates to the first navigable item in the first navigable block. This trigger fires after successful completion of any Navigational trigger (i.e) It will not fire if the control retuns to the CALLING FORM from the CALLED FORM.

Usage: For initialization at FORMS STARTUP.

58. What is an INDEX ? Why are indexes used in a table ?
INDEX is a general term for an ORACLE / SQL feature used primarily to speed execution an impose UNIQUENESS upon certain data. INDEX provides a faster access method to one table’s data than doing a full table scan. There are several types of Indexes :
UNIQUE INDEX, COMPRESSED INDEX, CONCATENATED INDEX. An Index has an entry for each value found in the table’s Indexed field(s) ( except those with a NULL value ) and pointer(s) to the rows having that value.

59. What is an UNIQUE INDEX ?
An UNIQUE INDEX ia an index that imposes uniqueness on each value in indexes. The index may be one column or concatenated columns.

60 What is an COMPRESSED INDEX ?
A COMPRESSED INDEX is an index for which only enough index information is stored to identify unique enrties; information that an index stores with the previous or following key is “compressed” (truncated) and not stored to reduce the storage overhead required by an index.

A CONCATENATED INDEX is one that is created on more than one column of a table. It can be used to guarentee that those columns are unique for every row in the table and to speed access to rows via those columns

62. What is a UNION, UNION ALL,INTERSECTION and MINUS operator ?
The UNION operator returns ALL DISTINCT ROWS selected by either query.
The UNION ALL operator returns ALL ROWS selected by either query including duplicates.
The INTERSECTION operator returns ONLY ROWS that are COMMON to both the queries.
The MINUS operator returns ALL DISTINCT ROWS selected only by the first query and not by the second.

63. What does ‘GROUP BY’ statement do ?
GROUP BY statement causes a SELECT statement to produce ONE SUMMARY ROW for all selected rows that have identical values in one or more specified column or expressions. Each expe\ressionin the SELECT clause must be one of the following :
2] A Function without parameters
3] A GROUP function like SUM , AVG.
4] Matched IDENTICALLY to a expression in the ‘GROUP BY’ clause.

64. In 2 SELECT statements SELECT A FROM DUAL; and SELECT B FROM DUAL; What will be
the difference in using ‘UNION’ and ‘UNION ALL’ ?
UNION returns all distinct rows selected by either of the query, whereas UNION ALL returns ALL ROWS selected by either query including duplicates.

64. Give one example where you will use DATABASE TRIGGERS ?
For AUDITING purposes we use database triggers.

65. Do you have any idea about ROW-CHAINING ? How will you resolve the issue if there is row-
chaining in a table ?
When a row NO LONGER FITS WITHIN THE DATABLOCK, it is stored in more than one database block, and that therefore have several row pieces.
Resolving: Use ANALYZE to identify chained rows and also provides statistics on the chained rows. Eg: ANALYZE ledger LIST CHAINED ROWS INTO CHAINED_ROWS:
(CHAINED_ROWS is a user defined table)
For creating chained_rows run the UTLCHAIN.SQL script.

66. What is an OPTIIMIZER ?
OPTIMIZER is an utility used to determine how to access data requested in the query by the USER or APPLICATION PROGRAM. The output of an optimizer is EXECUTION PLAN.

67. How OPTIMIZATION is done by the Oracle in case of a query ?
1] RULE based, and 2] COST based.

68. What is a] RULE based optimization, b] COST based optimization ?
RULE based optimization USES A FIXED SET OF RULES to determine how to access the data.
COST based optimization USES STASTISTICS STORED IN THE DATA DICTIONARY WITH CERTAIN RULES to determine how to access the data.
Two modes - a] ALL_ROWS, B] FIRST_ROW. With the help of ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS / FIRST_ROW, We can alter the modes of cost based optimizer.

Did you like this resource? Share it with your friends and show your love!

Guest Author: Michael S     27 Feb 2013
how to dynamically show/hide columns in oracle form child block and set_item_property hint text in programatically in multi record block is very informative. regards


Post Comment:

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)

    Type the numbers and letters shown on the left.

    Submit Article     Return to Article Index

    Awards & Gifts
    Active Members
    TodayLast 7 Daysmore...

    ISC Technologies, Kochi - India. Copyright © All Rights Reserved.