1.
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 errors (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.
2.
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;
3.
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.
4.
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.
5.
What is Bulk binding of Bulk collect?
Bulk Binding:-
Ø 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 (varray,
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 COLLECTS into collection_name,
6.
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.
v 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 creates 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.
v 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
v Complete
Refresh is performed by deleting the rows from the snapshot and inserting the
rows satisfying the MVIEW query.
v In
Fast refresh only the rows updated since last refresh are pulled from the
master table to insert into MVIEW.
v This
requires a log table called as MVIEW Log to be created on the Master Table.
v Force
refresh first tries to run a Fast refresh if possible.
v 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. Multitier
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.
VIEWS
Ø Views
are built using the columns from one or more tables.
Ø The
single table view can be updated, but the view with multiple tables cannot be
updated.
Ø A
snapshot contains a complete or partial copy of a target master table from a
single point in time.
Ø A
snapshot may be read only or up datable.
7.
How duplicate rows are deleted?
Duplicate rows are
deleted by using ROWID
Syntax à delete from <Table>
Where ROWID not in
(Select max (ROWID) from <Table>
Group by
<Column_name>);
8.
How do you call function and procedure in PL/SQL as well as in SQL
prompt?
SQL*plus à EXECUTE
< Function/Procedure name >;
(SQL prompt)
PL/SQL à <
Procedure Name / Function Name>; (From
another procedure)
Development Tools à
<Procedure name>;
9.
Difference between IN and OUT parameters.
Three types of
parameters
1.
IN 2. OUT 3.IN OUT
IN
parameter:
Ø This
parameter passes a values from the calling environment into the procedure.
Ø This
is the default mode
Ø A
formal parameter of In mode cannot be assigned a value ( we IN parameter cannot
be modified in the body of the procedure )
Ø IN
parameters can be assigned a default value in the parameter list.
Ø IN
parameters are passed by reference.
OUT
parameters:
Ø OUT
parameter must be assigned a value before returning to the calling environment.
Ø OUT
parameter passes a value from the procedure to the calling environment
Ø OUT
parameter can not be assigned a default value in the parameter list.
IN
OUT parameter:
Ø this
type of parameter pass a value from the calling environment into the procedure
and a possibly different value from the procedure back to calling environment
using the same parameter.
Ø IN
OUT parameter cannot be assigned a default value. * By default OUT & IN OUT
parameters are passed by value.
Ø These
can be passed by reference by using NOCOPY.
10. Triggers:
Ø A
trigger is a PL/SQL block or a PL/SQL procedure associated with a table view
schema or the database.
Ø The
code in the trigger executes implicitly whenever a particular event occurs.
Two types of triggers:
Application trigger
Ø fires
whenever an event occurs with in a particular application
Database
Trigger
Ø Fires
whenever a data event (Such as DML ) or system event (such as log on or shut
down) occurs on a schema or database.
Ø Executes
implicitly when a data event. Such as DML on a table (insert, delete or
Update), an INSTEAD OF trigger on a VIEW or DDL statements are issued no matter
which user is connected or which application is used.
Ø Also
executes implicitly when some user or data base system actions occur.
Ex.
When user logs on to the system.
Ø When
DBA shuts down the data base.
Ø Date
base triggers can be defined on tables and on views.
Ø If a DML operations as issued on a view, the
INSTEAD OF trigger defines what action takes place, if these actions include
any DML operations on tables, then any triggers on the base tables are fired.
Ø Data
base triggers can be system triggers on a data base or a schema.
Ø With
a data base, triggers fire for each event for all users, with a schema,
triggers fire for each event for the specific user.
Recursive
trigger :
Ø This
is a trigger that contains a DML operation changing the very same table.
Cascading
Trigger:
Ø The
action of one triggers cascades to another trigger, causing this second trigger
to fire.
Ø Oracle
server allows up to 32 triggers to cascade at any one time.
Ø This
number can be changed by changing the value of the OPEN - CORSORS. Data bases
initialization parameter. (Default value is 50).
Ø A
triggering statement should contain
1.
Trigger Timing
Before,
After (For Table)
Instead
of (For View)
Ø Determines
when the trigger needs to be fired in relation to the triggering event.
2.
Triggering Event->Insert,
Update, Delete
Ø Determines
which on the table or view causes the trigger to fire.
3 Trigger Type-> Statements, Row
Ø Determines
how many times the trigger body executes
4 Table name->Table,
View
5 Trigger body à PL/SQL
– block
Ø Determines
what actions the trigger should perform.
ü INSTEAD
of triggers are used to provide a transparent way to modifying views that
cannot be modified directly through SQL, DML statements because the view is not
modifiable.
-à INSTEAD of triggers provide writing
of Insert, Update and Delete statements against the view.
Ø The
INSTEAD if trigger works invisibly in the background performing the action
coded in the trigger body directly on the underlying tables.
Ø INSTEAD
of trigger execute the trigger body instead of the triggering statement.
Statement Triggers
Ø In
this type of triggers, the trigger body executes once for the triggering event.
Ø This
is the default.
Ø Statement
triggers fires once, even if no rows are affected at all.
Row Trigger
Ø In
this type, the trigger body executes once for each row affected by the
triggering event.
Ø Row trigger is not executed if the triggering
event affects no rows.
ü A
view cannot be modified by normal DML if the view query contains set operators,
group functions, group by, connect By, start with clauses or joins.
11. Mutating Table
Ø A
Mutating table is a table that is currently being modified by an UPDATE, DELETE
OR INSERT statement, or a table that might need to be updated by the effects of
a declarative DELETE CASCADE referential integrity action.
Ø A
table is not considered mutating for statement triggers.
Ø A
mutating table cannot be changed because the resulting DML could change data
that is in consistent state.
12. What is SQL Trace?
Ø SQL
Trace is the main method for collecting SQL execution information in Oracle
collecting a wide range of information and statistics that can be used to tune
SQL operations.
Ø The
SQL – Trace facility can be enabled / disabled for an individual session or at
the instance level.
Ø If
the initialization parameter SQL-TRACE is set to TRUE in the init.ora of an
instance, then all sessions will be traced.
Ø SQL-TRACE can be set at the instance level by using the
initialization parameter SQL-TRACE.
Ø SQL-TRACE
can also be enabled / disabled at the system/session level by using. Alter
system/session set SQL-TRACE = TRUE/FALSE.
13. Explain Plan
Ø Explain
plan command generates information that details the execution plan that will be
used on a particular query.
Ø A
uses a pre created table (PLAN_TABLE) in the current schema to store
information about the execution plan chosen by the optimizer.
à Creating the plan table
Ø Plan
table is created by using the script utl x plan, sql
(Oracle Home /
RDBMS / admin / uti x plan.sql)
UNIX à $ ORACLE_HOME / rdbms / admin
This
script creates an output table, called PLAN-TABLE for holding the output of the
explain command.
à
Populating the PLAN TABLE
Ø PLAN
TABLE is populated using the explain plan.
SQL> Explain Plan for
select * from emp where emp no = 1000;
Ø This
command inserts the execution plan of the SQL statement into the plan table.
Ø A
name tag can be added to explain information by using the set statement_id
clause.
Displaying the Execution
Plan
Ø Once
the table has been populated, the explain info needs to be retrieved and
formatted.
Ø Number
of scripts is available to format the plan table data.
$ ORACLE_HOME/rdbms
/ admin / utlxpls. Sql – to format serial explain plans.
$
ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel explain plans.
14. AUTOTRACE
Ø The
AUTOTRACE facility in SQL* plus allows analysts to view the execution pan d some
useful statistics for a SQL statement within a SQL*plus session.
Ø AUTOTRACE
needs to be initiated in the SQL*Plus session prior to executing the statement.
Ø SET
AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
Ø As
with the explain plan command, to obtain an execution plan the PLAN-TABLE must
be created in the user’s schema prior to Auto Tracing.
SQL>
Set Auto trace trace only explain
SQL>
Select * from dual;
Ø To
enable viewing of STATISTICS data, the auto tracing user must have access to
dynamic performance tables.
Ø To
achieve this, grant PLUS TRACE role to the user.
PLUS
TRACE role is created by the plus trace. Sql script
$
ORACLE_HOME / sql plus admin
SYS
user must run this script.
DBA
can them grant the role to the users who wish to use the AUTOTRACE.
15. TK PROF
Ø Tk
prof facility accepts as input a SQL Trace File and produces a formatted output
file.
Ø Tk
Prof Filename_source filename_output EXPLAIN = [user name / password] sys =
[yes/no] TABLE = [Table Name]
16. How do you add trace to a
report?
By using the package SRW.TRACE_ADD_OPTION
17. How do you execute a specified
DDL in a report?
BY
using the package PW.DO_SQL
18. How do you generate message in
reports?
By
using the packages PW.MESSAGE (Reg Num, );
19. Explain BLOBS of CLOBS?
LOBà A LOB is a data type that is used to store
large, unstructured data such as text, graphic images, video, clippings etc.
ü Four large object data types.
BLOB:
Represents a binary large object
CLOB:
Represents a character large object
NCLOB:
Represents a multi byte character object.
BFILE:
Represents a binary file store of in an os binary file outside the data base.
LOB’S à
Internal LOBS (CLOB, BLOB, NCLOB), External Files (BFILE)
Ø Depending
on the storage aspects and their interpretation by Oracle server.
ü LONG_TO_LOB
API is used to migrate LONG columns to LOB columns.
LOB’S
LOB Locator
Ø A
table can have multiple LOB columns
Ø The
maximum size of a LOB can be 4 GB
Ø LOB’S
return the locator
Ø LOB’S
store a locator in the table end data in a different segment unless the data is
less than 4000 bytes.
LOB value (real data)
LOB locator (pointer to the location of the LOB
value)
Ø A
LOB column doesn’t contain the data and it contains the located of the LOB
value.
Ø When
a table is created with LOB column, the default storage is ENABLE STORAGE IN
ROW.
Ø If
DISABLE storage in Row option is used the LOB value is not stored in the ROW
even if the size is less then 4000 bytes.
Internal LOB’S
Ø Stored
inside the Oracle server.
Ø BLOB,
NCLOB, CLOB.
BFILE
Ø BFILE’S
are external LOB’S.
Ø These
are stored in OS files outside the database table spaces.
Ø The
data type is BFILE.
Ø BFILE
data file stores a locator to the physical file.
Ø BFILE
can be GIF, JPEG, MPEG, text or other formats.
ü DBMS_LO.READ
of DBMS_lob.WRITE are used to manipulate LOBS.
20. Oracle Applications Architecture
Ø Internet
computing Architecture is a frame work for 3-tired, distributed computing that
supports Oracle Applications products.
Ø The
Three tiers are
1.
Data Base Tier
2.
Application Tier
3.
Desk Top Tier
Ø Database
tier manages Oracle 8i database.
Ø Application
tier manages Oracle Applications and other tools.
Ø Desktop
tier provides the user interface displace.
Ø With
internet computing architecture, only the presentation layer of Oracle
Applications is on the Desk Top tier in the form of a plug-in to a standard
internet browser.
No comments:
Post a Comment