1.What is NOCOPY?
By default the IN parameter is passed by reference and the OUT
and IN OUT parameters are passed by value.
NOCOPY: is a compiles hint that can be
used with OUT and IN OUT parameter to request to pass by reference. This
improves the performance with OUT and INOUT parameters.
2.REPORT TYPES
A) Tabular
Report
B) Group Left Report
C) Group above
Report
D) Form like report
E) Matrix
Report
F) Multi Media Report
G) Mailing Label
Report
H) OLE Report.
3.ANCHOR
Anchors are used to determine the vertical and horizontal
positioning of a child object relative to its parent object. The end of the
anchor should be attached to the parent object.
A) Parent
Object
B) Child Object
Since the size of the some layout objects may change when the
report runs (When the data is actually fetched), anchors need to be defined to
make the appearance of the object
An Anchor defines the relative position of an object to the
object to which if this anchored.
* Symbol is By pressing the shift key Anchor can be moved.
Anchor Properties:
A) Child Edge percent on child edge type
B) Child Object Name
C) Collapse
Horizontally
D) Collapse vertically
E) Comments
F) Name
* Two child objects can be related to one parent object by an
Anchor.
4.User Exits :
A user exit is: - Program that can be written and linked into
the report builder executable or user exit DLL files.
User exits are building when ever the control needs to be passed
from the report builder to a program, which performs some function and then
control returns to the Report Builder.
Types of user exits:-
Oracle Pre-Complier
user exits.
OCI (Oracle Call
Interface user exits)
Non – Oracle user
exits.
User exits can perform the following tasks.
Perform complex
data manipulation.
Pass data to report
builder from OS text files.
Support PL/SQL
blocks.
Control real time
devices are printer or robot.
5.Types of Triggers in Reports :-
* Report
Triggers
* Data
Triggers
* Layout Triggers
Report Triggers:-
After Parameter
form Trigger.
After Report
Trigger
Before parameter
form Trigger
Before Report
Trigger.
Between Pages
Trigger
+ Firing Sequence
Before Parameter form Trigger
A fire before the runtime parameter form is displayed.
The parameter values can be accessed and changed.
After Parameter form Trigger.
Fires after the runtime parameter form is displayed.
The parameters can be accessed and their values can be
checked.
Before Report Trigger
Fires before the reports is executed but after the queries
are passed and data is fetched.
Between Pages Trigger.
Fires between each page of the report are formatted,
except the very first pages.
This is used for customized page formatting.
After Report Trigger
Fires after exiting from the run time premier or after
report output is sent to a specified destination. (File, Printer, Mai lid
etc….)
This is used to clean up any initial processing that was
done such as deleting the tables.
This Trigger always fires irrespective of success or
failure of the report.
DATA TRIGGERS:
Ref Cursor Query.
This uses PL/SQL to fetch data for the report.
In this a PL/SQL function need to be specified to return a
cursor value from a cursor variable.
Group Filter:
This is PL/SQL function that determines which records to
be included in a group in the property is PL/SQL.
The function must return a BOOLEAN value.
True …….. Includes the current record in the report.
False ……. Excludes the current
record from the report.
Formula
These are Pl/SQL functions that populate formula or place
holder columns
Validation Trigger
These are also PL/SQL functions that are executed when
parameter values are specified on the command line and when the runtime
parameter form is accepted.
Are also used to validate the initial value property of
the parameter.
Layout Triggers
Format Trigger.
These are PL/SQL functions executed before the object is
formatted.
Used to dynamically change the formatting attributes of
the object.
Action Trigger
These are Pl/SQL procedures executed when a button is
selected in the run time previener.
This can be used to dynamically call another report or
execute any other PL/SQL.
6.Formula Column
It performs a user-defined computation on another columns data,
including Place-holder columns.
Formulas are PL/SQL functions that populate formula or place
holder columns.
Cannot be used to populate parameter values.
7.Summary Column
Performs a
computation on another columns data like sum, average, count, minimum, maximum,
%, total.
For group reports, the report wizard and data wizard
create ‘n’ summary fields in the data model for each summary column that is
defined.
--- One at each group level above the column being
summarized.
--- One at the report level.
8.Place Holder Column
A Place holder
column is a column for which, the data type and value can be set dynamically
(Programmatically)
The value can be
set for a place holder column in the following places.
Before report
trigger if the place holder is a report level column.
Report level
formula column, if the place holder is a report level column.
A formula in the
place holders group below it (The value is set once for each record of the
group)
9.Repeating Frame
Repeating frame
surrounds all of the fields that are created for a groups columns.
Repeating frame
prints once for each record of the group.
For frames and
repeating frames, the property elasticity defines whether the size of the frame
or repeating frame should with the objects inside of it at runtime.
10. Frame
Surrounds the objects and protect them from being over written
or pushed by other objects.
11. System Parameters
in Reports
* Background
* Copies
* Currency
*
Decimal
* Desformat
* Desname
* Destype
*
Mode
* Orientation
* Print
Job
* Thousands.
12. Data Link
Data links relate
the results of multiple queries.
A data link (Parent
– Child Relation Ship) causes the child query to be executed once for each
instance of its parent group.
13. In which tables FF
are stored?
A) FND – ID – FLEXS
B) FND-ID-FLEX-STRUCTURES
14. Advantages of
stored functions and procedures
Applications can be modularized.
Easy
maintenance.
Rowtines can be
modified online without interfering other users.
One routine can be
modified to effect multiple applications.
Improved data security and integrity.
Indirect access to
database objects can be controlled from non privileged users with security
privileges.
Improved performance.
Reparsing for
multiple users can be avoided by exploiting the shared SQL area.
PL/SQL parsing at
run-time can be avoided by pursing at compile time.
Number of calls to
the database can be reduced and network traffic decreased by bundling commands.
Improved code clarity.
The clarity of code
increases by using appropriate identifier names to describe the action of the
routines which reduces the need for comments.
15. Difference between
a function and a procedure
Functions
Procedures
Invoke as a part of an expression.
Execute as a PL/SQL statement.
Must contain a RETURN
clause Do
not contain a RETURN Clause.
in the
header
in the header
Must return a single value.
Can return none, one or many values.
Must contain at fast one RETURN
Can contain a RETURN
Statement. Statement.
Do not contain OUT and INOUT
Can contain
IN, Out and IN OUT
IN OUT
parameters.
Parameters.
16. About Cursors
Oracle server uses some private work areas to execute SQL
statements and to store processing information.
By using PL/SQL cursors these private SQL areas can be
named and the stored information can be accessed.
Two Types:
Implicit Cursors
Implicit cursors
are declared by PL/SQL implicitly for all DML and PL/SQL select statements,
including queries that return only one row.
Oracle Server
implicitly opens a cursor to process each SQL statement not associated with on
explicitly declared cursor.
The most recent
implicit cursor can be returned as the SQL cursor.
Explicit Cursors
For queries that
return more than one row, explicit cursors are declared and named by the
programmes and manipulated through specific statements in the block’s
executable actions.
Explicit cursors
are used to individually process each row returned by a multiple-row SELECT
statement.
The set of rows
returned by a multiple – row query is called as active set.
Declare
Open
Fetch
Empty? Close
Cursor Attributes:-
Attribute
Type
Description
% is open
Boolean Evaluates to TRUE if the cursor is open.
% not found
Boolean Evaluates to TRUE if the most recent
fetch
doesn’t return a row.
%
found
Boolean Evaluate to TRUE if the most
recent fetch
returns a row. Complement of % not found.
% Row Count
Number Evaluates the total number of rows
returned so far.
PARAMETERIZED CURSORS:-
Parameters can be
passed to the cursor in a cursor for loop.
It allow to open
and close an explicit cursor several times in a block, returning a different
active set on each occasion for each execution, the previous cursor is closed
and reopened with a new set of parameters.
Sizes should not be
mentioned for the data types of parameters the parameters names are for
references in the query expression of the cursor.
17. Confined Mode:-
If it is on, child
objects cannot be moved outside their enclosing parent objects.
If it is off child
objects can be moved out sides their enclosing parent objects.
FLEX MODE:-
If it is on, parent
borders stretch when child objects are moved against them.
If it is off,
parent borders remain fixed when child objects are moved against them.
18. Parameters
A parameter is a
variable whose value can be set at runtime (from the run time
parameter of the command line).
User parameters are
created by the user and system parameters are created by Report Builder.
System parameters
cannot be renamed or deleted.
Bind Parameters (Variables)
Bind references (or
Variables) are used to replace a single value in SQL or PL/SQL, such as a
character string, number or date.
Bind references may
be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,
CONNECT BY and START WITH clauses of queries.
Bind references
cannot be referenced in FROM clauses.
Bind variables can
be referenced by entering a colon (:) followed immediately by the column or
parameter name.
If the parameter /
column is not created before making a bind reference, report builder will
create a parameter.
Lexical Parameters (Variables)
Lexical references
are place holders for text that is embedded in a SELECT statement.
Lexical Variables
can replace the clauses appearing after SLECT, FROM, WHERE, GROUP BY, ORDER BY,
HAVING, CONNECT BY, and START WITH.
19. What is % Row type
% Row types is used
to declare a record based on a collection of columns in a database table or
view.
The fields in the
record take their names and data types from the columns of the table or view.
The record can also
store an entire row of data fetched from a cursor or cursor variable.
% Row type should
be prefixed with the database table.
Ex: Declare
Emp_record
employee 5% row type.
Then emp_record
will have a structure consisting of all the fields each representing a column
in the employees table.
20. What is a Ref
Cursor?
Oracle server uses
unnamed memory spaces to store data used in implicit cursors.
Ref cursors are
used to define a cursor variable, which will point to that memory space and can
be used like pointers in SQL ‘S’.
No comments:
Post a Comment