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
v 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.
v 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.
v 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:
v 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.
v 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