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 MAXSIZEare set to zero. Values for NEXTand MAXSIZE must be respecified in further ALTER TABLESPACEAUTOEXTEND statements.ON: Specify
ONto enable autoextend.NEXT integer: Specify the disk space to allocate to the datafile when more extents are requiredmaxsize_clause: The maxsize_clauselets 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 Kor M to specify this space in kilobytes or megabytes UNLIMITED: Specify UNLIMITEDto set no limit on allocating disk space to the datafile. MINIMUMEXTENT 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.
LOGGINGis 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, ALTERINDEX ... REBUILDPARTITION, ALTERINDEX ... SPLITPARTITION, ALTERTABLE ... SPLITPARTITION, and ALTERTABLE ... MOVEPARTITION 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
NOLOGGINGoperation. DEFAULT storage_clause
Specify the default storage parameters for all objects created in the tablespace. For a dictinary-managed temporary
ONLINE | OFFLINE ONLINE: Specify ONLINEto make the tablespace available immediately after creation to users who have been
granted access to the tablespace. This is the default.
OFFLINE: Specify OFFLINEto make the tablespace unavailable immediately after creation.
The data dictionary view DBA_TABLESPACESindicates whether each tablespace is online or offline.
PERMANENT | TEMPORARY
PERMANENT: Specify PERMANENTif the tablespace will be used to hold permanent objects. This is the default.
TEMPORARY: Specify TEMPORARYif the tablespace will be used only to hold temporary objects, for example, Restriction: If you specify
TEMPORARY, you cannot specify EXTENT MANAGEMENTLOCAL. 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 usersADD 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 usersRENAME 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
INparameter, an OUT parameter, and an IN OUTparameter. When you call the subprogram, the
INparameter is passed by reference. That is, a pointer to the INactual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location,
which holds the value of the actual parameter.
parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the
values assigned to the
OUTand IN OUTformal 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
NOCOPYhint, which allows the PL/SQL compiler to pass
OUTand IN OUTparameters by reference. In the following example, you ask the compiler to pass
IN OUTparameter 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,
NOCOPYis a hint, not a directive. So, the compiler might pass my_staffby value despite your request. Usually, however,
NOCOPYsucceeds. 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 RETURNINGclause for remote or parallel deletes.RETURNING INTO ... : Used only for DML statements that have a RETURNINGclause (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 INTOclause.
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_clausecan retrieve column expressions using the affected row, rowid, and REFsto the affected row and store them in host variables or PL/SQL variables. · When operating on multiple rows, a DML statement with the
returning_clausestores values from expressions, rowids, and REFsinvolving the affected rows in bind arrays. | |
For each expression in the
RETURNINGlist, you must specify a corresponding type-compatible PL/SQL variable or host variable in the
INTOlist. 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. Keysare 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 Operations1) 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
LEADINGhints on different tables, then all of them are ignored. If you specify the
ORDEREDhint, then it overrides all LEADINGhints. 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 FROMclause 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 TABLEclause) 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
ORlogical 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 SELECTstatement is also called an inline view. A subquery in the
WHEREclause 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 viewsallow 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 DELETECASCADE
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 INSTEADOF triggers. For all row triggers, or for statement triggers that were fired as the result of a
DELETECASCADE, 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 transactionexecutes 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_bonusis 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_ratingbefore 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_ratingcan 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: implicitand 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 OPENstatement, which identifies the result set. Then, you can execute
FETCH repeatedly until all rows have been retrieved, or you can use the
BULKCOLLECT 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 cursorsA cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of
a cursor must be
INparameters. Therefore, they cannot return values to actual parameters. Also, you cannot impose the constraintNOTNULL 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
SQLcursor, which has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They give you useful information about the execution of data manipulation statements. The SQLcursor has an additional attribute,
%BULK_ROWCOUNT, designed for use with the FORALLstatement. %BULK_ROWCOUNT
This is a composite attribute designed for use with the
FORALLstatement. 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
UPDATEor DELETE statement. If the ith execution affects no rows,
%BULK_ROWCOUNT(i) returns zero. %FOUND
This attribute yields
TRUEif an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTOstatement returned one or more rows. Otherwise, it yields
FALSE. index
%ISOPEN
This attribute always yields
FALSEbecause Oracle closes the SQLcursor automatically after executing its associated SQL statement.
%NOTFOUND
This attribute is the logical opposite of
%FOUND. It yields TRUE if an INSERT, UPDATE, or DELETEstatement affected no rows, or a
SELECTINTO statement returned no rows. Otherwise, it yields FALSE. %ROWCOUNT
This attribute yields the number of rows affected by an
INSERT, UPDATE, or DELETEstatement, or returned by a SELECTINTO statement. SQL
You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the
SQLcursor 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
REFCURSOR type, then declare cursor variables of that type. Define REFCURSOR 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_nameis a type specifier used in subsequent declarations of cursor variables and return_typemust 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: DECLARETYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSORtypes can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REFCURSOR 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
REFCURSOR 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 REFCURSOR 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 NEXTlets 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 DELETEnested tables stored in the database. For example, the built-in procedure TRIMcannot 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
ISNULL 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 objectproject_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 NEXTVALstores 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
CURRVALand NEXTVAL only in a SELECTlist, the VALUES clause, and the SETclause. 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 CONNECTBY statement to organize rows from a database table into a tree
structure.
LEVELreturns 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 WITHclause, 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
PRIORoperator. 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_idfor 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
UROWIDvariable to the ROWID pseudocolumn in the
WHEREclause of an UPDATE or DELETEstatement 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 ROWNUMof 1, the second row has a ROWNUMof 2, and so on. If a SELECT statement includes an
ORDERBY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.
You can use
ROWNUMin an UPDATE statement to assign unique values to each row in a table. Also, you can use ROWNUM in the WHEREclause 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
ROWNUMincreases only when a row is retrieved, so the only meaningful uses of ROWNUMin 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_FOUNDif a SELECT INTOstatement returns no rows. To handle other Oracle errors, you can use the
OTHERS handler. The functions SQLCODE and SQLERRMare especially useful in the
OTHERShandler because they return the Oracle error code and message text. Alternatively, you can use the pragma
EXCEPTION_INITto 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
RAISEstatements. 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: DECLAREpast_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_INITtells 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_INITin the declarative part of a PL/SQL block, subprogram, or package using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where
exception_nameis 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:
DECLAREdeadlock_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_errorlets you issue user-defined error messages from stored raise_application_error(error_number, message[, {TRUE | FALSE}]);
where
error_numberis a negative integer in the range -20000 .. -20999 and messageis 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_erroronly from an executing stored subprogram (or method). When called, raise_application_errorends 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
SQLCODEand SQLERRM in an OTHERShandler. Also, it can use the pragma EXCEPTION_INITto map specific error numbers returned by
raise_application_errorto 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
RAISEstatements 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 eventsDBMS_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
ROWIDsand 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 databaseDBMS_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
FUNCTIONand 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
PROCEDUREand 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_clausethat 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
ROLLUPoperation to produce subtotal values. Example: given three expressions in the
ROLLUPclause 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
CUBEoperation 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:
CASTexpression 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_namemust be the name of a built-in datatype or collection type and the
operandmust 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;
Renamethe datafile using the
ALTER TABLESPACEstatement with the RENAME DATAFILEclause: 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 SELECTstatements 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 PCTUSEDfull. The percent is of block space available for data after overhead is subtracted from total space.
The default for
PCTUSEDis 40 percent; any integer between 0 and 99, inclusive, is acceptable as long as the sum of PCTUSED and PCTFREEdoes not exceed 100. · Reduces processing costs incurred during
UPDATE and DELETEstatements 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 PCTUSEDthat 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 PCTFREEthat sums to 90% would make the best use of database space. · If the sum equals 100, then Oracle attempts to keep no more than
PCTFREEfree space, and the processing costs are highest. · The smaller the difference between 100 and the sum of
PCTFREEand PCTUSED (as in PCTUSEDof 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
emptable, 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 emptable: 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 clusterprovides 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 keyis 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 :
TRUNCATEstatement removes all rows from a table or cluster and reset the STORAGEparameters 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
LOADINGor FAILED. * You cannot roll back a
TRUNCATEstatement eg., TRUNCATE TABLE emp; Delete: The DELETE statement removes entire rows of data from a specified table or view.The
DELETEANY TABLEsystem 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 SUDHIRCONNECT 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 ROLLBACKTO 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 INSERTstatement tries to store a duplicate value in the empnocolumn, the predefined exception DUP_VAL_ON_INDEXis 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
SAVEPOINTstatement 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 requirepartition 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
BEGINand END keywords to enclose the executable statements. An anonymous block consists of an optional declarativepart, 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_FOUNDor 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_tabtable, using the DBMS_OUTPUTpackage: DECLAREEmp_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 DATAINFILE '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 conditionallyto 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