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 :
An
user exit is :- Program that can be written and linked into the report builder
executable or user exit DLL files.
User
exits are build when ever the control need 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
:-
A) Oracle
Pre-Complier user exits.
B) OCI
(Oracle Call Interface user exits)
C) 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 be 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
-
Fires 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 report is executed
but after the queries are passed and data is fetched.
* Between
Pages Trigger.
-
Fires between each page of the
report is 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 in the
header. Do not contain a RETURN Clause
in the header.
* Must return a single value. Can return none, one or many
values.
* Must contain at
fast one RETURN Statement. Can contain
a RETURN Statement.
* Do not contain
OUT and INOUT Can
contain IN, Out and
IN OUT parameters. IN
OUT 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.
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%row type.
Then emp_record will have a structure
consisting of all the fields each representing a column in the employees table.
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’.
About
Exceptions
-
An exception is an identifier in
PL/SQL that is raised during the execution of a black that terminates its main
body of actions.
-
A block always terminates when
PL/SQL raises an exception so that an exception handler should be specified to
perform final actions.
*
Exception can be raised in two ways exception is raised automatically.
- Ex:- when no rows
are retrieved from the database in a SELECT statement, then error
ORA-01403 occurs
and the exception NO-DATA-FOUND is raised by PL/SQL.
- Ex:- Exception can
be raised explicitly by issuing the RAISE statement with in the
block.
-
The exception being raised may be
either.
User-Defined
or Pre Defined
Trapping
an exception:-
-
If the exception is raised in
executable section of the block, processing branches to the corresponding
exception handler in the exception section of the block.
-
If PL/SQL successfully handles the
exception, then the exception doesn’t propagate to the enclosing block or
calling environment.
-
The PL/SQL block terminates
successfully.
Propagating
an exception:-
-
If the exception is raised in the executable section of the block and there is
no corresponding exception handler, the PL/SQL block terminates with failure
and the exception will be propagated to the calling environment.
Types of
exceptions:-
A)
Pre-Defined Oracle Server
Exceptions. -Ã
Implicitly Raised.
B)
Non-Pre defined Oracle server
exceptions. -Ã
Implicitly Raised.
C)
User-defined exceptions -Ã Explicitly Raised.
Pre-Defined
Oracle Server Exceptions:-
-
These are the error(20) that occur
most often in PL/SQL code.
-
These exceptions need not be
declared and raised implicitly by Oracle Server, NO-DATA-FOUND, LOGIN_DENIED,
ZERO_DIVIDE.
Non-Pre-Defined
Oracle Server Exceptions:-
-
These are the other standard Oracle
Server errors.
-
These exceptions need to be declared
ion the declarative section and raised by Oracle server implicitly.
User
Defined Exceptions:-
-
These are the conditions that the
developer determines as abnormal.
-
These need to be declared and raised
explicitly.
PRAGMA
EXCEPTION_INIT
Statement
is used to associate a declared exception with the standard Oracle Server error
number.
Syntax:- PRAGMA EXCEPTION_INIT
(exception, error number)
*
SQLCODE, SQL ERRM are two functions which can be used to identify the
associated error code or error message when exception occurs.
-
SQLCODE function returns the numeric
value for the error code.
-
SQLERRM function returns the
character data containing the message associated with the error number.
-
SQLCODE f SQLERRM cannot be used
directly in SQL statements.
What is
Dynamic SQL?
-
Dynamic SQL is a SQL statement that
contains variables that can change during runtime.
-
It is a SQL statement with place
holders and is stored as a character string.
-
Dynamic SQL enables DDL, DCL or
session control statements to be written and executed (by) from PL/SQL.
* Dynamic SQL can be written in two ways.
A)
DBMS_SQL. -Ã 8i
B)
Native Dynamic SQL. -Ã 8i
-
Basically Dynamic SQL means creating
the SQL statements dynamically at runtime by using variables.
Ex:- Dynamic SQL can be used to create a
procedure that operates on a table whose name is not known until runtime or to
execute DDL/DCL/SCS statements.
----Ã In Pl/SQL such
statements cannot be executed statically.
--Ã EXECUTE
IMMEDIATE Statement can perform dynamic
single row queries.
Declare
D_str varchar2 (200);
Val
varchar2 (20);
Begin
D_str=
insert into table1 values (;val);
Val=
‘ Bye’ Execute Immediate str using val; end;
What are
Autonomous Transactions?
-
Autonomous transactions are the
processes run independently of its parent.
-
By means of Autonomous Transaction,
the current transaction can be temporarily suspended and another operation can
be begun.
-
The basic idea behind this is to
have some operation take place independently of the current transaction.
Ex:- to allow error messages written to
table to be committed but to rollback everything
else that has taken place prior to the error.
-
The autonomous or child transaction
can commit or rollback as applicable with the execution of the parent
transaction being resumed upon its completion.
-
The parent may then perform further
operations of any operations performed with in the child transaction.
-
By using Autonomous Transactions,
modular and reusable components can be developed more easily.
-
In fact Oracle already uses similar
functionality internally, known as
recu transactions to handle the
updating of system resources.
Ex:- When one application selects ‘nextval’
from a non eached sequence, the value is in
the database.
-
Thus a second application will
always get the incremented application has committed or rolled back.
-
Autonomous Transaction should be
defined in PL/SQL in the following manner.
PRAGMA
AUTONOMOUS_TRANSACTION;
-
Autonomous transaction also can be
nested.
-
The parent transaction remains
active while any statements specified in the declare section of the autonomous
unit are executed.
-
As the code unit exits and control
returns to the parent the main (parent) transaction is resumed and the
transaction context is switched back to the parent.
What is
Bulk binding of Bilk collect?
Bulkbind:-
-
The assignment of values to PL/SQL
variables in SQL statements is called binding.
-
The binding of an entire collection
at once is refilled to as bulk binding.
-
Bulk bind improves performance by
minimizing the number of context switches between PL/SQL and SQL engines while
they pass an entire collection of elements (varay, nested tables, index-by
table or host array) as bind variables back and forth.
-
Prior to Oracle 81, the execution of
every SQL statements required a switch between the Pl/SQL and SQL engines, where
as bulk binds use only one context switch.
* Bulk binding includes the following
A)
Input collections; use the FORALL
statement.
B)
Output collections, use the BULK
COLLECT clause.
Input
Collections:-
-
Input collections are data passed
from Pl/SQL engine to the SQL engine to execute INSERT, UPDATE and DELETE
statements.
Syntax:-
FORALL index in lower_bound.. upper_bound sql_statement;
Output
Collections:-
-
Output collections are the data
passed from the SQL engine to the PL/SQL engine as a result of SELECT or FETCH
statements.
-
The keyword BULK COLLECT can be used
with SLECT INTO, FETCH INTO and RETURNING INTO clauses.
Syntax:-
BULK COLLECT into collection_name, ……
What are
Materialized Views and Snapshots?
Materialized
View:-
-
A Materialized view is a replica of a
target master from a single point in time.
-
In Oracle 7, it is terned as
SNAPSHOT
-
Oracle 7.1.6 --Ã Uptable Snapshots
-
Oracle 7.3 -Ã Primary Key
Snapshots
-
Oracle 8 -Ã Materialized view
-
Oracle 9 -Ã Multifier
Materialized View.
-
Materialized views can be used both
for creating summaries to be utilized in data warehouse environments.
* Replicating data in distributed
environments.
Target Master -Ã The table(s) which
is (are) referenced by the MVIEW query.
Best Table -Ã The tables are
that is (are) created by MVIEW create statement and that stores data that
satisfy the MVIEW query.
Syntax:-
Create materialized view <name>
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from <master
table>;
-
Since this is a fast refreshed MVIEW
the master table should have a log (Master log) to record the changes on it
that can be created by running.
Create materialized view log on
master_table;
-Ã this statement creats the following
objects
-
a table called MLOG$_Master_table
-
an internal trigger on Master_table
that populates the log table.
* Master Log tables (MLOG$) are used by
fast refresh procedure.
Refreshing
Materialized Views:-
-
Initially a materialized view
contains the same data as in the master table.
-
After the MVIEW is created, changes
can be made to the master table and possibly also to the MVIEW.
-
To keep a MVIEW data relatively
current with the data in the master table, the MVIEW must be periodically
refreshed.
* Refresh can be accomplished by one of the
following procedures.
Dbms_mview.refresh (<mview list>,
<Refresh types>)
Dbms _ refresh.refresh (<Refresh
Groups>)
Refresh Types -Ã Complete Refresh,
Fast Refresh, Force Refresh
*
Complete Refresh is performed by deleting the rows from the snapshot and
inserting the
rows satisfying the MVIEW query.
*
In Fast refresh only the rows updated since last refresh are pulled from the
master table to
insert into MVIEW.
*
This requires a log table called as MVIEW Log to be created on the Master
Table.
*
Force refresh first tries to run a Fast refresh if possible.
*
If fast refresh is not possible, it performs complete refresh.
Refresh
Groups
-
These are used to refresh multiple
snapshots in a transitionally consistent manner.
-
When a refresh group is refreshed
all MVIEWS in that group are populated with data from a consistent point in
time.
-
Refresh groups are managed by using
the procedures in the package DBMS –
REFRESH.
-
DBMS-REFRESH, MAKE of
DBMS-REFRESH.ADD are used to create a refresh group and add new snap shots to
an existing group.
Types of
Materialized Views:-
1 Read-only materialized views
-
DML cannot be performed on the
snapshots in this category.
2 Up datable materialized views
-
These MVIEWS eliminates the
restriction of DML’s on snapshots.
3 Sub query materialized views
-
These are the MVIEW’S that are created
with sub queries in the WHERE clause of a MVIEW query.
4 Row id Vs Primary Key materialized views
-
MVIEW’S that use Row id for refresh
are called Row id MVIEW’s (Oracle 7).
-
MVIEW’S that use primary key for refresh are called
primary key MVIE’S
(Oracle 8) .
*
Fast refresh requires some association (mapping)
Between rows at snapshot and master tables.
5 Multifier materialized views (Oracle 9)
-
In this type MVIEW, its master table
itself is a MVIEW.
-
This feature enables fast refresh of
MVIEW’S that have MVIEW’S as their masters.
-
Many companies are structured on at
least three levels
A)
International
B)
National
C)
Local
-
Many nodes at both the national and
local levels are required
-
The best possible solution in such
cases is to use multifier MVIEW’S.
6 Simple Vs Complex MVIEW’S.
-
MVIEW’S being simple or complex
determines whether it can be fast refreshed or not.
-
A MVIEW is fast refreshable if it is
simple.
-
A MVIEW is not fast refreshable if
it is complex.
-
A MVIEW can be considered CONNECT
BY, INTERSECT, MINUS or UNION or UNION ALL
clauses in its detining query.
* The
following data types are not supported in MVIEW replication.
A)
LONG B)
LONG RAW
C) BFILE D)
UROWID
-
MVIEW’S are typically used in data
ware house or decision support systems.
Snapshots
-
Snapshots are mirror or replace of
tables in a single point of time.
-
A Snapshot is a local copy of a
table data that originates from one or more remote master tables.
-
To keep a snapshots data current
with the data of its master the Oracle server must periodically refresh the
snapshot.
