1) Difference between 8 and
8i
1.
Transportable Tablespaces
This feature allows a user to move a subset of an Oracle
database into another Oracle database. It is a lot like
unplugging the
subset from the original database and plugging it into another one.
2.
Extended Aggregate Operations
The GROUP BY
clause of SQL queries can now specify additional OLAP aggregate operations.
These new
operations are
called ROLLUP and CUBE, and are effective for computing subtotals and
cross-tabulations across
multiple
dimensions
3.
Function-Based Indexes
Indexes can now
be created on functions and expressions that involve one or more columns in the
table being
indexed. A
function-based index precomputes the value of the function or expression and
stores it in the index.
4.
Descending Indexes
The DESC keyword on the CREATE INDEX statement is no longer
ignored. It specifies that the index should be
created in descending order.
5.
General Enhancements
Some of the key enhancements include:
• LOB column support
provides the ability to store large objects, such as text documents and images
required
by
various data cartridges and Web-based applications.
• Secondary index support
allows for efficient access using non-primary key columns.
• Index-organized tables
(IOTs) can be rebuilt with the new MOVE clause for ALTER TABLE, which also
supports
an ONLINE keyword. The ONLINE keyword may only be used for IOTs and allows DML
operations on the IOT while the primary key
index is being built.
CREATE
TABLE... AS SELECT enables parallel loading of an index-organized table.
6.
Triggers on Nested Table View Columns
7.
DBMS_REPAIR Package
Oracle8i provides enhanced block corruption repair
capability through the new DBMS_REPAIR package. It
provides the DBA
with a three-stage approach to addressing corruptions.
8.
Redo Log Analysis Using LogMiner
Log files contain a wealth of useful
information about the activities and history of an Oracle database, but until
Oracle8i there has been no easy tool that could tap
into this information. LogMiner allows online and archived redo
log files to
be read, analyzed, and interpreted by
the user using SQL.
9.
You can drop a new column
10.
Online Read-Only Tablespaces
Oracle8i improves the performance of the operation that
places a tablespace in read-only mode. A tablespace in
Oracle8i can be
placed in read-only mode when there are no outstanding transactions in that
tablespace alone, unlike
previous versions
of Oracle where the operation completed
only when there were no outstanding transactions in the
entire database.
11.
TRIM Function
This enhancement implements the ANSI standard
TRIM function. It combines the functionality of the existing
LTRIM and RTRIM functions, allowing the user
to trim leading or trailing characters, or both, from a character
string.
12.
SQL*Loader Enhancements
For Oracle8i provides the following the
SQL*Loader enhancements:
• SQL*Loader now includes
support for the loading of objects, collections, and LOBs.
• There is no longer a 64K
physical record size limit.
• A new keyword, FILLER, can
be used to specify a filler field: a data file mapped field which corresponds
to no database
column. The filler field is assigned values
from the data field to which it is mapped.
13.
Export and Import Utilities
Many of the changes for the Import and Export
utilities in Oracle8i are in support of other features. However, there
are a few
enhancements in these utilities which stand on their own.
• The ability to specify a
query for the select statements that export uses to unload the tables.
• The ability to specify
multiple dump files for an export command. (This allows users to circumvent the
previous 2Gb limit
for export dump files.)
14.
Autonomous PL/SQL Blocks
Autonomous PL/SQL
blocks are PL/SQL blocks that have a transaction scope independent of the
transaction scope
of the calling
PL/SQL block. They can perform operations, commit, and rollback independent of
the transactions in
the calling block, before returning to the calling block.
Transactions within an autonomous PL/SQL block are
referred to as
autonomous transactions.
15
Parameter Passing by Reference
In Oracle8i, PL/SQL supports three parameter
passing modes: IN, IN OUT, and OUT. IN parameters are passed by
reference; IN OUT
parameters support copy-in and copy-out semantics; OUT parameters support
copy-out
semantics.
Through a new syntax, using NOCOPY mode, Oracle8i allows all parameters to be
passed efficiently by
reference
16
OEM
17.
Execute immediate function to execute DDL in PL/SQL.
18.
Java support. web-based.
2) Difference
between 9i and 8i
3) Difference between
tablespace and datafile.
TableSpace: A database is divided into one or more logical storage units called
tablespaces.
Tablespaces
are divided into logical units of storage called segments, which are further
divided into
extents.
•
The SYSTEM Tablespace
•
Using Multiple Tablespaces
•
Space Management in Tablespaces
•
Online and Offline Tablespaces
•
Read-Only Tablespaces
•
Temporary Tablespaces
•
Transporting Tablespaces between Databases
Syntax:
CREATE TABLESPACE tabspace_5
DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 10M;
CREATE TABLESPACE tabspace_3
DATAFILE 'tabspace_file5.dbf' SIZE 2M
MINIMUM EXTENT 64K
DEFAULT STORAGE (INITIAL 128K NEXT 128K)
LOGGING;
DATAFILE filespec
autoextend_clause
OFF : Specify
OFF to disable autoextend
if it is turned on. NEXT and MAXSIZE
are set to zero. Values for NEXT
and MAXSIZE must be
respecified in further ALTER TABLESPACE
AUTOEXTEND statements.
ON: Specify
ON
to enable autoextend.NEXT integer: Specify
the disk space to allocate to the datafile when more extents are requiredmaxsize_clause: The maxsize_clause
lets you specify the maximum disk space allowed for allocation to the
datafile.
integer: Specify
in bytes the maximum disk space allowed for allocation to the tempfile. Use K
or M to specify this space in kilobytes or megabytes UNLIMITED: Specify UNLIMITED
to set no limit on allocating disk space to the datafile.
MINIMUM
EXTENT integer
Specify the minimum size of an extent in the tablespace.
This clause lets you control free space fragmentation in the tablespace by
ensuring that every used or free extent size in a tablespace is at least as
large as, and is a multiple of,
integer.
LOGGING
| NOLOGGING
Specify the default logging attributes of all tables,
indexes, and partitions within the tablespace.
LOGGING
is the
default.
The tablespace-level logging attribute can be overridden by
logging specifications at the table, index, and partition
levels.
·
DDL:
CREATE TABLE
... AS SELECT,
CREATE INDEX,
ALTER INDEX
... REBUILD, ALTER
INDEX ... REBUILD
PARTITION, ALTER
INDEX ... SPLIT
PARTITION, ALTER
TABLE ... SPLIT
PARTITION, and ALTER
TABLE ... MOVE
PARTITION
dictionary changes). When applied during media recovery,
the extent invalidation records mark a range of blocks as
logically corrupt, because the redo data is not logged.
Therefore, if you cannot afford to lose the object, you should
take a backup after the
NOLOGGING
operation.
DEFAULT storage_clause
Specify the default storage parameters for all objects created
in the tablespace. For a dictinary-managed temporary
ONLINE | OFFLINE
ONLINE:
Specify ONLINE
to make the tablespace available immediately after creation to users who have
been
granted access to the tablespace. This is the default.
OFFLINE: Specify OFFLINE
to make the tablespace unavailable immediately after creation.
The data
dictionary view DBA_TABLESPACES
indicates whether each tablespace is online or offline.
PERMANENT | TEMPORARY
PERMANENT: Specify PERMANENT
if the tablespace will be used to hold permanent objects. This is the default.
TEMPORARY: Specify TEMPORARY
if the tablespace will be used only to hold temporary objects, for example,
Restriction: If you
specify
TEMPORARY, you cannot
specify EXTENT MANAGEMENT
LOCAL.
extent_management_clause
Datafiles: A tablespace in an Oracle database
consists of one or more physical datafiles. A datafile can be associated
with
only one tablespace and only one database.
The
data associated with schema objects in a tablespace is physically stored in one
or more of the datafiles
that
constitute the tablespace. Note that a schema object does not correspond to a
specific datafile; rather, a
datafile
is a repository for the data of any schema object within a specific tablespace.
Oracle allocates space
for
the data associated with a schema object in one or more datafiles of a
tablespace. Therefore, a schema
object
can span one or more datafiles.
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
The value of
NEXT is the minimum size of the increments added to the file when it extends.
The value of MAXSIZE is the maximum size to which the file can automatically
extend.
Taking Datafiles Offline in NOARCHIVELOG
Mode
The following statement takes the specified datafile
offline:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;
Renaming Datafiles in a Single Tablespace
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
4)
Oracle
Architecture.
Background Processes
---------------------
1) Database Writer: The
database writer writes modified blocks from the database buffer cache to the
datafiles.
initialization parameter
DB_WRITER_PROCESSES specifies the number of DBWn processes.
2) Log Writer : The log writer writes redo log entries to
disk. Redo log entries are generated in the redo log buffer of the
system global area(SGA),and LGWR writes the
redo log entries sequentially into an online redo log file.
3) Checkpoint: At specific
times, all modified database buffers in the system global area are written to
the datafiles by DBWn; this event is called a checkpoint.
4) System Monitor (SMON)
The
system monitor performs crash recovery when a failed instance starts up
again.
In a multiple instance system (one that uses Oracle Parallel Server),
the
SMON process of one instance can perform instance recovery for other
instances that have failed.
SMON also cleans up temporary segments that are no longer in use and recovers
dead transactions skipped during crash and instance recovery because of
file-read or offline errors. These transactions are eventually recovered by
SMON when the tablespace or file is brought back online. SMON also coalesces
free extents within the database's dictionary-managed tablespaces to make free
space contiguous and easier to allocate.
5) Process Monitor (PMON)
The
process monitor performs process recovery when a user process fails. PMON
is responsible for cleaning
up the cache and freeing resources that the process was using. PMON also checks
on dispatcher (see below) and server processes and restarts them if they have
failed.
6) Archiver (ARCn)
The
archiver copies the online redo log files to archival storage when they
are
full or a log switch occurs. Although a single ARCn process (ARC0) is
sufficient
for most systems, you can specify up to ten ARCn processes by
using
the dynamic initialization parameter LOG_ARCHIVE_MAX_PROCESSES. If the
workload
becomes too great for the current number of ARCn processes, LGWR
automatically starts another
ARCn process up to the maximum of ten processes. ARCn is active only when a
database is in ARCHIVELOG mode and automatic
archiving
is enabled.
7) Recoverer (RECO)
The
recoverer is used to resolve distributed transactions that are pending
due to a network or system
failure in a distributed database. At timed intervals, the local RECO attempts
to connect to remote databases and automatically complete the commit or
rollback of the local portion of any pending distributed transactions.
8) Dispatcher (Dnnn)
Dispatchers are optional
background processes, present only when a multi-threaded server configuration
is used. At least one dispatcher process is created for every communication
protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for
routing requests from connected user processes to available shared server
processes and returning the responses back to the appropriate user processes.
9) Lock (LCK0)
The
lock process (LCK0) is used for inter-instance locking in the Oracle
Parallel
Server.
10) Job Queue (SNPn)
In a
distributed database configuration, up to thirty-six job queue processes
(SNP0,
..., SNP9, SNPA, ..., SNPZ) can automatically refresh table snapshots.
These
processes wake up periodically and refresh any snapshots that are
scheduled to be
automatically refreshed. If more than one job queue process is used, the
processes share the task of refreshing snapshots. These processes also execute
job requests created by the DBMS_JOB package and propagate queued messages to
queues on other databases.
11) Queue Monitor (QMNn)
The
queue monitor(s) are optional background processes that monitor the
message
queuesfor Oracle Advanced Queuing (Oracle AQ). You can configure up
to
ten queue monitor processes.
Memory Structures
----------------------------
System Global Area
The System Global Area (SGA) is a shared memory
region that contains data and control information for one Oracle
instance. An
SGA and the Oracle background processes constitute an Oracle instance. Oracle allocates the system global area when an instance starts
and deallocates it when the instance shuts down. Each instance has its own
system global area.
Program Global Areas (PGA)
A program global area (PGA) is a memory region
containing data and control information for a single process
(server or background). Consequently, a PGA is sometimes
called a process global area.
Q)
What
is named and positioned notation in procedures and function
Positional versus Named Notation
When calling a subprogram, you can write
the actual parameters using either positional or named notation.
That is, you can indicate the association between an actual
and formal parameter by position or name. So,
given the declarations
DECLARE
acct INTEGER;
amt REAL;
PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS ...
BEGIN
credit_acct(acct, amt); -- positional notation
credit_acct(amount => amt, acct_no => acct); -- named notation
credit_acct(acct_no => acct, amount => amt); -- named notation
credit_acct(acct, amount => amt); -- mixed notation
Using Positional Notation
The first procedure call uses positional notation. The PL/SQL
compiler associates the first actual parameter,
acct,
with the first formal parameter, acct_no.
And, the compiler associates the second actual parameter, amt,
with the second formal parameter, amount.
Using Named Notation
The second procedure call uses named
notation. An arrow (
=>) serves as
the association operator, which associates the formal
parameter to the left of the arrow with the actual
parameter to the right of the arrow. The third procedure call
also uses named
notation and shows that you can list the parameter pairs in
any order. So, you need not know the order in which the formal
parameters are listed.
Using Mixed Notation
The fourth procedure call shows that you
can mix positional and named notation. In this case, the first parameter uses
positional
notation, and the second parameter uses named notation.
Positional notation must precede named notation. The reverse is not
allowed. For example, the following procedure call is
illegal:
credit_acct(acct_no => acct, amt); -- illegal
Q) what
does the NOCOPY compiler hint do?
Using the NOCOPY Compiler Hint
Suppose a subprogram declares an
IN
parameter, an OUT parameter, and
an IN OUT
parameter. When you call
the subprogram, the
IN
parameter is passed by reference. That is, a pointer to the IN
actual parameter is
passed to the corresponding formal parameter. So, both
parameters reference the same memory location,
which holds the value of the actual parameter.
By default, the
OUT
and IN OUT
parameters are passed by value. That is, the value of the IN
OUT actual
parameter is copied into the corresponding formal
parameter. Then, if the subprogram exits normally, the
values assigned to the
OUT
and IN OUT
formal parameters are copied into the corresponding actual
parameters.
When the parameters hold large data
structures such as collections, records, and instances of object types,
all this copying slows down execution and uses up memory.
To prevent that, you can specify the
NOCOPY
hint, which allows the PL/SQL compiler to pass
OUT
and IN OUT
parameters by reference.
In the following example, you ask the
compiler to pass
IN OUT
parameter my_staff by reference
instead of
by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember,
NOCOPY
is a hint, not a directive. So, the compiler might pass my_staff
by value despite your
request. Usually, however,
NOCOPY
succeeds. So, it can benefit any PL/SQL application that passes around
large data structures
NOCOPY lets you trade
well-defined exception semantics for better performance. Its use affects
exception
handling in the following ways:
- Because
NOCOPYis a hint, not a directive, the compiler can passNOCOPYparameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of theNOCOPYactual parameters. - By default, if a subprogram exits with an
unhandled exception, the values assigned to its
OUTandINOUTformal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specifyNOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
Restrictions on NOCOPY
In the following cases, the PL/SQL
compiler ignores the
NOCOPY hint and
uses the by-value parameter-
passing method (no error is generated):
- The actual parameter is an element of an index-by table. This restriction does not apply to entire index-by tables.
- The actual parameter is constrained (by scale
or
NOTNULLfor example). This restriction does not extend to constrained elements or attributes. Also, it does not apply to size-constrained character strings. - The actual and formal parameters are records,
one or both records were declared using
%ROWTYPEor%TYPE, and constraints on corresponding fields in the records differ. - The actual and formal parameters are records,
the actual parameter was declared (implicitly) as the index of a cursor
FORloop, and constraints on corresponding fields in the records differ. - Passing the actual parameter requires an implicit datatype conversion.
- The subprogram is involved in an external or remote procedure call.
Q) Can
u write return statement in procedures ? if yes what will happen?
No, we cannot have a return statement in procedure. If given it gives
the foll. Info.
PLS-00372 In a procedure, RETURN statement cannot contain
an expression
Cause:
In a procedure, a RETURN statement
contains an expression, which is not allowed. In functions, a RETURN statement
must contain an expression because its value is assigned to the function
identifier. However, in procedures, a RETURN statement lets you exit before the
normal end of the procedure is reached.
Q)
How
can u make a procedure return a value?
Specify OUT parameter.
Procedure Syntax :
[CREATE [OR REPLACE]]
PROCEDURE procedure_name[(parameter[, parameter]...)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype
[{:= | DEFAULT} expression]
Q) What does the returning clause
in DML operation will do?
Returning_clause: This
clause lets you return values from the deleted rows, thereby eliminating the
need to
SELECT the rows
beforehand. You can retrieve the column
values into variables and/or host variables, or into collections and/or host
arrays. However, you cannot use the RETURNING
clause for remote or parallel deletes.
RETURNING INTO ... : Used only for DML statements that have a RETURNING
clause (without a BULK COLLECT
clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML
statement, there must be a corresponding, type-compatible variable in the
RETURNING INTO
clause.
returning_clause
The returning clause retrieves the
rows affected by a DML (
INSERT,
UPDATE, or DELETE)
statement. You can specify
this clause for tables and snapshots, and for views with a
single base table.
·
When operating on a
single row, a DML statement with a
returning_clause
can retrieve column expressions using the affected row, rowid, and REFs
to the affected row and store them in host variables or PL/SQL variables.
·
When operating on
multiple rows, a DML statement with the
returning_clause
stores values from expressions, rowids, and REFs
involving the affected rows in bind arrays. |
|
For each expression in the
RETURNING
list, you must specify a corresponding type-compatible PL/SQL variable or host
variable in the
INTO
list.
RETURNING Clause Example
The following example returns column
sal from the deleted rows
and stores the result in bind array :1: DELETE FROM emp
WHERE job = 'SALESMAN' AND COMM < 100
RETURNING sal INTO :1;
Q) Types of indexes
Indexes are optional
structures associated with tables and clusters. You can create indexes on one
or more columns of a table
to speed SQL statement
execution on that table.
that no two rows of a table have duplicate
values in the columns that define the index.
Nonunique indexes do not impose this
restriction on the column values.
2) Composite Indexes: A composite
index (also called a concatenated index) is an index that you
create on multiple columns in a table. Columns in a composite index can appear
in any order and need not be adjacent in the table.
3) Function-Based Indexes: A
function-based index precomputes the value of the function or
expression and stores it in the index. You can create a function-based index as
either a B-tree or a bitmap index. Function-based indexes provide an efficient
mechanism for evaluating statements that contain functions in their WHERE
clauses. You can create a function-based index to materialize
computational-intensive expressions in the index, so that Oracle does not need
to compute the value of the expression when it processes SELECT and DELETE statements.
4) B-tree indexes: The
B-tree structure has the following advantages:
·
All leaf blocks of the
tree are at the same depth, so retrieval of any record from anywhere in the
index takes approximately the same amount of time.
·
B-trees provide
excellent retrieval performance for a wide range of queries, including exact
match and range searches.
·
B-tree performance is
good for both small and large tables, and does not degrade as the size of a
table grows.
7) Reverse key indexes:
Creating a reverse key index, compared to a standard index, reverses
the bytes of each column indexed (except the rowid) while keeping the column
order. Such an arrangement can help avoid performance degradation in an Oracle
Parallel Server environment where modifications to the index are concentrated
on a small set of leaf blocks. By reversing the keys of the index, the
insertions become distributed across all leaf keys in the index.
the index. Because lexically adjacent keys are not stored
next to each other in a reverse-key
index, only fetch-by-key or full-index (table) scans can be
performed.
Bitmap indexing benefits data warehousing applications
which have large amounts of data and
ad hoc
queries but a low level of concurrent transactions. For such applications,
bitmap indexing provides:
Bitmap
indexes are not suitable for OLTP applications with large numbers of concurrent
transactions modifying the data. These indexes are primarily intended for
decision support in data warehousing applications where users typically query
the data rather than update it.
Indexes and Keys:
Indexes are structures actually stored in the database, which users
create, alter, and drop using SQL statements. You create an
index to provide a fast access path to table data. Keys
are strictly a logical concept. Keys correspond to another feature of
Oracle called integrity constraints, which enforce the
business rules of a database.
Indexes and Nulls:
NULL values in indexes are considered
to be distinct except when all the non-NULL values in two or more rows of an
index
are identical, in which case the rows are considered to be
identical. Therefore, UNIQUE indexes prevent rows containing
NULL values from being treated as identical. This does not
apply if there are no non-NULL values--in other words, if the
rows are entirely NULL.
Q) What are optimizer hints?
Hints allow you to make decisions usually made by the
optimizer. You can use hints to specify the
following:
Hints provide a mechanism to direct the optimizer to choose
a certain query execution plan based on the
following criteria:
Hints for Join Operations
1) USE_NL: hint causes Oracle to join each specified table to another row source with a nested loops join using the
specified table as the inner table
SELECT /*+ ORDERED USE_NL(customers) to get first row faster */
accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno;
2) USE_MERGE: hint causes Oracle to join each specified table with another row source with a sort-merge join.
SELECT /*+USE_MERGE(emp dept)*/ *
FROM emp, dept
3) USE_HASH: hint causes Oracle to join each specified table with another row source with a hash join.
SELECT /*+use_hash(emp dept)*/ *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
4) DRIVING_SITE: hint forces query execution to be done at a different site than that selected by Oracle. This hint can
be used with either rule-based or cost-based optimization.
SELECT /*+DRIVING_SITE(dept)*/ *
FROM emp, dept@rsite
WHERE emp.deptno = dept.deptno;
5) LEADING: hint causes Oracle to use the specified table as the first table in the join order.
If you specify two or more
LEADING
hints on different tables, then all of them are ignored. If you specify
the
ORDERED
hint, then it overrides all LEADING
hints.
Q) What are diff types of joins?
1. Equijoins: An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows
that have equivalent values for the specified columns.
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
2. Self Joins: A self join is a join of a table to itself. This table appears twice in the FROM
clause and is followed by
table aliases that qualify column names in the join condition.
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"
FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;
3. Outer Joins: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join
Condition and those rows from one table for which no rows from the other satisfy the join condition.
Outer join queries are subject to the following rules and
restrictions:
·
The (+) operator can
appear only in the
WHERE clause or, in
the context of left-correlation (that is, when specifying the TABLE
clause) in the FROM clause, and
can be applied only to a column of a table or view.
·
If A and B are joined
by multiple join conditions, you must use the (+) operator in all of these
conditions. If you do not, Oracle will return only the rows resulting from a
simple join, but without a warning or error to advise you that you do not have
the results of an outer join.
·
The (+) operator can
be applied only to a column, not to an arbitrary expression. However, an
arbitrary expression can contain a column marked with the (+) operator.
·
A condition containing
the (+) operator cannot be combined with another condition using the
OR
logical operator.
·
A condition cannot use
the IN comparison operator to compare a column marked with the (+) operator
with an expression.
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno;
SUBQUERY: A
subquery in the
FROM clause of a SELECT
statement is also called an inline view.
A subquery in the
WHERE
clause of a SELECT statement is
also called a nested subquery.
A correlated subquery is evaluated once for each row processed by
the parent statement.
SELECT select_list
FROM table1 t_alias1
WHERE expr operator
(SELECT column_list
FROM table2 t_alias2
WHERE t_alias1.column operator t_alias2.column);
UPDATE table1 t_alias1
SET column =
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
DELETE FROM table1 t_alias1
WHERE column operator
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
Q) What are the 2 types of views?
A view is a tailored
presentation of the data contained in one or more tables or other views. A view
takes the output of a
query and treats it as a
table. Therefore, a view can be thought of as a stored query or a virtual
table.
1) Updatable Join Views
A join view is defined as a
view that has more than one table or view in its FROM clause (a join)
and that does not use any of
these clauses: DISTINCT, AGGREGATION, GROUP BY, START WITH,
CONNECT BY, ROWNUM, and set operations
(UNION ALL, INTERSECT, and so on).
An updatable join view is a
join view that involves two or more base tables or views, where UPDATE, INSERT,
and
DELETE operations are permitted. The data dictionary views
ALL_UPDATABLE_COLUMNS,
DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain
information that indicates which of the
view columns are updatable.
2) Object Views:
In the Oracle object-relational database, object views
allow you to retrieve, update, insert, and delete relational data as if
they were stored as object types. You can also define views
that have columns which are object datatypes, such as
objects, REFs, and collections (nested tables and VARRAYs).
3) Inline Views:
An inline view is not a schema object. It is a
subquery with an alias (correlation name) that you can use like a view
within a SQL statement.
SELECT v.year, s.prod_name, SUM(s.sum_sales) FROM sumtab s,
(SELECT DISTINCT t.month, t.year FROM time t) v
WHERE s.month = v.month
GROUP BY v.year, s.prod_name;
4) Materialized Views:
Materialized views,
also called snapshots, are schema objects that can be used to
summarize, precompute, replicate, and
distribute data. They are suitable in various computing
environments such as data warehousing, decision support, and
distributed or mobile computing:
Q) Can u create a view without creating
the underlying table.
No (Check this).
Q) What is mutating and constraining
table error??
Mutating
Tables : A mutating table is a table that is currently being
modified by an UPDATE, DELETE,
or INSERT
statement, or it is a table that might need to be
updated by the effects of a declarative DELETE
CASCADE
referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress.
Tables are never considered mutating for statement
triggers unless the trigger is fired as the result of a
DELETE CASCADE.
Views are not considered mutating in INSTEAD
OF triggers.
For all row triggers, or for statement triggers that were
fired as the result of a
DELETE
CASCADE, there are two
important restrictions regarding mutating tables. These restrictions prevent a
trigger from seeing an inconsistent set of data.
·
The SQL statements of
a trigger cannot read from (query) or modify a mutating table of the triggering
statement.
The mutating error prevents the trigger from reading or
modifying the table that the parent statement is modifying. Before Oracle8i,
there was a "constraining error" that prevented a row trigger from
modifying a table when the parent statement implicitly read that table to
enforce a foreign key constraint. As of Oracle8i, there is no
constraining error. In addition, checking of the foreign key is deferred until
at least the end of the parent statement.
Q) What is a trigger and what are the
types of DB Triggers?
Triggers are procedures that
are stored in the database and implicitly run, or fired, when something
happens.
Types of Triggers
A trigger is either a stored PL/SQL block or a PL/SQL, C,
or Java procedure associated with a table, view, schema, or the database
itself. Oracle automatically executes a trigger when a specified event takes
place, which may be in the form of a system event or a DML statement being
issued against the table.
Triggers can be created or fired on any of the following:
Q) What are autonomous transactions?
An autonomous transaction (AT) is an independent
transaction started by another transaction, the main transaction (MT).
It
lets you suspend the main transaction, do SQL operations,
commit, or roll back those operations, then resume the main
transaction. An autonomous transaction
executes within an autonomous scope. An autonomous scope is a routine
you mark
with the pragma (compiler directive)
AUTONOMOUS_TRANSACTION.
Q) Advantages of using packages?
Packages encapsulate related procedures, functions, and
associated cursors and variables together as a unit in the database.
Advantages of Packages
Packages are used to define related
procedures, variables, and cursors and are often implemented to provide
advantages in the
following areas:
Q) What is Procedure/Package overloading
?
Procedure Overloading: creating multiple
procedures with the same name in the same package, each taking arguments of
different
number or datatype.
Package Overloading: PL/SQL allows two or more packaged subprograms
to have the same name. This option is useful
when you want a
subprogram to accept parameters that have different datatypes.
Q) What is forward declaration?
PL/SQL requires that you
declare an identifier before using it. Therefore, you must declare a subprogram
before calling it.
For example,
the following declaration of procedure
award_bonus
is illegal because award_bonus calls
procedure calc_rating, which is not
yet declared when the call is made: DECLARE
...
PROCEDURE award_bonus IS
BEGIN
calc_rating(...); -- undeclared identifier
...
END;
PROCEDURE calc_rating (...) IS
BEGIN
...
END;
In this case, you can solve the problem
easily by placing procedure
calc_rating
before procedure award_bonus.
However, the
easy solution does not always work. For example, suppose
the procedures are mutually recursive (call each other) or you
want to define them in logical or alphabetical order.
You can solve the problem by using a special subprogram
declaration called a forward declaration, which consists of a
subprogram spec terminated by a semicolon. In the following example, the
forward declaration advises PL/SQL that the body of procedure
calc_rating
can be found later in the block. DECLARE
PROCEDURE calc_rating ( ... ); -- forward declaration
...
Although the formal
parameter list appears in the forward declaration, it must also appear in the
subprogram body. You can
place the subprogram body anywhere after the forward
declaration, but they must appear in the same program unit.
Q) What are recursive functions/Sub
Programs?
A recursive subprogram is one that calls itself. Think of a
recursive call as a call to some other subprogram that does the
same task as your subprogram. Each recursive call creates a
new instance of any items declared in the subprogram, including
parameters, variables, cursors, and exceptions. Likewise,
new instances of SQL statements are created at each level in the
recursive descent.
There must be at least two paths through a recursive
subprogram: one that leads to the recursive call and one that does not. At
least one path must lead to a terminating condition.
Otherwise, the recursion would (theoretically) go on forever. In practice,
if a recursive subprogram strays into infinite regress,
PL/SQL eventually runs out of memory and raises the predefined
exception
STORAGE_ERROR.
FUNCTION fac (n POSITIVE) RETURN INTEGER IS -- returns n!
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
Q) What are different types of cursors??
Cursor:
Oracle uses work areas to execute SQL statements and store processing
information
2 Kinds of Cursors are: implicit
and explicit.
Explicit Cursors
The set of rows returned by a query can
consist of zero, one, or multiple rows, depending on how many rows meet your
search
criteria. When a query returns multiple rows, you can
explicitly declare a cursor to process the rows. Moreover, you can declare a
cursor in the declarative part of any PL/SQL block,
subprogram, or package.
You use three commands to control a
cursor:
OPEN, FETCH,
and CLOSE. First, you
initialize the cursor with the OPEN
statement, which identifies the result set. Then, you can
execute
FETCH repeatedly until all
rows have been retrieved, or you can
use the
BULK
COLLECT clause to fetch
all rows at once. When the last row has been processed, you release the cursor
with the CLOSE statement. You can
process several queries in parallel by declaring and opening multiple cursors
A cursor can take parameters, which can appear in the
associated query wherever constants can appear. The formal parameters of
a cursor must be
IN
parameters. Therefore, they cannot return values to actual parameters. Also,
you cannot impose the constraintNOT
NULL on a cursor
parameter.
Implicit Cursor
Oracle implicitly opens a cursor to process each SQL
statement not associated with an explicit cursor. PL/SQL lets you refer to
the most recent implicit cursor as the
SQL
cursor, which has four attributes: %FOUND,
%ISOPEN, %NOTFOUND,
and %ROWCOUNT. They give you
useful information about the execution of data manipulation statements. The SQL
cursor has an
additional attribute,
%BULK_ROWCOUNT,
designed for use with the FORALL
statement. %BULK_ROWCOUNT
This is a composite attribute designed for use with the
FORALL
statement. This attribute has the semantics of an index-by
table. Its ith element stores the number of rows
processed by the ith execution of an
UPDATE
or DELETE statement. If the
ith execution affects
no rows,
%BULK_ROWCOUNT(i) returns
zero. %FOUND
This attribute yields
TRUE
if an INSERT, UPDATE,
or DELETE statement affected
one or more rows or a SELECT INTO
statement returned one or more rows. Otherwise, it yields
FALSE.
index
%ISOPEN
This attribute always yields
FALSE
because Oracle closes the SQL
cursor automatically after executing its associated SQL
statement.
%NOTFOUND
This attribute is the logical opposite of
%FOUND.
It yields TRUE if an INSERT,
UPDATE, or DELETE
statement affected
no rows, or a
SELECT
INTO statement returned no
rows. Otherwise, it yields FALSE.
%ROWCOUNT
This attribute yields the number of rows affected by an
INSERT,
UPDATE, or DELETE
statement, or returned by a SELECT
INTO statement. SQL
You can use cursor attributes in procedural statements but
not in SQL statements. Before Oracle opens the
SQL
cursor
automatically, the implicit cursor attributes yield
NULL.
The values of cursor attributes always refer to the most
recently
executed SQL statement, wherever that statement appears. It
might be in a different scope. So, if you want to save an attribute
value for later use, assign it to a Boolean variable
immediately.
Q) what are ref cursors or Cursor
variable what is their advantages?
Cursor variable points to
the current row in the result set of a multi-row query. But, cursors differ
from cursor variables the way constants differ from variables. Whereas a cursor
is static, a cursor variable is dynamic because it is not tied to a specific
query. You can open a cursor variable for any type-compatible query.
Cursor Variables: which hold
the memory location (address) of some item instead of the item itself.
Advantages:
·
Dynamic (U can pass dynamic queries).
·
To pass query result sets between PL/SQL stored subprograms and
various clients.
·
you can declare a cursor variable on the client side(if PL/SQL
Engine exists), open and fetch from it on the server side, then continue to
fetch from it back on the client side.
·
Can reduce network traffic by having a PL/SQL block open (or
close) several host cursor variables in a single round trip.
Defining REF CURSOR Types
First, define a
REF
CURSOR type, then declare
cursor variables of that type. Define REF
CURSOR types in any PL/SQL
block, subprogram, or package using the syntax TYPE ref_type_name IS REF CURSOR [RETURN return_type];
where
ref_type_name
is a type specifier used in subsequent declarations of cursor variables and return_type
must represent a record or a row in a database table. In the following example,
you specify a return type that represents a row in the database table dept:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR
types can be strong (restrictive) or weak (nonrestrictive).
As the next example shows, a strong REF
CURSOR type definition
specifies a return type, but a weak definition does not: DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
Strong
REF
CURSOR types are less
error prone because the PL/SQL compiler lets you associate a strongly typed
cursor variable only with type-compatible queries. However, weak REF
CURSOR types are more
flexible because the compiler lets you associate a weakly typed cursor variable
with any query.
Q) Diff between nested tables, varrays
and index by tables?
Nested Table: This can be
considered one-column database tables
Within PL/SQL, nested tables are like one-dimensional
arrays.
However,
nested tables differ from arrays in two important
·
Arrays have a fixed
upper bound, but nested tables are unbounded. So, the size of a nested table
can increase
dynamically.
·
Arrays must be dense
(have consecutive subscripts). So, cannot delete individual elements from an
array. Initially, nested tables are dense, but they can be sparse
(have nonconsecutive subscripts). So, can delete elements from a nested table
using the built-in procedure
DELETE.
That might leave gaps in the index, but the built-in function NEXT
lets you iterate over any series of subscripts.
Nested Tables versus Index-by Tables
·
Nested tables can be stored in a database column (hence the term
"nested table") but index-by tables cannot.
·
Nested tables extend the functionality of index-by tables by
letting you
SELECT, INSERT,
UPDATE, and DELETE
nested tables stored in the database. For example, the built-in procedure TRIM
cannot be applied to index-by tables.
·
An uninitialized nested table is atomically null (that is, the
table itself is null, not its elements), but an uninitialized index-by table is
merely empty. So, you can apply the
IS
NULL comparison operator
to nested tables but not to index-by tables.
·
PL/SQL supports implicit (automatic) datatype conversion between
host arrays and index-by tables (but not nested tables). So, the most efficient
way to pass collections to and from the database server is to use anonymous
PL/SQL blocks to bulk-bind input and output host arrays to index-by tables.
·
index-by tables are initially sparse. So, they are convenient for
storing reference data using a numeric primary key (account numbers or employee
numbers for example) as the index.
·
index-by tables can have negative subscripts (nested tables
cannot).
·
to extend a nested table, you must use the built-in procedure
EXTEND,
but to extend an index-by table, you just specify larger subscriptsVarrays versus Nested Tables
·
Varrays are always
dense, but nested tables can be sparse. So, you can delete individual elements
from a nested table but not from a varray.
·
Oracle stores varray
data in-line (in the same table) unless it exceeds 4K, in which case the data
is stored out-of-line (but in the same tablespace). But, Oracle stores nested
table data out-of-line in a store table, which is a system-generated
database table associated with the nested table.
·
When stored in the
database, varrays retain their ordering and subscripts, but nested tables do
not.
Varray Syntax
CREATE TYPE Project AS OBJECT( --create object
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2))
/
CREATE TYPE ProjectList AS VARRAY(50) OF Project -- define VARRAY
type
/
CREATE TABLE department ( -- create database table
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList) -- declare varray as column
Nested Table Syntax
----------------------------
CREATE TYPE Course AS OBJECT (
course_no NUMBER(4),
title VARCHAR2(35),
credits NUMBER(1));
CREATE TYPE CourseList AS TABLE OF Course;
CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
Q) What are pseudo columns?
CURRVAL, NEXTVAL, LEVEL,
or ROWNUM
Before you
can reference
CURRVAL in a
session, you must use NEXTVAL to generate
a number. A reference to NEXTVAL
stores the current sequence number in CURRVAL.
NEXTVAL increments the
sequence and returns the next value. To obtain the current or next value in a
sequence, you must use dot notation, as follows: sequence_name.CURRVAL
sequence_name.NEXTVAL
After creating a sequence, you can use it to generate
unique sequence numbers for transaction processing. However, you can use
CURRVAL
and NEXTVAL only in a SELECT
list, the VALUES clause, and the SET
clause. In the following example, you use a sequence to insert the same
employee number into two tables: INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);
LEVEL: LEVEL used with the SELECT CONNECT
BY statement to organize
rows from a database table into a tree
structure.
LEVEL
returns the level number of a node in a tree structure. The root is level 1,
children of the root are
level 2, grandchildren are level 3, and so on. In the
START WITH
clause, you specify a condition that identifies the
root of the tree. You specify the direction in which the
query walks the tree (down from the root or up from the
branches) with the
PRIOR
operator.
ROWID: ROWID returns the rowid (binary address) of a row in a database table. You can
use variables of type UROWID
to store rowids in a
readable format. In the following example, you declare a variable named row_id
for that purpose:
DECLARE
row_id UROWID;
When you select or fetch a physical
rowid into a
UROWID variable,
you can use the function ROWIDTOCHAR,
which
converts the binary value to an 18-byte character string.
Then, you can compare the
UROWID
variable to the ROWID
pseudocolumn in the
WHERE
clause of an UPDATE or DELETE
statement to identify the latest row fetched from a cursor.
ROWNUM: ROWNUM returns a number indicating the order in which a row was selected from a
table. The first row
selected has a ROWNUM
of 1, the second row has a ROWNUM
of 2, and so on. If a SELECT statement
includes an
ORDER
BY clause, ROWNUMs
are assigned to the retrieved rows before the sort is done.
You can use
ROWNUM
in an UPDATE statement to assign
unique values to each row in a table. Also, you can use ROWNUM in the WHERE
clause of a SELECT statement to
limit the number of rows retrieved, as follows: DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE sal > 2000 AND ROWNUM < 10; -- returns 10 rows
The value of
ROWNUM
increases only when a row is retrieved, so the only meaningful uses of ROWNUM
in a WHERE
clause are
... WHERE ROWNUM < constant;
... WHERE ROWNUM <= constant;
Q) What are the types of exception and
how u generate a user defined exception?
Predefined
Exception:
An internal exception is raised implicitly whenever your PL/SQL
program violates an Oracle rule or exceeds a system-
dependent limit. Every Oracle error has a number, but
exceptions must be handled by name. So, PL/SQL predefines some
common Oracle errors as exceptions. For example, PL/SQL
raises the predefined exception
NO_DATA_FOUND
if a SELECT INTO
statement returns no rows.
To handle other Oracle errors, you can
use the
OTHERS handler. The
functions SQLCODE and SQLERRM
are especially
useful in the
OTHERS
handler because they return the Oracle error code and message text.
Alternatively, you can use the
pragma
EXCEPTION_INIT
to associate exception names with Oracle error codes. User-Defined Exceptions
PL/SQL lets you define exceptions of your own. Unlike
predefined exceptions, user-defined exceptions must be declared and
must be raised explicitly by
RAISE
statements. Declaring Exceptions
Exceptions can be declared only in the declarative part of
a PL/SQL block, subprogram, or package. You declare an
exception by introducing its name, followed by the keyword
EXCEPTION.
In the following example, you declare an
exception named
past_due:
DECLARE
past_due EXCEPTION;
Using EXCEPTION_INIT
To handle unnamed internal exceptions,
you must use the
OTHERS handler or
the pragma EXCEPTION_INIT.
A pragma is a compiler directive, which can be
thought of as a parenthetical remark to the compiler. Pragmas (also called
pseudoinstructions) are processed at compile time, not at run time.
In PL/SQL, the pragma
EXCEPTION_INIT
tells the compiler to associate an exception name with an Oracle error number.
That allows you to refer to any
internal exception by name and to write a specific handler for it.
Code the pragma
EXCEPTION_INIT
in the declarative part of a PL/SQL block, subprogram, or package using the
syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where
exception_name
is the name of a previously declared exception. The pragma must appear
somewhere after the
exception declaration in the same declarative section, as
shown in the following example:
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
END;
Using raise_application_error
Package
DBMS_STANDARD,
which is supplied with Oracle, provides language facilities that help your
application interact
with Oracle. For example, the procedure
raise_application_error
lets you issue user-defined error messages from stored raise_application_error(error_number, message[, {TRUE | FALSE}]);
where
error_number
is a negative integer in the range -20000 .. -20999 and message
is a character string up to 2048 bytes
long. If the
optional third parameter is
TRUE,
the error is placed on the stack of previous errors. If the parameter is FALSE
(the default), the error replaces all previous errors.
Package
DBMS_STANDARD is an
extension of package STANDARD,
so you need not qualify references to its contents.
An application can call
raise_application_error
only from an executing stored subprogram (or method). When called, raise_application_error
ends the subprogram and returns a user-defined error number and message to the
application. The
error number and message can be trapped like any Oracle
error.
CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS
curr_sal NUMBER;
BEGIN
SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;
IF curr_sal IS NULL THEN
/* Issue user-defined error message. */
raise_application_error(-20101, 'Salary is missing');
ELSE
UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;
END IF;
END raise_salary;
The calling application gets a PL/SQL
exception, which it can process using the error-reporting functions
SQLCODE
and SQLERRM in an OTHERS
handler. Also, it can use the pragma EXCEPTION_INIT
to map specific error numbers returned
by
raise_application_error
to exceptions of its own, as the following Pro*C example shows: EXEC SQL EXECUTE
/* Execute embedded PL/SQL block using host
variables my_emp_id and my_amount, which were
assigned values in the host environment. */
DECLARE
...
null_salary EXCEPTION;
/* Map error number returned by raise_application_error to user-defined exception. */
PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
...
raise_salary(:my_emp_id, :my_amount);
EXCEPTION
WHEN null_salary THEN
INSERT INTO emp_audit VALUES (:my_emp_id, ...);
...
END;
END-EXEC;
This technique allows the calling
application to handle error conditions in specific exception handlers.
Using the RAISE Statement
PL/SQL blocks and subprograms should
raise an exception only when an error makes it undesirable or impossible to
finish
processing. You can place
RAISE
statements for a given exception anywhere within the scope of that exception.
In the
following example, you alert your PL/SQL block to a
user-defined exception named
out_of_stock:
DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER(4);
BEGIN
...
IF number_on_hand < 1 THEN
RAISE out_of_stock;
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- handle the error
END;
Q) Different built in packages
DBMS_ALERT: Provides support for the asynchronous notification of database events
DBMS_DDL: Provides access to some SQL DDL statements from
stored procedures, and provides special
administration
operations not available as DDLs.
DBMS_DESCRIBE: Describes the
arguments of a stored procedure with full name translation and security
checking.
DBMS_OUTPUT: Accumulates information in a buffer so that it
can be retrieved out later.
DBMS_ROWID:
Provides procedures to create
ROWIDs
and to interpret their contents.DBMS_SESSION: Provides access to SQLALTERSESSIONstatements, and other session information, from stored
procedures.
DBMS_SQL: Lets you use dynamic SQL to access the database
DBMS_UTILITY: Provides
various utility routines.
DBMS_TRANSACTION:
Provides access to SQL transaction statements from stored procedures and
monitors transaction activities.
UTL_FILE:
Enables your PL/SQL programs to read and write operating system (OS) text files
and provides a restricted version of standard OS stream file I/O.
Q) Difference between procedure and
function?
Function: Functions are similar to operators in
that they manipulate data items and return a result. Functions differ from
operators in the format of their arguments.
A function is
a subprogram that can take parameters and be invoked. Generally, you use a
function to compute a value. A function has two parts: the specification and
the body. The specification (spec for short) begins with the keyword
FUNCTION
and ends with the RETURN clause,
which specifies the datatype of the return value. Parameter declarations are
optional. Functions that take no parameters are written without parentheses. The
function body begins with the keyword IS
(or AS) and ends with the
keyword END followed by an
optional function name.
The function body has three parts: an optional declarative
part, an executable part, and an optional exception-handling part. The declarative
part contains declarations of types, cursors, constants, variables, exceptions,
and subprograms. These items are local and cease to exist when you exit the
function. The executable part contains statements that assign values, control
execution, and manipulate Oracle data. The exception-handling part contains
handlers that deal with exceptions raised during execution.
Procedure: A procedure
is a subprogram that can take parameters and be invoked. Generally, you use a
procedure to perform an action. A procedure has two parts: the specification
and the body. The specification (spec for short) begins with the keyword
PROCEDURE
and ends with the procedure name or a parameter list. Parameter declarations
are optional. Procedures that take no parameters are written without
parentheses. The procedure body begins with the keyword IS
(or AS) and ends with the
keyword END followed by an
optional procedure name.
The procedure body has three parts: an optional declarative
part, an executable part, and an optional exception-handling part. The
declarative part contains declarations of types, cursors, constants, variables,
exceptions, and subprograms. These items are local and cease to exist when you
exit the procedure. The executable part contains statements that assign values,
control execution, and manipulate Oracle data. The exception-handling part
contains handlers that deal with exceptions raised during execution.
Q) What does the following function do?
trim, cast, floor ceil, rollup, cube.
TRIM:
TRIM enables you to
trim leading or trailing characters (or both) from a character string.SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL;
TRIM example
------------
98723489
FLOOR:
FLOOR returns largest
integer equal to or less than n.
SELECT FLOOR(15.7) "Floor" FROM DUAL;
Floor
----------
15
CEIL:
CEIL returns smallest integer greater than or equal to n.
SELECT CEIL(15.7) "Ceiling" FROM DUAL;
Ceiling
---------
16
ROLLUP:
ROLLUP is an
extension to the group_by_clause
that groups the selected rows based on the values of the first n,
n-1, n-2, ... 0 expressions
for each row, and returns a single row of summary for each group. You can use
the
ROLLUP
operation to produce subtotal values.
Example: given three expressions in the
ROLLUP
clause of the group_by_clause,
the operation results in n+1 = 3+1 = 4 groupings.
Rows based on the values of the first 'n'
expressions are called regular rows, and the others are called
superaggregate rows
SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);
CUBE:
CUBE is an extension to
the group_by_clause that
groups the selected rows based on the values of all possible
combinations
of expressions for each row, and returns a single row of summary information
for each group. You can
use the
CUBE
operation to produce cross-tabulation values.
Example: given three
expressions in the
CUBE clause of the group_by_clause,
the operation results in 2n = 23 = 8 groupings.
Rows based on the values of
'n' expressions are called regular rows, and the rest are called superaggregate
rows
SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY CUBE (dname, job);
CAST:
CAST
expression converts one built-in datatype or collection-typed value into
another built-in datatype or
collection-typed
value.
You can cast an unnamed
operand (such as a date or the result set of a subquery) or a named collection
(such as a
varray or a nested table) into
a type-compatible datatype or named collection. The
type_name
must be the name of a
built-in datatype or
collection type and the
operand
must be a built-in datatype or must evaluate to a collection
value.
Q) What are the different types of check
constraints?
CHECK integrity constraint
on a column or set of columns requires that a specified condition be true or
unknown for every
Row of the table. If a DML
statement results in the condition of the CHECK constraint evaluating to false,
then the statement
is rolled back.
CHECK
constraints enable you to enforce very specific or sophisticated integrity
rules by specifying a check condition. The condition of a CHECK constraint has some
limitations:
·
It must be a Boolean
expression evaluated using the values in the row being inserted or updated, and
Q) How do u add a constraint to an
existing column
ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref)REFERENCES emp;
Q) How 2 add a datafile to an existing tablespace.
ALTER TABLESPACE accounting NOLOGGING
ADD DATAFILE 'disk3:pay3.dbf' (Datafile path)
SIZE 50K
AUTOEXTEND ON
NEXT 10K
MAXSIZE 100K;
Rename
the datafile using the
ALTER TABLESPACE
statement with the RENAME DATAFILE
clause: ALTER TABLESPACE accounting
RENAME DATAFILE 'diska:pay1.dbf'
TO 'diskb:receive1.dbf'
Q) What is PCTUSED & PCTFREE
parameters?
PCTUSED and PCTFREE are used
to
Setting
PCTFREE, you should
understand the nature of the table or index data. Updates can cause rows to
grow.
When
using
NUMBER, VARCHAR2,
LONG, or LONG
RAW, new values might not be the same size as values they
replace.
If there are many updates in which data values get longer, then increase
PCTFREE;
if updates to rows do not affect
the
total row width, then
PCTFREE can be low.PCTFREE also affects the
performance of a given user's queries on tables with uncommitted transactions
belonging to other users. Assuring read consistency might cause frequent
reorganization of data in blocks that have little free space.
A lower
PCTFREE:
·
Might save space,
because the total data for a table or index is stored in fewer blocks (more
rows or entries per block)
·
Increases processing
costs because blocks frequently need to be reorganized as their free space area
becomes filled with new or updated data
·
Potentially increases
processing costs and space required if updates to rows or index entries cause
rows to grow and span blocks (because
UPDATE,
DELETE, and SELECT
statements might need to read more blocks for a given row and because chained
row pieces contain references to other pieces)
·
Lessens processing
costs, because blocks infrequently need reorganization of their free space area
PCTUSED
Once the percentage of free space in a
data block reaches
PCTFREE, no new
rows are inserted in that block until the
percentage of space used falls below
PCTUSED.
Oracle tries to keep a data block at least PCTUSED
full. The percent is of
block space available for data after overhead is subtracted
from total space.
The default for
PCTUSED
is 40 percent; any integer between 0 and 99, inclusive, is acceptable as long
as the sum of PCTUSED and PCTFREE
does not exceed 100.
·
Reduces processing
costs incurred during
UPDATE and DELETE
statements for moving a block to the free list when the block has fallen below
that percentage of usage Choosing Associated PCTUSED and PCTFREE Values
If decided not to use the default values
for
PCTFREE and PCTUSED,
then use the following guidelines.
·
If the sum is less
than 100, then the ideal compromise of space utilization and I/O performance is
a sum of
PCTFREE and PCTUSED
that differs from 100 by the percentage of space in the available block that an
average row occupies. For example, assume that the data block size is 2048
bytes, minus 100 bytes of overhead, leaving 1948 bytes available for data. If
an average row requires 195 bytes, or 10% of 1948, then an appropriate
combination of PCTUSED and PCTFREE
that sums to 90% would make the best use of database space.
·
If the sum equals 100,
then Oracle attempts to keep no more than
PCTFREE
free space, and the processing costs are highest.
·
The smaller the
difference between 100 and the sum of
PCTFREE
and PCTUSED (as in PCTUSED
of 75, PCTFREE of 20), the more
efficient space usage is at some performance cost.
Q) What are co-related queries ?
A correlated subquery is
evaluated once for each row processed by the parent statement.
Eg: The following statement assigns an alias to
emp, the table containing
the salary information, and then uses the alias
in a correlated subquery:
SELECT deptno, ename, sal
FROM emp x
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno)
ORDER BY deptno;
For each row of the
emp
table, the parent query uses the correlated subquery to compute the average
salary for members of the same department. The correlated subquery performs the
following steps for each row of the emp
table:
3.
If that row's salary
is greater than the average salary for that row's department, then the row is
returned.
Q) What is clusters? Their usage ?
Cluster: A cluster
provides an optional method of storing table data. A cluster is made up of a
group of tables that share the
same
data blocks, which are grouped together because they share common columns and
are often used together. For
example,
the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and
DEPT tables,
Oracle
physically stores all rows for each department from both the EMP and DEPT
tables in the same data blocks.
You
should not use clusters for tables that are frequently accessed individually.
Because clusters store related rows of different tables
together in the same data blocks, properly used clusters offer two primary
benefits:
·
The cluster key
is the column, or group of columns, that the clustered tables have in common.
You specify the columns of the cluster key when creating the cluster. You
subsequently specify the same columns when creating every table added to the
cluster. Each cluster key value is stored only once each in the cluster and the
cluster index, no matter how many rows of different tables contain the value.
Therefore, less storage might be
required to store related table and index data in a cluster than is necessary
in non-clustered table
format. For example, notice how each cluster key (each
DEPTNO) is stored just once for many rows that contain the same value
in both the EMP and DEPT tables.
Q) Difference between delete and
truncate statement?
Truncate :
TRUNCATE
statement removes all rows from a table or cluster and reset the STORAGE
parameters to the values
when the table or cluster was created.
Restrictions:
·
You cannot
individually truncate a table that is part of a cluster. You must either
truncate the cluster, delete all rows from the table, or drop and re-create the
table.
·
You cannot truncate
the parent table of an enabled referential integrity constraint. You must
disable the constraint before truncating the table. (An exception is that you
may truncate the table if the integrity constraint is self-referential.)
·
You cannot truncate a
table if any domain indexes defined on any of its columns are marked
LOADING
or FAILED.
* You cannot roll back a
TRUNCATE
statement eg., TRUNCATE TABLE emp;
Delete: The DELETE statement removes entire rows of data from a specified table or view.
The
DELETE
ANY TABLE
system privilege also allows you to delete rows from any table or table
partition, or any view's
base table.
Q) Query to write top 3 salaried
employee?
SELECT EMPNO,SAL,DEPTNO
FROM EMP E
WHERE 3 > (SELECT COUNT(*)
FROM
EMP B
WHERE
B.SAL > E.SAL)
ORDER BY E.SAL DESC
Q) Query to remove duplicate records in
a table?
delete
from emp a
where rowid not in (Select min(rowid) from emp group
by empno)
Q) what is DB link? Syntax?
A database link is a schema
object that causes Oracle to connect to a remote database to access databases
other than your local database
1) CREATE DATABASE LINK SUDHIR
CONNECT TO CURRENT_USER
USING 'RAO';
2) CREATE DATABASE LINK SUDHIR
CONNECT TO scott IDENTIFIED BY tiger
USING 'rao';
CREATE SHARED PUBLIC DATABASE LINK SUDHIR
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY RANGUSS IDENTIFIED BY SUDHIR
USING 'rao';
Q) What are synonyms ? their uses?
A
synonym is an alias for a table, view, snapshot, sequence, procedure, function,
or package.
CREATE PUBLIC SYNONYM SUDHIR_EMP FOR RIJESH.Emp_tab;
CREATE SYNONYM market FOR scott.market_research;
USE : Synonyms provide both data independence and location transparency.
Synonyms permit applications to function
without modification
regardless of which user owns the table or view and regardless of which
database holds the
table or view.
Q) What does savepoint do?
SAVEPOINT names and marks
the current point in the processing of a transaction. Used with the ROLLBACK
TO statement, savepoints
let you undo parts of a transaction instead of the whole transaction. In the
example below, you mark a savepoint before doing an insert. If the INSERT
statement tries to store a duplicate value in the empno
column, the predefined exception DUP_VAL_ON_INDEX
is raised. In that case, you roll back to the savepoint, undoing just the
insert. DECLARE
emp_id emp.empno%TYPE;
BEGIN
UPDATE emp SET ... WHERE empno = emp_id;
DELETE FROM emp WHERE ...
...
SAVEPOINT do_insert;
INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO do_insert;
END;
When you roll back to a savepoint, any savepoints marked
after that savepoint are erased. However, the savepoint to which you roll back
is not erased
If you mark a
savepoint within a recursive subprogram, new instances of the
SAVEPOINT
statement are executed at each level in the recursive descent. However, you can
only roll back to the most recently marked savepoint.
Savepoint names are undeclared identifiers and can be
reused within a transaction. This moves the savepoint from its old position to
the current point in the transaction. Thus, a rollback to the savepoint affects
only the current part of your transaction. An example follows:
BEGIN
SAVEPOINT my_point;
UPDATE emp SET ... WHERE empno = emp_id;
...
SAVEPOINT my_point; -- move my_point to current point
INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO my_point;
END;
The number of active savepoints per session is unlimited.
An active savepoint is one marked since the last commit or rollback.
Q) What is partioning?? what is its use?
Partitioning addresses the key problem of supporting very large tables
and indexes by allowing you to decompose them into smaller and more manageable
pieces called partitions. Once partitions are defined, SQL statements
can access and manipulate the partitions rather than entire tables or indexes.
Partitions are especially useful in data warehouse applications, which commonly
store and analyze large amounts of historical data.
Partitioning Methods
1) range partitioning: which
partitions the data in a table or index according to a range of values,
2) hash partitioning: which
partitions the data according to a hash function.
3) composite partitioning: partitions the data by range and further
subdivides the data into subpartitions using a hash
function.
CREATE TABLE sales ( acct_no NUMBER(5),
acct_name CHAR(30),
amount_of_sale NUMBER(6),
week_no INTEGER )
PARTITION BY RANGE ( week_no ) ...
(PARTITION sales1 VALUES LESS THAN ( 4 ) TABLESPACE ts0,
PARTITION sales2 VALUES LESS THAN ( 8 ) TABLESPACE ts1,
...
PARTITION sales13 VALUES LESS THAN ( 52 ) TABLESPACE ts12 );
To select the records from a
single partition then the syntax is:
SELECT * FROM SALES PARTITION
(SALES1)
Advantages of Partitioning
1) Very Large Databases (VLDBs)
A very large database (VLDB) contains
hundreds of gigabytes or even a few terabytes of data. Partitioning provides
support for VLDBs that contain mostly structured data,
rather than unstructured data. These VLDBs typically owe their
size to the presence of a few very large data objects
(tables and indexes) rather than to the presence of a very large
number of data objects.
·
On-Line
Transaction Processing (OLTP) databases
are designed for large numbers of concurrent transactions, where each
transaction is a relatively simple operation processing a small amount of data.
·
Decision Support
Systems (DSS) are designed for very
complex queries that need to access and process large amounts of data.
2) Reducing Downtime for Scheduled Maintenance
Partitions enable data management operations like data
loads, index creation, and data purges at the partition level, rather
Partitioning can significantly reduce the impact of
scheduled downtime for maintenance operations:
·
By introducing partition
maintenance operations that operate on an individual partition rather than
on an entire table or index
·
By providing partition
independence so that maintenance operations can be performed concurrently
on different partitions
3) Reducing Downtime Due to Data Failures
Some maintenance operations are unplanned events, required
to recover from hardware or software failures that cause
data loss or corruption. Recovery from hardware failures
and many system software failures is accomplished by running
the RECOVER statement on a database, tablespace, or
datafile. Any tables or indexes that have records in a tablespace or
datafile being recovered remain unavailable during
recovery. Increased availability is particularly important for mission-critical
OLTP databases.
Because partitions are independent of each other, the
unavailability of a piece or a subset of pieces does not affect access
to the rest of the data. Storing
partitions in separate tablespaces provides the following benefits:
·
Downtime due to
execution of the RECOVER statement is reduced because the unit of recovery (a
tablespace) is smaller.
·
Disk resources needed
for recovery of an offline tablespace (deferred rollback segments) are reduced
because the unit of recovery is smaller.
·
The amount of
unavailable data is reduced, because only the partitions stored in the
recovered tablespace have to be taken offline. User applications and
maintenance operations can still access the other partitions. This is another
example of partition independence.
4) DSS Performance
DSS queries on very large tables present special
performance problems. A query that requires a table scan can take a
long time, because it must inspect every row in the table.
There is no way to identify and skip subsets of irrelevant rows.
5) I/O Performance
Partitioning can control how data is spread across physical
devices. To balance I/O use, you can specify where to store
the partitions of a table or index. With
this level of location control, you can accommodate the special needs of
applications that require fast response time by reducing
disk contention and using faster devices. On the other hand, data
that is accessed infrequently, such as old historical data,
can be moved to slow disks or stored in subsystems that support
a storage hierarchy.
6) Disk Striping: Performance versus Availability
Disk striping and partitioning are both tools that can
improve performance through the reduction of contention for disk
arms. Which tool to use, or in which proportions to use
them together, is an important issue to consider when physically
designing databases. These issues should be considered not
only with respect to performance, but also with respect to
availability and partition independence.
7) Partition Transparency
The vast majority of application programs require
partition transparency. That is, the programs should be insensitive to
whether the data they access is partitioned and how it is
partitioned. A few application programs, however, can take
advantage of partitions by explicitly requesting access to
an individual partition, rather than the entire table. For example,
a user might want to break a long batch job on a very large
table into a sequence of short nightly batch jobs on individual
partitions.
Q) Can u display the output of a table
twice?
Yes. Using UNION ALL.
Q) Can u write commit in a DB trigger?
No. U cannot write any DDL statements in
DB trigger.
Q) How do u set a specific rollback
segment for a particular transaction?
A transaction can be explicitly assigned to a specific rollback
segment using the SET TRANSACTION statement with the
USE ROLLBACK SEGMENT clause. Transactions are explicitly
assigned to rollback segments for the following reasons:
·
The anticipated amount
of rollback information generated by a transaction can fit in the current
extents of the assigned rollback segment.
·
Additional extents do
not have to be dynamically allocated (and subsequently truncated) for rollback
segments, which reduces overall system performance.
To assign a transaction to a rollback
segment explicitly, the rollback segment must be online for the current
instance, and the
SET TRANSACTION USE ROLLBACK SEGMENT statement must be the
first statement of the transaction. If a specified
rollback segment is not online or a SET TRANSACTION USE ROLLBACK
SEGMENT clause is not the first statement in a
transaction, an error is returned.
For example, if you are about to begin a
transaction that contains a significant amount of work (more than most
transactions),
you can assign the transaction to a large rollback segment,
as follows:
SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;
After the transaction is committed, Oracle will
automatically assign the next transaction to any available rollback segment
unless the new
transaction is explicitly assigned to a specific rollback segment by the user.
Q) What are distributed databases?
A distributed database
system allows applications to access data from local and remote databases.
In a homogenous
distributed system, each database is an Oracle
database. In a heterogeneous distributed system, at least one of the
databases is
a non-Oracle database.
Distributed database uses a client-server architecture to process
information requests
Homogenous Distributed Database Systems
A homogenous distributed database
system is a network of two or more Oracle databases that reside on one or
more
machines. An application can simultaneously access or
modify the data in several databases in a single distributed
environment.
Heterogeneous Distributed Database Systems
In a heterogeneous distributed database system, at
least one of the databases is a non-Oracle system. To the application, the
heterogeneous distributed database system appears as a
single, local, Oracle database; the local Oracle server hides the
distribution and heterogeneity of the data.
Client-Server Database Architecture
A database server is the
Oracle software managing a database, and a client is an application
that requests information from a
server. Each computer in a network is a node that can
host one or more databases. Each node in a distributed database system
can act as a client, a server, or both, depending on the
situation.
Q) By_Value/By Reference Method
by-value method : the
value of an actual parameter is passed to the subprogram.
by-reference method:
only a pointer to the
value is passed, in which case the actual and formal parameters reference the
same
item.
Q) What is PL/SQL wrapper?
You can deliver your stored procedures in object code
format using the PL/SQL Wrapper. Wrapping your PL/SQL code
hides your application internals. To run the PL/SQL
Wrapper, enter the
WRAP statement at
your system prompt using the
following syntax:
wrap INAME=input_file [ONAME=ouput_file]
Q) What is an anonymous block?
An anonymous
block is a PL/SQL program unit that has no name and it does not require the
explicit presence of the
BEGIN
and END keywords to enclose
the executable statements. An anonymous block consists of an optional declarative
part, an executable part, and one or more optional exception
handlers.
The declarative part declares PL/SQL variables, exceptions,
and cursors. The executable part contains PL/SQL code and SQL statements, and
can contain nested blocks. Exception handlers contain code that is called when
the exception is raised, either as a predefined PL/SQL exception (such as
NO_DATA_FOUND
or ZERO_DIVIDE) or as an
exception that you define.
The following short example of a PL/SQL anonymous block
prints the names of all employees in department 20 in the
Emp_tab
table, using the DBMS_OUTPUT
package: DECLARE
Emp_name VARCHAR2(10);
Cursor c1 IS SELECT Ename FROM Emp_tab
WHERE Deptno = 20;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
END;
What
is Oracle's Parallel Server option?
Can
u have package specification w/o pakage body? when do u require this?
Steps
involved in execution of an sql statement.
SQL Loader
----------
Can
u give a filtering condition in sql loader control file?
A field condition is a statement about a field in a logical
record that evaluates as true or false. It is used in the NULLIF and
DEFAULTIF clauses, as well as in the WHEN clause.
A field condition is similar to the condition in the
CONTINUEIF clause, with two important differences. First, positions in
the field condition refer to the logical record, not to the
physical record. Second, you may specify either a position in the
logical record or the name of a column that is being
loaded.
Comparing Fields to Literals
When a data field is compared to a literal string that is
shorter than the data field, the string is padded. Character
strings are padded with blanks, for example:
NULLIF (1:4)=" "
This example compares the data in
position 1:4 with 4 blanks. If position 1:4 contains 4 blanks, then the clause
NULLIF (1:4)=X'FF'
The BLANKS
keyword makes it possible to determine easily if a field of unknown length is
blank.
full_fieldname... NULLIFcolumn_name=BLANKS
The BLANKS keyword only recognizes blanks, not tabs. It can
be used in place of a literal string in any field comparison. The condition is
TRUE whenever the column is entirely blank.
The BLANKS keyword also works for fixed-length fields.
Using it is the same as specifying an appropriately sized literal string of
blanks. For example, the following specifications are equivalent:
fixed_field CHAR(2) NULLIF fixed_field=BLANKS
fixed_field CHAR(2) NULLIF fixed_field=" "
Note: There can be
more than one blank in a multibyte character set. It is a good idea to use the
BLANKS keyword with
these
character sets instead of specifying a string of blank characters.
Q) Can u insert data into 2 tables using
the control file?
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'
1) REPLACE
2) INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)
2) INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO
3) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
3) projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
2) INTO TABLE proj
4) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
4) projno POSITION(29:31 INTEGER EXTERNAL) -- 2nd proj
2) INTO TABLE proj
5) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
5) projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
1.
REPLACE specifies that
if there is data in the tables to be loaded (EMP and PROJ), SQL*loader should
delete the data before loading new rows.
2.
Multiple INTO clauses
load two tables, EMP and PROJ. The same set of records is processed three
times, using different combinations of columns each time to load table PROJ.
3.
WHEN loads only rows
with nonblank project numbers. When PROJNO is defined as columns 25...27, rows
are inserted into PROJ only if there is a value in those columns.
4.
When PROJNO is defined
as columns 29...31, rows are inserted into PROJ only if there is a value in
those columns.
5.
When PROJNO is defined
as columns 33...35, rows are inserted into PROJ only if there is a value in
those columns.
Q) How do u specify the date format in
the control file?
The data
field contains character data that should be converted to an Oracle date using
the specified date mask. The syntax is:
LOAD DATA
INTO TABLE DATES (COL_A POSITION (1:15) DATE "DD-Mon-YYYY")
BEGINDATA
1-Jan-1991
1-Apr-1991 28-Feb-1991
Attention: Whitespace is ignored and dates are parsed from left to
right unless delimiters are present.
Q) What are the diff modes in which data
can be loaded into a table?
Loading Data into Nonempty Tables
Caution: When the
REPLACE or TRUNCATE keyword is specified, the entire table is
replaced, not just individual rows. After the rows are successfully deleted, a
commit is issued. You cannot recover the data that was in the table before the
load, unless it was saved with Export or a comparable utility.
APPEND
If data already exists in the table,
SQL*Loader appends the new rows to it. If data does not already exist, the new
rows are
simply loaded.
REPLACE
TRUNCATE
Using this method, SQL*Loader uses the
SQL TRUNCATE statement to achieve the best possible performance. For the
TRUNCATE statement to operate, the table's referential
integrity constraints must first be disabled. If they have not been
disabled, SQL*Loader returns an error.
Once the integrity constraints have
been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE
CASCADE functionality is needed, then the contents of the
table must be manually deleted before the load begins.
Q) how do u assign null values through
the control file?
Setting a Column to Null or Zero
If you want all inserted values for a given column to be
null, omit the column's specifications entirely. To set a column's
values conditionally
to null based on a test of some condition in the logical record, use the NULLIF
clause. To set a numeric column to zero instead of NULL, use the DEFAULTIF
clause
COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")
DEFAULTIF Clause
Using DEFAULTIF on character (CHAR or DATE) data sets the
column to null
A column may have both a NULLIF clause
and a DEFAULTIF clause, although this often would be redundant.
NULLIF Clause
Use the NULLIF clause after the
datatype and optional delimiter specification, followed by a condition. The
condition has the
same format as that specified for a WHEN clause. The
column's value is set to null if the condition is true. Otherwise, the
value remains unchanged.
NULLIF field_condition
COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")
This specification may be useful if you want certain data
values to be replaced by nulls. The value for a column is first
determined from the datafile. It is then set to null just
before the insert takes place.
Note:
The same effect can be achieved with the SQL string and the NVL function.
Null Columns at the End of a Record
When the control file specifies more
fields for a record than are present in the record, SQL*Loader must determine
whether
the remaining (specified) columns should be considered null
or whether an error should be generated. The TRAILING
NULLCOLS clause
INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE
)
what
is a parameter file and what are its uses?
what
are the different parameters in sql loader?
Forms
-----
Q) Difference between Forms6i and
previous versions?
1. LOV Wizard
2.
Web-based. Forms can be run on the web.
3.
Auto-indenting of code.
4.
You can create a block based on a stored procedure or object
5.
Syntax palette:- Helps in reducing coding time by automatically provided
the built in func and proc and also
basic syntax
in a pl/sql block. (Somewhat same as the
auto-complete feature of VB)
6.
You can perform a global search through all the opened forms.
7.
Seperate PL/SQL engine stored in the forms server.
8. Property for different
items has the option of choosing the colors
from color palette instead of color codes(earlier
version).
9.
Color syntax for oracl reserve words, comments.
Q) Diff between open call and new form.
when to use which one?
Call form: Runs an indicated form while keeping
the parent form active. Form Builder
runs the called form with the same
Runform preferences as the parent
form. When the called form is exited
Form Builder processing resumes in the
calling form at the point from which you
initiated the call to CALL_FORM.
CALL_FORM(theformname,
hide, no_replace, no_query_only, pl_id);
New Form: Exits the current form and enters the
indicated form. The calling form is
terminated as the parent form. If the
calling form
had been called by a higher form, Form Builder keeps the higher call active and
treats it as a call to
the new form. Form Builder releases memory (such as
database cursors) that the terminated form was using.
Form Builder runs the new form with the
same Runform options as the parent form.
If the parent form was a
called form, Form Builder runs the new
form with the same options as the parent form.
NEW_FORM(formname);
Open Form: Opens the indicated form. Use OPEN_FORM to create multiple-form
applications, that is, applications that
open more than one form at the same
time.
OPEN_FORM (form_name,
activate_mode, session_mode, data_mode, paramlist_id);
Q) What are different triggers (form
level) that fire when u run a form and the sequence in which they fire?
1)
Pre-Form
2)
When-new-form-instance
3)
Post-Form
Q) describe the different master-detail
property and triggers ?
1) on-clear_Details (form)
2) on-populate_Details (block)
3) on-check_Delete-master (block)
Q ) Can u set property classes at
runtime?
Yes. By set_item_property for
item.
Q) Can u set visual attributes at
runtime?
Yes. By set_item_property
Q) Can u change the label of an alert
button at runtime?
Yes.
SET_ALERT_PROPERTY(title)
Q) Can u change the record group for an
LOV at runtime?
Yes. By Populate_ group_with_query
Q) What are the different objects on
which a block can be based?
Table, Procedure, Transactional Triggers and Ref
Cursors
Q) what does the following built in do?
SYNCHRONIZE: Synchronizes the terminal screen with
the internal state of the form. That is,
SYNCHRONIZE updates the
screen display to
reflect the information that Form Builder has in its internal representation of
the screen.
NAME_IN: The NAME_IN function
returns the contents of an indicated variable or item. Use the NAME_IN function
to
get the value of an item without referring to the item directly. Note: The NAME_IN function cannot
return the
contents of a global or local variable.
The following statements are
equivalent:
IF :emp.ename = 'smith' --
direct reference
IF NAME_IN('emp.ename') =
'smith' -- indirect reference
The
return value is always a character string.
To use NAME_IN for a DATE or NUMBER item, convert
the
string to the desired data type with the appropriate conversion function:
date_var :=
TO_DATE(Name_In('order.date_item'));
num_var :=
TO_NUMBER(Name_In('order.number_item'));
COPY: The COPY procedure
assigns an indicated value to an indicated variable or item. Unlike standard PL/SQL
assignment,
however, using the COPY procedure allows you to indirectly reference the item
whose value is
being
set:
:emp.ename := 'smith'; -- direct reference
Copy('smith','emp.ename'); -- indirect reference
COPY
can be used with the NAME_IN function to assign a value to an item whose name is
stored in a
reference
variable or item:
/*
put value 'smith' in item whose name is stored in ref_item */
Copy('smith',Name_In('control.ref_item'));
Referencing items indirectly allows you to
write more generic, reusable code. By
using variables in place of actual item
names, you can write a subprogram that can
operate on any item whose name has been assigned to the indicated variable.
Also, using indirect reference is mandatory
when you refer to the value of a form bind variable (item, parameter, global
variable) in PL/SQL that you write in a
library or a menu module. Because
libraries, menus, and forms are separate
application modules, you cannot refer directly
to the value of a form item in a menu-item command or library procedure.
ID_NULL: Returns
a BOOLEAN value that indicates whether the object ID is available.
Use
ID_NULL when you want to check for the existence of an object created
dynamically at runtime. For
example,
if a specific record group already exists, you will receive an error message if
you try to create that
record
group. To perform this check, follow
this general process:
1. Use the appropriate FIND_ built-in to obtain
the object ID.
2. Use ID_NULL to check whether an object with
that ID already exists.
3. If the object does not exist, proceed to
create it.
If
you are going to test for an object’s existence at various times (that is, more
than once during a run), then
you
need to reissue the appropriate FIND_ every time -- once preceding each use of
ID_NULL.
LOGON: Performs
the default Form Builder logon processing with an indicated username and
password. Call this
procedure
from an On-Logon trigger when you want to augment default logon processing.
LOGON (username ,
password , logon_screen_on_error);
Q) What are the different system
variables ? name a few
BLOCK_STATUS, CURRENT_BLOCK, CURRENT_DATETIME,
CURRENT_FORM, CURRENT_ITEM CURSOR_ITEM, CURSOR_RECORD, CURSOR_VALUE,
EVENT_WINDOW, FORM_STATUS, LAST_FORM, LAST_QUERY, LAST_RECORD, MASTER_BLOCK,
MESSAGE_LEVEL, MODE, RECORD_STATUS, SUPPRESS_WORKING.
Q) What are object library, PL/SQL
library ?
The Object Library provides
an easy method of reusing objects and enforcing standards across the entire
development
organization. We can use the
Object Library to:
1.
Create, store, maintain, and distribute standard and reusable objects.
2.
Rapidly create applications by dragging and dropping predefined objects to your
form.
There are several advantages
to using object libraries to develop applications:
1.
Object libraries are automatically re-opened when you startup Form Builder,
making your reusable objects
immediately accessible.
2.
You can associate multiple object libraries with an application. For example, you can create an object library
specfically for corporate standards, and
you can create an object library to satisfy project-specific requirements.
3.
Object libraries feature SmartClasses-- objects that you define as being the
standard. You use SmartClasses to
convert objects to standard objects.
Q) What are property classes? can u
attach a trigger to it?
A
property class is a named object that contains a list of properties and their
settings. Once you create a property class
you
can base other objects on it. An object
based on a property class can inherit the setting of any property in the class
that
makes sense for that object.
Yes. We can attach a trigger to it.
suppose
u have a trigger on a property class and also on the item on
...which
it is defined, which one will fire first?
Q) For a block how many times will
post-query fire?
Perform an action after
fetching a record, such as looking up values in other tables based on a value
in the current record. Fires once for
each record fetched into the block.
Q) What are timers? syntax for creating
timers?
Timer: A timer is an
"internal time clock" that you programmatically create to perform an
action each time the timer expires.
DECLARE
the_timer
CHAR := GET_APPLICATION_PROPERTY(TIMER_NAME);
BEGIN
SET_TIMER(the_timer, 60000, REPEAT);
END;
Q) Suppose u have 3-4 timers in a form
how do u determine will one expired first?
When working with multiple
timers, remember that the When-Timer-Expired is a form-level trigger. It fires any time a timer
expires. If your application contains several timers,
your When-Timer-Expired trigger should contain code that will handle
the different timers
accordingly.
Note: To retrieve the timer name of the most recently executed timer,
initiate a call to GET_APPLICATION_PROPERTY
from
within a When-Timer-Expired trigger.
Otherwise, the results of the built-in are undefined.
DECLARE
expired_timer CHAR(20);
BEGIN
expired_timer:=GET_APPLICATION_PROPERTY(TIMER_NAME);
IF expired_timer='T1'
THEN /* handle timer T1 */;
ELSIF expired_timer='T2'
THEN /* handle timer T2 */;
ELSE /* handle all other timers */;
END IF;
END;
Q) What are restricted and unrestricted
built ins?
Restricted : Any built-in subprogram that
initiates navigation is restricted. This
includes subprograms that move the input
focus from one item to another, and those
that involve database transactions.
Restricted built-ins are not allowed
in triggers that fire in response to
navigation.
Eg., GO-ITEM,
NEXT_SET
UnRestricted: Form Builder
provides built-in subprograms that you can call from triggers and user-named
subprograms that
you write
yourself. Built-ins provide programmatic control over standard application
functions, including
navigation, interface control, and transaction processing.
Q) Can u write go_item in post text
item?
No.
Q) PL/SQL Wrapper ?
Use this standalone utility
to deliver PL/SQL applications without exposing your source code. The Wrapper converts
PL/SQL source code into
portable object code.
Advantages of the PL/SQL
Wrapper include:
1. platform independence
2. dynamic loading
3. dynamic binding
4. dependency checking
5. no affect on importing or exporting
Q) Diff between post text and key next and
when validate item trigger
Post_Text-Item: Fires during the Leave the
Item process for a text item.
Specifically, this trigger fires when the input focus
moves
from a text item to any other item. The Post-Text-Item trigger does not fire
when the input focus is
in a
text item and the operator uses the mouse to click on a button, check box, list
item, or radio group item
that
has the Mouse Navigate property Off.
When Mouse Navigate is Off for these items, clicking them
with
the mouse is a non-navigational event, and the input focus remains in the
current item (in this
example,
a text item).
key next :
Q) Can u disable the required property
for an item at runtime using some form
level property ?
Yes, using Defer Required
Enforcement to YES (Default is no)
Q) Can u create record groups at
runtime?
CREATE_GROUP_FROM_QUERY:
Creates a record group with the given name. The record group has columns
representing
each column you include in the select list of the query. Add rows to the record group with the
POPULATE_GROUP
built-in.
DECLARE
group_id RecordGroup;
query_ok NUMBER;
BEGIN
/* create the group
prod_group and assign its id to the
variable group_id */
group_id := Create_Group_From_Query
('prod_group', 'SELECT product.id, product.name,
inventory.warehouse_id, inventory.amount_in_stock
FROM
product, inventory WHERE product.id =
warehouse.product_id');
/* now execute the new
group's query, using the variable
group_id to identify the group */
query_ok := Populate_Group(group_id);
/* if the query failed, abort this trigger by
raising a predefined exception */
IF query_ok <> 0 THEN
RAISE Form_Trigger_Failure;
END IF;
END;
Q) how do u come out of enter query
mode?
Exit_Form;
Q) what does last_recod do?
Navigates to the last record
in the block's list of records. If a
query is open in the block, Form Builder fetches the remaining
selected records into the
block's list of records, and closes the query.
when
does on-insert trigger fire?
Q) what is the text_io package?
The Text_IO Package contains
constructs that provide ways to write and read information to and from
files. There are
several procedures and
functions available in Text_IO, falling into the following categories:
file operations: The FILE_TYPE record, the FOPEN and
IS_OPEN functions, and the FCLOSE procedure enable you to
define FILE_TYPE variables, open files, check
for open files, and close open files, respectively.
output (write) operations: The PUT, PUTF, PUT_LINE, and NEW_LINE
procedures enable you to write information to an
open file or output it to the
Interpreter.
input (read) operations: The GET_LINE procedure enables you to
read a line from an open file.
Q) What are Diff types of canvases?
Content, Stacked, Horizontal
and Vertical Toolbar, Tab Canvas.
Q) What does forms_ddl do?
Issues dynamic SQL
statements at runtime, including server-side PL/SQL and DDL.
Note: All DDL
operations issue an implicit COMMIT and will end the current transaction
without allowing Form Builder
to process any pending changes.
BEGIN
Forms_DDL('create table temp(n NUMBER)');
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
Message ('Table Created');
END IF;
END;
Q) What is the key-others trigger used
for?
A Key-Others trigger fires
when an operator presses the associated key. It is associated with all keys
that can have key
triggers associated with
them but are not currently defined by function key triggers (at any level).
A Key-Others trigger
overrides the default behavior of a Runform function key (unless one of the
restrictions apply). When this occurs,
however, Form Builder still displays the function key's default entry in the
Keys screen.
Q) How do u call a report from a form?
what are the diff parameters?
Invokes
one of the supported Oracle tools products and specifies the name of the module
or module to be run. If the called
product is unavailable at the time of the
call, Form Builder returns a message to the end user.
If you create a parameter
list and then reference it in the call to RUN_PRODUCT, the form can pass text
and data parameters
to the called product that
represent values for command line parameters, bind or lexical references, and
named queries. Parameters of type DATA_PARAMETER are pointers to record groups
in Form Builder. You can pass DATA_PARAMETERs to Report Builder and Graphics
Builder, but not to Form Builder.
PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/* Check to see if the 'tmpdata' parameter
list exists. */
pl_id :=
Get_Parameter_List('tmpdata');
/* If it does, then delete it before we
create it again in case it contains parameters that are not useful for our
purposes here. */
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/* Create the 'tmpdata'
parameter list afresh. */
pl_id :=
Create_Parameter_List('tmpdata');
/* Add a data parameter to this parameter
list that will establish the
relationship between the named query
'EMP_QUERY' in the report, and the
record group named 'EMP_RECS' in the
form. */
Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
/* Pass a Parameter into PARAMFORM so that a
parameter dialog will not appear for the parameters being
passing in. */
Add_Parameter(pl_id, 'PARAMFORM',
TEXT_PARAMETER, 'NO');
/* Run the report synchronously, passing the
parameter list */
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME, FILESYSTEM,
pl_id, NULL);
END;
SYNCHRONOUS: Specifies
that control returns to Form Builder only after the called product has been
exited. The
end
user cannot work in the form while the called product is running.
ASYNCHRONOUS: Specifies
that control returns to the calling application immediately, even if the called
application
has
not completed its display.
Q) What does the message_level system
variable do?
SYSTEM.MESSAGE_LEVEL stores
one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The
default value
is 0. SYSTEM.MESSAGE_LEVEL
can be set to either a character string or a number. The values assigned can be
any value
between 0 and 25, but values
lower than 0 or higher than 25 will generate an error. During a Runform session,
Form Builder suppresses all messages with a severity level that is the same or
lower (less severe) than the indicated severity level.
Assign a value to the
SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level
:= value;
Q) What are the 4 system variables whose
values can be set ?
1. SYSTEM.DATE_THRESHOLD
SYSTEM.DATE_THRESHOLD
represents the database date requery threshold.
This variable works in conjunction
with
the three system variables $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$, and
controls how often
Form
Builder synchronizes the database date with the RDBMS.
2. SYSTEM.EFFECTIVE_DATE
SYSTEM.EFFECTIVE_DATE
represents the effective database date.
The variable value must always be in the
following
format:
DD-MON-YYYY HH24:MI:SS
3. SYSTEM.MESSAGE_LEVEL
SYSTEM.MESSAGE_LEVEL
stores one of the following message severity levels: 0, 5, 10, 15, 20, or 25.
The
default
value is 0. SYSTEM.MESSAGE_LEVEL can be set to either a character string or a
number. The values
assigned
can be any value between 0 and 25, but values lower than 0 or higher than 25
will generate an error. During
a Runform session, Form Builder suppresses all
messages with a severity level that is the same or lower (less
severe)
than the indicated severity level.
Assign a value to the
SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level
:= value;
4. SYSTEM.SUPPRESS_WORKING:
SYSTEM.SUPPRESS_WORKING
suppresses the "Working..." message in Runform, in order to prevent
the screen
update
usually caused by the display of the "Working..." message. The value
of the variable is one of the following
two
CHAR values:
TRUE Prevents Form Builder from issuing the "Working..." message.
FALSE Allows Form Builder to continue to
issue the "Working..." message.
Q) how do u call a user-defined trigger
in forms?
A user-named trigger is one
that has a unique, user-supplied name.
Because its name does not correspond to any Form
Builder event, a user-named
trigger can only be executed by calling it from within a built-in trigger, menu
item command, or
user-named subprogram. To call a user-named trigger, use the
EXECUTE_TRIGGER built-in procedure. This
procedure
takes a parameter that names
the trigger to be fired:
Execute_Trigger('my_user_named_trigger');
User-named triggers are
required only in special situations. For
most applications, writing a user-named subprogram and
then calling that from a
trigger or menu item command is preferred.
Q) How do u interact with operating
system application?
Host:
Executes an indicated operating system command.
Parameters
system_command_
string: Specifies the system command
you want to pass to your particular
operating
system.
screen_action: Specifies one of the
following constants:
no
parameter Specifies that Form Builder
will:
1.
Clear the screen
2.
Prompt the operator to return from the command
NO_PROMPT Specifies that Form Builder will:
1.
Clear the screen (does not prompt the operator to return from the command)
NO_SCREEN Specifies that Form Builder will:
1. Not clear the screen
2.
Not prompt the operator to return from the system command
(The HOST command should not
send output to the screen when using the NO_SCREEN parameter.)
Q) What does NULL statement do?
The NULL statement
explicitly specifies inaction; it does nothing other than pass control to the
next statement. In a construct
allowing alternative
actions, the NULL statement serves as a placeholder. Syntax
null_statement ::=
NULL;
Comments: The NULL statement improves readability by
making the meaning and action of conditional statements
clear. It tells readers that the associated
alternative has not been overlooked, but that indeed no action is
necessary. Each clause in an IF statement
must contain at least one executable statement. The NULL
statement meets this requirement. So, you can
use the NULL statement in clauses that correspond to
circumstances in which no action is taken.
Do not confuse the NULL
statement with the Boolean non-value NULL; they are unrelated.
Q) Diff between post and commit?
POST: Writes data in
the form to the database, but does not perform a database commit. Form Builder first validates the
form. If there are
changes to post to the database, for each block in the form Form Builder writes
deletes, inserts,
and updates to the database. Any data that you post to
the database is committed to the database by the next
COMMIT_FORM that executes during the current Runform
session. Alternatively, this data can be
rolled back by
the next CLEAR_FORM.
COMMIT: Commit processing is
the way Form Builder attempts to make the data in the database identical to the
data
in
the form. Form Builder's normal cycle of operation is:
1. Read
records from the database.
2. Allow
the end user to make tentative insertions, updates, and deletions. The
tentative changes appear
only in the form. The database remains
unchanged.
3. Post
changes to the database. Form Builder does all of its remaining processing and
sends the data to the
database. After posting the data, Form
Builder can only roll back the changes (via the [Clear Form]
function key or CLEAR_FORM built-in) or
commit them.
4. Form
Builder commits the posted changes. They become permanent changes to the
database.
Q) Suppose u have a key next item at item ,block and form level which one
will fire first
Item
Level, Block Level and Form Level
Q) What are the properties in form that
can be set for better performance
1. Query Array Size property
Specifies the maximum number of records that
Form Builder should fetch from the database at one time.
A size of 1 provides the fastest perceived
response time, because Form Builder fetches and displays only 1 record at
a time.
By contrast, a size of 10 fetches up to 10 records before displaying any
of them, however, the larger size
reduces
overall processing time by making fewer calls to the database for records.
2. Number of Records
Buffered property
Specifies the minimum number of records buffered in
memory during a query in the block.
Form
Builder buffers any additional records beyond the maximum to a temporary file
on disk.
1.
Improve processing speed by increasing the number of records buffered.
2.
Save memory by decreasing the number of records buffered. This can, however, result in slower disk I/O.
3.
If you anticipate that the block may contain a large number of records either
as the result of a query or of heavy
data entry, consider raising the Number of
Records Buffered property to increase performance.
4.
Consider lowering the Number of Records Buffered property if you anticipate
retrieving large items, such as
image items, because of the amount of
memory each item buffered may require.
3.
Update Changed Columns Only property
When
queried records have been marked as updates, specifies that only columns whose
values were actually
changed
should be included in the SQL UPDATE statement that is sent to the database
during a COMMIT. By
default,
Update Changed Columns Only is set to No, and all columns are included in the
UPDATE statement.
1.
If the DML Array Size property is set to a value greater than 1, this Update
Changed Columns Only property will
be ignored at runtime. That is, a DML Array Size greater than 1
causes all columns to be updated – even if
Update Changed Columns Only was set to Yes.
2.
When Update Changed Columns Only is No, Form Builder can reuse the same SQL
statement for multiple
updates, without having to reparse each
time. Setting Update Changed Columns Only to Yes can degrade
performance because the UPDATE statement
must be reparsed each time. In general, you should only set Update
Changed Columns Only to Yes when you know
that operators will seldom update column values that will take a
long time to transfer over the network,
such as LONGs.
3.
Set Update Changed Columns Only to Yes in the following circumstances:
1.
To save on network traffic, if you know an operator will primarily update only
one or two columns.
2.
To avoid re-sending large items that are not updated, such as images or LONGs.
3.
To fire database triggers on changed columns only. For example, if you implement a security
scheme
with a database trigger that fires when a
column has been updated and writes the userid of the person
performing the update to a table.
Q) What are the diff values for block
status, record status?
SYSTEM.BLOCK_STATUS
Represents the status of a Data block where the
cursor is located, or the current data block
during trigger processing. The
value can be one of three character strings:
CHANGED Indicates that the block contains at least
one Changed record.
NEW Indicates that the block contains only New
records.
QUERY Indicates that the block contains only
Valid records that have been retrieved from the database.
Each time this value is
referenced, it must be constructed by Form Builder. If a block contains a large number of
records, using SYSTEM.BLOCK_STATUS could adversely affect performance.
SYSTEM.RECORD_STATUS
Represents the status of the
record where the cursor is located. The
value can be one of four character
strings:
CHANGED Indicates that a queried record's validation
status is Changed.
INSERT
Indicates that the record's
validation status is Changed and that the record does not exist in the
database.
NEW Indicates
that the record's validation status is New.
QUERY Indicates
that the record's validation status is Valid and that it was retrieved from the
database.
Both
SYSTEM.RECORD_STATUS and the GET_RECORD_PROPERTY built-in return the status of
a record in a given
block, and in
most cases, they return the same status.
However, there are specific cases in which the results may differ.
SYSTEM.RECORD_STATUS can in
certain cases return a value of NULL, because SYSTEM.RECORD_STATUS is
undefined when there is no
current record in the system. For
example, in a When-Clear-Block trigger, Form Builder is at the
block level in its processing
sequence, so there is no current record to report on, and the value of
SYSTEM.RECORD_STATUS is NULL.
GET_RECORD_PROPERTY, on the
other hand, always has a value of NEW, CHANGED, QUERY, or INSERT, because it
returns the status of a
specific record without regard to the processing sequence or whether the record
is the current record.
Q) Diff between pre-query and
post-query?
The Pre-Query and Post-Query
triggers allow control over query processing.
They can be defined at the form or block level.
Most often, attach them to
specific blocks to control the query functionality of those blocks.
The Pre-Query trigger fires just before Form
Builder issues the SELECT statement to the database, after the operator has
defined the example record
by entering query criteria in Enter Query mode. Inside a Pre-Query trigger, the
example record defined by the query criteria is the current record. This means that trigger code can read and set
the values of items in the example record using standard :block_name.item_name
syntax.
A Pre-Query trigger can be
used to disallow query conditions that might be invalid. When a form is in Enter Query mode,
normal validation is
suspended and no validation triggers fire as they do in Normal mode. The Pre-Query trigger thus allows
you to verify that any
values entered by the operator are valid query conditions.
When invalid query
conditions have been entered, you can abort the query by raising the
FORM_TRIGGER_FAILURE
built-in exception in the
Pre-Query trigger.
You can also call
SET_BLOCK_PROPERTY to modify the block's WHERE and ORDER BY clauses from within
the Pre-
Query trigger, to further
restrict or order the records the query will retrieve.
The Post-Query trigger fires after the query has
executed, when Form Builder is fetching records into the form. Post-Query fires once for each record
retrieved into the form, which allows you to read and set the values of items
in a fetched record before the operator sees them displayed in the block.
A Post-Query trigger is also
useful for populating control items whose values are derived from a table other
than the block's
base table.
how
do u get the total no of records that will be fetched based on a query
condition?
Q)
how u run a form in debug mode?
There is an option in the form builder to
run the form in debug mode.
Reports
-------
Q) Types of reports?
1. Tabular
2. Group Left
3. Group Above
4.
Form-Like
5. Mailing Label
6. Form Letter
7. Matrix
8. Matrix with group
Q) How u call a report from a report?
Q) How 2 perform DML using reports
Use DML or DDL in your
PL/SQL, you should use the SRW.DO_SQL
packaged function. Note that
SRW.DO_SQL should only be
used for DML and DDL; you should not use it to fetch data.
SRW.DO-SQL: This procedure executes the
specified SQL statement from within Report Builder. The SQL statement can be DDL (statements that
define data), or DML (statements that manipulate data). DML statements are usually faster when they
are in PL/SQL, instead of in SRW.DO_SQL.
Since you cannot perform DDL
statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful
for
performing them within
Report Builder, instead of via a user exit.
Syntax
SRW.DO_SQL
(sql_statement CHAR);
E.g. FUNCTION
CREATETAB RETURN BOOLEAN IS
BEGIN
SRW.DO_SQL('CREATE
TABLE CHECK (EMPNO NUMBER NOT NULL
PRIMARY KEY, SAL NUMBER (10,2))
PCTFREE 5 PCTUSED 75');
RETURN(TRUE);
EXCEPTION
WHEN
SRW.DO_SQL_FAILURE THEN
SRW.MESSAGE(100, 'ERROR WHILE CREATING CHECK
TABLE.');
SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE
RUNTIME
PARAMETER FORM.');
RAISE SRW.PROGRAM_ABORT;
END;
Q) What are the various report triggers
and what is execution sequence?
Before Parameter Form, After Parameter Form,
Before Report, Between Pages, After Report
Q) How many group are there in a mtarix
report ? Explain the answer?
A matrix (crosstab) report
contains one row of labels, one column of labels, and information in a grid
format that is related to
the row and column
labels. A distinguishing feature of
matrix reports is that the number of columns is not known until the
data is fetched from the
database.
To create a matrix report,
you need at least four groups: one
group must be a cross-product group, two of the groups must be
within the cross-product
group to furnish the "labels," and at least one group must provide
the information to fill the cells. The
groups can belong to a single query or to multiple queries.
Q) What does the no of repeating frames
in a report indicate?
The Maximum Records Per Page
property is the maximum number of instances of the repeating frame that will be
formatted
on a logical page. Suppose that you have a repeating frame with
many instances. To improve the
appearance of your report,
you prefer to have at most
three instances of the repeating frame on a given logical page. To ensure that you never have more
than three instances per
logical page, you set Maximum Records Per Page to 3.
Values: a whole number from 1 through 32K, means that
number of instances is the maximum that can be formatted on a
logical page.
blank Means that as many instances of the repeating frame as possible
can be formatted on a logical page.
Q) Which property automatically takes
care of the width and height of text items?
Vertical
Elasticity & Horizontal Elasticity
Q) What are place holder ,formula
columns and summary columns ?
1. Placeholder: A
placeholder is a column for which you set the datatype and value in PL/SQL that
you define. You can
set
the value of a placeholder column in the following places:
1.
The Before Report Trigger, if the placeholder is a report-level column
2. A
report-level formula column, if the placeholder is a report-level column
3. A
formula in the placeholder's group or a group below it (the value is set once
for each record of the
group)
2.
Formula Column: A formula column performs a user-defined computation on another
column(s) data.
Formulas are
PL/SQL functions that populate formula or placeholder columns. You can access the
PL/SQL for
formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette
(i.e., the
PL/SQL Formula
property).
A column of datatype Number
can only have a formula that returns a value of datatype NUMBER. A
column of Datatype Date can
only have a formula that returns a value of datatype DATE. A column of
Datatype Character can only have a formula
that returns a value of datatype CHARACTER, VARCHAR,
or VARCHAR2.
3.
Summary Column: A summary column performs a computation on another
column's data. Using the Report Wizard
or
Data
Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.
You
can also create a summary column manually in the Data Model view, and use the
Property Palette to
create
the following additional summaries:
first, last, standard deviation, variance.
Q) How u display message using reports?
SRW.MESSAGE: This procedure displays a message with
the message number and text that you specify.
The message is displayed in the format below. After the message is raised and you accept
it, the report execution will continue.
MSG-msg_number: msg_text.
Syntax
SRW.MESSAGE
(msg_number NUMBER, msg_text CHAR);
Q) What does a data link do ?
Data links relate the
results of multiple queries. A data link
(or parent-child relationship) causes the child query to be
executed once for each
instance of its parent group. When you
create a data link in the Data Model view of your report,
Report Builder constructs a
clause (as specified in the link's Property Palette) that will be added to the
child query's SELECT
statement at runtime. You can view the SELECT statements for the
individual parent and child queries in the Builder, but can not view the SELECT
statement that includes the clause created by the data link you define.
Oracle
Reports does not support data links between queries that contain column
objects. If you attempt to create such a
link,
a message
dialog box displays, which enables you to choose whether to tell Reports to
create a group-to-group query instead
(using
the parent groups), or to cancel the operation.
If you want to create a link between any type of column and a column
object,
you can manually type the SQL statement using the appropriate column alias(es).
Q) Will the between pages trigger fire
when u move from 1 page to the previous page?
The Between Pages trigger
fires before each page of the report is formatted, except the very first
page. This trigger can be
used for customized page
formatting. In the Runtime Previewer or
Live Previewer, this 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.
Displays an error message
when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that
returned FALSE are not formatted. If the
trigger returns FALSE on the last page, nothing happens because the report is
done formatting. The Between Pages
trigger does not fire before the first page.
If the trigger returns FALSE on the first page, the first page is
displayed, but, if you try to go to the second page, an error message is
displayed.
Q) What should be done to suppress the
parameter form screen?
Action Trigger can be
written to suppress. In tools(toolbar) preferences we can check the run time
parameter field.
No comments:
Post a Comment