You can also purchase my ebook- Oracle Developer 2000 (D2K) from amazon.
Question 1: What is Oracle Developer?
Oracle Developer consists of Oracle Components that are helpful in designing the meaningful applications.
Question 2: What is a Report Builder?
Report Builder is a component of Oracle Developer that is used to display data in a specified format required by the user.
Question 3: What are the various styles used in the Reports?
Various styles used in Reports are:
c) Mailing Label
d) Form Letter
e) Group Left
f) Group Above
h) Matrix with Group
Question 4: What is GUI?
GUI stands for Graphical User Interface. GUI is a program interface that takes advantage of the computer’s graphics Capabilities to make the program easier to use.
Question 5: What are the different Report Builder Components?
Report Builder Components are:
a) Object Navigator
b) Property palette
c) Data Model Editor
d) Layout Model Editor
e) Parameter Form Editor
Question 6: What is a difference between Object Navigator and property Palette?
The object navigator shows a hierarchical view of objects in the report and a property palette is a window that displays the settings for defining an oracle report object.
Question 7: What is a Syntax Palette?
Syntax palette is a programming tool that helps in copying the syntax of Constructs into the PL/SQL Editor.
Question 8: What are the different ways of viewing the Report output?
Different ways of viewing the Report output are:
Question 9: Can Report output to multiple destinations?
Yes, the Report can be Output to multiple Destinations in one single run of the Report.
Question 10: What is a difference between Screen view and Preview?
Use the Screen font to format. It does not consider printer fonts. Use the Printer font to format and Screen font to Display. Using Preview gives you a better impression of how the printed result will appear.
Question 11: What are the different types of Report Builder Modules?
Different types of Report Builder Modules are:
c) External SQL Queries
d) PL/SQL Libraries
Question 12: Explain different types of Report Builder Views?
Different types of Report Builder Views are:
a) Data Model
b) Layout Model
c) Live Previewer
d) Parameter Form
Data Model: Defines the Data structure and the values to be Displayed.
Layout model: Formatting Information about how the values appear in the Output.
Live Previewer: Displays Report Output and allows simple modifications to the Layout without having opened the Layout Model.
Parameter Form: Allows the user input values at runtime through runtime parameter values.
Question 13: Explain Report builder objects?
Report builder objects falls into following three categories:
Data model objects:
a) Query: defines the Data structure i.e. SQL Query.
b) Group: Each group is owned by a Query. Used to group columns selected in the query. Oracle Report automatically creates a group for each query.
c) Columns: consists of Formula, Summary and Placeholder columns.
d) Data Link: Join Queries for complex data relationships.
e) Parameter: consists of System and User Parameters.
Layout model objects:
a) Repeating Frame: Contains objects that can print once for each record of associated group.
b) Frame: Contains objects that can print only once.
c) Field: Contains data and other variable values and their format.
d) Boilerplate: Consists of general, text or graphics type.
Parameter form objects:
a) Field: Contains Parameter values.
b) Boilerplate: Contains constant text or graphics that appears on the runtime parameter form. Consists of graphical, text and image type.
Question 14: What are the different styles of breaking the reports?
There are two different styles of breaking the reports:
a) Group left
b) Group above
Question 15: Name three different templates options in report builder?
Three different templates (.tdf) options in report builder are:
a) Predefined Template
b) Template File (browse)
c) No Template
Question 16: What are the different ways to store Report builder module?
There are two different ways to store Report builder module:
a) Operating System File
b) Report Builder Database Table
Question 17: Where can you store Template module?
Template module can be stored in an operating system file only, not in the database tables.
Question 18: Explain different extensions of Reports?
Different extensions of Reports are:
a) . Rdf: Binary mode, executable (Full Report definition is modifiable)
b) . Rep: Binary mode, executable (not modifiable, no source code)
c) . Rex: Contains ASCII text. Not executable.
Question 19: Give any one reason of saving the report builder module in the database?
One reason for saving in the database is to use the reports utility to document your Report definition.
Question 20: How can you convert your report definitions from one storage type to another?
You can convert your report definitions from one storage type (.rdf, .rep, .rex) to another by using RWCON60 executable.
Question 21: How many query and group do Tabular style, Group above and Group left prepares?
The objects that the report wizard creates by default depend on the style of report that you choose:
a) Tabular Style: one query, one group
b) Group Above, Group Left: one query, two or more groups.
Report Wizard creates only one Query.
Question 22: How can you delete a database column from the group?
You cannot delete a database column object directly from the group. To delete a column you must remove the corresponding expression from the SELECT statement in the Query.
Question 23: Do Report wizard supports page summaries?
Report Wizard does not support page summaries. Create the field manually in the Layout editor.
Question 24: What is the minimum number of groups in Matrix Report?
The minimum number of groups in Matrix Report is Four.
Question 25: Name the three sections of Layout model?
Three sections of Layout model are:
a) Header Section
b) Main Section
c) Trailer Section
Question 26: What is a difference between Confine and Flex mode?
To avoid errors in Layout model always work in the layout using the Confine Mode and Flex Mode.
Confine Mode consist of:
a) Close Padlock: Restrict to move objects position
b) Open Padlock: Does not restrict to move objects.
Flex Mode consist of:
a) Enabled: Layout boundary moves with the objects.
b) Disabled: Only the object can move.
Question 27: What is an Anchor?
Anchor is used to position the object position in relative to another object. Anchor is also used for suppressing spaces.
Question 28: What are the advantages of using Button on Reports?
Button is used to perform additional actions such as:
1) Accessing a URL
2) Calling another report etc.
Question 29: What is Conditional Formatting?
Conditional formatting is a programming tool that is used to hide, change font, color etc of objects based on some conditions.
Question 30: Explain different types of columns used in report builder?
Different Columns contained in Report Builder are:
a) Data Columns
b) Formula Columns
c) Summary Columns
d) Placeholder Columns
Data Columns: Data Columns contain the data values for a report. Data Columns are corresponding to the table columns included in the select list of a query. Each column is placed in the group associated with the query.
Formula Columns: Formula Columns performs user-defined computations. Formula Columns executes a PL/SQL function and must return a value. Formula Columns can be placed at the Report or Query level. Formula Columns should not be used to set a parameter’s value. Formula Columns is denoted by CF_
Summary Columns: Summary Columns are used for calculating summary information like: Sum, Average, Minimum, Maximum, Count, First, Last, % of Total, Std Deviation and Variance. Summary Columns can be placed at the Report or Query level. Summary Columns is denoted by CS_.
Placeholder Columns: Placeholder Columns are storage areas in the Data Model. Used for Temporary storage of data for future reference. Placeholder Columns value can be set in the following places: Before Report Trigger and Report-Level Formula Columns. Placeholder Columns can be populated by a Before Report Trigger but are generally populated by a Formula. Although Placeholders allow PL/SQL to be written within them, they cannot be Self-populated, their value must be set by an external program Unit and any attempts at self-population will result in a runtime compilation error. However the Formula assigned to the Placeholder must return a value. Placeholder Columns is denoted by CP_.
Example: At Formula column:
Function CF_1formula return Number is
If :no_in_stock<10 then
:CP_1:=’Only’||:no_in_stock||’Items left in stock!!’;
Example at Placeholder column:
Function CP_1formula return Char is
Question 31: How many types of Report Builder Triggers are there?
There are five different types of Report Builder Triggers:
a) Before Parameter Form
b) After Parameter Form
c) Before Report
d) Between Pages
e) After Report
Question 32: Explain Report Builder Triggers in detail?
Before Parameter Form fires before the Runtime Parameter Form is displayed. From this Trigger, you can access and change the value of parameters, PL/SQL Global variables and report-level columns. If the runtime parameter form is suppressed, this trigger still fires. This Trigger can be used for validation of command line parameters.
After Parameter Form fires after the Runtime Parameter Form is displayed. From this Trigger, you can access and check Parameter values. Is used to perform dynamic query through Lexical parameters. Columns from the Data Model are not accessible from this Trigger If the runtime parameter form is suppressed, this trigger still fires. This Trigger can be used for validation of command line parameters. Any processing that will affect the data retrieved by the report should be performed in these triggers (Before and After Parameter Form Triggers).These are the two triggers that fire before anything is parsed or fetched.
Before Report fires before the report is executed but after the query is parsed.
Between Pages fires before each page of the report is formatted, except the very first page. This trigger can be used for customised page formatting. In the previewer, the 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.
After Report fires after you exit the previewer, or after report output is sent to a specified destination, such as a file, printer etc. This trigger can be used to clean up any initial processing that was done such as deleting tables. This trigger always fires, whether or not your report completed successfully. Any processing that will not affect the data retrieved by the report can be performed in these triggers (Before, After report and between Pages).
Question 33: What is a Format Trigger? Explain in brief?
Format Trigger is used to format objects based on some conditions. Format trigger are PL/SQL functions executed before the object is displayed. The function must return a Boolean value (TRUE or FALSE). Format triggers do not affect the data retrieved by the report. Don’t perform calculations or use DML in a format trigger because it cannot be sure how many times a format trigger will fire for a particular object. For example: Highlighting a value, Highlighting a row, Suppressing labels, Suppressing values, Placing commas between fields etc.
Question 34: What are Lexical Parameters?
Lexical Parameters performs dynamic SQL query. Use to change the data definition at runtime. Use Lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. Is denoted by ‘&’. You cannot make lexical references in a PL/SQL statement. However, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Code is written in AFTER PARAMETER FORM Trigger.
DATA MODEL QUERY:
Select * from employee where dept_id=10 &status—–lexical parameter
CODING AT AFTER PARAMETER FORM
Function AfterPForm return Boolean is
IF :status=’ALL’ Then
:status:=’AND (STATUS IN (“P”,”N”) OR STATUS IS NULL)’;
ELSIF :status=’P’ Then
:status:=’AND STATUS=”P” ‘;
Question 35: What is difference between Vertical and Horizontal Elasticity?
Vertical Elasticity property deals with how the vertical size of the object may change at runtime to accommodate the objects or data within it.
Vertical Elasticity is categorised into four properties:
a) Contract: Means the vertical size of the object decreases. If the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the Report Editor. Truncation of data may occur.
b) Expand: Means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the Report Editor.
c) Fixed: Means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Truncation of data may occur.
d) Variable: Means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space).
Horizontal Elasticity property deals with how the horizontal size of the object may change at runtime to accommodate the objects or data within it.
Vertical Elasticity is categorised into four properties:
a) Contract: Means the horizontal size of the object decreases. If the formatted objects or data within it are wide enough, but it cannot increase to a width greater than that shown in the Report Editor. Truncation of data may occur.
b) Expand: Means the horizontal size of the object increases, if the formatted objects or data within it are wide enough, but it cannot decrease to a width less than that shown in the Report Editor.
c) Fixed: Means the width of the object is the same on each logical page, regardless of the size of the objects or data within it. Truncation of data may occur.
d) Variable: Means the object may expand or contract horizontally to accommodate the objects or data within it (with no extra space).
Question 36: What is difference between Page Break Before and page Break After?
Page Break before indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page. Suppose that you want each instance of a repeating frame to be on a logical page by itself. First, set Maximum Records per Page to 1 for the repeating frame so that only one instance appears on each logical page. Then, specify Page Break Before and Page Break After, to make sure that the first instance of the repeating frame starts on a new logical page.
Page Break After property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set to Yes. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page. Suppose that you want each instance of a repeating frame to be on a logical page by itself. First, set Maximum Records per Page to 1 for the repeating frame so that only one instance appears on each logical page. Then, specify Page Break Before and Page Break After, to make sure that the first instance of the repeating frame starts on a new logical page.
Question 37: What is Page Protect in Oracle Reports?
The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Suppose that you have a group report. If at all possible, you would like to have all of the details and the master appearing on the same page. To do this, you specify Page Protect for the master repeating frame (the outermost repeating frame). If the details and the master cannot fit on the first page on which they are triggered to print, they will be triggered to print on the next page instead.
Question 38: What are the parameters of RUN_PRODUCT Procedure in Oracle Reports?
The Syntax of RUN_PRODUCT is:
RUN_PRODUCT (Product Number, Module Varchar2, Commmode Number, Execmode Number, Location Number, Paramlist_id Varchar2, Display Varchar2)
Product: Oracle product you want to invoke can be a Forms, Graphics, Reports, Book.
Module: Defines a Path from where to call a Product.
Commmode: Communication mode to be used when running the called product. Can be either:
a) Synchronous: Specifies that control returns to form only after the called product has been exited. The end user cannot work in the form while the called product is running.
b) Asynchronous: Specifies that control returns to the calling application immediately even if the called application has not completed its display.
Execmode: Execution mode to be used when running the called product. For Reports and Graphics Builder execmode can be either BATCH or RUNTIME. For Form Builder only RUNTIME is used.
Location: Location can be either FILE SYSTEM or the DATABASE.
Paramlist_Id: Specifies the Parameter list to be passed to the called Product.
Display: Used only for Graphics Builder. (Blockname.Item_name)
Question 39: What we use SRW Package in Oracle Reports?
Some of the uses of SRW Package are:
Question 40: What is difference between Live Previewer and Runtime Preview?
The main difference between Live Previewer and Runtime Preview is as below:
|Live Previewer||Runtime Preview|
|1||Can open one at a time||Can Open many at once throughSRW.Run_Report Package defined at Button.|
|2||Can modify the layout without opening the layout Editor||Cannot modify the Layout.|
|3||Cannot magnifying or reduce the report O/P.||Can magnifying or reduce the report O/P.|
|4||Cannot split the report output horizontally or vertically||Can split the report output horizontally or vertically|
Question 41: Explain System Parameters of Oracle Reports in detail?
The main System Parameters of Oracle Reports are:
a) BACKGROUND: Is whether the report should run in the foreground or the background.
b) COPIES: Is the number of report copies that should be made when the report is printed.
c) CURRENCY: Is the symbol for the currency indicator (e.g., “$”).
d) DECIMAL: Is the symbol for the decimal indicator (e.g., “.”).
e) DESFORMAT: Is the definition of the output device’s format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
f) DESNAME: Is the name of the output device (e.g., the file name, printer’s name, mail userid).
g) DESTYPE: Is the type of device to which to send the report output (screen, file, mail, printer, or screen using PostScript format).
h) MODE: Is whether the report should run in character mode or bitmap.
i) ORIENTATION: Is the print direction for the report (landscape, portrait, default).
j) PRINTJOB: Is whether the Print Job dialog box should appear before the report is run.
k) THOUSANDS: Is the symbol for the thousand’s indicator (e.g., “,”).
Question 42: How can you create Additional Report Layout in Oracle Reports?
Additional Report Layout can be created by dragging an Additional Default Layout button from a tool palette in layout section.