SQL, PL/SQL
1) What is SQL?
1)
SQL stands for "Structured Query Language". It is a slandered
for relation database system. It is 4th generation non-procedural language
where we have to just specify what to get and its system's responsibility how
to get it.
2) How many types of SQL Statements
are there in Oracle?
2) There are basically 6 types of
SQL statements. They are
1. Data Definition Statements (DDL)
DDL statements define, maintain, and drop objects when they are no longer
needed. DDL statements also include statements that permit a user to grant
other users the privileges, or rights, to access the database and specific
objects within the database.
2. Data Manipulation Statements
(DML) DML statements manipulate the database's data. For example, querying,
inserting, updating, and deleting rows of a table are all DML operations;
locking a table or view and examining the execution plan of an SQL statement
are also DML operations.
3. Transaction Control Statements
Transaction control statements manage the changes made by DML statements. They
allow the user or application developer to group changes into logical
transactions. Examples include COMMIT, ROLLBACK, and SAVEPOINT.
4. Session Control Statements
Session control statements allow a user to control the properties of his current
session, including enabling and disabling roles and changing language settings.
The two session control statements are ALTER SESSION and SET ROLE.
5. System Control Statements System
control commands change the properties of the Oracle Server instance. The only
system control command is ALTER SYSTEM; it allows you to change such settings
as the minimum number of shared servers, to kill a session, and to perform
other tasks.
6. Embedded SQL Statements Embedded
SQL statements incorporate DDL, DML, and transaction control statements in a
procedural language program (such as those used with the Oracle Precompilers).
Examples include OPEN, CLOSE, FETCH, and EXECUTE.
3) Built-in Data types in ORCALE 9i
3)
a) VARCHAR2 - Variable length
character string. The maximum size is 4000 bytes. Will not have trailing blanks.
b) NVARCHAR2 - Variable length
character string. Generally used with national character sets. The maximum size
is 4000 bytes. Will not have trailing
blanks.
c) NUMBER - Variable length numeric
data. Maximum length is 21 bytes.
d) LONG - Variable length character
data. Maximum length is 2 gigabyte. Provide for backward capability.
e) DATA - Fixed length date and
time. Uses 7 bytes.
f) TIMESTAMP - Contain year, month,
day, second, and fractional seconds.
g) INTERVELYEAR/DAY - Store period
of time in years and months.
i) RAW - Variable length raw binary
data. Maximum length is 2000 bytes. Provide for backward capability.
j) LONG RAW - Variable length raw
binary data. Maximum length is 3 gigabytes. Provide for backward capability.
h) ROWID - Hexadecimal string
containing the address of the row.
i) UROWID - Hexadecimal string
containing the logical address of the row.
j) CHAR - Fixed length character
string. The maximum size is 2000 bytes. Will
have trailing blanks.
k) NCHAR - Variable length
character string. Generally used with national character sets. The maximum size
is 2000 bytes. Will have trailing
blanks.
l) CLOB - Single byte character
data upto 4 gigabyte.
m) NCLOB - Single byte or fixed
length multibyte National Character set up 4 gigabyte.
n) BLOB- Unstructured binary data
up to 4 gigabyte.
o) BFILE - Binary data stored in an
external file.
4) What are the data types
supported by oracle (INTERNAL)?
4) Varchar2, Number, Char,
MLSLABEL.
5) Char vs VarChar ?
5) The differee difference between
CHAR and VAR CHAR is that CHAR takes up a fixed amount of space on the disk
while VARCHAR takes up only the space required for the actual string (or the
number of bytes specified by r if you use the VARCHAR(m,r) syntax).
For example:
DEFINE char_str CHAR(10)
DEFINE var_str1 VARCHAR(10)
DEFINE var_str2 VARCHAR(10,5)
LET char_str = "A"
LET var_str1 = "A"
LET var_str2 = "A"
On disk, the following space will
be used (I use the hyphen to mean a space)
char_str A---------
var_str1 A
var_str2 A----
(<- 5 bytes total because of its declaration)
So, varchars make much better use
of you disk space-wise. There is, of
course, a price to pay.
1. When you add varchars to a
table, the rows are no longer of a fixed length. If your select does a sequential scan of the
table, it will now take longer because it will need to calculate the starting
location of each row.
For example, before the engine can
examine record #2, it must figure out the length of record #1. Etc.
2. Record updates become more
difficult.
For example, imagine a record with
a varchar of 10 bytes. If you update the
record so that the varchar is now 20
bytes, the record will most likely have to be moved to another spot on the disk
where it can gain the 10 extra bytes it needs.
So, the pros and cons work out like
this:
Pro: Disk space is conserved when
the lengths of data items vary widely or when only a few items are longer than
average. (i.e. The r value of the VARCHAR(m,r) syntax)
Pro: Queries on the more compact
tables can be faster.
Con: Updates of a table can be
slower in some circumstances.
Con: Sequential scans can be slower
(as if they weren't bad enough already!)
As far as some of the other
comments go.
1) I think that char strings are
stored with their space padding on the disk.
It doesn't matter, though, since they are allocated a fixed number of
bytes either way.
2) VARCHAR is not equivalent to
TEXT in anyway! Blobs are handled
completely differently than VARCHAR!
3) Varcher may or may not be
efficient depending upon your circumstances and what you mean by
efficient (disk space vs.
performance).
My personal experience is that, for
the most part, char is better than VARCHAR.
However, there definitely are times when a VARCHAR is what you want.
6)
What is B-Tree, Reverse Key, Bit-Mapped Key, Function Based index and ,
Index organized Tables(IOTs) ?
6) Upto 30 column can be placed for
multicolumn Index.
a) B-Tree Index - All Indexs are
B-Tree Indexes be default unless otherwise stipulated whenever the index is created. Rowid is used to locate the
actual row in the table(Simple Index or Unique index).
b) Reverse Key Index - It is
actually B-Tree Index with reverse key. It reverse the byte of column indexed.
The rowed is not reversed. For example 'Wentz' will be 'ztneW'. It is also a
bit map index.
Advantage of reverse key indexes is
that insertion become distributed across all leaf keys in the index. This work
well in the equality comparisons such as where lastname = 'Wentz'.
DisAdvantage - Rang scanning does
not work very well.
c) Bit Map Index -
1. A Bit Map Index is an
alternative to the B-Tree Index. In this Bit Map is created for each key value
instead of a list of Rowids as in a
B-Tree index.
2.Each bit in bit map is
corresponding top a rowId. When bit is set then it means that the row with the
corresponding RowId contains the key value.
3.A mapping function convert the
bit position to an actual Rowid so the row can be found whenever performing a
query.
Ex -
Product_no = 'P00001' Product_no = 'PO3453' Product_no= 'P06734'
----------------------------- ------------------------------ ---------
1 0 0
0 1 0
0 0 1
0 0 1
0 1 0
Each entry or 'bit' in the bitmap
index corresponds to a single row of the table. The value of each bit depends
upon the values of the corresponding row in the table. For instance, the bitmap
Product_no = 'P00001' contains 1 as its
first bit. This is because the product no P00001 is in the first row of the
table.
Advantages
1. Work well with the tables
containing million of rows.
2. Bit Map index improve response
time dramatically whenever multiple join conditions reference the column having
the Bit Map index created on it. Bit Map index also works good for join
conditions having many (AND, OR clause).
3. Bit Map works well when the column
referenced in the Bit Map index is not updated or deleted frequently.
4. Bit Map indexes work well when
the columns contained in the Bit Map index has a low cardinality is usually (a
small number of distinct values). Low
cardinality is usually whenever a column has less then 12 distinct values. An
example would be a column containing YES or NO.
DisAdvantages
1. Does not work well with the
tables that have the column in the Bit Map Index updated frequently.
2. The bit Map index requires
rebuilding frequently.
d) Function based Index -
1. A function based index
precomputes the value of the expression or function and stores it in the index.
2. The expression or function can't
be aggregate function such as SUM, AVG, COUNT, MAX, MIN, GROUPING, STDDEV,
LAST, FIRST, RANK etc.
3. Function Based indexes still
used the B- tree indexing method.
4. Use function based index
whenever a function is used in the where clause or join condition of a query.
An example would be whenever searching for a persons last name using UPPER
function.
5. If a person's last name is
stored in mixed case and the search condition is provide in the upper case, a
full table scan is performed if a function based index is not created using the
UPPER function.
6. If function is a PL/SQL function
or package and the function or package
is disabled Oracle will not use the Function Based index.
e) Index Organized Table (IOTs):
1. Index Organized Table (IOTs)
stores the indexed columns of the table in the table itself. An index Organized
table (IOTs) will in most instance contain an index on the primary key of the
table that does not change frequently.
2. Index organized tables store the
data in the order of primary key of the table.
3. Index organized tables can
provide very fast access to table data when the query execute a exact match or
range search on the indexed primary key of the table.
4. The decreased time is primarly
because the Oracle only has to make a singe trip to the table since the index
is part of the table index. No going to index and then to the table as in other
indexes.
5. Storage requirement for the
index are reduced since the index column
is not duplicated in the separate index.
6. The keyword ORGANIZATION INDEX
indicates to Oracle that the table will be on IOT.
7. Indexed organized tables can be
become large since they contain all rows of the table. The mandatory OVERFLOW
clause provides the necessary space to divide a row into parts.
8. If you use IOT, make sure your
application will not update primary key frequently.
7) Object Types: Abstract Datatype, Nested Tables
, Varying Array, Large Objects, References, Object Views.
7)
1. Abstract Datatype: Abstract
datatype is a datatype that consists of
one or more subtypes. abstract
data types can be nested and can contain references to other abstract data
types.
2. Nested Tables: A nested table is
a table within a table. A nested table is a collection of rows, represented as
a column within the main table. For each record within main table, the nested
table may contain multiple rows. In a
sence, It's a way of storing a one-tomany relationship within one table.
Consider a table that contained
information about departments, in which each department have many projects in
progress at any ont time, In a strictly relational model, two separate tables
would be created.
Nested tables allow storing the
information about projects within DEPARTMENT table. The project table records
can be accessed directly via the DEPARTMENT table, without the need to perform
join.
The ability to select data without
traversing joins makes data access easier. Even if methods for accessing nested
data are not defined. Department and Project data have clearly been associated.
3. Varying Array: A varying array
is a set of objects, each with the same data types. The size of the array is
limited when it is created.
(When the table is created with a
varying array, the array is a nested table with a limited set of rows) .
Varying arrays also known as
VARRAYS, allows storing repeated attributes in tables.
For example, suppose there is a
PROJECT table, and projects have workers assigned to them.
A project can have many workers,
and a worker can work on multiple projects. In a strictly relational
implementation, a PROJECT table, a WORKER table, and an intersection table
PROJECT_WORKER can be created that stores the relationships between them.
Varying array can be used to store
the worker names in the PROJECT table. If the projects are limited to ten
workers or fewer, varying array with a limit of the entries can be created. The
datatype for the varying array can be created. The datatype for the varying
array will be whatever datatype is appropriate for the worker name values.
The varying array can then be
populated, so that for each project the names of all of the project's workers
can be selected without querying the WORKER table.
4. Large Objects: A large object,
or LOB, is capable of storing large volumes of data. The LOB datatypes
available are BLOB, CLOB, NLOB and BFILE.
a. CLOB - Single byte character
data upto 4 gigabyte.
b. NCLOB - Single byte or fixed
length multibyte National Character set up 4 gigabyte.
c. BLOB- Unstructured binary data
up to 4 gigabyte.
d. BFILE - Binary data stored in an
external file. It exist at operating system level. The data base
only maintains a pointer to the
file. The size of the external file is limited only by the OS. Since the data
is stored outside the database, ORACLE does not maintain concurrency or
integrity of the data.
Multiple LOBs per table can be
used. For example, there can be table with a CLOB column and two BLOB columns.
5. References : Nested tables and
varying arrays are embedded objects. They are physically embedded within
another object. Another type of object, called referenced objects are
physically separate from the object that refer to them. References (also know as REFs) are
essentially pointers to row objects. A row object is different from a column
object. An example would be a varying array, it is an object that is treated as
a column in a table. A row object, on the other hand, always represent a row.
6. Object Views : Object view allow adding OO concepts on top
of existing relational tables. For example, an abstract datatype can be created
based on existing table definition. Thus, object views give the benefits of
relational table storage and OO structures. Object views allow the development
of OO features within a relational database, a kind og bridge between the
relational and OO worlds.
8) What is rowid? when it changed ?
Can you delete data using rowid?
8) Hexadecimal string containing
the address of the row. If you ENABLE ROW MOVEMENT on a partitioned table, then the rowid of a row
can change if the partitioning or sub-partitioning key is updated such that the
row must be moved into a different partition or sub-partition. However, this is
not the default.
However, you cannot assume that the
rowids of deleted rows will not be reused by subsequent inserts to the same
table. In fact, once the delete transaction has been cleaned out, you can almost
guarantee that they will be. So you will need to find another way of doing your
deletes (possibly using the primary key).
The ROWID never changes for a row
unless you change it using EXP/IMP utilitie
9) When will you get invalid rowid
error?
9) Oracle 8i's ROWID is a base
64-encoded physical address and has significant difference from Oracle7 ROWID.
If you database has not been migrated to 8i, you may get invalid rowid error.
10) How to delete duplicate record
from table using row-id?
10)
a) delete from &&table_name
t1 where t1.&&column_name in (select &&column_name
from &&table_name t2 where
t1.rowid > t2.rowid and t1.&&column_name = t2.&&column_name)
/
undefine table_name
undefine column_name
The script will prompt for a table
name and a column name from which duplicate values are to be removed. It will
then remove all rows with ROWID values higher than the lowest ROWID for this
particular column.
b) Let us take a table containing 3
columns, then we can use the following command to delete the duplicate rows
from the table.
delete from table where rowid in
( SELECT
rowid FROM group by rowid,col1,col2,col3
minus SELECT min(rowid) FROM group by col1,col2,col3);
c) delete from table where rowid
not in ( SELECT min(rowid) FROM group by
col1,col2,col3 );
d) delete from employee where empid
not in (Select min(empid) from employee)
e) select column_name from
table_name group by column_name having count(*) > 1
11) How to find Second Max() and
Min() value of a Column..?
11)
a) For Second Max: Select columnA
from tableA order by columnA desc limit 1,1;
b) For Second Min: Select columnA
from tableA order by columnA asc limit 1,1;
a) Select max(salary) from table
where salary not in(select max(salary) from table);
b) Select min(salary) from table
where salary not in(select min(salary) from table);
nth Max() and Min() value of a
Column:
Select distinct col from table a
where 3 = ( select count(distinct col) from table b where b.col >= a.col)
12) What is the difference between
DELETE and TRUNCATE table ?
12) Delete:
At the simplest level, delete scans
the table and removes any rows that match the given criteria in the (optional)
where clause. It generates rollback information so that the deletions can be
undone should it be necessary. Index entries for the deleted rows are removed
from the indexes. You must commit to make the deletions permanent.
When deleting rows from a table,
extents are not deallocated, so if there were 50 extents in the table before
the deletion, there will still be 50 after the deletion. In addition the High
Water Mark is not moved down, so it remains where it was before the deletion
began. This means that any subsequent full table scans may (still) take a long
time to complete - because a full table scan always scans up to the HWM. So, by
example, if a select count(*) from very_large_table; took 15 minutes to
complete before all the rows were deleted, you will find that it still takes
about 15 mins after the deletion - because Oracle is still scanning every
single block up to the HWM - even though some (or all) of the blocks may have
no data in them.
Truncate
Truncate, on the other hand, simply
moves the high water mark on the table right down to the beginning. It does
this very quickly, and does not need to be committed. Once you truncate a
table, there is no going back. Indexes are also truncated. There is no facility
to be able to specify which rows to 'delete' as you can with the where clause
on the delete command.
When a table is truncated, all its
extents are deallocated leaving only the extents specified when the table was
originally created. So if the table was originally created with minextents 3,
there will be 3 extents remaining when the tables is truncated.
If you specify the reuse storage
clause, then the extents are not deallocated. This saves time in the recursive
SQL department if you intend to reload the table with data from an export for
example, and can reduce the time it takes to do the import as there is no need
to dynamically allocate any new extents
13) When having clause is used?
13) The having clause is used to
restrict the result set returned by the GROUP BY clause. When you use GROUP BY
with the HAVING clause, the GROUP BY clause divides the rows into sets of
grouped rows and aggregates their values, and then the HAVING clause eliminates
undesired aggregated groups (filter the
rows that a group by statement returns).
SQL> select * from students;
NAME DATE_OF_B G STUDENT_ID EXAMS GPA
---------- --------- - ----------
--------- ---------
Smith 04-JUL-65 F 2 2 5.8
Green 04-JUL-65 F 3 2 3.3
Taylor 01-JAN-77 F 4 1 0.8
Bane 01-JAN-55 M 5 4 4
SQL> select gender, count(*)
from students group by gender;
You should see the following
result.
G COUNT(*)
- ---------
F 3
M 1
Select gender from students group
by gender having avg(gpa) > 3.9;
You should see the following
result.
SQL> select gender from students
group by gender having avg(gpa) > 3.9;
G
-
M
14) Is it mandatory to have a group
by clause if , having exists ?
14) Yes.
15) What are the set operator?
15) Set operators combine the
results of two queries into one result. INTERSECT returns all distinct rows
selected by both queries. MINUS returns all distinct rows selected by the first
query but not by the second. UNION returns all distinct rows selected by either
query. UNION ALL returns all rows selected by either query, including all
duplicates.
1.If both queries select values of
datatype CHAR, the returned values have datatype CHAR.
2.If either or both of the queries
select values of datatype VARCHAR2, the returned values have datatype VARCHAR2.
16) What are Row Operators?
16) Row operators return or
reference particular rows. ALL retains duplicate rows in the result of a query
or in an aggregate expression. DISTINCT eliminates duplicate rows from the
result of a query or from an aggregate expression. PRIOR refers to the parent
row of the current row returned by a tree-structured query. You must use this
operator in the CONNECT BY clause of such a query to define the parent-child
relationship.
17) What are comparison operators?
17) Typically, you use comparison
operators in the WHERE clause of a data manipulation statement to form
predicates, which compare one expression to another and always yields TRUE,
FALSE, or NULL. You can use all the comparison operators listed below to form
predicates. Moreover, you can combine predicates using the logical operators
AND, OR, and NOT.
ALL
Compares a value to each value in a
list or returned by a subquery and yields TRUE if all of the individual
comparisons yield TRUE.
ANY, SOME
Compares a value to each value in a
list or returned by a subquery and yields TRUE if any of the individual
comparisons yields TRUE.
BETWEEN
Tests whether a value lies in a
specified range.
EXISTS
Returns TRUE if a subquery returns
at least one row.
IN
Tests for set membership.
IS NULL
Tests for nulls.
LIKE
Tests whether a character string
matches a specified pattern, which can include
18) What is the use of dynamic SQL
statement prepare and execute
18) Dynamic SQL lets programs
accept or generate SQL statements at run time. Unlike precompiled SQL or SQL
module language, in which SQL statements are known at compile time, dynamic SQL
lets the user formulate the statements at run time.
Preparing the SQL statement for
execution: When a SQL statement enters the Oracle library cache, the following
steps must occur before the statement is ready to execute:
1. Syntax check - The SQL statement
is checked for proper spelling and word order.
2. Semantic parse - Oracle verifies
all of the tables and column names against the data dictionary.
3. Stored Outline check - Oracle
checks the data dictionary to see if a stored outline exists for the SQL
statement.
4. Generate execution plan - Oracle
uses cost-based optimizer algorithms and statistics in the data dictionary to
determine the optimal execution plan.
5. Create binary code-Oracle
generates a binary executable based on the execution plan.
Once a SQL statement is prepared
for execution, subsequent executions will happen very fast, because Oracle
recognizes identical SQL statements and re-uses executables for those
statements.
However, for systems that generate
ad hoc SQL or SQL with embedded literal values, SQL execution plan generation
time is significant, and previous execution plans often can't be used. For
those queries that join many tables, Oracle can spend a significant amount of
time determining the proper order to join the tables together.
Evaluating table join order
The most expensive step in the SQL
preparation process is the generation of the execution plan, particularly when
dealing with a query with multiple joins. When Oracle evaluates table join
orders, it must consider every possible combination of tables. For example, a
six-way table join has 720 (permutations of 6, or 6 * 5 * 4 * 3 * 2 * 1 = 720)
possible ways that the tables can be joined together. This permutation issue
becomes even more pronounced when you have more than 10 joined tables in a
query: For a 15-way table join, there are over one trillion (1,307,674,368,000
to be exact) possible query permutations that must be evaluated.
19) What are the Different
Optimisation Techniques
19) The Various Optimisation
techniques are
a) Execute Plan: we can see the
plan of the query and change it accordingly based on the indexes
b) Optimizer_hint:
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */
Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql:
By setting the Optimize_Sql = No,
Oracle Forms assigns a single cursor for all SQL statements.This slow downs the
processing because for evertime the SQL must be parsed whenver they are
executed.
f45run module = my_firstform userid
= scott/tiger optimize_sql = No
d) Optimize_Tp:
By setting the Optimize_Tp= No, Oracle Forms
assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the
cursor.
f45run module = my_firstform userid
= scott/tiger optimize_Tp = No
20) Where does oracle store
information about query?
20) Oracle actually 'stores' two
relevant things in buffer.
Firstly, it stores the actual code
you're executing along with the execution plan. It stores this in the Library
Cache which is a component of the Shared Pool which is a component of the
System Global Area (SGA). When Oracle parses the statement and determines the
execution plan (which takes time and in the case of complex queries can take a
substantial amount of time), this is stored away so that when the same code is
subsequently executed, Oracle doesn't have to redo all the work of parsing and
redetermining the execution plan. It's already there (note all users have
access to this area of
memory).
Therefore, the first time you
execut the quesry it probably needs to be (hard) parsed and so initially needs
to go through this overhead. Subsequent times you execute the query, Oracle
probably finds the SQL in the Library cache and is more efficient as a result
as the hard parse is not performed.
To 'get rid' of this, you can issue
the ALTER SYSTEM FLUSH SHARED_POOL;
Secondly, Oracle stores the
'blocks' that contain the rows of interest in a Buffer Cache that is also a
component of the SGA. The reason it does this is two fold. Firstly, Oracle can
only manipulate and extract information out of the blocks once they're loaded
into memory. However they are stored in a
shared memory structure because
having gone to all the trouble of retrieving the data from disk, there might be
a possibility that the blocks might need to be accessed again. Note this
possibility is greater for the session that has read in the block(s) but
depending on the data (eg. block on a freelist)
and the data structure (eg. popular
index) it is quite likely that another session may want access to the same
block. The Buffer Cache is organized in a number of linked lists (eg. Least
Recently Used LRU list) to encourage popular blocks to remain cached in memory.
Therefore, the first time you run
your query, the blocks need to be read from disk and loaded into the Buffer
Cache. However when you subsequently re-execute the query, the blocks are
probably still cached in memory, so you avoid the (expensive) disk reads and
the query runs faster as a result.
There is no flush Buffer Cache
command (as it never makes sense to do tis).
21) Simple join and outer join?
21) Outer Joins:
The 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. Such rows are not returned by a simple join. To
write a query that performs an outer
join of tables A and B and returns all rows from A, apply the outer join
operator (+) to all columns of B in the join condition. For all rows in A that
have no matching rows in B, Oracle returns NULL for any select list expressions
containing columns of B.
Example I
This query uses an outer join to
extend the results of Example XIV:
SELECT ename, job, dept.deptno,
dname FROM emp, dept
WHERE emp.deptno (+) = dept.deptno;
ENAME
JOB
DEPTN
DNAME
CLARK
MANAGER
10
ACCOUNTING
KING
PRESIDENT
10
ACCOUNTING
MILLER
CLARK
10
ACCOUNTING
ADAMS
CLARK
20
RESEARCH
BLAKE
ANALYST
20
RESEARCH
TURNER
SALESMAN
20
RESEARCH
WARD
MANAGER
30
SALES
JONES
MANAGER
30
SALES
40
OPERATION
In this outer join, Oracle returns
a row containing the OPERATIONS department even though no employees work in
this department. Oracle returns NULL in the ENAME and JOB columns for this row.
The join query in Example X selects only departments that have employees.
22) What is TTITLE, BTITLE,
REPHEADER, PERFOOTER ?
22) you can set a title to display
at the top of each page of a report. You can also set a title to display at the
bottom of each page. The TTITLE command defines the top title; the BTITLE
command defines the bottom title.
You can also set a header and
footer for each report. The REPHEADER command defines the report header; the
REPFOOTER command defines the report footer.
23) How do u implement the If
statement in the Select Statement
23) We can implement the if
statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,'1','First','2','Second',Null);
Here the Null is the else statement
where null is done .
If you want to compare, for
instance, tbl.Payment with 200,use this code:
Select decode(sign(tbl.Payment
-200),0,'two hundred',1,'Greater than 200','less than 200');
This is a nice shortcut rather than
writing the procedure.
The value of sign(tbl.Payment -200)
is 0 if tbl.Payment = 200.
The value of sign(tbl.Payment -200)
is 1 if tbl.Payment > 200.
otherwise, or
The value of sign(tbl.Payment -200)
is -1 if tbl.Payment < 200
24) What is SERVEROUTPUT setting?
Can I print inside a PL/SQL program?
24) One can use the DBMS_OUTPUT
package to write information to an output buffer. This buffer can be displayed
on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For
example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging
PL/SQL programs. However, if you print too much, the output buffer will
overflow. In that case, set the buffer size to a larger value, eg.: set
serveroutput on size 200000
If you forget to set serveroutput
on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you
haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure,
SQL*Plus will display the entire contents of the buffer when it executes this
dummy PL/SQL block.
25) What built-in
functions/operators are available for manipulating strings?
25) The most useful ones are
LENGTH, SUBSTR, INSTR, and ||:
1. LENGTH(str) returns the length
of str in characters.
2. SUBSTR(str, m, n) returns a
portion of str, beginning at character m, n characters long. If n is omitted,
all characters to the end of str will be returned.
3. INSTR(str1,str2,n,m) searches
str1 beginning with its n-th character for the m-th occurrence of str2 and
returns the position of the character in str1 that is the first character of
this occurrence.
4. str1 || str2 returns the
concatenation of str1 and str2
26) Is it possible to write a
PL/SQL procedure that takes a table name as input and does something with that
table?
26) For pure PL/SQL, the answer is
no, because Oracle has to know the schema of the table in order to compile the
PL/SQL procedure. However, Oracle provides a package called DBMS_SQL, which
allows PL/SQL to execute SQL DML as well as DDL dynamically at run time.
For example, when called, the
following stored procedure drops a specified database table:
CREATE PROCEDURE drop_table
(table_name IN VARCHAR2) AS
cid INTEGER;
BEGIN
-- open new cursor and return cursor ID
cid := DBMS_SQL.OPEN_CURSOR;
-- parse and immediately execute dynamic SQL statement
-- built by concatenating table name to DROP TABLE command
DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);
-- close cursor
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
-- if an exception is raised, close cursor before exiting
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
-- reraise the exception
RAISE;
END drop_table;
.
RUN;
27) What is the correct syntax for
ordering query results by row-type objects?
27) As a concrete example, suppose
we have defined an object type PersonType with an ORDER MEMBER FUNCTION, and we
have created a table Person of PersonType objects. Suppose you want to list all
PersonType objects in Person in order. You'd probably expect the following to
work:
SELECT * FROM Person p ORDER BY p;
But it doesn't. Somehow, Oracle
cannot figure out that you are ordering PersonType objects. Here is a hack that
works:
SELECT * FROM Person p ORDER BY DEREF(REF(p));
28) How you specify column
separators?
28)
The solution is to concatenate the rows together with whatever separator
you choose to display. For example:
SELECT col_a || '|' || col_b
FROM table;
This will show the result like:
first|100
second|200
thrid|300
29) Can U disable database trigger?
How?
29) Yes. With respect to table
ALTER TABLE TABLE [DISABLE all_trigger]
30) What are attributes of cursor?
30)
%FOUND , %NOTFOUND , %ISOPEN, %ROWCOUNT
31) What are pseudo columns? Name
them?
31) A pseudo column behaves like a
table column, but is not actually stored in the table. You can select from pseudo columns, but you
cannot insert, update, or delete their values.
These are: CURRVAL, NEXTVAL,
LEVEL, ROWID, ROWNUM.
32) Rownum with ORDER BY clause?
32) One of the most often uses of
the pseudo column rownum is to provide serial numbers to the records in a
query. This feature is widely used in reports to represent systematic display
of information.
For instance
Listing A
Select rownum, ename, empno from
emp10;
Table A
ROWNUM ENAME EMPNO
--------- ---------- ---------
1 KING 7839
2 BLAKE
7698
3 CLARK
7782
4 JONES 7566
5 MARTIN
7654
However, when we order this
statement the rownum gets disturbed as shown below ( Listing B and Table B).
Listing B
select rownum, ename, empno from
emp10 order by ename;
Table B
ROWNUM ENAME EMPNO
--------- ---------- ---------
2 BLAKE 7698
3 CLARK 7782
4 JONES 7566
1 KING 7839
5 MARTIN 7654
As we can see from above the
employee names did get ordered but the rownum also got the wrong order. The desired result was BLAKE having rownum 1
, CLARK having a rownum of 2 and so on.
To achieve this we have to outer join this table with dual that process forces
a implicit order on the rownum as shown below ( Listing C, Table C).
Listing C
select rownum, ename, empno from
emp10 a , dual d where a.ename = d.dummy
(+) order by ename;
Table C
ROWNUM ENAME EMPNO
------- ---------- ---------
1 BLAKE 7698
2 CLARK 7782
3 JONES
7566
4 KING 7839
5 MARTIN 7654
The trick is to do an outer join
with the column that you want to order and this process does not disturb the
rownum order. In addition to that if the
column is of number datatype then one
should make sure to use TO_CHAR datatype conversion function.
32) Can you use select in FROM
clause of SQL select ?
32)
Yes.
33) Inline View?
33) An inline view is just a
subquery in place of a table in an INSERT, UPDATE, DELETE, or SELECT. If you could have issued a "create view
as <subquery>" and then inserted/updated/deleted/selected from it,
you can skip the create view and just inline it in the DML.
Consider:
SQL> select * from ( select
empno, ename from emp where deptno = 10 );
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
SQL> delete from ( select empno,
ename from emp where deptno = 10 );
3 rows deleted.
SQL> update ( select empno,
ename from emp where deptno = 20 ) set ename = lower( ename );
5 rows updated.
SQL> insert into ( select empno,
ename from emp where deptno = 20 with check
option ) values ( 1, 'x' );
insert into ( select empno, ename
from emp where deptno = 20 with check option )
values ( 1, 'x' )
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION
where-clause violation
SQL> insert into ( select empno,
ename, deptno from emp
2 where deptno = 20 with check
option ) values ( 1, 'x', 20 );
1 row created.
34) How many types of Exceptions
are there?
34) There are 2 types of
exceptions. They are
a) System Exceptions:
e.g. When no_data_found, When
too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
35) How many columns can table
have?
35) The number of columns in a table can range
from 1 to 254 (32 bit).
36) What is the max. length for
variable name and value to be stored in it
36)
A variable name must begin with character and can be followed by a
maximum of 29 character.
37) What are the uid and user
function?
37) UID returns an integer that
uniquely identifies the session user (the user who logged on).
SELECT UID FROM DUAL;
UID
----------
19
USER returns the name of the
session user (the user who logged on) with the datatype VARCHAR2. Oracle
compares values of this function with blank-padded comparison semantics.
In a distributed SQL statement, the
UID and USER functions identify the user on your local database. You cannot use
these functions in the condition of a CHECK constraint.
SELECT USER, UID FROM DUAL;
USER UID
------------------------------
----------
SCOTT 19
38) What does the set escape
command does? or How does one disable interactive prompting in SQL*Plus?
38)
ESC[APE] {\|c|OFF|ON}
Defines the character you enter as
the escape character. OFF undefines the escape character. ON enables the escape
character. ON changes the value of c back to the default "\".
You can use the escape character
before the substitution character (set through SET DEFINE) to indicate that
SQL*Plus should treat the substitution character as an ordinary character
rather than as a request for variable substitution
If your data contains special
punctuation characters, you may encounter difficulties in SQLPLUS of ORACLE.
There are the solutions:
To obtain a single quote character
('), use two single quotes, i.e., '', to get one single quote character. For
example, you would enter 'Smith''s Home' in SQL for the string "Smith's
Home".
The '&' character is used by
SQLPLUS as substitution character for data input. To use an '&' character
as part of your string value, you need to turn on the escape character first
use the following command:
SQL>set escape \
If you want enter a string
containing '&, you would use '\' to escape the '&' character. For
example, to enter the string, "Johnson & Son Co.", as 'Johnson
\& Son Co.'
39)
How to insert an ampersand into an table?
39) create table x (a
varchar2(10));
Table created.
SQL> insert into x values
('a'||chr(38)||'b');
1 row created.
SQL> set escape on
SQL> insert into x values
('x\&y');
1 row created.
SQL> set escape off
SQL> set define ^
SQL> insert into x
values('m&n');
1 row created.
SQL> select * from x;
a&b
x&y
m&n
39) With set autocommit on command,
a PL/SQL block having multiple insert, update or delete statements is executed.
Now if the script aborts in between, will all the changes be committed or none
at all ?
39) SET AUTOCOMMIT ON treats a
PL/SQL block as a single transaction. In other words, no matter how many
INSERT, UPDATE and DELETE statements that are executed in the PL/SQL block, the
commit will happen only at the end of the complete block. Therefore, if the
script aborts in the middle of the block, then nothing will be committed. But
as long as the block completes successfully, all statements executed within the
block will be committed.
40) How to execute operating system
commands from SQL plus? Or What is the difference between ! and HOST?
40) host ls -alrt
Both "!" and
"HOST" will execute operating system commands as child processes of
SQL*Plus. The difference is that "HOST" will perform variable
substitution (& and && symbols), whereas "!" will not.
(Note: use "$" under MVS, VMS, and Windows environments, not
"!")
41) What is the difference between
& and &&?
41)
"&" is used to create a temporary substitution variable
and will prompt you for a value every time it is referenced.
"&&" is used to
create a permanent substitution variable as with the DEFINE command and the
OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a
value it will use that value every time the variable is referenced.
Eg: SQL> SELECT * FROM TAB WHERE
TNAME LIKE '%&TABLE_NAME.%';
42) How can a SQL script be
executed automatically as soon as SQLplus is invoked?
42) When SQL*Plus starts up, it
will look for a global login script called glogin.sql in the
$ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.
Thereafter, sqlplus will try to
find a local login script called login.sql. It will look for the login.sql
script in the directory where you start sqlplus from, alternatively it will
search the directories listed in the SQLPATH environment variable for such a
script. When found, sqlplus will execute it.
43) What is %type and %rowtype?
43)
The %TYPE and %ROWTYPE Attributes The %TYPE attribute provides the
datatype of a variable, constant, or
column. This attribute is particularly useful when declaring a variable or
procedure argument that refers to a column in a database table. The %ROWTYPE
attribute is useful if you want to declare a variable to be a record that has
the same structure as a row in a table or view, or a row that is returned by a
fetch from a cursor.
When you declare a construct using
%TYPE and %ROWTYPE, you do not need to know the datatype of a column or
structure of a table. For example, the argument list of a procedure that
inserts a row into the EMP table could be declared as
CREATE PROCEDURE hire_fire(emp_record
emp%ROWTYPE) AS ... END;
If you change the column or table
structure, the constructs defined on their datatypes or structure automatically
change accordingly.
However, while one type of
dependency is eliminated using %TYPE or %ROWTYPE, another is created. If you
define a construct using object%TYPE or object%ROWTYPE, the construct depends
on object. If object is altered, the constructs that depend on object are
invalidated.
44) What are Procedure, functions
and Packages?
44) Procedures and functions
consist of set of PL/SQL statements that are grouped together as a unit to
solve a specific problem or perform set of related tasks. procedure do not
return values while function return one value.
Packages: Packages provide a method
of encapsulating and storing related procedures, functions, variables and other
Package Contents.
45) What are Database Triggers and
Stored Procedures?
45) Database Triggers: Database
Triggers (DT) are procedures that are automatically executed as a result of
insert in, update to, or delete from table.
DT have the values old and new to
denote the old value in the table before it is deleted and the new indicated
the new value that will be used. DT are useful for implementing complex
business rules which cannot be enforced using the integrity rules. We can have
the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g.: operations insert, update,
and delete 3 before, after 3*2 a total of 6 combinations
At statement level (once for the
trigger) or row level (for every execution)
6 * 2 A total of 12.
Thus a total of 12 combinations are
there and the restriction of usage of 12 triggers has been lifted from Oracle
7.3 Onwards.
What is a stored procedure?
Pre-compiled SQL statement residing in the database you can pass in parameters
and you can receive result sets(s) out.
Why use stored procedures?
Pre-compile once, use many times stored procedure is a re-usable object
reduce network traffic Separate
business rules and logic from prresentation layer They're faster
Stored Procedures: Oracle Stored
Procedures (OSP) are Procedures that are stored in Compiled form in the
database. The advantage of using the stored procedures is that many users can
use the same procedure in compiled and ready to use format. It also reduce the
network traffic.
OSP should contain as much of the
business rules logic for an application as possible. In front-end which handles
only the user interface; OSP in the back-end encapsulate all the business logic
of the app. The front-end basically gathers user input and passes it to OSP as
parameters; the OSP contain all the logic data validation, etc. There are no
SQL statements anywhere in the app.
There are several advantages to
this approach. First, when the business rules change, OSP can be changed as
necessary individually. There is no "library" that needs wholesale
replacement, as with VB modules or .dll's.
At least with Oracle, procedures can be re-created without shutting down
the server or the front-end app. This architecture permits a clear distinction
in functionality between the front-end and back-end. You'll find that
centralizing the business rule logic on the server back-end side makes both
development and maintenance much easier in the long run.
46) What are the implicit & explicit
cursor attributes?
46)
Both Implicit and Explicit cursor have 4 attributes:
Implicit Cursor:
1. %ISOPEN: The Oracle engine
automatically opens and closed the SQL cursor after executing its associated
select, insert, update or delete SQL statement has been processed in case of
implicit cursor. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be
referenced outside of its SQL statement. As a result, SQL%ISOPEN always
evaluates to FALSE.
2. %FOUND: Evaluates to TRUE, if an
insert, update or delete affected one or more rows, or a single-row select
returned one or more rows. Otherwise it evaluates to FALSE. The syntax for
accessing this attribute is SQL%FOUND.
3. %NOTFOUND: It is logical
opposite of %FOUND. It evaluates to TRUE, if an insert , update or delete
affected no rows, or otherwise it evaluates to FALSE. The syntax for accessing
this attribute is SQL%NOTFOUND.
4. %ROWCOUNT: Returns the numbers
of rows affected by an insert , update or delete, or select into statement. The
syntax for accessing this attribute is SQL%ROWCOUNT.
Explicit Cursor:
1. %ISOPEN: Return TRUE if cursor
is open, FALSE otherwise.
2. %FOUND: Return TRUE if record
was fetched successfully, FALSE otherwise.
3. %NOTFOUND: Return TRUE if record
was not fetched successfully, FALSE otherwise.
4. %ROWCOUNT: Returns number of
records processed from the cursor.
47) What is the difference between
implicit and explicit cursors?
47) An explicit cursor is declared
opened and fetched from in the program block where as an implicit cursor is
automatically generated for SQL statements that process a single row only.
48) For which SQL statements is the
implicit cursor opened?
48) SQL statements that processes a
single row.
49) How cursor attributes can be
referenced in case of implicit cursor?
49) By using SQL%FOUND,
SQL%NOTFOUND, SQL%ROWCOPUNT, SQL%ISOPEN.
50) Which is the form of the
cursor, where these operations are made automatically?
50) In case if Implicit Cursor.
51) When is it appropriate to use
explicit cursors, give an example?
51) When more than one row of a
table is to be processed by an SQL statement.
52) Can you pass a parameter to a
cursor? What is the mode of the parameter that can be passed?
52) Yes, It can be of type value/ variable/
expression.The scope of cursor parameters is local to that cursor, which means
that they can be referenced only within the query declared in the cursor
declaration. Each parameter in the declaration must have a corresponding value
in the open statement.
53) What are the advantages of using
PL/SQL?
53) PL/SQL is a completely
portable, high-performance transaction processing language that offers the
following advantages:
1. Support for SQL.
2. Higher productivity.
3. Better performance
4. Portability.
5. integration with Oracle
54)
The procedural and SQL statements in a PL/SQL block are processed by the
SQL statement executor and the Procedural statement executor. Explain how this
works in a client server environment and where the components reside.
54) Application development tools
that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks
and subprograms.
Anonymous PL/SQL blocks can be
embedded in an Oracle Pre-compiler or OCI program. At run time, the program,
lacking a local PL/SQL engine, sends these blocks to the Oracle Server, where
they are compiled and executed.
When it contains the PL/SQL engine,
an application development tool can process PL/SQL blocks. The tool passes the
blocks to its local PL/SQL engine. The engine executes all procedural
statements at the application site and sends only SQL statements to Oracle.
Thus, most of the work is done at the application site, not at the server site
55) What are 4 of the places (or
vehicles) for the use of PL/SQL?
55) What is the purpose of each?
1. As anonymous blocks which are
compiled and run when loaded. Required to do once off tasks.
2. As triggers for integrity
purposes. Required for integrity purposes.
3. As procedures and functions
(collectively known as subprograms). To permanently store blocks of application
code in the database for reuse at to refer to from other blocks of
code(modularization).
4. As packages: essentially a named
declarative section for storing related objects including procedures, functions
and variables. See above subprograms.
(Triggers, procedures, functions
and packages are stored in the database and can be called directly from other
PL/SQL blocks)
Embedded in a host language such as
C. To combine PL/SQL with a more powerful language such as C for specific
tasks.
56) What are mutating tables?
56) When a table is in state of
transition it is said to be mutating. e.g. If a row has been deleted then the
table is said to be mutating and no operations can be done on the table except
select.
A mutating table is a table that is
in the process of being modified by an UDPATE, DELETE or INSERT statement. A
table may also be considered to be mutating if
it needs to be updated due to a DELETE CASCADE referential integrity
constraint. The reason you may get a mutating table error (i.e. ORA-04091) is that
if you fire a trigger which attempts to look at or modify the same table, the
trigger is effectively prevented from seeing a consistent view of the data.
For example, let's say that you
attempt to give every employee who makes $10/hour a 10% salary increase as
follows: UPDATE employee SET salary =
salary * 1.10 WHERE salary = 10 ;
This would mean that the changed
salary for all these employees would be $11. Your UPDATE statement caused an
AFTER ROW trigger to be fired that contained the following statement: SELECT
salary FROM employee WHERE . . .
You would get the mutating table
error because the trigger tried to query the original employee table but the
original table was in the process of change. Which version of the table should
Oracle retrieve when the SELECT is issued? That's the problem! Oracle simply
will not allow this statement to occur, because it doesn't have any way of
knowing how to provide the correct set of data (i.e. a consistent view of the
data). Oracle would generate the ORA-04091 error and rollback both the trigger
body and the triggering statement (i.e. the UPDATE).
Bottom-line: you cannot touch the
table you are updating, deleting from or inserting into in any trigger or
user-defined function code that it may execute
57) What restrictions apply to the
use of PL/SQL in triggers?
57) First define mutating table and
a constraining table.
A mutating table is one that is one
that is currently being modified by a DML statement. For a trigger this is the
table on which the trigger is defined. Also if a table is being updated as a
result of a DELETE CASCADE referential integrity constraint it is also
mutating.
A constraining table is a table
that might need to be read from for a referential integrity constraint.
For all row level triggers and also
a statement triggers that have been fired as a result of a DELETE CASCADE
operation.
SQL statements in a trigger body
may not
-read from or modify any mutating
table of the triggering statement. This includes the triggering table itself.
- read from or modify the primary,
unique, or foreign key columns of a constraining table of the triggering table.
They may, however, modify the other columns if desired.
58) What are the Locks in Oracle?
58)
Shared Locks:
1. Shared locks are placed on resource
whenever a READ operation(SELECT) is performed.
2. Multiple shared locks can be
simultaneously set on a resource.
Exclusive Locking:
1. Exclusive locks are placed on
resources whenever Write operations (INSERT, UPDATE and DELETE) are performed.
2. Only one exclusive lock can be
placed on a resource at a time i.e. the first user who acquire an exclusive
lock will continue to have the sole ownership of the resource, and no other
user can acquire an exclusive lock on that resource.
59) What is the use of 'For
Update'?
59)
Explicit Locking: The technique of
lock taken on a table or its resources by a user is called Explicit Locking.
Who can Explicitly Lock?
User can lock tables they own or
any tables on which they have been granted table privileges(such as select ,
insert, update, delete).
Oracle provide facilities by which
the default locking strategy can be overridden. Table(s) or row(s) can be
explicitly locked by using either the Select...for update statement, or a lock
table statement.
Select...For Update Statement
It is used for acquiring exclusive
row level locks in anticipation of performing updates on records. It is often
followed by one or more update statement with a where clause.
Example:
1. Two client machine Client A and
Client B are generating sales_orders simultaneously.
2. Client A fires the following
select statement.
Client A> SELECT * FROM
Sales_Order Where order_no = '00001' for UPDATE;
When the above select statement is
fired, the Oracle engine locks the record 00001, this lock is released when a
commit or rollback is fired by Client A.
Now Client B fires a Select
statement, which points to record 00001, which has already been locked by
client A.
Client B> SELECT * FROM
Sales_Order Where order_no = '00001' for UPDATE;
The Oracle engine will ensure that
Client B's SQl statement waits for the lock to be released on Sales_Order by a
COMMIT or ROLLBACK statement fired by client a forever.
In order to avoid unnecessary
waiting time, a NOWAIT option can be used to inform the Oracle engine to
terminate the SQL statement if the record has already been locked.
If
this happens the Oracle engine terminates the running DML and comes up
with a message indicating that the resource is busy.
If Client B fires the following
select statement now with a NOWAIT clause.
Client B> SELECT * FROM
Sales_Order Where order_no = '00001' for UPDATE NOWAIT;
Oracle engine will display the
following message:
SQL> 00054: resource busy and
acquire with nowait specified.
The Select...for update cannot be
used with the following:
1. Distinct and the group by
clause.
2. Set operators and group
functions.
60) What is exception handler?
60) An Exception Handler is nothing
but a code block in memory that will attempt to resolve the current exception
condition. The Oracle engine can recognize every exception condition that
occurs in memory. To handle very common and repetitive exception conditions the
Oracle engine uses Named Exception Handlers. The Oracle engine has about
fifteen to twenty named exception handlers. In addition to this Oracle engine
uses more than twenty thousand numbered exception handlers. These exception
handlers are identified not by names but by four integers preceded by a
hyphen(i.e. -1414) . These exception handlers names are actually a set of negative
signed integers. Each Exception Handler, irrespective of how it is identified,
(i.e. by Name or Number) has code attached that will attempt top resolve an
exception condition. This is how Oracle's "Default
Exception-Handling" strategy works.
Oracle's default exception handling
code can be overridden. When this is done Oracle's default exception handling
code is not executed but the code block that takes care of the exception
condition, in the exception section, of
the PL/SQL block executed. This is an example of a programmer giving explicit
exception handling instructions to an Oracle exception handler.
This means that Oracle engin's
Exception Handler must establish whether to execute its own exception handling
code or whether it has to execute user-defined exception handling code.
As soon as the Oracle engine
invokes an exception handler the exception handler goes back to the PL/SQL
block from which the exception was raised. The exception handler scans the
PL/SQL block for the existence of an Exception section within the PL/SQL block,
If an exception section within the PL/SQL block exists the exception handler
scan the first word, after the action word When, within the exception section.
If the first word after the action
word "When", is the exception handler's name then the exception
handler executes the code contained in the "Then" section of the
construct as follows:
Exception: When {<Exception
Name>} Then
{<User defined action to be
carried out>}
The first word that follows the
action word When must be String. Hence this technique will work well for the
fifteen to twenty named exception handlers. In addition to these Oracle has
twenty thousand numbered exception handlers, which are raised automatically can
appropriately when the oracle engine recognizes exception condition. User
defined exception handling code must be permitted even for these(numbered)
exception handlers.
61) Pre-Defined Oracle error
handlers?
61)
1. DUP_VAL_ON_INDEX: Raised when an
insert or update attempts to create two rows with duplicate values in colums/s
constrained by a unique index.
2. LOGIN_DENIED: Raised when an
invalid username/password was used to log onto Oracle.
3. NO_DATA_FOUND: Raised when a
select statement returns zero rows.
4. NOT_LOGGED_ON: Raised when PL/SQL
issues an Oracle call without being logged onto Oracle,
5. PROGRAM_ERROR: Raised when
Oracle has internal problem.
6. TIMEOUT_ON_RESOURCE: Raised when
Oracle has been waiting to access a resource beyond the user-defined
timeout limit.
7. TOO_MANY_ROWS: Raised when a
select statement returns more than one row.
8. VALUE_ERROR: Raised when the
data type or data size is invalid.
9. OTHERS: Stands for all other
exceptions not explicitly named.
62) USER-Named Exception Handlers?
62)
To handle unnamed internal exceptions, you must use the OTHERS handler
or the
pragma EXCEPTION_INIT. A pragma is
a compiler directive, which can be thought of as a parenthetical remark to the
compiler. Pragmas (also called pseudoinstructions) are processed at compile
time, not at run time. In PL/SQL, the pragma EXCEPTION_INIT tells the compiler
to associate an exception name with an Oracle error number. That allows you to
refer to any
internal exception by name and to
write a specific handler for it. You code the pragma EXCEPTION_INIT in the
declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA
EXCEPTION_INIT(exception_name, Oracle_error_number);
where exception_name is the name of
a previously declared exception. The pragma must appear somewhere after the
exception declaration in the same declarative section, as shown in the
following example:
DECLARE
deadlock_detected EXCEPTION;
PRAGMA
EXCEPTION_INIT(deadlock_detected, -60);
User-Defined Exceptions
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
END;
or
The technique that is used to bind
a numbered exception handler to a name using Pragma Eception_init(), This
binding of a numbered exception handler, to a name (i.e. a String), is done in
the Declare section of a PL/SQL block.
All objects declared in the Declare
section of a PL/SQL block not created until actually required within the PL/SQL
block. However, the binding of a numbered exception handler to a name must be
done exactly when declared not when the exception handler is invoked due to an
exception condition.
The Pragma action word is a call to
a pre-compiler, which immediately binds the numbered exception handler to a
name when encountered.
The function Exception_init() takes
two parameters the first is the user defined exception name the second is the
Oracle engine's exception number. These lines will be included in the Declare
section of the PL/SQL Block.
Syntax:
DECLARE
<exception_name> EXCEPTION;
PRAGMA EXCEPTION_INIT(<exception_name>,<error_code_no>);
BEGIN
Using this technique it is possible
to bind appropriated number exception handlers to names and use these names in
the Exception section of a PL/SQL block. When this is done the default
exception handling code of the exception handler is override and the user-defined
xeception handling code is executed.
Syntax:
DECLARE
<exception_name> EXCEPTION;
PRAGMA EXCEPTION_INIT(<exception_name>,<error_code_no>);
BEGIN
....
....
EXCEPTION
WHEN <exception_name> THEN
<action >
END;
Using raise_application_error: Package DBMS_STANDARD, which is supplied with
Oracle, provides language facilities that help your application interact with
Oracle.
For example, the procedure
raise_application_error lets you issue user-defined error messages from stored
subprograms. That way, you can report errors to your application and avoid
returning unhandled exceptions.To
call raise_application_error, use the syntax
raise_application_error(error_number, message[, {TRUE | FALSE}]);
where error_number is a negative
integer in the range -20000 .. -20999 and
message is a character string up to
2048 bytes long. If the optional third parameter
is TRUE, the error is placed on the
stack of previous errors. If the parameter is
FALSE (the default), the error
replaces all previous errors. Package DBMS_STANDARD is an extension of package
STANDARD, so you need not qualify references to its contents.
63) What is the block structure of
PL/SQL block?
63)
64) Bind variable?
64)
Bind variable, also referred to as input variables and output
varaibles, are used to promote the reuse
of SQL statements. For example, given
the famous emp table in the scott schema, you may code:
SQL> select * from emp where
empno = 1234;
SQL> select * from emp where
empno = 5678;
Unfortunately, as far as Oracle is
concerned, the above two queries are two completely different queries and each
will need to be parsed, optimized, and validated for access (can you select *
from the emp table?). If on the other hand you coded:
SQL> variable empno number
SQL> exec :empno := 1234;
SQL> select * from emp where
empno = :empno;
SQL> exec :empno := 5678;
SQL> select * from emp where
empno = :empno;
You would get the same results, but
the second execution of the select statement would 'reuse' the already
compiled, parsed, optimized query plan (eg: it would run faster the second time
around). Since SQL query plans are shared not only across sessions (eg: you
could log out and log back in between executions of the select statement and
still see the speed up) but across users, commonly executed SQL will get
parsed/compiled/optimized once per database instance, not per user/per use.
Bind variables can be used anywhere
a database column can be used. Anywhere
you can put a column, you can put a bind variable. You cannot use bind variables in place of
database objects however, so for example:
SQL> select * from :some_table;
Will always fail. This SQL query
cannot be parsed and compiled and optimized since the value of :some_table will
be changing. In order to accomplish the
above you need to use dynamic sql (see dbms_sql if pl/sql is the access
language, pro*c docs for documentation on dynamic sql methods 1-4 otherwise).
OR
You can use bind variables
anywhere. PL/SQL actually converts all local variables to bind variables in your
static SQL automatically. With SQL, your host application has to supply actual
values for bind variables if you use them.
NOT using bind variables where
appropriate (and it's almost everywhere) is a major performance and scalability
inhibitor. Without them, all your SQL is unique to Oracle, and each statement
is hard-parsed and is kept in the library cache as new unique SQL, which
quickly fills up the SGA with unique non-shareable statements and introduces
library cache latch contention among other bad things. There are cases when you
would want not to use bind variables (to allow CBO to see actual values so that
it can come up with optimal plan for your particular data distribution), but
these are actually rare.
Tom Kyte's book (Expert one-on-one:
Oracle) lists a method for detecting SQL statements with literals, which allows
to spot such statements quickly and reliably.
Optimizer and Bind variables: The
first time the optimizer sees a SQL statement with bind variables, it checks
the actual input values and uses those values to generate an execution path.
Thereafter, the same path is used for that SQL text, regardless of the actual
values for the bind variables in the subsequent uses of the text. This behavior
also appears if you use cursor_sharing=force,
but can be modified if you use cursor_sharing=exact.
In 8i and later, there is
CURSOR_SHARING parameter, which automatically replaces literals with bind
variables when set to EXACT (or SIMILAR in 9i), but it has a number of bugs and
caveats associated with it, so it's not a silver bullet, though it is
particularly useful with dynamic SQL. You may want to look at it, but I would
discourage setting it at instance level and only set it to EXACT in session
when it is really needed. Ideally, you should explicitly use bind variables
where they are appropriate and not rely on some database setting to do it for
you automatically.
OR
A Bind: A bind variable is like a
parameter to a query. For example the
query:SQL> select * from emp where ename = 'KING'; make no use of bind
variables. If we want to change the search criteria from 'KING' to 'BLAKE' we
must rewrite the entire query, reparse and execute it. If on the other hand we
had coded: SQL> select * from emp where ename = :x;
We would 'bind' the value of 'KING'
to :x and simply parse and execute the query.
Later we can change the value of our bind variable :x to BLAKE and
simply re-execute the query (skipping the parse phase).
To a forms developer, this is all
pretty much transparent. D2K and pl/sql
do this transparently for you. When you
build a block and go into enter query mode, forms will build a query that uses
bind variables(in the hope that someone else has already parsed/optimized such
a query for us in the shared pool in the database and we can save time). also, whenever you code pl/sql, the pl/sql
parser is finding bind varaables
and turning them into 'real' bind
variables when possible. Consider the
following example executed in sql*plus: SQL> alter session set
sql_trace =true;
Session altered.
SQL> @a
SQL> declare
2 l_cnt number;
3 l_str varchar2(20) default 'KING';
4 begin
5 select count(*) into l_cnt
from emp where ename = l_str;
6 end;
7 /
PL/SQL procedure successfully
completed. Now, when we run 'tkprof' on the generated trace file (from the
alter session command) we get to see the 'real' sql we executed which was:
SELECT COUNT(*) FROM EMP WHERE ENAME = :b1
See how pl/sql rewrote the query
using placeholders AKA bind variable in the query.
65) Advantages of packages.
65) Advantages of Packages:
1. Packages enable the organization
of commercial applications into efficient modules. Each package is easily
understood, and the interface between package are simple, clear and well
defined.
2. Package allows granting of
privileges efficiently.
3.
A Package's public variables and cursor persist for the duration of the
session. Therefore all cursor and procedures that execute in this environment
can share them.
4.
Package enables the overloading of procedure and function when required.
5. Package improves performance by
loading multiple objects into memory at once. Therefore, subsequent calls to
related subprograms in the package require no I/O.
6. Package promote code reuse
through the use of libraries that contain stored procedures and functions,
reducing redundant coding.
65) PL/SQL table?
65) PL/SQL tables are a lot more
useful than you might think. True, a PL/SQL table is LIKE a 1-dimensional
array. However, unlike an array, it is a sparse structure. You only use the
memory that you need. It's unlimited in it's size. Well, it IS limited by the
amount of memory you have.
If you need to hold 100 items, then
you must declare an array of that size with your normal programming langauges
(such as C or Pascal or Basic). However, there really is no way to search the
array other than a sequential search UNLESS you use hashing or a binary search.
With a PL\SQL table, the index is a
binary index. The indexes do NOT have to be in sequential order. I can declare
a PL/SQL table as such:
TYPE error_table_typ IS TABLE of NUMBER(7)
INDEX BY BINARY_INTEGER;
my_table error_table_typ;
I could then store numbers as
follows:
my_table(1) := 100;
my_table(100) := 205;
my_table(150) := 1;
All that is in memory are these
three "records". That's it. Now, if I wanted to store them this way
in an array in C or Pascal, I would have to create an array of at least 150
elements. Of course, some of you may be thinking, "WHY would I want to
store elements out of order?" Well, a PL/SQL table works great if you have
data you have to constantly verify against. Selecting against a PL/SQL table is
a LOT faster than having to constantly go against a table in the database.
Loading the table into a PL/SQL table and then using the PL/SQL table to verify
your data is much faster. THIS is where a PL/SQL table comes in handy.
Let's say you have a table that has
an ID to indentify a business. You want the business name. The ID's are NOT
neccessarily in a sequential order. But, they ARE numbers. Here's the table:
COMPANY_TABLE
company_id_nbr NUMBER(8);
company_name_txt VARCHAR2(200);
We create a PL/SQL table as
follows:
DECLARE
TYPE company_name_table_typ IS TABLE of
VARCHAR2(200)
INDEX BY BINARY_INTEGER;
my_table company_name_table_typ;
CURSOR my_cursor IS
SELECT company_id_nbr,
company_name_txt
FROM company_table;
my_cursor_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
FETCH my_cursor
INTO my_cursor_rec;
WHILE my_cursor%FOUND
LOOP
my_table(my_cursor_rec.company_id_nbr) :=
my_cursor_rec.company_name_txt;
FETCH my_cursor
INTO my_cursor_rec;
END LOOP;
CLOSE my_cursor;
END;
Put this code into a procedure
before you're main code runs (make the PL/SQL tables global, of course). Then,
if we ever need to verify the company name and all we have is the id, we can
then do the following:
DECLARE
name_txt VARCHAR2(200);
id_nbr NUMBER(8);
BEGIN
name_txt := my_table(id_nbr);
...
EXCEPTION
WHEN no_data_found
THEN
/* This will happen if the record
does NOT exists */
/* in the PL/SQL table. */
END;
This is MUCH faster than going
against the table in the database directly. THIS is the beauty of PL/SQL
tables. At first, I too felt they were useless. Now,
I've gone back to many programs
I've written and rewrote them to use
PL/SQL tables with this kind of programming. I have noticed a 25%+
increase in the speed in the verification routines of my programs, which also
decreases the amount of time my programs run (especially my overnight processes
that are
huge).
With PL/SQL 2.3, you will be able
to have PL/SQL tables with more than one
column which means you can create a PL/SQL table that is a direct copy of a
record of a table in the database. Until then, if you use PL/SQL version before
2.3, then you won't be able to have more than one column per PL/SQL table. But,
you just have to create a PL/SQL
table for each column that has a different type and index each table by the
same index. It's that simple.
66) How the nulls are handled in PL/SQL?
66) A NULL does not equal NULL.
Null is always undefined. Since it has no definition it cannot be equal to
another undefined value. This is how NULL are treated in ORACLE. if both
columns that were being compared were null then they were not equal. One way to
handle this is to wrap nvl(your_column_her,0) in your cursor select or in the
comparison this will ensure that NULL
will be handled identically.
IF nvl(a,0) = nvl(b,0) then
do something;
END IF;
Watch out! The above example will
fail when a=0 and b is null, and When a is null and b=0.
The only way you can really be sure
is with something like this:
IF a = b or (a is null and b is
null)
Oracle's way of always returning
FALSE to any comparison where a column value is null indicates that
they don't work much in the *real*.
Ok, here is the partial package,
the rest you can fill in with your
rules for comparision ( is a null < null for
example?. Why is a null
< anything else? )
----------------------------------------------------------
create or replace package null
as
function "="( a in varchar2, b in
varchar2 ) return number;
pragma restrict_references ( "=",
WNDS, RNDS, WNPS, RNPS );
function "<"( a in varchar2, b in
varchar2 ) return number;
pragma restrict_references ( "<",
WNDS, RNDS, WNPS, RNPS );
end nulls;
/
create or replace package body
nulls
as
function "="( a in varchar2, b in
varchar2 ) return number
is
begin
if ( a = b OR ( a is NULL and b is NULL ) )
then
return 1;
else
return 0;
end if;
end "=";
function "<"( a in varchar2, b in
varchar2 ) return number
is
begin
if ( a is NULL and b is NOT NULL )
then
return 1;
elsif ( a < b )
then
return 1;
else
return 0;
end if;
end "<";
end nulls;
/
----------------------------------------------------------
select nulls."="( null,
null )
from dual
/
NULLS."="(NULL,NULL)
--------------------
1
select nulls."<"(
null, 1 )
from dual
/
NULLS."<"(NULL,1)
-----------------
1
select * from dual
where nulls."="( null,
null ) = 1
/
D
-
X
select * from dual
where nulls."<"( null,
dummy ) = 1
/
D
-
X
67)
Exception?
67) Exceptions are identifiers in
PL/SQL that are raised during the execution of a block to terminate its action.
A block is always terminated when PL/SQL raises an exception but you can define
your own error handler to capture exceptions and perform some final actions
before quitting the block. If PL/SQL handles the exception within the block
then the exception will not propagate out to an enclosing block or environment.
There are two classes of
exceptions, these are :-
Predefined - Oracle predefined
errors which are associated with specific error codes.
User-defined - Declared by the user
and raised when specifically requested within a block. You may associate a
user-defined exception with an error code if you wish.
If an error occurs within a block
PL/SQL passes control to the EXCEPTION section of the block. If no EXCEPTION
section exists within the block or the EXCEPTION section doesn't handle the
error that's occurred then the block is terminated with an unhandled exception.
Exceptions propagate up through nested blocks until an exception handler is
found that can handle the error. If no exception handler is found in any block
the error is passed out to the host environment. Exceptions occur when either
an Oracle error occurs (this automatically raises an exception) or you
explicitly raise an error using the RAISE statement.
The two most common errors
originating from a SELECT statement occur when it returns no rows or more than
one row (remember that this is not allowed). The example below deals with these
two conditions.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
BEGIN
SELECT ISBN, COST FROM JD11.BOOK INTO TEMP_ISBN, TEMP_COST WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST =
(TEMP_COST*1.175) WHERE ISBN > 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN > 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'NOT FOUND');
WHEN TOO_MANY_ROWS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'TOO MANY');
END;
The block above will generate an
error because there are more than one record with an ISBN greater than 21. The
exception raised from this will be passed to the EXCEPTION section where each
handled action will be checked. The statements within the TOO_MANY_ROWS action
will then be executed before the block is terminated.
If some other error occurred this
EXCEPTION section would not handle it because is isn't defined as a checkable
action. To cover all possible errors you can specify a catch all action named
OTHERS.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
BEGIN
SELECT ISBN, COST FROM JD11.BOOK INTO TEMP_ISBN, TEMP_COST WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST =
(TEMP_COST*1.175) WHERE ISBN > 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN > 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'NOT FOUND');
WHEN TOO_MANY_ROWS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'TOO MANY');
WHEN OTHERS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'SOME ERROR OCCURRED');
END;
This block will trap all errors. If
the exception isn't no rows returned or too many rows returned then the OTHERS
action will perform the error handling.
PL/SQL provides two special
functions for use within an EXCEPTION section, they are SQLCODE and SQLERRM.
SQLCODE is the Oracle error code of the exception, SQLERRM is the Oracle error
message of the exception. You can use these functions to detect what error has
occurred (very useful in an OTHERS action). SQLCODE and SQLERRM should be
assigned to variables before you attempt to use them.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN
SELECT ISBN, COST FROM JD11.BOOK INTO TEMP_ISBN, TEMP_COST WHERE ISBN
> 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST =
(TEMP_COST*1.175) WHERE ISBN > 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN > 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'NOT FOUND');
WHEN TOO_MANY_ROWS THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'TOO MANY');
WHEN OTHERS THEN
ERR_MSG := SUBSTR(SQLERRM,1,100);
ERR_CDE := SQLCODE;
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(ERR_CDE, ERR_MSG);
END;
If you explicitly need to raise an
error you can do this by using the RAISE statement. For example, if you wanted
to raise an error if a SELECT statement found a row (which it shouldn't have
found) you would only be able to do this by raising your own error.
DECLARE
TEMP_COST NUMBER(10,2);
TEMP_ISBN NUMBER(10);
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN
SELECT ISBN, COST FROM JD11.BOOK INTO TEMP_ISBN, TEMP_COST WHERE ISBN
< 0;
RAISE THIS_IS_WRONG
EXCEPTION
WHEN THIS_IS_WRONG THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, 'OOPS');
END;
The example above shouldn't find
any rows with an ISBN less than 0. If it did we raise our own exception to force
PL/SQL to pass control to our own action within the EXCEPTION section.
68)
How many triggers?
68) There are total 14 database
triggers
insert before
delete before
update before
insert after
delete after
update after
above all for each row
above all for each statement
these are 12
Istead of Trigger- INSTEAD-OF
trigger provide a transparent way of modifying views that
cannot be modified directly through DML statement
(INSERT, UPDATE, DELETE). These trigger are called Istead-of-triggers because,
unlike other types of triggers, oracle fires the trigger instead of executing
the triggering statement. The trigger perform update, insert or delete
operation directly on the underlying tables. Users write normal INSERT, UPDATE
and DELETE statement against the view nad the INSTEAD-OF trigger works
invisibly in the background to make the reght action take place.
Instead of trigger are only
applicable only for row level triggers.
8 System Level triggers - After
Startup, Before Shutdown, After Logging, before logoff,
ServerError, create, alter, delete
Total is 14 database triggers.
69) Copy a structure of a table
without records?
69) create table_name as select *
from source_table where 1=2;
70) If two structure are same and
one table in having data how to insert the data into empty
table?
70) insert into tablename select *
from tablename.
71) How to get salary whose salary
is greate then avg sal?
71) select salary from employee
where salary > (select avg(salary) from employee);
72) What will you get if you run
"select 1 from ja05_customer;'
72) It will return number or rows
with 1 like if there are to records it will return
1
1
No comments:
Post a Comment