SQL,PL/SQL,D2K Interview Questions Part II

SQL,PL/SQL,D2K Interview Questions

Part II

Continued from Part I………………..
1)Name the five global report triggers
i. before report
ii. after report
iii. between pages
iv. before parameter form
v.after parameter form
As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.
Report Builder has five global report triggers. You cannot create new global report triggers. The trigger names indicate at what point the trigger fires:
Before Report Fires before the report is executed but after queries are parsed.
After Report Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle Office userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.
Between Pages Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Before Parameter Form Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
After Parameter Form Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

2) Name the different types of alerts
note , stop ,caution
3)call_form ( ), new_form ( )
call_form ( ) :-Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM
(formmodule_name VARCHAR2);
New_form( ) :-
Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM
(formmodule_name VARCHAR2);
4) system.mode->
SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string.
NORMAL Indicates that the form is currently in normal processing mode.
ENTER-QUERY Indicates that the form is currently in Enter Query mode.
QUERY Indicates that the form is currently in fetch processing mode, meaning that a query is currently being processed.
Usage Notes
When using SYSTEM.MODE to check whether the current block is in Enter Query mode, be aware that if testing from a When-Button-Pressed trigger in a control block, Enter Query mode will never be entered, because the control block is not the current block.
4)What are important new(Advanced) features of Forms 6.0.
5)Different types of list boxes.
6)What are record groups ?.
7)In case of a block with multiple records how to display records on the screen without using cursors ?.
8)Can we access a row in the first block from second by referencing block name along with the rowid ?.
9)In case of a block with multiple records how do check where a user entering a duplicate record well before saving it ?.
10)How to take care of concurrency ?.
9.0 Forms 4.5
9.1 Object Groups
1. What is an object group ?
An object group is a container for a group of obects. You define an object group when you want to package related objects, so that you can copy or reference them in another module.
2. What are the different objects that you cannot copy or
reference in object groups ?
Objects of different modules
Another Objects group
Individual block depndent objects
Program Units.
9.2 Canvas Views
3. What are different types of canvas views ?
There are four types of canvas views :
Content canvas views
Stacked canvas views
Horizontal tool bar
Vertical tool bar
4. Explain about Content Canvas Views
Most canvas views are Content-Canvas Views. It is the base view that occupies the entire content pane of the window in which it is displayed.
5. Explain about Stacked Canvas Views ?
A stacked canvas view is displayed in a window on top of, or ¡§stacked¡¨ on the content canvas view assigned to that same window. Stacked canvas view obscure some part of the underlying content-canvas view, and are often shown and hidden programmatically.
6. Explain about Horizontal/Vertical toolbar Canvas-Views ?
Toolbar canvas-views are used to create toolbar for individual windows. Horizontal toolbars are displayed at the top a window, just under the menubar. Vertical toolbars are displayed along the leftside of a window.

7. Name the functions used to get/set the canvas properties ?
get_view_property, set_view_property.
9.3 Windows
8. What is the relation between windows and Canvas Views ?
Canvas-Views are the background objects on which you place the interface items(text items, check boxes, radio groups etc.,) and boilerplate objects(boxes,lines,images, etc.,) that operators interact with as they run your form. Each canvas-view is displayed in a window.
9. What are the Different Modals of Windows ?
There are Two different modals of Windows are
Modeless Windows
Modal Windows
10. What are Modeless Windows ?
More than one modeless windows can be displayed at the same time, and operators can navigate among them if your application allows them to do so. On most GUI platforms, modeless windows can also be layered to appear either in front of or behind other windows.
11. What are Modal Windows ?
Modal windows are usually used as dialogs, and have restricted funtionality compared to modeless windows. On some platforms,for example, operators cannot resize, scroll, or iconify a modal window.
12. How do you display console on a window ?
The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned.
To specify that the console should be displayed, set the console window form property to the name of any window in the form, To hide the console, set the console window to
13. What is the Remove on Exit property ?
For a modeless window, it determines whether Oracle Forms hides the window automatically, when the operator navigates to an item in another window.
14. How many windows in a form can have Console ?
Only one window in a form can display the console, and you cannot change the console assignment at runtime.
15. Can you have more than one content canvas view attached
with a window?
Yes. Each window you create must have at least one content canvas-view assigned to it. You can also create a window that has multiple content canvas-views. At runtime, only one of the content canvas-views assigned to a window is displayed at a time.
16. What are the different Window Events activated at Runtime ?
When-Window-Activated
When-Window_Closed
When-Window_Deactivated
When_Window_Resized
Within these triggers, you can examine the build-in system variable SYSTEM.EVENT_WINDOW to determine the name of the window for which the trigger fired

9.4 Modules
17. What are the different types of Modules available in Oracle
Forms ?
There are three types of modules in Oralcle Forms :
Form Module - A collection of objects and code routines
Menu Module - A collection of Menus( a main menu and any number of submenu objects) and menu item commands that together make up an application menu.
Library Module - A collection of user-named procedures,functions, and packages that can be called from other modules in the application.
18. What are the default extensions of the files created by Forms
Module ?
.FMB Form Module Binary
.FMX Form Module Executable
19. What are the default extensions of the files created by Menu
Module ?
.MMB Menu Module Binary
.MMX Menu Module Executable
20. What are the default extensions of the files created by
Library Module ?
.PLL PL/SQL Library Module Binary

9.5 Master - Detail
21. What is Master Detail Relationship ?
A Master-detail relationship is an association between two base table blocks - a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.
22. What is coordination Event?
Any event that makes a different record in themaster block the current record is a coordination-causing event.
23. What are the two phases of block coordination?
There are two phases of block coordination; the clear phase and the population phase. During the clear phase, Oracle forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, oracle forms issues a SELECT statement to repopulate the details block with the detail records associated with the new master record. These operations are accomblished through the execution og triggers.
24. what are most common types of complex master_detail
relationship?
There are three most common types of complex master-detail relationships:
master with dependent details
master with independent details
detail with two masters

25. What arethe different types of Delete details we can establish
in Master-Details?
Cascade
Isolate
Non-Isolate
26. What are the different default triggers created when Master
Deletes Property is set to Non-isolated?
Master Deletes Property Resulting Triggers
Non-Isolated (the default) On-Check-Delete-Master
On-Clear-Details
On-Populate-Details
27. What are the different default triggers created when Master
Deletes Property is set to Cascade?
Master Deletes Property Resulting Triggers
Cascading On-Clear-Details
On-Populate-Details
Pre-Delete
28. What are the different default triggers created when Master
Deletes Property is set to Isolated?
Master Deletes Property Resulting Triggers
Isolated On-Clear-Details
On-Populate-Details
29. What are the Coordination properties ina master detai
relationship?
The coordinatio in properties are
Deferred
Auto-Query
These properties determine when the population phase of block coordination should occur.
30. What are the different types of coordinations of the Master
with the Detail block?
Coordination of the detail block with its master can be
Immediate
Deferred with Auto-Query
Deferred with the no Auto-Query
31. what is the immediate coordination of the Master with the
Detail block?
Immediate(Deferred False, Auto-Querry False) The default settings.
When a coordination-causing event occurs, the detail records are fetched immediately.
32. What is the deferred with Auto-Quey Coordination of the
Master with the Detail block?
Deferred with Auto-Query(Deferred True,Auto-Query true) When a Coordination-causing events occurs,Oracle Forms defers fetching the associated detail records until the operator navigates to the detail block.
33 . What is the Deferred with no Auto-Query Coordination of
the Master with the Detail block?
Deferred with Auto-Query(Deferred True,Auto-Query False) When a Coordination-causing events occurs,Oracle Forms doesn¡¦t automatically fetch the detail records the operator must navigate to the detail block and explicitly execute a query.
34. What is an Alert?
An Alert is a modal Window that displays a message notifying the operator of some application condition.

35. When do you use Alert?
Use alerts to advise operators of unusual situations or to warn operators who are about to perform an action that might have undesirable or unexpected consequences.
36 . what are the different display styles of alert?
Find_alert
Show_alert
37 . How do you change the alert message during runtime?
You can change an alert message at run time by executing the SET_ALERT_PROPERTY built-in procedure. Changing an alert¡¦s message allows you to reuse the same alert object, but display a different message each time it is invoked.
Example
Set_Alert_Property(alert_id,ALERT_MESSAGE_TEXT,¡¦The product you selected is not in stock¡¦);

Editors
38 . What are the different types of editors?
There are three editors that can be used at run time the default editor, a system editor,or a
user-named editor.
39 . What is the default editor?
The default editor provides standard editing features,including search/replace and cut,copy and paste. The default editor is built into every form and is automatically available from every
text item.

40. What is the System Editor?
If there is a system editor available, you can specify that Oracle Forms should use the current system editor, rather than the default editor.
41 . What is the User named Editor?
A User named Editor has the same text editing functionality has the default editor, but, because it is a named object,you can specify editor attributes such as window display size,position,and title.
42 . What are the built-ins to display the user-named editor?
A User-named editor can be displayed programatically with built in procedure SHOW_EDITOR,EDIT_TEXTITEM independent of any particular text item.
43 . What is the difference between SHOW_EDITOR and
EDIT_TEXTITEM?
Show_Editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. whereas the edit_textitem built_in needs the input focus to be in the textitem before the built_in is executed.
LOV(List Of Values)
44. What is an LOV?
An LOV is a scrollable popup window that provides the operator with either a single or multi-column selection list.
45. What is the basic data structure that is required for creating
an LOV?
Record Group
46. What is the ¡§LOV for validation¡¨ Property of an item? What is
the use of it?
When LOV for validation is set to 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, the processing continues normally.
If the value in the text item doesn¡¦t 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.
47. What are the built-ins used to display the LOV?
Show_lov
list_values
47. What are the built-ins that are used to attach an LOV
programatically to an item?
Set_item_property
Get_item_property
(by setting the LOV_NAMEproperty)
48. What are the built-ins that are used for setting the LOV
properties at runtime?
get_lov_property
set_lov_property

Record Groups
49. What is the Record Group?
A record group is an internal Oracle Forms data structure that has a column/row framework similar to a database table. However, unlike database tables,record groups are separate objects that bekong to the form module in which they are defined.
50. How many number of columns a record group can have?
A record group can have an ultimate number of columns of type CHAR,LONG,NUMBER, and DATE provided that the total number of columns does not exceed 64k.
51 . What is the maximum allowed length of a Record Group
Column?
Record group column names cannot exceed 30 characters.

52. What are the different types of Record Group?
Query Record Groups
NonQuery Record Groups
Static Record Groups
53. What is a Query Record Group?
A Query record is a record group that has an associated SELECTstatement. The columns in a query record group derive their default names,datatypes and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retreived by the query associated with that record group.

54 . What is a Non Query Record Group?
A non-query record group is a group that does not have an associated query,but whose structures can be modified programatically at runtime.
55. What is a Static Record Group?
A Static record group is not associated with a query,rather, you define its structure and row values at design time, and they remain fixed at runtime.
56 . What are the built-ins used for creating and deleting
groups?
CREATE_GROUP(function)
CREATE_GROUP_FROM_QUERY(function)
DELETE_GROUP(Procedure)
57. What are the built-ins used for modifying a group structure?
ADD_GROUP_COLUMN(function)
ADD_GROUP_ROW(Procedure)
DELETE_GROUP_ROW(Procedure)
POPULATE_GROUP(function)
POPULATE_GROUP_WITH_QUERY(function)
SET_GROUP_CHAR_CELL(Procedure)
SET_GROUP_DATE_CELL(Procedure)
SET_GROUP_NUMBER_CELL(Procedure)
58 . What are the built-ins used for getting cell values?
GET_GROUP_CHAR_CELL(function)
GET_GROUP_DATE_CELL(function)
GET_GROUP_NUMBER_CELL(function)
59 . What are the built-ins used for processing rows?
GET_GROUP_ROW_COUNT(function)
GET_GROUP_SELECTION(function)
GET_GROUP_SELECTION(function)
RESET_GROUP_SELECTION(Procedure)
SET_GROUP_SELECTION(Procedure)
UNSET_GROUP_SELECTION(Procedure)
60. What are the built-ins used for finding Object ID functions?
FIND_GROUP(function)
FIND_COLUMN(function)
61. Use the ADD_GROUP_COLUMN function to add a column to
a record group that was created at design time
(1) True (2) False.
False
62. Use the Add_Group_row procedure to add a row to a static
record group.(1)True (2) False
False
PARAMETERS
63. What are the Parameters?
Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type CHAR,NUMBER, or
DATE that you define at design time.
64 . What are the Built-ins used for sending Parameters to
forms?
You can pass parameter values to a form when an application executes the CALL_FORM,NEW_FORM,OPEN_FORM,or RUN_PRODUCT.
65 . What is the maximum number of characters the parameter
can store?
The maximum number of characters the parameter can store is only valid for CHAR parameters, which can be up to 64k. Number parameters default to 23 bytes and DATE parameters default to 7 bytes.
66 . How do you call other ORACLE products from Oracle
Forms?
RUN_PRODUCT is a built-in used to invoke one of the supported Oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of calkl, Oracle Forms returns a message to the operator.
67. How do you reference a Parameter?
In PL/SQL, you can reference and set the values of form parameters using bind variable syntax.
Example
:PARAMETER.parameter_name=¡¦VIKRAM¡¦; or
:block.item=PARAMETER.parameter_name;
68 . How do you reference a parameter indirectly?
To indirectly reference a parameter
Use the NAME_IN and COPY built-ins to indirectly set and reference the parameter¡¦s value.
Example:
Name_In(¡¥PARAMETER.my_param¡¦)
Copy(¡¥SURESH¡¦,¡¦PARAMETER>my_param¡¦)
69 . What are the difference Parameter Types?
Text Parameters
Data Parameters
70 . When do you use DATA_PARAMETER type?
When the value of a data parameter being passed to a called product is always the name of a record group defined in the current form. Data paremeters are used to pass data to products invoked with the RUN_PRODUCT built-in subprogram.
71 . Can you pass data parameters to forms?
No.

Images
72. What are the different types of images?
BoilerPlate Images
Image Items
73 . What is the difference between Boilerplate images and
image items?
Boilerplate Images BoilerPlate images are static images(either vector or bitmap) that you import from the file system or database to use as graphical elements in your form, such as company logos and maps.
Image ItemsImage items are special types of interfaace controls that store and display either vector or bitmaps images. Like other items that store values, image items can be either base table items (items that relate directly to database columns) or control items. The definiton of an image item is storred as part of the form module. .FMB and .FMX files, but no image file is actually assciated with an image item until the item is populated at runtime.
74 . What are the triggers associated with the image items?
The Following triggers are available for responding programatically to image items events
When-Image-Activated
First when the operator double-clicks on a image item
When-Image_Pressed
Fires when an operator clicks or double-clicks on an image item
75. What is the use of IMAGE_ZOOM built_in?
You can use the IMAGE_ZOOM built-in subprogram to manipulate images in image items.
Working with Multiple Forms
76. How do you create a new session while opening a new form?
Using OPEN_FORM built-in setting the SESSION option.
Ex: OPEN_FORM(¡§STOCKS¡¦,ACTIVE,SESSION);
When you invoke multiple forms with OPEN_FORM and CALL_FORM in the
Same application,state whether the following are TRUE or FALSE
77 . Any attempt to navigate programatically to a disabled form
in a call form stack is allowed
FALSE
78 . An open form cannot execute the CALL_FORM procedure if
a chain of called forms has been initiated by another open
form
TRUE
79. When a form is invoked with CALL_FORM, does Oracle
Forms issues a savepoint? TRUE/FALSE
TRUE
80. What are the various subevents a mouse double click event
invokes?
Double_clicking the mouse consists of the mouse down,mouse up,mouse click,mouse down and mouse up events.
81 . State any three mouse event System Variables?
SYSTEM.MOUSE_BUTTON_PRESSED
SYSTEM.MOUSE_BUTTON_SHIFT_STATE
SYSTEM.MOUSE_ITEM
SYSTEM.MOUSE_CANVAS
SYSTEM.MOUSE_RECORD
OLE
82. What is an OLE?
Object Linking and Embedding (OLE) provides you with the capability to integrate objectrs from many MS Windows applications into a single compound document. Creating integrated applications enables you to use the features from several MS Windows application.
83. What is the difference between Object Embedding and
Linking in Oracle Forms?
An Ole server applications creates OLE objects that are embedded or linked in OLE containers. Examples of OLE servers are MS Word and MS Excel. OLE containers provide a place to store,display and manipulate objects that are created by OLE server applications.
Example: Oracle Forms is an example of an OLE container
84. What are the different styles of Activation of OLE objects?
In-place Activation
External Activation
Visual Attributes And Property Classes
85. What are Visual attributes?
Visual attributes are the font,color and pattern properties that you set for form and menu objects that appear in your application¡¦s interface.
86. What is a Property Class?
A Property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class inherit the setting of any property in the class that makes sense for that object.

87 . Can a Property class itself be based on a property class?
Yes
88 . What are the Important differences between property
classes and visual attributes?
The important differences betweeen property classes and visual attributes are 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 settings take precedence, and any visual attribute properties in the class are ignored.
Forms Built-ins
89. What is a TEXT_IO package?
The Text_IO package allows you to read and write information to a file in the file system.
90. What is an USER_EXIT?
Calls the user exit named in the user_exit_string invokes a 3GL program by name which has been properly linked into your current Oracle Forms executable.

91. What is SYNCHRONIZE?
Synchronizes the terminal screen with the internal state of the form. That is SYNCHRONIZE updates the screen display to reflect the information that Oracle Forms has in its internal representation of the screen.
Triggers
92. What is WHEN-DATABASE-RECORDN Trigger?
Fires when Oracle Forms first marks a record as an insert or an update. That is, the trigger fires as soon as Oracle Forms Determines through validation that the record should be processed by the next post or commit as an insert or update. This generally occurs only when the operator modifies the first item in a record, and after the operator attempts to navigate out of the item.
93. What are Master_Detail Triggers?
ON_CHECK_DELETE_MASTER
ON_CLEAR_DETAILS
ON_POPULATE_DETAILS
System Variables
94. What is the difference between $$DATE$$ and
$$DBDATE$$?
$$$$DBDATE$$ retrieves the current database date
DBDATE$$ retrieves the current operating system date.
95. What is SYSTEM.COORDINATION_OPERATION?
SYSTEM.COORDINATION_OPERATION represents the coordination causing event that occureson the master block in master detail relationship.
Example: System.Cooordination_Operation=
Miscelleneous
96. What are the differences between LOV and List item?
LOV is a Property whereas List item is an item.
A List item can have only one column whereas an LOV can have one or more columns.

97. What are the different display styles of List item?
Poplist
Text list
Combo box.
98 . What is a Poplist?
The poplist style list item appears initially as a single field(similar to a text item field. When The operator selects the list icon, a list of available choices appears.
99 . What is a Text list?
The text list style item appears as a rectangular box which displays a fixed number of values. When the text list contains values that cannot be displayed(due to the display area of the item), a vertical scroll bar appears, allowing the operator to view and select undisplayed values.
100. What is a Combo Box?
The Combo Box style list item combines the features found in list and text items. Unlike the poplist or the text list style list items, the combo box style list item will both display fixed values and accept one operator-entered values.
101. What are Display items?
Display items are similar to text items with the exception that displays items only store and display fetched or assigned values. Display items are generally used asa boilerplate or as conditional text.
102. What is the difference between OPEN_FORM AND
CALL_FORM?
when one form invokes another form by executing OPEN_FORM, the first form remains displayed, and operators can navigate between the forms as desires.
When one form invokes another form by executing CALL_FORM, the called form is modal with respect to the calling form. That is, any windowa that belong to the calling form are disabled and operators cannot navigate to them until they first exit the called form.
103. What is NEW_FORM built-in?
When one form invokes another form by executing New_form, Oracle Forms exists the first form and releases its memeory before loading the new form. Calling NEW_FORM completely replaces the first form with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
104. what is a Library?
A library is a collection of subprograms, including user- named procedures,functions and packages.
105. What are the advantages of libraries?
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 units from triggers, menu item commands and user-named routines you write in the modules to which modules to which you have attached the library.
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.
106. What is a STRIP_SOURCE generate option?
Removes the source code from the library file and generates a library file that contains only pcode.
The resulting file can be used for final deployment, but cannot be subsequently edited in the designer.
Example:f45gen module=old_lib.pll userid=scott/tiger
Strip_source=YES output_file=
107. What are VBX controls?
VBX controls provide a simple method of building and enhancing user interfaces. The controls can be used to obtain user input and display program output. The controls can be used to obtain user input and display program output. VBX controls were originally developed as extensions for the MS Visual Basic environment and include such items as sliders,grids and knobs.
108. What is a Timer?
A timer is an ¡§internal time clock¡¨ that you programatically create to perform an action each time the timer expires.
109. What are the built-ins associated with timers?
FIND_TIMER
CREATE_TIMER
DELETE_TIMER
110. What is the difference between POST-DATABASE-COMMIT
and POST-FORM-COMMIT?
POST-FORM-COMMIT fires once during the Post and Commit Transactions process, after the database commit occurs. The Post-Forms-Commit trigger fires after inserts, updates and deletes have been posted to the database, but before the transaction has been fibalized by issuing the commit. The Post-Database-Commit Trigger fires after Oracle Forms issues the Commit to finalize the transaction.
111. What is the difference between PRE-SELECT and PRE-
QUERY?
Fires during Execute Query and Count Query processing, after Oracle Forms constructs the SELECT statement to be issued, but before the statement is actually issued.
The Pre-Query trigger fires just before Oracle Forms
issues the SELECT statement to the database, after the operator has defined the example record by entering query criteria in Enter Query Mode.
Pre-Query trigger fires before Pre-Select trigger.
112. What is the trigger associated with the TIMER?
WHEN-TIMER-EXPIRED
113. What is the use of the Transactional Triggers?
Using Transactional triggers we can control or modify the default functionality of the Oracle Forms.
ORACLE FORMS 4.5 NOTES
Program units : User-named procedures, functions, or packages, can be defined in forms, menus, or library modules.
Application Partitioning : PL/SQL engine is available on both 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 partitioning 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, whichever makes the most sense for your particular application and configuration.
Navigator Views :
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.
Module Preference : File, Database, File/Database(Filter dialog)
Default font scaling off : ignores the runtime font, and scales objects according to the size of the default design time font. on :scales objects according to the size of the default runtime font.
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.
Only objects with PL/SQL option displays only code objects
Default Visual attributes : specifies that that the object should be displayed with default color, pattern, and font settings. When Visual Attribute Name is set to Default, the individual attribute settings reflect the current system defaults.
Custom Visual attributes : design time specification
Named visual attributes : specifies that the object should use the attribute settings defined for the named visual attribute
Property class can contain other property classes.
Property class Vs. Named visual attributes :
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 ignored.
Variant property : defined separetely in the property window.
Inherit property : inherits from property class.
Copy object option : automatically copies objects owned by the item being copied. gives dialog options for associated objects.
copying a reference object results in referencing only
In referencing when the source object name changes, it has to be updated for all referencing objects in the reference source information property.
Object groups :
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.
Object groups cannot contain other object groups. Members of object groups should be defined in the same form.
Events : Interface events, Internal processing events

Overview of Trigger Categories
This section provides an overview of commonly used triggers, grouped into the following functional categories:
o block-processing triggers o interface event triggers
o master-detail triggers o message-handling triggers
o navigational triggers o query-time triggers
o transactional triggers o validation triggers
When-Event Triggers : A When-event signals a point at which you can augment Oracle Forms default processing with additional tasks or operations.
On-Event Triggers : An On-event signals a point at which you can replace Oracle Forms default processing.
Pre-Event Triggers : A Pre-event signals a point just prior to the occurrence of either a When-event or an On-event.
Post-Event Triggers : A Post-event signals a point just following the occurrence of either a When-event or an On-event.
Key Triggers : Key triggers have a one-to-one relationship with specific keys. That is, the trigger fires when the operator presses a specific key or key-sequence.
Master-Detail Relationships :
Block Coordination
To maintain the master-detail relationship at runtime, Oracle Forms coordinates the master and detail blocks to ensure that the records displayed in the detail block are associated with the current record in the master block.
Any event that makes a different record in the master block the current record is a coordination-causing event. Deleting a record or pressing [Up] or [Down] to move to a different record are both examples of coordination-causing events. When such an event occurs, Oracle Forms automatically does the processing necessary to coordinate the master and detail blocks.
There are two phases of block coordination: the clear phase and the population phase. During the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with the detail records associated with the new master record. These operations are accomplished through the execution of triggers.
The Copy Value from Item Property
The mechanism that Oracle Forms used to coordinate the population of the detail block with the current record in the master block is the Copy Value from Item property on the foreign key item in the detail block. The Copy Value from Item property specifies the primary key item in the master block whose value gets copied to the foreign key item in the detail block whenever a detail record is created or queried.
Because the value of the primary key item in the master record gets copied to the foreign key item in the detail block, it is automatically incorporated in the WHERE clause of the SELECT statement that Oracle Forms issues to populate the detail block.
When blocks are related through a compound join, the Copy Value from Item property is set on two or more foreign key items in the detail block.
The Relation Object
When you create a relation, Oracle Forms generates the triggers and PL/SQL procedures required to enforce coordination between the master and detail blocks. The actual code that Oracle Forms generates depends on how the properties of the relation are set.
The properties that affect the functionality of a relation include Master Deletes, Coordination, and Prevent Masterless Operation.
Master Deletes Property: The Master Deletes property allows you to specify how the deletion of a record in the master block should affect records in the detail block. It can be set to Non-Isolated, Isolated, or Cascading.
Non-Isolated : The default setting. Prevents the deletion of a master record if associated detail records exist in the database.
Isolated : Allows the master record to be deleted and does not affect the associated detail records in the database.
Cascading: Allows the master record to be deleted and automatically deletes any associated detail records from the base table at commit time. When relations are nested to several levels, only records in the immediate detail block are deleted. That is, deletions do not automatically cascade to multiple levels of a relation chain.
Note: If your database is using the ORACLE7 Server cascading deletes feature, do not use the Cascading deletes option in Oracle Forms.
Coordination Properties :
The Coordination properties Deferred and Auto-Query determine when the population phase of block coordination should occur. Coordination of the detail block with its master can be Immediate, Deferred with Auto-query, or Deferred with No Auto-query.
Immediate (Deferred False, Auto-Query False) The default setting. When a coordination-causing event occurs, the detail records are fetched immediately.
Deferred with Auto-Query (Deferred True, Auto-Query True)When a coordination-causing event occurs, Oracle Forms defers fetching the associated detail records until the operator navigates to the detail block.
Deferred with No Auto-query (Deferred True, Auto-Query False) When a coordination-causing event occurs, Oracle Forms does not automatically fetch the detail records. To fetch the detail records, the operator must navigate to the detail block and explicitly execute a query.
Choosing the Appropriate Coordination
Deferred coordination can more accurately be thought of as "deferred population." That is, when a coordination-causing event occurs in the master block, the population phase of coordination is postponed, but the records in the detail block are cleared immediately. This functionality prevents a detail block from displaying records that are inconsistent with the current record in the master block.
As a general rule, choose immediate coordination (Deferred False, Auto-Query False) when the detail block is visible to the operator, and when it is assumed that the operator will always want to see the detail records associated with the current master record.
Deferred coordination with Auto-query is preferable when the detail block is not immediately visible; for example, when the operator must navigate to the detail block in a different window to be able to view detail records.
Deferred coordination is also useful in situations where the operator may not need to view the detail records at all. In this case, deferring coordination can prevent an unnecessary database query.
Deferred coordination with No Auto-query is useful when you want operators to be able to go into Enter Query mode and specify additional query criteria in the detail block before population occurs. Also, Deferred coordination with No Auto-query allows operators to navigate through detail records without forcing coordination, and its attendant processing, to occur until it is actually required.
Setting the Properties of Foreign Key Items in the Detail Block
When you create a relation, Oracle Forms sets the Copy Value from Item property on the foreign key items in the detail block automatically. The Copy Value from Item property specifies the name of the corresponding primary key item in the format master_block.item_name. At runtime, the value stored in the primary key item in the master block is copied to the foreign key item in the detail block whenever a detail record is created or queried.

When you create the relation in the New Block window, Oracle Forms also alters the properties of the foreign key item(s) in the detail block by doing the following:
o setting the Canvas property to NULL to make the item a NULL-canvas item.
o setting the following properties to False:
o Displayed
o Enabled
o Navigable
o Query Allowed
o Update Allowed
o sequencing items in the Navigator such that the foreign key items are last in the block's navigation sequence
Also, if the relation was created in the New Block window, Oracle Forms does not create a boilerplate text label for the foreign key items.
When you create a relation in the Object Navigator, rather than in the New Block window, you might want to set these same properties yourself.
The purpose of these settings is to hide the foreign key item(s) from the operator, since the same information is likely to be displayed in the primary key item(s) in the detail block. These settings are most appropriate when operators can view both the master and detail blocks at the same time. If operators cannot see the master block when viewing detail records, you may want to undo these settings so that foreign key item(s) are visible to the operator. If you do so, make sure that the item Update Allowed property is set to False, so that operators cannot edit the foreign key value and thus disrupt master-detail coordination.
Deleting a Relation
You can delete a relation by selecting it in the Object Navigator and choosing Navigator->Delete. The following table shows what happens when you delete a relation or an object that is part of a relation:
If you delete... This is the result...
------------------------------------------------------------------------------------------------------
A relation. Oracle Forms deletes all of the master-detail
triggers that were attached to the relation's
master block and clears the Copy Value from
Item property of the foreign key item(s) in the
detail block.
The master or detail block Oracle Forms deletes the relation and all of the
in a relation.master-detail triggers. Oracle Forms does not
delete the master-detail procedures.
A detail block in a Oracle Forms deletes the relation, and removes
master-with-independent- the relevant detail section from the
details relation. On-Clear-Details trigger and, if present, the
On-Populate-Details trigger. Any comments
or code that you added in this section are
removed also.
The foreign key item in a The master-detail block coordination will be
detail block. (Not disrupted. Although the foreign key item in
recommended) the detail block can be hidden by making it a
NULL-canvas item, it cannot be deleted
entirely.

Any master-detail trigger Oracle Forms does not prevent you from
or procedure. (Not deleting a master-detail trigger or procedure.
recommended). Doing so, however, disrupts master-detail
block coordination.
Preventing Masterless Operations in the Detail Block
It is often desirable to prevent operators from peforming masterless operations in a detail block; that is, to prevent them from querying or inserting records in the detail block when there is no current record in the master block.
You can set the Prevent Masterless Operation property to True to prevent operators from performing masterless operations in the detail block of the relation. Setting this property to True has the following effects:
Oracle Forms does not allow records to be inserted in the detail block when there is no master record in the master block. Any attempt to insert a record generates error FRM-41105: Cannot create records without a parent record.
Oracle Forms does not allow querying in the detail block when there is no master record that came from the database in the master block. Any attempt to query generates error FRM-41106: Cannot query records without a parent record.
It is usually appropriate to prevent masterless inserts when operators are likely to encounter an error if they attempt to commit detail records that have been created independently of a master record. Such an error occurs when the detail block derives the value of its foreign-key items from the primary key items in the master record (by way of the Copy Value from Item property).
Similarly, you might want to prevent masterless queries in a detail block because operators cannot perform effective queries from the detail block. Operators can query the existence of specific detail records, but they cannot determine which master records own them. (This assumes that the foreign key item(s) in the detail block are not displayed.)
Preventing Navigation to the Detail Block Setting the Prevent Masterless Operation property to True prevents operators from querying and inserting in a detail block for which there is no corresponding master record, but it does not prevent them from navigating to the detail block and attempting these operations.
In some applications, it may be desirable to disallow any attempt to navigate to a detail block when there is no master record.
The following example shows one way to prevent operators from navigating to a detail block for which there is no master. It is based on the detail block warehouse and the master block region.
When-New-Block-Instance trigger on detail block warehouse:
DECLARE
alert_dummy NUMBER;
BEGIN
/*
** See if there is a master record by checking the status
** of the current record in the master block
*/
IF Get_Record_Property(Get_Block_Property('region',
current_record),'region', STATUS) = 'NEW'
OR :region.region_id IS NULL THEN
/*
** There isn't a master record; display an alert that tells the ** operator to query or enter a region record before moving to
** the warehouse block
*/
alert_dummy := SHOW_ALERT('my_alert');
/*
** Put the input focus in the master block
*/
GO_BLOCK('region');
END IF;
END;
The When-New-Block-Instance trigger fires whenever the operator navigates to the detail block.
The built-in function GET_RECORD_PROPERTY is used to determine the status of the current record in the master block. (The function GET_BLOCK_PROPERTY is nested as the first argument to GET_RECORD_PROPERTY to return the record number of the current record in the region block.)
If the status of the current record is NEW, indicating that the record is not an existing master record, Oracle Forms displays an alert window with the message "Query or enter a master record before moving to the detail block." The trigger then navigates to the master block to allow the operator to do just that.
Creating Complex Master-Detail Relationships
Many applications require complex master-detail relationships that involve more than two blocks. To create such relationships, simply define as many individual relations as needed.
There is no practical limit to the number of relations that can be defined in a form. Further, any block can be the master or detail in more than one relation, and a block that is the master in one relation can be the detail in another.
When you create complex master-detail relationships, Oracle Forms automatically adjusts the existing triggers to manage the relations you define.
This topic describes three of the most common types of complex master-detail relationships:
o master with dependent details o master with independent details
o detail with two masters
Complex Relations and Cascading Deletes
When you set the Master Deletes property of a relation to Cascading, be aware that deletes are enforced only for the immediate detail block in the relation. Thus, in a complex master-detail relationship involving relations between blocks A and B (A_B), and B and C (B_C), if relation A_B is cascading, the detail records in Block C are not automatically deleted unless Relation B_C is also cascading.
Master with Dependent Details
A master with dependent details relationship includes a master block and n levels of detail blocks, such that the first detail block is itself a master for its own detail block.
To create a master with dependent details relationship, define the individual relations A_B and B_C separately. The relations can be created in any order.
When you create this type of relationship, consider the effect of the Master Deletes and Coordination properties on the detail blocks. For example, if one relation in a chain of related blocks is set to deferred coordination, all subsequent blocks will also be deferred. Thus, when relation A_B is deferred and relation B_C is immediate, Oracle Forms does not coordinate Block C with Block B until Block B is coordinated with Block A.
Master with Independent Details
A master with independent details relationship involves two or more detail blocks, each of which has the same master block. This structure is useful when you want to display more than one set of detail records for a single master record. A coordination-causing event in the master block results in both detail blocks being populated with the appropriate detail records.
In the sample application, the master block region could be displayed along with the detail blocks warehouse and customer. The operator could then see all of the warehouses and customers within the currently selected region.
To create this type of relationship, define the individual relations A_B and A_C separately. The relations can be created in any order.
Detail with Two Masters
A detail with two masters relationship involves a single detail block that has two master blocks. Oracle Forms displays the appropriate detail records for whichever master block is the current block in the form. For example, in the sample application referred to earlier, the ord block could be a detail block having two master blocks, customer and emp. The operator could then see all of the orders for a particular customer, or for a particular sales representative (employee).
Usually, when you create a detail with two masters relationship, you will be joining on different foreign key items in the detail block. In such cases, the Copy Value from Item property is set for each foreign key item in the detail block to point to the primary key item in the appropriate master block. To create this type of relationship, you need only define the two relations, and Oracle Forms will automatically support coordination.
In other cases, however, you might want to create a master with two details with each relationship joining on the same foreign key item in the detail block. However, because the Copy Value from Item property can only point to one master block item, you will need to write additional code to coordinate querying and updating for the second relation if you are joining on the same item in the detail block.
Depending on the desired functionality, this might include writing a Pre-Query trigger for the detail block to ensure that the correct primary key item value is copied to the foreign key item in the detail block. For example,
Copy(name_in(:System.Master_Block||'.primary_key_item'),
'detail_block.item');
You might also want to create a When-New-Block-Instance trigger that checks the coordination status of the detail block whenever the operator navigates to a different master block, and, if necessary, populates the detail block. For more information on triggers and built-ins available for customizing master-detail functionality, refer to "Modifying the Default Master-Detail Functionality."
About Master-Detail Triggers and Procedures
When you create a relation, Oracle Forms generates different triggers depending on how the Master Deletes and Coordination properties are set. The following table shows how the Master Deletes property determines which triggers Oracle Forms creates. When you change the setting of the Master Deletes property for an existing relation, Oracle Forms adds or removes triggers as needed. The actual code in these triggers depends on how the Coordination properties are set, and on whether the relation is part of a complex relation chain.
Master Deletes Property Resulting Triggers
-----------------------------------------------------------------------------------------------------
Non-Isolated (the default) On-Check-Delete-Master
On-Clear-Details
On-Populate-Details
Cascading On-Clear-Details
On-Populate-Details
Pre-Delete
Isolated On-Clear-Details
On-Populate-Details
Master-Detail Triggers
This section describes the triggers that Oracle Forms creates when you define a master-detail relationship. The example trigger text shown with each trigger description shows the basic structure of the trigger. The actual trigger text that you will see in your own forms will be application-specific, and may look somewhat different.
Oracle Forms also creates user-named procedures that are called by master-detail triggers.
Oracle Forms generates comments in the default trigger and procedure code. Comments are denoted by the standard PL/SQL double-hyphen ('- -').
On-Clear-Details Trigger The On-Clear-Details trigger is required for all master-detail relations. It fires during the clear phase of coordination, and clears all of the detail records in the detail block. This trigger calls the CLEAR_ALL_MASTER_DETAILS procedure, as shown in the following example trigger text:
-- Begin default relation program section
--
Clear_All_Master_Details;
--
-- End default relation program section
--
On-Check-Delete-Master Trigger Oracle Forms creates the On-Check-Delete-Master trigger when the Master Deletes property is set to Non-Isolated. It fires when there is an attempt to delete a master record. The trigger queries the database to see if detail records exist for the master record. If no details exist, the trigger deletes the master record. If detail records are found, the trigger displays the message "Cannot delete master record when matching detail records exist." as shown in the following example trigger text:
-- Begin default relation declare section
--
Declare
Dummy_Define char(1);
--
-- Begin B detail declare section
--
cursor B_cur is
select null from MASDET
where PARENT = :A.ID;
--
-- End B detail declare section
--
--
-- End default relation declare section
--
--
-- Begin default relation program section
--
Begin
--
-- Begin B detail program section
--
Open B_cur;
Fetch B_cur into Dummy_Define;
if ( B_cur%found ) then
Message('Cannot delete master record when matching
detail records exist.');
Close B_cur;
raise Form_Trigger_Failure;
end if;
Close B_cur;
--
-- End B detail program section
--
End;
--
-- End default relation program section
On-Populate-Details Trigger Oracle Forms creates the On-Populate-Details trigger for every master-detail relation. It fires during the population phase of block coordination.The trigger first checks the status of the master record and the value of its primary key field, then navigates to the detail block to issue the appropriate query. This trigger calls the QUERY_MASTER_DETAILS procedure, as shown in the following example trigger text:
-- Begin default relation declare section
--
Declare
recstat char(20) := :system.record_status;
start_item char(61) := :system.cursor_item;
relation_id relation;
--
-- End default relation declare section
--
-- Begin default relation program section
--
Begin
if ( recstat = 'NEW' or recstat = 'INSERT' ) then
return;
end if;
--
-- Begin B detail program section
--
if ((:A.ID is not null)) then
relation_id := find_relation('A.AB');
Query_Master_Details(relation_id, 'B');
end if;
--
-- End B detail program section
----
End;
Pre-Delete Trigger Oracle Forms creates the Pre-Delete trigger when the Master Deletes property is set to Cascading. It deletes the records in the detail block's base table that correspond to the master record that is being deleted.
-- Begin default relation program section
--
BEGIN
--
-- Begin detail_block detail program section
--
DELETE FROM detail_block_base_table
WHERE foreign_key_column = :master_block.primary_key_item;
--
-- End detail_block detail program section
--
END;
--
-- End default relation program section
Master-Detail Procedures
This section shows the text of the master-detail procedures that Oracle Forms creates automatically when you define a relation. These procedures are called from the master-detail triggers or from other procedures.
CLEAR_ALL_MASTER_DETAILS Procedure This procedure is called by the On-Clear-Details trigger. It navigates to the detail block and clears the detail records.
PROCEDURE Clear_All_Master_Details IS
mastblk CHAR(30); /* Initial Master Block Causing Coord */
coordop CHAR(30); /* Operation Causing the Coord */
trigblk CHAR(30); /* Cur Block On-Clear-Details Fires On*/
startitm CHAR(61); /* Item in which cursor started */
frmstat CHAR(15); /* Form Status */
curblk CHAR(30); /* Current Block */
currel CHAR(30); /* Current Relation */
curdtl CHAR(30); /* Current Detail Block */
FUNCTION First_Changed_Block_Below( Master CHAR ) RETURN CHAR
IS
curblk CHAR(30); /* Current Block */
currel CHAR(30); /* Current Relation */
retblk CHAR(30); /* Return Block */
BEGIN
/*
** Init Local Vars
*/
curblk := Master;
currel :=Get_Block_Property(curblk,FIRST_MASTER_RELATION);
/*
** While there exists another relation for this block
*/
WHILE currel IS NOT NULL LOOP
/*
** Get the name of the detail block
*/
curblk := Get_Relation_Property(currel,DETAIL_NAME);
/*
** If this block has changes, return its name
*/
IF Get_Block_Property(curblk,STATUS) IN
('CHANGED','INSERT') THEN
RETURN curblk;
/*
** Otherwise, recursively look for changed blocks below
*/
ELSE
retblk := First_Changed_Block_Below(curblk);
/*
** If some block below is changed, return its name
*/
IF retblk IS NOT NULL THEN
RETURN retblk;
/*
** Otherwise, Consider the next relation
*/
ELSE
currel := Get_Relation_Property (currel,
NEXT_MASTER_RELATION);
END IF;
END IF;
END LOOP;
/*
** If we get here, no changed blocks were found
*/
RETURN NULL;
END First_Changed_Block_Below;
BEGIN
/*
** Init Local Vars
*/
mastblk := :System.Master_Block;
coordop := :System.Coordination_Operation;
trigblk := :System.Trigger_Block;
startitm := :System.Trigger_Item;
frmstat := :System.Form_Status;
/*
** If the coord op is anything but CLEAR_RECORD, then
** continue checking.
*/
IF coordop <\> 'CLEAR_RECORD' THEN
/*
** If we're processing the driving master block...
*/
IF mastblk = trigblk THEN
/*
** If something in the form is changed, find the
** first changed block below the master
*/
IF frmstat = 'CHANGED' THEN
curblk := First_Changed_Block_Below(mastblk);
/*
** If we find a changed block below, go there
** and Ask to commit the changes.
*/
IF curblk IS NOT NULL THEN
Go_Block(curblk);
Check_Package_Failure;
Clear_Block(ASK_COMMIT);
/*
** If user cancels commit dialog, raise error
*/
IF NOT( :System.Form_Status = 'QUERY' /* Yes */ OR
:System.Block_Status = 'NEW' /* No */ ) THEN
RAISE Form_Trigger_Failure;
END IF;
END IF;
END IF;
END IF;
END IF;
/*
** Clear all the detail blocks for this master without
** any further asking to commit.
*/
currel := Get_Block_Property(trigblk,FIRST_MASTER_RELATION);
WHILE currel IS NOT NULL LOOP
curdtl := Get_Relation_Property(currel,DETAIL_NAME);
IF ( Get_Block_Property(curdtl, STATUS) <\> 'NEW' ) THEN
Go_Block(curdtl);
Check_Package_Failure;
Clear_Block(NO_VALIDATE);
IF ( :System.Block_Status <\> 'NEW' ) THEN
RAISE Form_Trigger_Failure;
END IF;
END IF;
currel := Get_Relation_Property(currel,NEXT_MASTER_RELATION);
END LOOP;
/*
** Put cursor back where it started
*/
IF ( :System.Cursor_Item <\> startitm ) THEN
Go_Item(startitm);
END IF;
EXCEPTION
WHEN Form_Trigger_Failure THEN
IF :System.Cursor_Item <\> startitm THEN
Go_Item(startitm);
END IF;
RAISE;
END Clear_All_Master_Details;
QUERY_MASTER_DETAILS Procedure This procedure is called from the On-Populate-Details trigger. It navigates to the detail block and then executes a query to fetch the appropriate detail records.
PROCEDURE Query_Master_Details(rel_id Relation, detail CHAR) IS
oldmsg CHAR(2); /* Old Message Level Setting */
reldef CHAR(5); /* Relation Deferred Setting */
BEGIN
/*
** Initialize Local Variable(s)
*/
reldef := Get_Relation_Property(rel_id,
DEFERRED_COORDINATION);
oldmsg := :System.Message_Level;
/*
** If NOT Deferred, Goto detail & execute the query.
*/
IF reldef = 'FALSE' THEN
Go_Block(detail);
Check_Package_Failure;
:System.Message_Level := '5';
Execute_Query;
:System.Message_Level := oldmsg;
/*
** If Deferred, Mark the detail block as un-coordinated
*/
ELSE
Set_Block_Property(detail, coordination_status,
NON_COORDINATED);
END IF;
EXCEPTION
WHEN Form_Trigger_Failure THEN
:System.Message_Level := oldmsg;
RAISE;
END Query_Master_Details;
CHECK_PACKAGE_FAILURE Procedure This procedure is called by the CLEAR_ALL_MASTER_DETAILS and QUERY_MASTER_DETAILS procedures. It checks to see if the previous statement executed successfully by looking at the status of the built-in Oracle Forms error variable FORM_SUCCESS.
PROCEDURE Check_Package_Failure IS
BEGIN
IF (NOT Form_Success) THEN
RAISE Form_Trigger_Failure;
END IF;
END;
Triggers for Complex Master-Detail Relations
When you create a complex master-detail relationship, Oracle Forms adds or edits the existing master-detail triggers to support the relationship you have defined. The following examples illustrate how Oracle Forms adds or edits triggers as required.
Example 1: Master with Independent Details Consider a single relation A_B, with Master Deletes set to Non-Isolated and Coordination set to Immediate (default settings). Oracle Forms creates the following triggers on the master block to manage the relation:
o On-Clear-Details o On-Check-Delete-Master
o On-Populate-Details
If you then create a second relation A_C (having the same master block but a different detail block), you have created a master-with-independent-details relationship; that is, one master block with two detail blocks.
To manage this relationship, Oracle Forms does not create any additional triggers. Instead, Oracle Forms adds a section to each existing trigger to facilitate clearing and populating the second detail block. The generated comments in these triggers delimit the sections that manage each detail block. For example, the comments in the On-Populate-Details trigger clearly show where each detail block section begins and ends:
-- Begin default relation program section
BEGIN
--
-- Begin first_detail_block program section
--
/* trigger text to clear the first detail block*/
--
-- End first_detail_block program section
--
--
-- Begin second_detail_block program section
--
/* trigger text to clear the second detail block*/
--
-- End second_detail_block program section
--
/* final trigger text */
END;
-- End default relation program section
--
Example 2: Master with Dependent Details In this example, the simple relation A_B is extended by adding a second relation B_C to create a master with dependent details relationship.
To manage the second relation B_C, Oracle Forms removes the form-level On-Clear-Details trigger, and creates two additional triggers that enforce coordination with the new detail block. The additional triggers are attached to Block B, the master for the second relation (B_C).
Modifying the Default Relation Triggers
You may at some point want to add your own comments and code to the default master-detail triggers that Oracle Forms creates. You can edit a default trigger as you would any trigger that you had created yourself.
Remember, however, that if you later change the properties of the relation, Oracle Forms may need to delete or edit the default triggers. For example, changing a relation's Master Deletes property from Cascading to Isolated causes Oracle Forms to remove the now unnecessary Pre-Delete trigger.
Consider the following points when you edit master-detail triggers:
o Do not alter or delete the comments that Oracle Forms generates. These comments tell Oracle Forms where to insert or delete trigger text when changes are necessary.
o If you add code to a trigger, insert it before the "Begin default relation program section" comment or after the "End default relation program section" comment.
o Oracle Forms does not delete a master-detail trigger that you have modified, provided that you place your code outside the default relation program section. (Oracle Forms removes the relation program section, but does not delete the trigger.)
Modifying the Default Master-Detail Functionality
In special situations, you may want to modify or extend the default master-detail functionality that Oracle Forms provides. For example, if your application runs against non-ORACLE data sources or includes long relation chains that require non-standard functionality, you might want to implement your own block coordination mechanism.
Oracle Forms provides a number of triggers and built-in subprograms that are useful for such tasks, some of which are listed here. For complete information on these triggers and routines, refer to the Oracle Forms Reference Manual, Vol. 1.
Triggers:
o On-Clear-Details o On-Populate-Details
o On-Check-Delete-Master o When-New-Record-Instance
o When-New-Block-Instance
Built-in subprograms:
o GET_BLOCK_PROPERTY o SET_BLOCK_PROPERTY
o GET_FORM_PROPERTY o GET_RELATION_PROPERTY
o SET_RELATION_PROPERTY
For example, you can use these built-in subprograms as follows:
o Use GET_FORM_PROPERTY to get the name of the first and last block in the form (FIRST_BLOCK, LAST_BLOCK).
o Use GET_BLOCK_PROPERTY to find out a block's current COORDINATION_STATUS (either COORDINATED or NON_COORDINATED) and a block's FIRST_RELATION, that is, the name of the first relation in which the block is a master.
o Use SET_BLOCK_PROPERTY to set block properties dynamically.
o Use GET_RELATION_PROPERTY to get the current property settings for a relation (MASTER_DELETES, DEFERRED_COORDINATION, and AUTOQUERY), the names of the master and detail blocks (MASTER_NAME, DETAIL_NAME), and the name of the next relation in the form (NEXT_MASTER_RELATION, NEXT_DETAIL_RELATION). For example, to assign the name of the master block in a relation to a local variable you might write the following:
DECLARE
master_block VARCHAR2;
BEGIN
master_block := Get_Relation_Property('my_relation',
MASTER_BLOCK);
END;
o Use SET_RELATION_PROPERTY to set the properties of a relation dynamically. For example, to set the Master Deletes property you could call the following procedure:
Set_Relation_Property('my_relation', MASTER_DELETES,
DEFERRED_COORDINATION);
Canvas-Views
Canvas-views are the background objects on which you place the interface items (text items, check boxes, radio groups, etc.) and boilerplate objects (boxes, lines, images, etc.) that operators interact with as they run your form. Each canvas-view is displayed in a window.
Content Canvas-Views Most canvas-views are content canvas-views. A content canvas-view is the "base" view that occupies the entire content pane of the window in which it is displayed. You must define at least one content canvas-view for each window you create. More than one content canvas-view can be assigned to the same window at design time, but at runtime, only one of them at a time is displayed in the window.
Stacked Canvas-Views A stacked canvas-view is displayed in a window on top of, or "stacked" on the content canvas-view assigned to that same window. Stacked canvas-views obscure some part of the underlying content canvas-view, and are often shown and hidden programmatically. More than one stacked canvas-view can be displayed in a window at the same time.
Horizontal/Vertical Toolbar Canvas-Views Toolbar canvas-views are used to create toolbars for individual windows. Horizontal toolbars are displayed at the top of a window, just under its menu bar. Vertical toolbars are displayed along the left side of a window.
Windows
MDI applications display a default parent window, called the application window. All other windows in the application are either document windows or dialog windows.
Document windows are always displayed within the MDI application window frame. If the operator resizes the application window so that it is smaller than a document window, the document window is clipped. Operators can maximize a document window so that it occupies the entire content area of the application window.
Dialog windows are free-floating, and the operator can move them outside the application window if they are defined as Movable. If the operator resizes the application window to make it smaller than a dialog window, the dialog window is not clipped.
A window can be either modeless or modal.
Modeless windows can remain displayed until they are dismissed by the operator or hidden programmatically. You can set the Remove on Exit property for a modeless window to specify whether it should remain displayed when the operator navigates to another window.
Modal windows are usually used as dialogs, are often displayed with a platform-specific border unique to modal windows. On some platforms, modal windows are "always-on-top" windows that cannot be layered behind modeless windows.
In addition to platform-specific restrictions, modal windows have the following characteristics:
The Remove on Exit property does not apply to modal windows. By default, Oracle Forms prevents operators from navigating out of modal windows with the mouse, but does allow them to navigate to another window with keyboard commands. When such navigation occurs, Oracle Forms always closes the modal window, unless the target window is itself a modal window.
Modal windows cannot have scroll bars, and setting the Scroll Bar properties for a modal window has no effect.
Show_lov : the lov need not be attached to the text item.
List_values : the lov should be attached to the text item.
lovs are based on record groups.
Changing a column in the record group does not update lov columns automatically
Record Groups :
The following built-in subprograms are available for creating and manipulating record groups programmatically:
Creating and deleting groups:
o CREATE_GROUP (function) o CREATE_GROUP_FROM_QUERY (function)
o DELETE_GROUP (procedure)
Modifying a group's structure:
o ADD_GROUP_COLUMN (function) o ADD_GROUP_ROW (procedure)
o DELETE_GROUP_ROW (procedure)
Populating groups:
o POPULATE_GROUP (function)
o POPULATE_GROUP_WITH_QUERY (function)
o SET_GROUP_CHAR_CELL (procedure)
o SET_GROUP_DATE_CELL (procedure)
o SET_GROUP_NUMBER_CELL (procedure)
Getting cell values:
o GET_GROUP_CHAR_CELL (function)
o GET_GROUP_DATE_CELL (function)
o GET_GROUP_NUMBER_CELL (function)
Processing rows:
o GET_GROUP_ROW_COUNT (function)
o GET_GROUP_SELECTION_COUNT (function)
o GET_GROUP_SELECTION (function)
o RESET_GROUP_SELECTION (procedure)
o SET_GROUP_SELECTION (procedure)
o UNSET_GROUP_SELECTION (procedure)
Object ID functions:
o FIND_GROUP (function)
o FIND_COLUMN (function)
Populate Group with query : can be used to modify record group at run time but columns cannot be altered ( created during design time).
To populate a list item during run time using record group, the record group must contain two columns of char type
Oracle Forms Datatypes
The following table shows the FIND_ function and return type for each object:
Object Function Return Type
Alert FIND_ALERT ALERT
Block FIND_BLOCK BLOCK
Canvas FIND_CANVAS CANVAS
Record Group Column FIND_COLUMN GROUPCOLUMN
Editor FIND_EDITOR EDITOR
Form FIND_FORM FORMMODULE
Record Group FIND_GROUP RECORDGROUP
Item FIND_ITEM ITEM
List of Values FIND_LOV LOV
Menu Item FIND_MENU_ITEM MENUITEM
Parameter List GET_PARAMETER_LIST PARAMLIST
Relation FIND_RELATION RELATION
Timer FIND_TIMER TIMER
View FIND_VIEW VIEWPORT
Using object id improves performance, makes code generic and easier to maintain.
Each time you reference an object by name in a PL/SQL statement, Oracle Forms does the necessary processing to look up the object's ID internally.
Default_value built in procedure assigns the value to the indicated variable only if it is null.
Consider the following factors when deciding whether to use a global or NULL-canvas item as a variable:
o Items can be defined as CHAR, NUMBER, or DATE data types; global variables store only character strings, and using conversion functions like TO_DATE and TO_NUMBER requires additional processing.
o Items can be dimensioned by setting their Maximum Length property; global variables are always 255 bytes.
o Global variables are visible across multiple modules during a runtime session; NULL-canvas items are not.
o The CLEAR_FORM operation sets the value of a NULL-canvas item in the current form to NULL; the value of a global variable is not affected by CLEAR_FORM.
o Both items and global variables can be referenced in the default WHERE clause for a base table block and record group SQL statements.
Subprograms and functions :
Defined in a form module can be called only in that module
Defined in a library can be called from anywhere.
PL/SQL Packages:
A package is a PL/SQL construct that groups logically related types, objects, procedures, and functions. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary.
The first time a package is called (by any user on the instance) the entire package is loaded into the Oracle7 SGA to make subsequent invocation of any procedures or functions it contains very fast.
LIBRARIES
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. A library can be attached to any 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.
File formats : .pll - source and compiled (pcode) source can be removed using STRIP_SOURCE. .pld - only source
libraries cannot refer - form variables, system variables, global variables, form parameters
Instead use name_in, copy for referencing
Unlike PL/SQL menu items, a user-defined trigger defined in a form module can refer directly to the values of form items. If you want to write a user-named routine or anonymous block that uses direct references, you can place it in a user-named trigger and then call that trigger from a PL/SQLmenu item command with the EXECUTE_TRIGGER built-in
Menus :
Background Menu : only one per menu module. it is assigned the name BGM.
Features :
* There is a logical key associated with the first 10 menu items on the background menu. Operators can press a background menu key to execute the command assigned to a background menu item, without having to select the item on the BGM menu.
* At any time, operators who have been granted background menu privileges can display the items on the background menu in a separate "show-keys" window by pressing [Show BGM]. Similarly, the background menu can be displayed programmatically by executing the SHOW_BACKGROUND_MENU routine.
PECS : Performance event collection services
FORMS 4.5 ADVANCED TECHNIQUES MANUAL
Exceptions :
When a built - in failes, no exception is raised, subsequent statements are executed. So the outcome of the built-ins is to be tested;
User named triggers :
When an unhandled exception is raised in a user-named trigger, the user-named trigger fails, but the exception does not propagate to the calling trigger. Rather, Oracle Forms treats the failure as an error in the built-in procedure EXECUTE_TRIGGER, and sets the return values of the built-in error functions accordingly. Thus, the outcome of a user-named trigger can be trapped in the same way as a call to any other built-in subprogram; that is, by evaluating the built-in error functions:
Error Handling for Stored Procedures
There are three primary methods for trapping ORACLE errors that are returned from the kernel during the processing of your PL/SQL code:
o checking DBMS_ERROR_TEXT and DBMS_ERROR_CODE built-in subprograms within a form-level ON-ERROR trigger
o creating appropriate user-defined exceptions
o evaluating the SQLCODE and SQLERRM functions in a WHEN OTHERS exception handler
User defined exceptions :
exception_init : associate an oracle error number with an exception name of our choice.
Multiple form applications
There are three ways that one form can programmatically invoke another form:
o Execute the OPEN_FORM procedure to open an independent form.
o Execute the NEW_FORM procedure to replace the current form with a different form.
o Execute the CALL_FORM procedure to call a modal form.
When one form invokes another form by executing OPEN_FORM, the first form remains displayed, and operators can navigate between the forms as desired. An opened form can share the same database session as the form from which it was invoked, or it can create a separate session of its own. For most GUI applications, using OPEN_FORM is the preferred way to implement multiple-form functionality.
When one form invokes another form by executing NEW_FORM, Oracle Forms exits the first form and releases its memory before loading the new form. Calling NEW_FORM completely replaces the first form with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
When one form invokes another form by executing CALL_FORM, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.
Multiple-Form Applications and the Root Window
Only one root window can be displayed even in multiple form applns. So root windows are to be avoided when more than one form is to be displayed at the same time.
If form a has a root window defined and invokes form b which also has root window, form b is displayed on the root window of a thus hiding form a.
Opening forms in different database sessions :
To open a form without creating a new session:
Open_Form('stocks'); -- default; NO_SESSION is implicit
Open_Form('stocks',ACTIVATE,NO_SESSION) -- explicit; for clarity
To open a form in its own, independent session, call OPEN_FORM with the SESSION parameter, as shown here:
Open_Form('stocks',ACTIVATE,SESSION);
When COMMIT is initiated, processing is done for those forms that share the same session.
Opening multiple instances of the same form :
To navigate use form id.
Post Vs Commit :
Posting consists of writing updates, deletions, and insertions in the form to the database, but not committing these transactions to the database. Oracle Forms does all of the default validation and commit processing, but does not issue the COMMIT statement to finalize these transactions. If posted the status of the records need not be maintained in the form.
Commit finalizes these transactions
Post only mode :
When a calling form has pending updates or deletes that have not been explicitly posted, Oracle Forms runs the called form in post-only mode.
Commiting from child form :
update records in form A, post , call form B, make changes and commit.
Commiting from parent form :
update records in form A, call form B, post return to A in no-rollback mode and commit.
Calling Other Products from Oracle Forms
You can invoke other products from Oracle Forms with the RUN_PRODUCT built-in procedure. The syntax for RUN_PRODUCT is shown here:
RUN_PRODUCT(product, document, commmode, execmode, location,
list, display);
For example, to invoke Oracle Reports, you could make the following call:
Run_Product(REPORTS,'stats',ASYNCHRONOUS,BATCH,FILESYSTEM);
By default, when you invoke Oracle Reports or Oracle Graphics with RUN_PRODUCT, the called product logs on to ORACLE using the current form operator's USERID.
Oracle Forms uses the parameters you pass to RUN_PRODUCT to construct a valid command line invocation of the called product. RUN_PRODUCT takes the following parameters:
Product A numeric constant that specifies the Oracle tool to be invoked: FORMS, REPORTS, GRAPHICS, or BOOK.
Document Specifies the document or module to be opened by the called product.
Commmode Specifies the communication mode to be used when running the called product. Valid numeric constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.
o SYNCHRONOUS specifies that control returns to Oracle Forms only after the called product has been exited. The operator cannot work in the form while the called product is running. Synchronous is required when passing a record group to a called product as a DATA_PARAMETER; for example, when invoking Oracle Graphics to return an Oracle Graphics display that will appear in a form chart item.
o ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display. Do not use ASYNCHRONOUS when passing a record group to a called product as a DATA_PARAMETER; for example, when invoking Oracle Graphics to return an Oracle Graphics display that will appear in a form chart item.
Execmode Specifies the execution mode to be used when running the called product, either BATCH or RUNTIME. When you run Oracle Reports and Oracle Graphics, execmode can be either BATCH or RUNTIME. When you run Oracle Forms, always set execmode to RUNTIME.
Location Specifies the location of the document or module you want the called product to execute, either the file system or the database.
List Specifies the name or ID of a parameter list to be passed to the called product.
Display Specifies the name of the Oracle Forms chart item that will contain the display generated by Oracle Graphics.
Chart item : Does not store database values. contains objects generated by oracle graphics
OLE :Object Linking and Embedding (OLE) provides you with the capability to integrate objects from many MS Windows applications into a single compound document. In Oracle Forms, embedded objects become part of the form module, and linked objects are references from a form module to a linked source file. Embedded objects are activated by In-place/ External activations. Linked objects - External activation
An OLE server application creates objects that are embedded or linked in OLE containers; OLE containers store and display OLE objects. Oracle Forms is an OLE container application, and MS Word is an example of an OLE server application.OLE server applications can create many object classes. During the installation of an OLE server application, the object classes that an OLE server can create are installed in a registration database. When you install MS Windows applications that support OLE, a registration database is created on your computer, if it does not already exist. The registration database contains the object classes that are valid for embedding and linking into a form module. For instance, MS Word classes include MS Word 6.0 Document, MS Word 6.0 Picture, and MS WordArt 2.0.
OLE objects are documents created from OLE server applications such as MS Word. Another example of an OLE object is a spreadsheet created in MS Excel. OLE objects are linked or embedded into compound documents created by OLE container applications such as Oracle Forms.
Embedded Objects
An embedded object, such as a spreadsheet or chart, is created by an MS Windows OLE server application and is embedded in an Oracle Forms form module. An embedded object is stored as part of a form module or as an item in the database.
You can modify the content of an embedded object within Oracle Forms if the OLE server application that created the OLE object is accessible by your computer. Editing an embedded object is performed with in-place activation or external activation.
An example of object embedding is to insert an MS Excel spreadsheet in an OLE container of a form module. The MS Excel spreadsheet is stored as part of the form module or as an item in the database; there is no separate source file containing the Excel spreadsheet.
Linked Objects
A linked object, such as a word processor document, is created by an MS Windows OLE server application. A linked object is stored in a separate source file created from an OLE server application. An image representation of the linked object and information about the location of the linked object's source file is stored in a form module or as item in the database. The content of the linked object is not stored as part of a form module or in the database; it is retained in a separate file known as the linked source file.
An example of object linking is to link an MS Word document in a form module. An image of the MS Word document appears in the OLE container of the form module and the location of the MS Word file is stored as part of the form module or as an item in the database.
In-Place Activation : When container surrounds the object ( oracle forms surrounding the spreadsheet) Some of the forms menu is replaced by the objects menu. to deactivate click outside the window
External Activation : is started in a separate window. does not replace forms menu/toolbar to deactivate explicit quit is required.
An OLE container in Oracle Forms is a type of custom item. A custom item in Oracle Forms can be an OLE Container, VBX Control, or User Area. In an OLE container, you can link or embed OLE objects. OLE objects can be used as base table items or contol items.
VBX Controls : VBX controls provide a simple method of building and enhancing user interfaces. The controls can be used to obtain user input and display program output.
VBX Controls in Oracle Forms :
A VBX control in Oracle Forms is a type of custom item. A custom item in Oracle Forms can be an OLE Container, VBX Control, or User Area. Like other Oracle Forms item types, VBX controls serve as a way to represent and manipulate data that displays on a form. VBX controls can be used as base table items or control items.
VBX Control as an Oracle Forms Item :
You can interchange a VBX control with other Oracle Forms items without affecting your intended use for the item. A text item in Oracle Forms displays data from the database on a form. A VBX control can accomplish the same task. For example, a text item displaying the number 10 can be depicted by a VBX control that is a knob. Both items also reflect changes in the data from the database. For instance, when the number 10 changes to the number 5, the number 5 appears in the text item on the form and the knob control redirects its position to represent the number 5.
PL/SQL Interface to Foreign Functions :
Foreign functions can be accessed through a user exit interface or through a PL/SQL interface. In most instances, creating user exit interface requires relinking Oracle Forms Runtime. Creating a PL/SQL interface to foreign functions requires the use of the ORA_FFI built-in package (Oracle Foreign Function Interface). The ORA_FFI package provides a public interface for calling foreign functions from PL/SQL. There are many benefits for accessing foreign functions through a PL/SQL interface:
o Additional code in the foreign function source code is not required.
o Tools for compiling and linking the foreign function are not necessary.
o Conflicts with shared libraries such as dynamic link libraries (DLLs)are reduced or eliminated.
o Relinking Oracle Forms Runform is not required.
Using a PL/SQL interface provides a much looser bind than that of a user exit interface, because accessing foreign functions through a user exit interface depends on a single dynamic link library and usually requires the relinking of Oracle Forms Runform.
ORA_FFI Package :
To access a foreign function through a PL/SQL interface, you need to know the foreign function's prototype and function's location. Relinking Oracle Forms and creating dynamic link libraries are unnecessary when using a PL/SQL interface to access foreign functions.
Types of Foreign Functions :
o Oracle Precompiler foreign functions
o OCI (ORACLE Call Interface) foreign functions
o non-ORACLE foreign functions
You can also write foreign functions that combine both the ORACLE Precompiler interface and the OCI.
Oracle Precompiler foreign functions :
With embedded SQL commands, an Oracle Precompiler foreign function can access Oracle databases as well as Oracle Forms variables and items. Although it is possible to access Oracle Forms variables and items, you cannot call Oracle Forms built-in subprograms from a foreign function. You can access Oracle Forms variables and items because you can use a set of Oracle precompiler statements that provide this capability.
OCI (ORACLE Call Interface) Foreign Functions :
An OCI foreign function incorporates the Oracle Call Interface. This interface allows you to write a subprogram that contains calls to Oracle databases. A foreign function that incorporates only the OCI (and not the Oracle Precompiler interface) cannot access Oracle Forms variables and items.
Non-Oracle Foreign Functions :
A non-Oracle foreign function does not incorporate either the Oracle Precompiler interface or the OCI. For example, a non-Oracle foreign function might be written entirely in the C language. A non-Oracle foreign function cannot access Oracle databases or Oracle Forms variables and items.
1. What built-in can you use to open a second form but keep the first form in control?
A*. OPEN_FORM
B . SYSTEM.MOUSE_FORM
C . SYSTEM.CURRENT_FORM
D . CALL_FORM
E . FIND_FORM
Explanation:
OPEN_FORM
The OPEN_FORM built-in includes a NO_ACTIVATE option stipulating that the form being opened should not receive control.
2. Which system variable can tell you the record on which the user has placed focus?
A . CURSOR_ITEM
B . CURRENT_RECORD
C*. CURSOR_RECORD
D . CURRENT_ITEM
Explanation:
CURSOR_RECORD
This is a record-level requirement, so the ITEM variables will not help you. There is no CURRENT_RECORD system variable.
3. You are modifying a Customer form so that it has the ability to place the contents of the customer's ZIP code into a separate form named Dealer. What built-in will you use?
A . ADD_PARAMETER
B . SET_APPLICATION_PROPERTY
C . WRITE_VALUE
D*. COPY
E . NAME_IN
Explanation:
COPY
This question requires the use of form bind variables, which cannot be referenced directly across modules. The built-ins NAME_IN and COPY are used to read and write values across modules with form bind variables. In this case, COPY is the right choice, because you wish to place values in another field, rather than read them from the field.
4. You have added an LOV to a form and now want to add code to determine whether the user has made a choice from the LOV or dismissed it. What built-in will help you?
A . WHEN-LIST-CHANGED
B . GET_LOV_PROPERTY
C . WHEN-LIST-ACTIVATED
D . POST-TEXT-ITEM
E*. SHOW_LOV
Explanation:
SHOW_LOV
The SHOW_LOV built-in has the ability to display an object (an LOV), and also return a Boolean value to the calling program indicating whether or not the user selected a value from the LOV. If you selected one of the WHEN- or POST- choices, be sure to reread the chapter before the exam...those are triggers, not built-ins.
5. Which of the following allows you to collect objects and easily reuse them in other forms?
A*. Object group
B . Trigger library
C . PL/SQL Library
D . Object package
E . Property class
Explanation:
Object group
Review the section "Grouping Related Items for Reuse" if you need a refresher on this topic.
6. You have created a client-lookup canvas, complete with code and all the necessary objects, that has proven popular enough that others want to use it in their applications. How can you make it available to the other applications from one central source point?
A . Copy the form module into a PL/SQL library.
B . Copy the canvas, code, and objects into a PL/SQL library.
C*. Copy the canvas, code, and objects into an object library.
D . Copy the form module into an object library.
E . Place the canvas, code, and objects into an object group that the other developers will reference.
Explanation:
Copy the canvas, code, and objects into an object library.
Review the section "Reusing Objects from an Object Library" if you need a refresher on this topic.
7. You have inherited an application from a developer who left to pursue a career in music. While looking through the SALARY item's Property Palette, you notice that to the left of its Data Type property is an arrow with an "X" at its point. What does this symbol indicate?
A . The setting has been derived from a Visual Attributes group, but has been overridden.
B . The setting has been derived from a Visual Attributes group.
C . The setting for this property is invalid.
D . The setting has been derived from a property class.
E*. The setting has been derived from a property class, but has been overridden.
Explanation:
The setting has been derived from a property class, but has
been overridden.
A Data Type property can only be derived from a property class. The arrow indicates that this has been done. The "X" at its point indicates that the setting inherited from the property class has been manually overridden for this item.
8. You want to read the value in an item on another form and use it in your current form. What built-in will you use?
A . FIND_ITEM
B . NAME_IN
C . SET_ITEM_PROPERTY
D . GET_ITEM_VALUE
E*. COPY
Explanation:
NAME_IN
Some of the built-in names offered as choices don't exist. Of the ones that do, NAME_IN and COPY are used to read and write values from/to items in other form modules. In this case, NAME_IN is the right choice, because you wish to read a value in another field.
9. You create a module with two forms: Employee and Product. The application allows users to have the forms open simultaneously. The users notice that when they save an Employee record, any unsaved Product records are also committed; the reverse is also true. This is not the behavior they want. What can you do to change it?
A . Open the first form using the OPEN_FORM built-in with the ACTIVATE option.
B . Open the first form using the OPEN_FORM built-in with the SESSION option.
C*. Open the second form using the OPEN_FORM built-in with the SESSION option.
D . Open the second form using the GO_FORM built-in with the ACTIVATE option.
E . Open the second form using the GO_FORM built-in with the NO_ACTIVATE option.
Explanation:
Open the second form using the OPEN_FORM built-in with the SESSION option.
Review the section "Calling One Form from Another" if you need a refresher on this topic.
10. You need a built-in that will copy a value into a global variable and create the variable if it is undefined. What built-in has this ability?
A*. DEFAULT_VALUE
B . SET_VAR
C . COPY
D . CREATE_VAR
Explanation:
DEFAULT_VALUE
Review the section "Built-In Subprograms that Assist Flexible Coding" if you need a refresher on this topic.
11. You are writing versatile code that checks whether your Employee form's Salary field is visible; if it is, the code hides it; if it isn't, the code shows it. What built-in can you use to determine which route the code will take?
A*. GET_ITEM_PROPERTY
B . GET_BLOCK_PROPERTY
C . GET_FORM_PROPERTY
D . GET_WINDOW_PROPERTY
E . GET_RECORD_PROPERTY
Explanation:
GET_ITEM_PROPERTY
Visibility is an item-level property, so you would use the GET_ITEM_PROPERTY to determine the current status.
12. You want to use a single multipage tab canvas for different purposes. Which built-in lets you set the labels for the pages dynamically when the application is running?
A . SET_PAGE_PROPERTY
B . SET_CANVAS_PROPERTY
C . SET_TAB_PROPERTY
D*. SET_TAB_PAGE_PROPERTY
Explanation:
SET_TAB_PAGE_PROPERTY
Review the section titled "Built-In Subprograms that Assist Flexible Coding" if you need a refresher on this topic.
13. Which built-in enables you to change window properties dynamically while the application is running?
A*. SET_WINDOW_PROPERTY
B . SET_VIEW_PROPERTY
C . GET_CANVAS_PROPERTY
D . GET_WINDOW_PROPERTY
E . SET_CANVAS_PROPERTY
Explanation:
SET_WINDOW_PROPERTY
14. At what object level do you place WHEN-WINDOW- triggers?
A*. Form
B . Canvas
C . Block
D . Window
Explanation:
Form
Windows do not have the capability to hold triggers, so you need to define a WHEN-WINDOWS- trigger one level higher in the object hierarchy: the form level.

15. What happens to a function key's default functionality when you define a key trigger for the function key?
A . The default functionality overrides whatever code is contained in the key trigger.
B . The default functionality is augmented by whatever code is contained in the key trigger.
C*. The default functionality is replaced by whatever code is contained in the key trigger.
D . Forms determines each time the function key is pressed whether it should execute the default functionality or the key trigger.
Explanation:
The default functionality is replaced by whatever code is contained in the key trigger.

16. Which property and setting will prohibit the user from deleting a master record if related detail records exist?
A . Relation property Delete Record Behavior set to Cascading
B . Master block property Delete Record Behavior set to Non Isolated
C . Master block property Delete Record Behavior set to Cascading
D*. Relation property Delete Record Behavior set to Non Isolated
E . Master block property Delete Record Behavior set to Isolated
Explanation:
Relation property Delete Record Behavior set to Non Isolated

17. What trigger can institute a default functionality, or no functionality, for every function key that does not have an explicit trigger?
A . KEY-ELSE
B . KEY-FUNCTION
C . KEY-Fn
D*. KEY-OTHERS
E . KEY-NONE
Explanation:
KEY-OTHERS
The purpose of the KEY-OTHERS command is to replace the functionality of any key that can have a trigger assigned to it but does not.
18. What built-in gives you the ability to change the cursor's appearance dynamically?
A*. SET_APPLICATION_PROPERTY
B . SET_CANVAS_PROPERTY
C . SET_CONTEXT
D . SET_ITEM_PROPERTY
E . SET_FORM_PROPERTY
Explanation:
SET_APPLICATION_PROPERTY

19. What built-in enables you to dynamically control when a detail block is populated?
A . SET_BLOCK_PROPERTY
B*. SET_RELATION_PROPERTY
C . SET_ITEM_PROPERTY
D . SET_WINDOW_PROPERTY
Explanation:
SET_RELATION_PROPERTY

20. What trigger is necessary for implementing a cascading delete in a master/detail relation?
A . PRE-CASCADE
B . PRE-POST
C*. PRE-DELETE
D . PRE-UPDATE
E . POST-CASCADE
Explanation:
PRE-DELETE
The PRE-DELETE trigger is the only one that has the capability to intercept a master-record deletion, check to determine if related detail records exist, and delete those detail records before proceeding to delete the master record. A PRE-CASCADE, POST-CASCADE, or PRE-POST trigger do not exist.
21. You need to add a display item to a form. The item will display a calculated total summarizing data from several different tables. You do not want to create any new objects in the database. What is the best course of action to take?
A . Using the Data Block Wizard, create a block with a stored procedure as its data source type.
B . Using the Data Block Wizard, create a block with a view as its data source type.
C . After creating a data block manually, set its Query Data Source Columns property to the desired columns and write the appropriate select command in its Query Data Source Arguments property.
D*. After creating a data block manually, set its Query Data Source Type to FROM clause query, and write the appropriate select command in its Query Data Source Name property.
Explanation:
After creating a data block manually, set its Query Data Source Type to FROM clause query and write the appropriate select command in its Query Data Source Name property. You cannot use a stored procedure or a view because both of these require adding a new item to the database. The correct approach is using a FROM clause query.
22. What built-in enables you to replace the query associated with a record group?
A*. POPULATE_GROUP_WITH_QUERY
B . CREATE_GROUP
C . ADD_GROUP_ROW
D . SET_GROUP_QUERY
Explanation:
POPULATE_GROUP_WITH_QUERY
23. What built-in can you use to open a second form modally?
A . OPEN_FORM
B*. CALL_FORM
C . NEW_FORM
D . RUN_PRODUCT
Explanation:
CALL_FORM
CALL_FORM is the built-in that opens forms in a modal window.
24. What built-in can provide the name of the current form?
A*. GET_APPLICATION_PROPERTY
B . GET_BLOCK_PROPERTY
C . GET_WINDOW_PROPERTY
D . GET_FORM_PROPERTY
Explanation:
GET_APPLICATION_PROPERTY
25. What built-in can populate a dynamic list item on a form with values from a record group?
A*. POPULATE_LIST
B . POPULATE_LIST_WITH_QUERY
C . SET_LIST_VALUES
D . RETRIEVE_LIST
Explanation:
POPULATE_LIST

26. What built-in enables you to change a nonquery record group into a query record group?
A . POPULATE_GROUP
B . CREATE_GROUP_FROM_QUERY
C*. POPULATE_GROUP_WITH_QUERY
D . POPULATE_LIST_WITH_QUERY
Explanation:
POPULATE_GROUP_WITH_QUERY
The purpose of the POPULATE_GROUP_WITH_QUERY built-in is to fill a record group with data based on a given query, even if the record group was originally a nonquery group.

27. How can you base a data block on a stored procedure that uses a ref cursor?
A . Using the Data Block Wizard, specify a data source type of table.
B*. Using the Data Block Wizard, specify a data source type of stored procedure.
C . After creating a data block manually, set the Query Data Source Name property to the appropriate stored procedure.
D . After creating a data block manually, set the Query Data Source Columns property to the appropriate stored procedure.
Explanation:
Using the Data Block Wizard, specify a data source type of stored procedure.
The options detailing the creation of a data block manually specify using the name of the stored procedure in properties not designed to hold a procedure name. Using the Data Block Wizard, you do not have to specify a data source type of table when you also have the option for stored procedure.
28. Name a benefit of using a FROM clause query as the basis for a data block.
A . Can perform server joins, calculations, and lookups without needing specific access rights to tables
B . Can utilize any PL/SQL code
C*. Can perform server joins, calculations, and lookups without needing to create a view
D . Can include user-defined parameters
Explanation:
Can perform server joins, calculations, and lookups without needing to create a view
The essence of the FROM clause query is its capability to nest SQL select statements in subqueries that perform lookups, table joins, and calculations without relying on a database view.

29. What built-in enables you to populate a record group with data that can be filtered dynamically at runtime?
A . CREATE_GROUP_FROM_PARAMETER
B . POPULATE_LIST
C*. POPULATE_GROUP
D . SET_GROUP_FILTER
Explanation:
POPULATE_GROUP

30. You have created a sales application that uses one form for the sales ticket and a second form to list the items being purchased. When the second form is called, the sales ticket is still open and has pending changes. What mode will the second form be opened in?
A*. Post-only mode
B . Commit mode
C . Enter-query mode
D . Open-transaction mode
Explanation:
Post-only mode

31. What built-in enables you to change the contents of a static record group at runtime?
A . POPULATE_GROUP_FROM_QUERY
B . POPULATE_GROUP
C . ADD_GROUP_ROW
D*. You cannot change the contents of a static record group at runtime.
Explanation:
You cannot change the contents of a static record group at runtime.
The definition of a static group is one whose contents cannot be changed at runtime.

32. What built-in enables you to pass data from a record group to a separate Oracle graph?
A . PASS_GROUP_DATA
B*. RUN_PRODUCT
C . OPEN_REPORT_WITH_GROUP
D . PASS_GROUP
Explanation:
RUN_PRODUCT
The RUN_PRODUCT built-in is designed to open other forms of graphics in their respective runtime programs.

33. When you need to design a pair of forms in which one passes values to the other, when and where should you define the parameters that will accept the values?
A*. In the called form, at design time
B . In the calling form, at design time
C . In the calling form, at runtime
D . In the called form, at runtime
Explanation:
In the called form, at design time
A parameter that is to be received must be defined at design time, and of course, it must be defined in the called form. See the section titled "Passing Data Between Forms Using Parameter Lists" for a refresher on this topic.

34. What reusable component enables you to lead your users through complicated processes?
A . Navigator class
B . ActiveX controls
C . Standard Object library
D . Picklist class
E*. Wizard class
Explanation:
Wizard class
The Wizard class enables you to create your own custom wizards, which can lead users through complicated processes.
35. What reusable component enables you to create an Object Navigator-like interface for your own applications?
A . Picklist class
B . ActiveX controls
C . Calendar class
D*. Navigator class
E . Standard Object library
Wizard class
Explanation:
Navigator class
The Navigator class contains objects that make it easy to implement a Navigator interface in your own applications.

36. What built-in enables you to find the internal ID of a timer?
A*. FIND_TIMER
B . WHEN-TIMER-EXPIRED
C . SET_TIMER
D . CREATE_TIMER
E . SET_TIMER_PROPERTY
Explanation:
FIND_TIMER
The FIND_TIMER built-in returns the internal ID of whatever timer's name is provided as an argument.

37. What reusable component enables you to create a customized SmartClass?
A . Picklist class
B . ActiveX controls
C . Calendar class
D . Navigator class
E*. Standard Object library
Explanation:
Standard Object library

38. What built-in enables you to eliminate a timer?
A*. DELETE_TIMER
B . REMOVE_TIMER
C . FIND_TIMER
D . SET_TIMER
E . SET_TIMER_PROPERTY
Explanation:
DELETE_TIMER
The DELETE_TIMER built-in's sole purpose is to deactivate and eliminate timers. A REMOVE_TIMER built-in does not exist.

39. You moved a number of your application's program units over to the server and started experiencing DBMS errors. What built-in can you use to capture these errors and the information they return?
A . DBMS_ERROR
B . DBMS_ERROR_NUM
C . DBMS_ERROR_STRING
D*. DBMS_ERROR_TEXT
Explanation:
DBMS_ERROR_TEXT
The DBMS_ERROR_TEXT built-in is designed specifically to return the text of error messages sent back by the database server.

40. What trigger is used to respond to timers, and at what level is it most commonly defined?
A . WHEN-TIMER-BEGINS at the block level
B . ON-TIMER at the window level
C . ON-TIMER-EXPIRE at the form level
D . ON-TIMER-BEGIN at the block level
E*. WHEN-TIMER-EXPIRED at the form level
Explanation:
WHEN-TIMER-EXPIRED at the form level

41. What trigger should you use to activate a calendar when the user presses the List of Values function key while in a date field?
A . WHEN-LOV-OPEN
B . ON-LIST-OPEN
C . KEY-LIST-OPEN
D . ON-LISTVAL
E*. KEY-LISTVAL
Explanation:
KEY-LISTVAL
The KEY-LISTVAL trigger fires whenever the user presses the LOV function key.

42. What are the steps for embedding an existing chart on a form that is open in the Layout Editor?
A . Execute the File | Import menu command, identify the chart file, and move the resulting chart to the correct position on the canvas.
B . Invoke the Chart Wizard, identify the chart file, and move the resulting chart to the correct position on the canvas.
C*. Create a chart item manually using the Chart Item button, identify the chart file in the item's Property Palette, and move the resulting chart to the correct position on the canvas.
Explanation:
Create a chart item manually using the Chart Item button, identify the chart file in the item's Property Palette, and move the resulting chart to the correct position on the canvas.
The Chart Wizard is only useful for creating new charts, so it is not a correct answer because the question specifies that you are dealing with an existing chart. The command File | Import does not exist in Form Builder. When dealing with an existing chart file, you bypass the Chart Wizard, create a new chart item manually, and alter the new item's properties to use the existing chart file.

43. What built-in enables you to manipulate table structures at runtime?
A . DDL_RUNTIME
B*. FORMS_DDL
C . RUNTIME_DDL
D . DDL_FORMS
E . FORMS_RUNTIME
Explanation:
FORMS_DDL
The FORMS_DDL built-in gives you the ability to execute SQL commands during runtime. All other potential answers to this question were made up (FORMS_RUNTIME is a program, not a built-in).

44. What built-in enables you to determine which timer fired a WHEN-TIMER-EXPIRED trigger?
A*. GET_APPLICATION_PROPERTY
B . GET_TIMER_PROPERTY
C . SYSTEM.TIMER
D . FIND_TIMER
Explanation:
GET_APPLICATION_PROPERTY

45. What file format must a third-party external procedure be in for Forms 6i to use it?
A . PL/SQL8
B*. DLL
C . C++
D . Structured Query Language
Explanation:
DLL
An external procedure can be written in a variety of third-party languages, but it must be stored in the .dll format in order for Forms 6i to use it.
46. What is the return data types of id_null, show_lov
A. Returns a Integer value.
B. Returns a char value
C. * Returns a Boolean value
D. None of the above
Explanation:
Returns a Boolean value
47. What is the order of firing the following triggers((Both are in form-level))
1. when-new-form-instance
2. pre-text-item
A. *Pre-Text , when-new-form-instance
B. When-new-form-instance, pre-text-item
C. None of the above
Explanation:
Pre-Text , when-new-form-instance

48. Difference between Post Query and Pre Query.
A. Post Query fires only once, Pre Query fires for each return row.
B. When Post Query fires, Pre Query returns more than one row.
C. *Post Query fires once for each record fetched from the query Pre Query fires only once.
D. None of the above
Explanation:
Post Query fires once for each record fetched from the query Pre Query fires only once.
49. What is the sequence of triggers firing?
Pre-form, post-form, when-new-form-instance, pre-text-item, pre-block, pre-record.
A. Pre-form, pre-record, pre-block, pre-text-item, post-form, when-new-form-instance
B. Pre-form, pre-block, pre-record, pre-text-item, post-form, when-new-form-instance
C. Pre-form, When-new-form-instance, pre-block, pre-record, pre-text-item, post-form
D. *Pre-form, pre-block, pre-record , pre-text-item, When-new-form-instance, post-form.
Explanation:
Pre-form, pre-block, pre-record , pre-text-item, When-new-form-instance, post-form.
50. What is the validation unit property of form module?
A. *Record_Scope, Item_Scope, Block_Scope, Default_Scope
B. Data_Scope, Trigger_Scope
C. Static_Scope, Dynamic_Scope
D. None of the above
Explanation:
Record_Scope, Item_Scope, Block_Scope, Default_Scope
Validation unit property of form module is
Record_Scope, Item_Scope, Block_Scope, Default_Scope and Form_Scope
1. You open a single property sheet to display properties for your DEPARTMENT data block and DEPARTMENT canvas simultaneously. Which Property Palette display mode is likely to show more properties?
A . Intersection
B*. Union
Explanation:
Union
The Intersection display mode shows only the properties that multiple selected objects have in common, while the Union display mode shows all properties for all selected objects, whether the objects share the properties in common or not.

2. What does it mean when the Property Palette displays ***** as a property's value?
A*. Two or more objects are selected, and their values for that property are not the same.
B . The value "*****" will be inserted into the field automatically.
C . You cannot update that property for the object you have selected.
D . The property is not applicable for the object you have selected.
Explanation:
Two or more objects are selected, and their values for that property are not the same
Because the Property Palette cannot display more than one value per property, the only way it can deal with multiple objects is to display something special when those objects' values are different. The special display is *****.
3. What happens if you select multiple objects, open the Property Palette, and change a property's value?
A . The changed value displays as *****.
B . The Property Palette shows each object's old and new values for that property.
C*. The change is applied to all selected objects.
D . You cannot change a property for multiple objects at one time.
Explanation:
The change is applied to all selected objects

4. Which data block property would you consider changing if your records include LONG items that are not likely to be edited?
A*. Update Changed Columns Only
B . Update Allowed
C . Query Allowed
D . DML Array Size
Explanation:
Update Changed Columns Only
If your records include LONG items that are not likely to be edited, this data block property can improve application performance by keeping the application from sending the voluminous LONG data back to the server during an update command.
5. What does the Property Palette toolbar field labeled Find do?
A . Locates other objects containing the same property as the one you currently have selected
B . Allows you to search-and-replace a given property value with a different value
C . Locates Form Builder files on your hard disk
D*. Forces the Palette to place its focus on the first property matching the characters you type
Explanation:
Forces the Palette to place its focus on the first property matching the characters you type
The Property Palette toolbar field labeled Find makes the Property Palette quickly jump to specific properties. If the group containing the matching property is closed, the Find field will even open it up for you.
6. What would be the result of completely deleting a data block from the Object Navigator?
A . The data blocks and components are deleted but can be retrieved using the Edit | Undo command.
B . The data blocks and components are irreversibly deleted, and all components from the data block are removed from any canvas that contained them, including the data block's frame and boilerplate title text.
C*. The data blocks and components are irreversibly deleted, and all components from the data block are removed from any canvas that contained them, but the data block's frame and boilerplate title text will stay on the canvas.
D . The data blocks and components are irreversibly deleted, and all components from the data block stay on the canvas and must be deleted manually.
Explanation:
The data blocks and components are irreversibly deleted, all components from the data block are removed from any canvas that contained them, but the data block's frame and boilerplate title text will stay on the canvas.

7. What does the Property Palette context bar do?
A . Nothing
B*. Identifies which object's properties are currently being displayed by the Property Palette
C . Identifies which program you are in when you open the Property Palette
D . Provides help instructions based on your location in the Property Palette
Explanation:
Identifies which object's properties are currently being displayed by the Property Palette.

8. Which of the following occur if you copy multiple properties from one object and paste them into another?
A*. No Answer is Correct
B . Properties not relevant to the destination object will be added to that object.
C . Properties with blank properties will be pasted.
D . It is not possible to copy multiple properties at one time.
E . If the destination is a different type of object than the source, the destination object will be changed to the same type of object as the source.
Explanation:
No Answer is Correct
The Property Palette can copy and paste individual or multiple properties. When copying multiple properties, the Palette pastes only the properties for which an actual value is shown, and only the properties that are relevant to the object receiving the pasted properties.
9. You created an LOV for stock items and included a Quantity Currently In Stock column in the LOV. How can you ensure that the user sees accurate "in stock" numbers each time the LOV is invoked?
A . There is no way to ensure this.
B . Programmatically requery all tables in the application when the user opens that canvas.
C*. Enable the LOV's Automatic Refresh property.
D . Enable the LOV's Automatic Select property.
Explanation:
Enable the LOV's Automatic Refresh property
The Automatic Refresh property determines whether the LOV's underlying query executes every time the LOV is invoked, or only the first time it is invoked. Setting the property to Yes configures it to requery every time.
10. You have created a SALES_TICKET form for a point-of-sale application. You now want to modify the Transaction_Date_Time item in the form so it is automatically populated with the current date and time each time a new record is created. How can you accomplish this?
A . Set the Default Value property to SYSDATE.
B . Set the Initial Value property to SYSDATE.
C . Set the Default Value property to $$DATETIME$$.
D*. Set the Initial Value property to $$DATETIME$$.
Explanation:
Set the Initial Value property to $$DATETIME$$.
The is no Item property called Default Value, and while SYSDATE is a valid parameter in a SQL query, it will not work in the Initial Value property; you must use $$DATETIME$$.
11. What does freezing the Property Palette do?
A . Enables you to change a property in multiple objects at one time
B . When multiple objects are selected, shows only those properties that all selected objects share in common
C*. Forces the Palette to continue displaying properties for the currently selected object(s), regardless of what object(s) you select from that point on
D . Opens a second Palette for comparing multiple objects' properties
Explanation:
Forces the Palette to continue displaying properties for the currently selected object(s), regardless of what object(s) you select from that point on

12. What is the best way to ensure that an item cannot accept query criteria?
A . Set the item's Query Length property to 0.
B . Set the item's Queryable property to No.
C*. Set the item's Query Allowed property to No.
D . Set the item's Disable Query property to Yes.
Explanation:
Set the item's Query Allowed property to No
There are no properties named Disable Query or Queryable. Setting the Query Length property to 0 simply tells Oracle Forms 6i to use the item's length as the maximum query length.
13. How many characters would you need to type in an LOV to select the WHEN-KEY-UP item from a list containing WHEN-BUTTON-PRESSED, WHEN-KEY-DOWN, and WHEN-KEY-UP, assuming they are the only items in the list and the LOV's properties are set to automatically display the LOV and automatically enter the value once a row is selected?
A . 3 characters
B . 1 character
C*. 2 characters
D . 10 characters
Explanation:
2 characters
Because all three choices begin with "WHEN-," the LOV only cares about the first differentiating character, which is the "K" that identifies the KEY group. The next character needed is the "D" to select DOWN, after which the row's key value will automatically be entered into the text item.
14. What is the definition of the term "text item"?
A . Any control on your form that allows the user to view and edit text, numbers, or dates
B . Any control on your form that allows the user to view and edit text
C . Any control on your form that allows the user to view and edit text or numbers
D . The label preceding a field on a form
E*. Any control on your form that allows the user to view and edit text, numbers, dates, or long data
Explanation:
Any control on your form that allows the user to view and edit text, numbers, dates, or long data

15. You have created an LOV for a text item on your canvas, and you would like the LOV to appear automatically each time the user enters that text item. What is required to make that happen?
A . Set the Automatic Select property in the LOV Property Palette to Yes.
B*. Set the Automatic Display property in the LOV Property Palette to Yes.
C . Set the Automatic Refresh property in the text item Property Palette to Yes.
D . Set the Automatic Refresh property in the LOV Property Palette to Yes.
E . Set the Automatic Display property in the text item Property Palette to Yes.
Explanation:
Set the Automatic Display property in the LOV Property Palette to Yes.
No text item property would effect this change. Of the LOV properties listed, Automatic Refresh determines whether the LOV's contents are requeried each time it is opened, and Automatic Select specifies whether the selected LOV row is placed into the specified text item without the user having to double-click on the row or click the OK button.
16. Which of the following actions can a user do with a display item?
A . Delete existing database values.
B . Update existing database values.
C*. View existing database values.
D . Insert new database values.
E . All Answers are Correct
Explanation:
View existing database values
A display item shows data but does not allow the user to change it. In essence, it is a read-only field.
17. To which of the following items does a control block directly correspond?
A . All Answers are Correct
B . Database
C*. No answer is correct
D . Table
E . Column
Explanation:
No answer is correct
A control block is not associated with a database object. Instead, it contains either controls (such as buttons), or a group of items with single values (such as calculated subtotals).
18. When a control block is created, what items does it contain?
A . None, because, you cannot put items in a control block.
B . Text items for all columns in the related database table.
C*. None. You must manually create any items that will go into a control block.
Explanation:
None. You must manually create any items that will go into a control block.
By definition, a control block is not related to a database table. And you can put items into a control block-that is what it's for. But you must do it manually after the block is created.
19. What is the definition of an input item?
A . A form object through which the user can view data
B . A dialog box
C . A graphics element affecting how a chart will look
D*. A form object through which the user can enter and change data
E . A form object through which the program can enter and change data
Explanation:
A form object through which the user can enter and change data
Input items are the basis of forms-they enable a user to add or edit data. A dialog box may contain input items, but the dialog itself is a window, not an input item.

20. Users of your application have requested that they be able to see STOCK and CUSTOMER canvases on the screen simultaneously. You add a second window to the application. How can you make the CUSTOMER canvas use the second window?
A . Change the window's Primary Canvas property.
B . Change the canvas's Visual Attributes group.
C*. Change the canvas's Window property.
D . It is not possible to change a canvas's display window.
Explanation:
Change the canvas's Window property
A canvas's Window property determines which window the canvas is visible in. The Visual Attributes Group has no window selection properties, and a window's Primary Canvas property specifies the primary canvas for a window that displays multiple canvases.
21. Which check box property controls the text that displays next to the check box?
A*. Label
B . Text
C . Name
D . A check box's text is fixed and cannot be changed.
Explanation:
Label
Remember that both Prompt and Label can place text next to a radio button.
22. Which type of canvas is best suited for displaying tutorial text on the same canvas as the form about which the user is being taught?
A . Tab
B . Viewport
C*. Stacked
D . Toolbar
E . Content
Explanation:
Stacked
The requirement that the tutorial text be visible on the same form limits the choices to either stacked or tab. A tab canvas might be useful for a multipage tutorial, but the requirements did not state the need for multiple pages, so a simple stacked canvas will fulfill the requirement.
23. You are working with an existing radio group in the Layout Editor and try to add a radio button to the group. The Layout Editor responds by:
A . Displaying a warning message, and then returning you to the Layout Editor
B . Offering to create a check box instead, since a radio button group already exists
C*. Presenting a dialog box giving you the chance to select a radio group for the new radio button, or create a new radio group for it
Explanation:
Presenting a dialog box giving you the chance to select a radio group for the new radio button, or create a new radio group for it
The Layout Editor is willing to add buttons to an existing radio group. It just needs to know which group will get the new button, or if a completely new group is what you desire.
24. Your Employee form includes a SALARY text item. You want to ensure that standard users cannot input or change a salary value, but you want the value to look exactly like a regular field. What is the best way to do this?
A . Set the item's Enabled property to No, and its Update Allowed property to No.
B*. Set the item's Insert Allowed property to No, and its Update Allowed property to No.
C . Set the item's Enabled property to No.
D . It is not possible for an unchangeable item to look like a changeable item.
Explanation:
Set the item's Insert Allowed property to No, and its Update Allowed property to No
Changing an item's Enabled property to No causes its contents to display with light gray characters instead of black. Therefore, this is the only valid choice.
25. What type of canvas can easily eliminate the need for a menu in your application?
A*. Toolbar
B . Content
C . Tab
D . Stacked
Explanation:
Toolbar
A toolbar canvas's sole purpose is holding buttons that initiate actions. The buttons can replace every menu action your users would need to take.
26. The DEPARTMENT table in your database has been augmented with a BUDGET column. You want to add BUDGET as an item on your Department form, but the item should be a read-only text box so users cannot change it. The best way to do this is:
A . In the Layout Editor, create a display item and set its Insert Allowed property to No, its Update Allowed property to No, and its Database Item property to No.
B . In the Data Block Wizard, move the BUDGET column into the Available Items area. Change the new data block item's Insert Allowed and Update Allowed properties to No. Proceed to the Layout Editor and add BUDGET as a text item.
C . In the Data Block Wizard, move the BUDGET column into the Available Items are C:
Proceed to the Layout Editor and add BUDGET as a text item, and change the item's Insert Allowed and Update Allowed properties to No.
D*. In the Layout Editor, create a display item and set its Column Name property to BUDGET.
Explanation:
In the Layout Editor, create a display item and set its Column Name property to BUDGET.
Setting an item's Database Item property to No keeps it from retrieving database data, eliminates two answers, but it creates a normal-looking text box that actually allows the user to type in data; it isn't until the user tries to save their work that the data block's Insert Allowed and Update Allowed properties halt the action. This is not optimal design. The third incorrect answer creates an application in which the field's data cannot be changed, but the user can still place focus on the field, which is also not optimal.
27. You have created a form that contains two canvases, ten database items, and four buttons. The items have all been placed into a group, and the buttons have been placed into a separate group. What happens when you click one of the buttons in the Layout Editor?
A*. The group of buttons is selected.
B . Nothing is selected.
C . All groups are selected
D . The button is selected.
E . All items on the button's canvas are selected
Explanation:
The group of buttons is selected.
The primary reason for groups is to ensure that when any item in the group is selected, all items are selected with it.
28. Which canvas type is most dissimilar to the others?
A . Content
B . Stacked
C . Tab
D*. Toolbar
Explanation:
Toolbar
Content, stacked, and tab canvases are all intended to display database data. The toolbar canvas type is not; it is intended to display buttons that work in concert with the items on the other three canvas types.
29. What is the primary difference between tab and stacked canvases?
A . A stacked canvas can contain push buttons.
B . A stacked canvas obscures what is beneath it.
C*. A tab canvas can contain multiple pages.
D . A tab canvas looks much cooler.
Explanation:
A tab canvas can contain multiple pages.
The essence of a tab canvas is the fact that it consists of multiple pages of data, each page overlaying the others when it is selected by the user or developer. It is not possible to get this functionality from a single stacked canvas.

30. What trigger would you use to execute code each time a user modifies the value of a check box?
A . ON-NEW-CHECKBOX-INSTANCE
B . WHEN-CHECKBOX-UNCHECKED
C . WHEN-CHECKBOX-CLICKED
D . WHEN-CHECKBOX-CHECKED
E*. WHEN-CHECKBOX-CHANGED
Explanation:
WHEN-CHECKBOX-CHANGED

31. You would like to create a trigger that fires each time a window is closed by the user. You will most likely place the trigger at which of the following levels:
A . Canvas level
B . Window level
C . Data block level
D . Item level
E*. Form level
Explanation:
Form level
Windows do not have triggers. Placing the WHEN-WINDOW-CLOSED trigger at the Form level allows it to fire when any window in the module is closed.
32. What trigger would fire each time a new record is created?
A . ON-NEW-RECORD
B*. WHEN-NEW-RECORD-INSTANCE
C . WHEN-VALIDATE-RECORD
D . WHEN-DATABASE-RECORD
Explanation:
WHEN-NEW-RECORD-INSTANCE
ON-NEW-RECORD is not a valid trigger name. Choices WHEN-VALIDATE-RECORD and WHEN-DATABASE-RECORD fire at other times. Review the section "Supplementing the Functionality of Input Items" if you need a reminder on this topic.
33. What do you need to do to within Form Builder to run a form module in debug mode?
A . Enable Debug Messages and then run your form. The Debugger will appear automatically.
B*. Enable the Debug Mode button, run the form, and the Debugger displays automatically.
C . Run the form, and in the Forms Runtime program execute the Help | Debug menu command.
D . Enable the Debug Mode button, run your form, and in the Forms Runtime program execute the Help | Debug menu command.
Explanation:
Enable the Debug Mode button, run the form, and the Debugger displays automatically.
See the section "Running a Form Module in Debug Mode" for a refresher on this topic.
34. When does the PRE-QUERY trigger fire?
A*. After the user enters query criteria, but before the query executes
B . Before the form enters Enter-Query mode
C . After the form enters Enter-Query mode, but before the user enters query criteria
D . After the query executes, but before records are shown to the user
Explanation:
After the user enters query criteria, but before the query executes
The PRE-QUERY trigger fires after Enter-Query mode but before a query's select statement has been finalized, and therefore before the query is executed.
35. Which built-in causes an editor to display for a text item?
A . WHEN-NEW-ITEM-INSTANCE
B . SHOW-EDITOR
C*. SHOW_EDITOR
Explanation:
SHOW_EDITOR
SHOW-EDITOR is formatted as a trigger, not a built-in, and doesn't exist. WHEN-NEW-ITEM-INSTANCE exists but is also a trigger, not a built-in.
36. You want to write a trigger that screens a query condition. At what level will you place the trigger?
A . Block
B*. Form
C . Item
D . Record
Explanation:
Form

37. You wish to have certain values in a form initialized when the form is first opened. What trigger will you use?
A . WHEN-NEW-CANVAS-INSTANCE
B*. WHEN-NEW-FORM-INSTANCE
C . WHEN-NEW-FORM
D . WHEN-FORM-OPENED
E . WHEN-NEW-CANVAS
Explanation:
WHEN-NEW-FORM-INSTANCE
None of the other choices are valid triggers.
38. What part of a trigger specifies the trigger's actions?
A . Type
B . Name
C*. Code
D . Scope
Explanation:
Code
A trigger's actions are defined entirely by its code.
39. Which built-in causes an LOV to display for a text item that has one defined?
A . WHEN-NEW-ITEM-INSTANCE
B . WHEN-NEW-LOV
C . GO_ITEM
D*. SHOW_LOV
Explanation:
SHOW_LOV
WHEN-NEW-ITEM-INSTANCE and WHEN-NEW-LOV are triggers, not built-ins. GO_ITEM navigates to an item but does not open an LOV.
40. What trigger can you use to ensure that a query entered by the user includes at least one item that is indexed, and keep the query from occurring if not?
A*. PRE-QUERY
B . WHEN-CLEAR-BLOCK
C . ON-SELECT
D . POST-SELECT
E . ON-FETCH
Explanation:
PRE-QUERY
While the other triggers listed are query triggers, only the PRE-QUERY trigger fires before the select statement is executed.
41. What is the purpose of the KEY-OTHERS trigger?
A . Provides code to execute if a key's own trigger fails
B*. Provides code to execute if user presses a key that has no trigger attached
C . Provides code to execute if user presses wrong key
D . Provides code that accesses another key's trigger and executes the code it contains
Explanation:
Provides code to execute if user presses a key that has no trigger attached.
See the section "Form Trigger Categories" for a refresher on this topic.
42. Your data analysis application is slowing the network to a crawl. You analyze the queries users are performing and discover that the majority of their queries are too broad, returning many more records than necessary. You decide to require that any query have at least three fields containing criteria. What type of trigger can you use to enforce that requirement?
A . POST-QUERY
B . ON-NEW-QUERY-INSTANCE
C . PRE-UPDATE
D*. PRE-QUERY
E . POST-UPDATE
Explanation:
PRE-QUERY
The PRE-QUERY trigger fires before a query's select statement has been finalized, and is therefore ideal for screening query criteria before the query is executed.

43. You have created an alert with three buttons. What value will be returned if the user selects the second button?
A . BUTTON2
B . DIALOG_BUTTON2
C*. ALERT_BUTTON2
D . It depends on the choice being offered by the button.
Explanation:
ALERT_BUTTON2

44. What is the default level at which validation occurs in the Forms Runtime program?
A*. Item
B . Form
C . Block
D . Record
Explanation:
Item
By default, the Forms Runtime program validates an item immediately when the user tries to leave the item.
45. How does the Forms Runtime program respond when a user enters text into a text item that has an LOV attached and the VALIDATE_FROM_LIST property set to Yes?
A . The Forms Runtime program ignores the LOV if the user types a value directly into the field.
B . The Forms Runtime program populates the item automatically with the first value in the LOV that matches the user's entry.
C*. The Forms Runtime program opens the LOV and shows only items that match what the user has typed so far.
D . Validate From List is a Data Block property, not an Item property.
Explanation:
The Forms Runtime program opens the LOV and shows only items that match what the user has typed so far.
See the section "Introduction to Form Builder Validation Properties" for a refresher on this topic.
46. How can you cause a block to use a database sequence to get unique IDs?
A . Set the Initial Value property to :sequence.sequence-name.nextval.
B . Set the Validate From List property to :sequence.sequence-name.nextval.
C . Set the DML Array Size property to :sequence.sequence-name.nextval.
D*. This action is not possible.
Explanation:
This action is not possible.
Data blocks cannot read sequences, and in fact cannot store values at all. Items, on the other hand, can.
Give yourself half a point if you answered:
Set the Initial Value property to
:sequence.sequence-name.nextval.
which would have been the right answer if the question had referred to an item instead of a block, and remember to pay closer attention to the wording of questions. In some Oracle exam questions, a single word defines why one choice is right and another choice wrong.
47. You have written a contact-tracking application that includes a field for the last date a client was contacted. You want to use a trigger to guarantee that whenever the date in that field is changed, the date entered is later than the date that was there before. What is the best trigger to use?
A . PRE-UPDATE
B . ON-COMMIT
C . ON-UPDATE
D*. PRE-COMMIT
E . POST-UPDATE
Explanation:
PRE-COMMIT
PRE-COMMIT is a form-level trigger that fires only once at the beginning of a transaction, so it cannot perform validation on a row-by-row basis. ON-UPDATE and ON-COMMIT only occur if you have replaced the default Forms Runtime transaction processing. POST-UPDATE occurs after the update has occurred, so it is too late for a validity check. The remaining trigger, PRE-COMMIT, is perfect.
48. Your form module's Validation Unit property is set to Form. The module includes a data block that has a PRE-TEXT-ITEM trigger. At what point will the trigger fire?
A*. Never
B . When data is committed
C . Before the form is validated
D . After the form is validated
Explanation:
Never
The trigger will not fire because the object level defined in the trigger name item is smaller than the module's validation unit.
49. You want to add a delete-confirmation dialog to your application. You can do so by creating which type of object?
A*. Alert
B . Message
C . Editor
D . Message box
Explanation:
Alert

50. You would like to keep the user from seeing the Forms Runtime program's "nn records applied and saved" messages. What would you put in the form's WHEN-NEW-FORM-INSTANCE trigger?
A . :system.message_level := 0;
B . :system.suppress_working := 'TRUE';
C . :system.suppress_working := 'FALSE';
D*. :system.message_level := 5;
Explanation:
:system.message_level := 5;
See the section "Controlling System Messages" for a refresher on this topic.
51. What is the last DML statement processed during a commit transaction?
A*. INSERT
B . UPDATE
C . DELETE
D . POST
Explanation:
INSERT
The post command does not perform a commit. Of the three remaining choices, their processing order is delete, update, and then insert.
52. Which of these built-ins can you use in a PRE-UPDATE trigger?
A . DOWN
B . COMMIT_FORM
C*. No Answer is Correct.
D . All Answers are Correct.
E . GO_ITEM
Explanation:
No Answer is Correct.
Each built-in listed is a navigational built-in, which cannot be used within the navigational trigger PRE-UPDATE.
53. Which navigational built-in will move the focus to a subsequent record and place it on the same item it was on in the original record?
A . NEXT_BLOCK
B . NEXT_ITEM
C*. DOWN
D . The described action is not possible from a single built-in.
Explanation:
DOWN

54. You would like to modify your form so it uses array processing to send DML statements to the server in batches of 50. How would you do this?
A . Set the canvas's DML Array Size property to 50.
B*. Set the data block's DML Array Size property to 50.
C . Set the window's DML Array Size property to 50.
D . Array processing is limited to 25 records per batch.
Explanation:
Set the data block's DML Array Size property to 50.

1. What is the size of Varchar in Oracle 8.0 ?
a. 2000
b. 4000
c. 254
d. none of the above
2. What is the size of Varchar in Oracle 7.0 ?
a. 2000
b. 4000
c. 254
d. none of the above
3. The default value the lpad function takes is
a. a space (¡§ ¡§)
b. an asterisk
c. The default value is not optional
d. None of the above
4. The no. of columns that may be used as composite primary key in oracle 8
a. 8
b. 16
c. 32
d. none of the above
5. which of the following is true about add_months
a. we can pass a numerical value in first parameter
b. we can pass a negative value in second parameter
c. Both a & b
d. None of the above
6. The latest date that can be stored in oracle 8
a. 31st Dec 4012 A.D
b. 31st Dec 4011 A.D
c. Dec 31st ,9999
d. None of the above.
7. What happens when the first date is greater than the second date that is passed to the months_between function in oracle 8.
a. It gives an error
b. It gives a negative value
c. None of the above
8. Regarding the Summary query which of the following is true
a. The order of the base column list in the select statement should be same in the Group by clause.
b. The order of the base column list in the select statement need not be same in the Group by clause.
c. None of the above
9. Regarding the Summary query which of the following is true
a. All the base table columns selected in the select list should be specified in the Group by clause.
b. All the base table columns selected in the select list need not be specified in the Group by clause.
c. None of the above.
10. How do u mask the user from entering irrelevant data ?
a. Synonym
b. View
c. Index
d. sequence
11. What does the length function returns when applied to column of char datatype ?
12. Which of the following is true about the packages ?
a. Package specification should contain return type of a function
b. Package specification need not contain return type of a function
c. Both a & b
d. None of the above
13. If the first parameter is negative, then second parameter
a. need not be negative
b. there is no such restrictions
c. should be positive
d. None of the above.
14. Which of the following is true about procedures
a. The size of the parameter should be mentioned in a procedure.
b. The size of the parameter should not be mentioned in a procedure
c. Both a & b
d. None of the above.
15. ¡§REFERENCING¡¨ in oracle 8
a. used to mention referential integrity
b. used for creating views
c. there is no such word in oracle 8
d. none of the above
16. If you want to restrict the user, to enter the same values that has been stored in other table then what constraint do u use?
a. Entity integrity
b. Referential Integrity
c. Both a & b
d. None of the above
17. Which of the following is true about NULL?
a. when an arithmetic operation is performed on NULL, u will get the result as NULL
b. NULL is same as 0.
c. NULL is same as blank date.
d. None of the above
18. For a DDL statement, which of the following is true
a. A DDL statement is preceded and followed by commit.
b. All the DML statements gets committed even when u get an error after writing DDL statement.
c. Both a & b
d. None of the above.
19. Which of the following is true for update clause?
a. We can update two base tables simultaneously
b. U can use a subquery in SET clause of the UPDATE statement.
c. Both a & b
d. None of the above
20. Which of the following is true for delete?
a. Delete statement can be given without writing where clause.
b. We can delete two tables simultaneously
c. Both a & b
d. None of the above
21. How do u rename a table ?
a. Using alter command
b. Using RENAME
c. Dropping the table and creating once again
d. None of the above.
22. In oracle 7 which of the following is true about manipulating the view
a. View based on two base tables can be manipulated
b. View having a column which contain operation can be manipulated
c. Both a & b
d. None of the above
23. In oracle 8 which of the following is true about manipulating the view
a. View based on two base tables can be manipulated
b. View having a column which contain operation can be manipulated
c. Both a & b
d. None of the above
24. Which of the following is true about packages
a. We can write a procedure in package body which has not been specified in package specification.
b. We cannot write a procedure in package body which has not been specified in package specification.
c. Both a & b
d. None of the above.
25. Which of the following is true about outer joins
a. The outer join symbol should be present on any one side of the join.
b. The outer join symbol may be present on both the sides of the join
c. The outer join return the rows from the two tables that donot have matching records in other table.
d. None of the above
26. Which of the following is true about procedure and a function
a. Procedure should return a value and a function may not return a value
b. A function has to return a value and a procedure don¡¦t have to
c. Both a & b
d. None of the above.
27. What does OFA stands for ?
a. Oracle Flexible Architecture
b. Oracle Financials Applications
c. Optimal Flexible Architecture
d. None of the above

Oracle Questions
„h What is SQL*Plus and where does it come from?
„h How does one use the SQL*Plus utility?
„h What commands can be executed from SQL*Plus?
„h What are the basic SQL*Plus commands?
„h What is AFIEDT.BUF?
„h How does one restore session state in SQL*Plus?
„h What is the difference between @ and @@?
„h What is the difference between & and &&?
„h What is the difference between ! and HOST?
„h What is the difference between ? and HELP?
„h How does one enable the SQL*Plus HELP facility?
„h How can one disable SQL*Plus prompting?
„h How can one trap errors in SQL*Plus?
„h How does one trace SQL statement execution?
„h How can one prevent SQL*Plus connection warning messages?
„h How can uses be prevented from executing devious commands?
„h How can one disable SQL*Plus formatting?
„h Can one send operating system parameters to SQL*Plus?
„h Can one copy tables with LONG columns from one database to another?
„h Where can one get more info about SQL*Plus?
What is SQL*Plus and where does it come from?
SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.
SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first releases of Oracle, its interface was extremely primitive and anything but user friendly.
How does one use the SQL*Plus utility?
Start using SQL*Plus by executing the "sqlplus" command-line utility. Valid options are:
userid/password@db -- Connection details
/nolog -- Do not login to Oracle. You will need to do it yourself.
-s or -silent -- start sqlplus in silent mode. Not recommended for beginners!
@myscript -- Start executing script called "myscript"
Look at this simple example:
sqlplus /nolog
SQL> connect scott/tiger
SQL> select * from tab;
SQL> disconnect
SQL> exit
What commands can be executed from SQL*Plus?
One can enter three kinds of commands from the SQL*Plus command prompt:
1. SQL*Plus commands - SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have to be terminated with a semicolon (;), as is the case with SQL commands. The rest of this page is dedicated to SQL*Plus commands, eg.
SHOW USER
2. SQL commands - for more information see the Oracle SQL FAQ. Eg:
SELECT * FROM TAB;

3. PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/
What are the basic SQL*Plus commands?
The following SQL*Plus commands are available:
ACCEPT Get input from the user
DEFINE Declare a variable (short: DEF)
DESCRIBE Lists the attributes of tables and other objects (short: DESC)
EDIT Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT Disconnect from the database and terminate SQL*Plus
GET Retrieves a SQL file and places it into the SQL buffer
HOST Issue an operating system command (short: !)
LIST Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT Display a text string on the screen. Eg prompt Hello World!!!
RUN List and Run the command stored in the SQL buffer (short: /)
SAVE Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
SET Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START Run a SQL script file (short: @)
How does one restore session state in SQL*Plus?
Look at the following example (Oracle8):
SQL> STORE SET filename REPLACE
SQL> (do whatever you like)
SQL> @filename
What is AFIEDT.BUF?
AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
In the prehistoric days when SQL*Plus was called UFI, the file name was "ufiedt.buf", short for UFI editing buffer. When new features were added to UFI, it was the initially named Advanced UFI and the filename was changed to "aufiedt.buf" and then to "afiedt.buf". They presumably needed to keep the name short for compatibility with some of the odd operating systems that Oracle supported in those days.
The name "Advanced UFI" was never used officially, as the name was changed to SQL*Plus before this version was released.
You can overwrite the default edit save file name like this:
SET EDITFILE "afiedt.buf"
What is the difference between @ and @@?
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
A single @ symbol runs the script in your current directory, or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH.
@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files.

What is the difference between & and &&?
"&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value every time the variable is referenced.
Eg: SQL> SELECT * FROM TAB WHERE TNAME LIKE '%&TABLE_NAME.%';
What is the difference between ! and HOST?
Both "!" and "HOST" will execute operating system commands as child processes of SQL*Plus. The difference is that "HOST" will perform variable substitution (& and && symbols), whereas "!" will not. (Note: use "$" under MVS, VMS, and Windows environments, not "!")
What is the difference between ? and HELP?
There is no difference. Both "?" and HELP will read the SYSTEM.HELP table (if available) and shows help text on the screen.
To use the help facility, type HELP followed by the command you need to learn more about. For example, to get help on the SELECT statement, type:
HELP SELECT
How does one enable the SQL*Plus HELP facility?
To enable HELP for SQl*Plus, run the supplied SQL and Loader scritps to create the Help table and to populate it. Look at this Unix example:
cd $ORACLE_HOME/sqlplus/admin/help
sqlplus system/manager @helptbl
sqlplus system/manager @helpindx
sqlldr system/manager control=plushelp.ctl
sqlldr system/manager control=sqlhelp.ctl
sqlldr system/manager control=plshelp.ctl
If the HELP command is not supported on your operating system, you can access the help table with a simple script like this:
HELP.SQL:
select info
from system.help
where upper(topic)=upper('&1')
/
How can one disable SQL*Plus prompting?
If you run a script that contains "&" symbols SQL*Plus thinks that you want to prompt the user for a value. To turn this off:
SET ESCAPE ON
SET ESCAPE "\"
SELECT 'You \& me' FROM DUAL;
or
SET DEFINE ?
SELECT 'You & me' FROM DUAL;
Note: You can disable substitution variable prompting altogether by issuing the SET DEFINE OFF commmand.

How can one trap errors in SQL*Plus?
Use the "WHENEVER OSERROR ..." to trap operating system errors and the "WHENEVER SQLERROR ..." command to trap SQL and PL/SQL errors. Eg:
SQL> WHENEVER OSERROR EXIT 9
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
How does one trace SQL statement execution?
1. Run the PLUSTRCE.SQL script from the SYS database user. This script is located the in $ORACLE_HOME/sqlplus/admin.
2. Create a PLAN_TABLE using the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin.
3. Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print the result of your query, an explain plan and high level trace information. Look at this example:
SQL> set autotrace on
SQL> select * from dual;

D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
1 consistent gets
0 physical reads
0 redo size
181 bytes sent via SQL*Net to client
256 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
How can one prevent SQL*Plus connection warning messages?
When I go to SQl*Plus, I get the following errors:
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
This messages will stop appearing when you create the PRODUCT_USER_PROFILE table in the SYSTEM schema. This is performed by the PUPBLD.SQL script.
Go to the $ORACLE_HOME/sqlplus/admin directory, connect as SYSTEM and run @PUPBLD from the sqlprompt.
How can users be prevented from executing devious commands?
Yes, command authorization is verified against the SYSTEM.PRODUCT_USER_PROFILE table. This table is created by the PUPBLD.SQL script. Note that this table is not used when someone signs on as user SYSTEM.
Eg. to disable all users whose names starts with OPS$ from executing the CONNECT command:
SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%', 'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);
How can one disable SQL*Plus formatting?
Issue the following SET commands to disable all SQL*Plus formatting:
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
These settings can also be entered on one line, eg.:
SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON
Can one send operating system parameters to SQL*Plus?
One can pass operating system variables to sqlplus using this syntax:
sqlplus username/password @cmdfile.sql var1 var2 var3
Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:
sqlplus scott/tiger @x.sql '"test parameter"' dual
Where x.sql consists of:
select '&1' from &2;
exit 5;

Can one copy tables with LONG columns from one database to another?
About the fastest way of copying data between databases and schemas are by using the SQL*Plus COPY statement. Look at this example:
COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB -
CREATE IMAGE_TABLE USING -
SELECT IMAGE_NO, IMAGE -
FROM IMAGES;
Interview questions:
1.query for self join.
2.deletion of duplicate rows.
3.post query trigger
4.pre query trigger
5.mastere ¡Vdetail relation
6.report triggers
7.report parameters.
8.format triggers in report.
9.order of triggering of when new item instance.