3) How many types of SQL Statements are there in Oracle

3) There are basically 6 types of SQL statements. They are

a) Data Definition Language (DDL):

The DDL statements define and maintain objects and drop objects.

b) Data Manipulation Language (DML):

The DML statements manipulate database data.

c) Transaction Control Statements:

Manage change by DML

d) Data Query Language(DQL):

Select Statement

e) Session Control:

Used to control the properties of current session enabling and disabling roles and changing. e.g. Alter Statements, Set Role

f) System Control Statements:

Change Properties of Oracle Instance. e.g.:: Alter System

g) Embedded SQL:

Incorporate DDL, DML and T.C.S in Programming Language. e.g.:: Using the SQL Statements in languages such as 'C', Open, Fetch, executes and close

4) What is a Transaction in Oracle?

A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

5) Key Words Used in Oracle

The Key words that are used in Oracle are:

a) Committing: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.

b) Rollback: A transaction that retracts any of the changes resulting from SQL statements in Transaction.

c) Save Point: For long transactions that contain many SQL statements, intermediate markers or save points are declared. Save points can be used to divide a transaction into smaller points.

d) Rolling Forward: Process of applying redo log during recovery is called rolling forward.

e) Cursor: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.

f) System Global Area (SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.

g) Program Global Area (PGA):: The PGA is a memory buffer that contains data and control information for server process.

h) Database Buffer Cache: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.

i) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.

j) Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.

k) Process: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

6) What are Procedure, functions and Packages?

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.

Procedures do not return values while Functions return one Value

Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

7) What are Database Triggers and Stored Procedures

Database Triggers: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.

Database triggers 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 is 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.

Stored Procedures: Stored Procedures 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.

8) How many Integrity Rules are there and what are they

There are Three Integrity Rules. They are as follows:

a) Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null

b) Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.

c) Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

9) What are the Different Optimization Techniques?

The Various Optimization 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 every time the SQL must be parsed whenever 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 separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.

F45run module = my_firstform userid = scott/tiger optimize_Tp = No

10) How do u implement the If statement in the Select Statement

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.

11) How many types of Exceptions are there

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

12) What are the inline and the precompiler directives?

The inline and precompiler directives detect the values directly

13) What are snap shots and views?

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated

14) What are the OOPS concepts in Oracle.

Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorize the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects

15) What is the difference between candidate key, unique key and primary key?

Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

16) What is concurrency?

Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are

a) Exclusive: The exclusive lock is useful for locking the row when an insert, update or delete is being done. This lock should not be applied when we do only select from the row.

b) Share lock: We can do the table as Share Lock as many share locks can be put on the same resource.

17) Privileges and Grants

Privileges are the right to execute a particular type of SQL statements.

e.g. Right to Connect, Right to create, Right to resource

Grants are given to the objects so that the object might be accessed accordingly. The grant has to be given by the owner of the object.

18) Table Space, Data Files, Parameter File, Control Files

Table Space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.

a) System Table space: This data file stores all the tables related to the system and DBA tables

b) User Table space: This data file stores all the user related tables

We should have separate table spaces for storing the tables and indexes so that the access is fast.

Data Files: Every Oracle Data Base has one or more physical data files. They store the data for the database. Every data file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.

Parameter Files: Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.::

db_block_buffers = 500

db_name = ORA7

db_domain = u.s.acme lang

Control Files: Control files record the physical structure of the data files and redo log files. They contain the Db name, name and location of Database, data files, redo log files and time stamp.

19) Physical Storage of the Data

The finest level of granularity of the database is the data blocks.

Data Block: One Data Block correspond to specific number of physical database space

Extent: Extent is the number of specific number of contagious data blocks.

Segments: Set of Extents allocated for Extents. There are three types of Segments

a) Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment

b) Index Segment: Each Index has index segment that stores data

c) Roll Back Segment: Temporarily store 'undo' information

d) Temp. Segment: It Used in sort order.

20) What are the PCT Free and PCT Used?

PCT Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly PCT Used is used to denote the percentage of the used space that is to be used when creating a table

eg.:: Pctfree 20, Pctused 40

21) What is Row Chaining?

The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.

22) What is a 2 Phase Commit?

Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2-phase commit.

a) Prepare Phase: Global coordinator asks participants to prepare

b) Commit Phase: Commit all participants to coordinator to Prepared, Read only or abort Reply

23) What is the difference between deleting and truncating of tables?

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 scans 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.

In delete database trigger fires, if any.

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.

Truncate do not fires database trigger.

24) What are mutating tables?

When a table is in state of transition it is said to be mutating. eg :: 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.

25) What are Codd Rules?

Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only RDBMS to satisfy the maximum number of rules.

26) What is Normalisation?

Normalisation is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalisation rules.

a) 1 Normal Form: A table is said to be in 1st Normal Form when the attributes are atomic

b) 2 Normal Form: A table is said to be in 2nd Normal Form when all the candidate keys are dependent on the primary key and it is in 1^st normal form.

c) 3rd Normal Form: A table is said to be third Normal form when it is not dependent transitively or all the non-columns are mutually independent. And it is to be in 2nd normal form.

27) Can U disable database trigger? How?

Yes. With respect to table

ALTER TABLE TABLE_NAME

[DISABLE all_trigger]

28) What is a pseudo column? Name them?

A pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

* CURRVAL

* NEXTVAL

* LEVEL

* ROWID

* ROWNUM

* SYSDATE

* User

29) How many columns can table have?

The number of columns in a table can range from 1 to 1000.

30) Is space acquired in blocks or extents?

In extents.

31) What is clustered index?

In an indexed cluster, rows are stored together based on their cluster key values. Cannot apply for HASH.

32) What are the data types supported by oracle (INTERNAL)?

Varchar2, Number, Char, MLSLABEL.

39) What are attributes of cursor?

%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

40) Can you use select in FROM clause of SQL select?

Yes.

41) Types of Snapshot Refresh

To ensure that a snapshot is consistent with its master table, you need to refresh the

snapshot periodically. Oracle provides the following three methods to refresh snapshots:

a) Fast refresh uses snapshot logs to update only the rows that have changed since the last refresh.

b) Complete refresh updates the entire snapshot.

c) Force refresh performs a fast refresh when possible. When a fast refresh is not possible, force refresh performs a complete refresh.

When it is important for snapshots to be transactional consistent with each other, you can organize them into refresh groups. By refreshing the refresh group, you can ensure that the data in all of the snapshots in the refresh group correspond to the same transactional consistent point in time. A snapshot in a refresh group still can be refreshed individually, but doing so nullifies the benefits of the refresh group because refreshing the snapshot individually does not refresh the other snapshots in the refresh group.

Database:

Explain Database Structure.

When Oracle server starts Instance gets initiated. Instance is the creation of SGA in server RAM and initiation of the background process. SGA is divided into shared pool, database buffer; redo log buffer and Java pool. Shared pool consists of data dictionary, where the entire table structures, stored procedures, functions, packages, privileges are stored.

When query fires, it gets parsed in shared pool area where all the syntaxes are checked with proper positions. Verification of tables, columns and grants will be taken place in data dictionary. Search for the required data will start first in shared pool, otherwise data will be fetch from the data files.

2. What is the Back ground processes in Oracle and what are they.

2. This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.

The various background processes in oracle are

a) Data Base Writer (DBWR): Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.

b) Log Writer (LGWR): Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transactions commit and log buffer fills, LGWR writes log entries into a online redo log file.

c) System Monitor (SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure

d) Process Monitor (PMON): The Process Monitor performs process recovery when user Process fails. PMON Clears and Frees resources that process was using.

e) Check Point (CKPT): At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint

f) Achieves (ARCH): The Archiver copies online redo log files to archival storage when they are busy.

g) Recoveror (RECO): The Recoveror is used to resolve the distributed transaction in network

h) Dispatcher (Dnnn): The Dispatcher is useful in Multi Threaded Architecture

i) Lckn: We can have up to 10 lock processes for inter instance locking in parallel SQL.

How to find out duplicate rows?

                               Select *  from my_table t1

                               where  exists (     select 'x' from my_table t2

                                                             where t2.key_value1 = t1.key_value1

                                                             and t2.key_value2 = t1.key_value2

                                                             and t2.rowid      > t1.rowid

);

How to delete duplicate rows with rowid and without rowid?

                               delete from my_table t1

                               where  exists (     select 'x' from my_table t2

                                                             where t2.key_value1 = t1.key_value1

                                                             and t2.key_value2 = t1.key_value2

                                                             and t2.rowid      > t1.rowid

);

What are types of joins? Where the + (Plus) should be given in outer join. Explain with example. Emp and Dept table.

what is a schema?

It is an logical grouping of database objects based on the user that owns the object.

I want to calculate the percentage EUROSALES against PLANNEDEUROSALES on WEEK basis, only for customers where the PLANNEDEUROSALES IS NOT NULL

There is one table SDM_CPLAN_INVOICES_W with the fields
WEEK, CUSTOMER_NUMBER, EUROSALES_TY, PLANEUROSALES

SELECT X.WEEK,

count(Y.perc),

count(X.percentage),

(count(Y.perc)/count(X.percentage))*100 PERC_CUST_REALIZED
FROM (SELECT CPLAN.WEEK WEEK,

CPLAN.CUSTOMER_NUMBER,

CPLAN.EUROSALES_TY,

CPLAN.PLAN_EUROSALES, round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) percentage

FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV'

and cplan.PLAN_EUROSALES != 0
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES )X ,

(SELECT CPLAN.WEEK WEEK,

CPLAN.CUSTOMER_NUMBER,

CPLAN.EUROSALES_TY,

CPLAN.PLAN_EUROSALES, round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) perc

FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV'

and           cplan.PLAN_EUROSALES != 0
HAVING round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) >= 100
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES
)Y
WHERE X.WEEK = Y.WEEK
GROUP BY X.WEEK

I get the following results
WEEK     COUNT(Y.PERC)     COUNT(X.PERCENTAGE)     PERC_CUST_REALIZED
1                            32                                32                                100
2                            16                                16                                100
3                            80                                80                                100
4                            48                                48                                100
5                            48                                48                                100
etc.
this is wrong.

The Good results shoulb something like beneath

WEEK     COUNT(Y.PERC)     COUNT(X.PERCENTAGE)     PERC_CUST_REALIZED
1                      2       16       12.5
2                      1       16       6.25
3                      5       16       31.25
4                      3       16       18.75
5                      3       16       18.75

SELECT X.WEEK,
SUM(X.realised) num_realised,
count(*) num_percs,
SUM(X.realised)/count(*)*100 PERC_CUST_REALIZED
FROM
(SELECT CPLAN.WEEK WEEK, DECODE(SIGN(CPLAN.EUROSALES_TY-CPLAN.PLAN_EUROSALES),-1,0,1) realised
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV' and cplan.PLAN_EUROSALES != 0
)X
GROUP BY X.WEEK;

If you want to find the top 10 earners in your company then,

select * from (
select empno,sal,rownum
from emp
order by sal desc
)
where rownum < 11

Is it possible to run another query if the results of a first query are null?

For example, query one is:
select max(date) from table1 where status = 'B' and account = '123'

Query 2 is:
select max(date) from table 2 where curr_status in('a','b','c') and account = '123'

If the results for query 1 are null, I need the results of query 2, but I don't want to have to test the results of query 1 in my program and then run query 2 if I can get oracle to do the test for me.

Select nvl((select max(date) from table1 where status = 'B' and account = '123'), (select max(date) from table 2 where curr_status in('a','b','c') and account = '123'))
from dual;

Select decode(x, 0, date2, date1)
from ( select max(date) date1,

count(*) x

from table1

where status = 'B' and account = '123'

),
( select max(date) date2

from table 2

where curr_status in('a','b','c') and account = '123'

);

I also have tried this type of statement with thousands of resultant records...not only one.-
using the same example we have:

SELECT table3.field_a,
nvl(
(select max(date) from table1 A where A.field2 = 'B' and a.field1 = c.field1),
(select max(date) from table2 B where curr_status in('a','b','c') and b.field1 = c.field1)
)
FROM table3 C
WHERE
c.field1='XXX'
.
.
. ;

I have 2 tables...

STUDENT
student_id (pk) Std_name
sjones Sam Jones
jdoe Jane Doe

ENROLLMENT
Student_id (pk) Course#(pk) Grade
---------------------------------------------------------------------

sjones csc211   A
sjones    csc212 A-
jdoe ect555    B-
jdoe is404    B
jdoe    is421 B+

For each student name, list the course number or numbers the student took where the student obtained the lowest grade.

OK, this is what I have so far...
SELECT student.std_name, enrollment.[course#], enrollment.grade
FROM enrollment INNER JOIN student ON enrollment.student_id = student.student_id;

This lists all of the students and the course #'s and grades. Now, I only want to show the lowest grade and the course # for each student. How can I sort them so B+ is greater than B, but B- is less than B.

Answer : you can use the decode function...

select STUDENT_ID, decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, ... -1) from ENROLLMENT

it basically translates the GRADE into a number value ... like a bunch of if statements... where the first parameter is the value to check on... and the last is the default value if no other value is met...

Other way1

select STUDENT_ID, COURSE#, GRADE from ENROLLMENT E1 where
decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1) =
(select min(decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1))
from ENROLLMENT E2 where E1.STUDENT_ID=E2.STUDENT_ID);

STUDENT_ID
---------------
COURSE#
---------------
GRADE
---------------
sjones
csc212
A-

jdoe
etc555
B-

Other Way 2

select a.std_name, a.course#, a.grade from enrollment a, (SELECT student.std_name,min(decode(grade, 'A+', 13, 'A', 12, 'A-', 11, 'B+', 10, 'B', 9, 'B-', 8, 'C+', 7, 'C', 6, 'C-',5,'D+', 4, 'D', 3, 'D-', 2, 'F', 1)) min_grade
FROM enrollment INNER JOIN student ON enrollment.student_id= student.std_name
group by student.std_name) b
where a.std_name=b.std_name
AND a.grade=decode(b.min_grade,13, 'A+', 12, 'A', 11, 'A-', 10, 'B+', 9, 'B', 8, 'B-', 7, 'C+', 6, 'C', 5, 'C-',4,'D+', 3, 'D', 2, 'D-', 1, 'F');

I wish to display the value that occurs most frequently. So for record 11 2 appears twice, and 3 only once. Therefore I wish to display 2.

Select id, status
from
(
select id, status, rank() over (partition by id order by stat_count desc, status) as rnk
from
(
select id, status, count(*) as stat_count
from my_table
group by id, status
)
)
where rnk=1

How many rows of table A in table B:

select count(*) from (select * from <table a> INTERSECT select * from <table b>);

How many rows of tablea NOT in table B

select count(*) from (select * from <table a> MINUS select * from <table b>);

How will you find no of columns in a table

select count(column_name) from all_tab_columns where table_name = 'EVALUATION9CODE';

One question on self join query. How to find out the emp_no doing same job but working in different departments.

Select a.emp_no from emp a ,emp b where a.job = b.job and a.dept_no <> b.dept_no;

Find out the count of three tables in one query. The output should be in the same line.

Select count1, count2 from (select count(*)count1 from tab1), (select count(*)count2 from tab2);

What are cursors? Difference in implicit and explicit cursors. Cursors attributes?

Cursors (PL/SQL)

=======

Oracle uses work areas to execute SQL statements and store processing

information. A PL/SQL construct called a "cursor" lets you name a work

area and access its stored information. There are two kinds of cursors:

implicit and explicit.

PL/SQL implicitly declares a cursor for all SQL data manipulation

statements, including queries that return only one row. For queries

that return more than one row, you can explicitly declare a cursor

to process the rows individually.

Explicit Cursors

----------------

The set of rows returned by a multi-row query is called the "active

set." Its size is the number of rows that meet your search criteria.

An explicit cursor points to the current row in the active set. This

allows your program to process the rows one at a time.

Multi-row query processing is somewhat like file processing. For

example, a COBOL program opens a file to process records, then closes

the file. Likewise, a PL/SQL program opens a cursor to process rows

returned by a query, then closes the cursor. Just as a file pointer

marks the current position in an open file, a cursor marks the current

position in an active set.

You use three commands to control the cursor: OPEN, FETCH, and CLOSE.

First, you initialize the cursor with the OPEN statement, which

identifies the active set. Then, you use the FETCH statement to

retrieve the first row. You can execute FETCH repeatedly until all

rows have been retrieved. When the last row has been processed, you

release the cursor with the CLOSE statement.

Forward references are not allowed in PL/SQL. So, you must declare a

cursor before referencing it in other statements. You define a cursor

in the declarative part of a PL/SQL block, subprogram, or package by

naming it and specifying a query. In the following example, you declare

a cursor named "c1":

DECLARE

CURSOR c1 IS SELECT ename, deptno FROM emp WHERE sal > 2000;

...

The cursor name is an undeclared identifier, not the name of a PL/SQL

variable. You cannot assign values to a cursor name or use it in an

expression.

Explicit cursors can take parameters, as the example below shows.

A cursor parameter can appear in a query wherever a constant can appear.

CURSOR c1 (median IN NUMBER) IS

SELECT job, ename FROM emp WHERE sal > median;

To declare formal cursor parameters, you use the syntax

CURSOR cursor_name [(parameter [, parameter, ...])] IS SELECT ...

where "parameter" stands for the following syntax:

parameter_name [IN] datatype [{:= | DEFAULT} expr]

The formal parameters of a cursor must be IN parameters. Therefore, they

cannot return values to actual parameters.

As the example below shows, you can initialize cursor parameters to default

values. That way, you can pass different numbers of actual parameters to a

cursor, accepting or overriding the default values as you please. Moreover,

you can add new formal parameters without having to change every reference

to the cursor.

DECLARE

CURSOR c1

(low INTEGER DEFAULT 0,

high INTEGER DEFAULT 99) IS SELECT ...

The scope of cursor parameters is local to the cursor, meaning that they

can be referenced only within the query used in the cursor declaration.

The values of cursor parameters are used by the associated query when

the cursor is opened.

Implicit Cursors

----------------

Oracle implicitly opens a cursor to process each SQL statement not

associated with an explicitly declared cursor. PL/SQL lets you refer

to the most recent implicit cursor as the "SQL" cursor. So, although

you cannot use the OPEN, FETCH, and CLOSE statements to control an

implicit cursor, you can still use cursor attributes to get information

about the most recently executed SQL statement.

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 (for example, in a sub-block). So, if you want to

save an attribute value for later use, assign it to a Boolean variable

immediately. The following example shows how failing to save an

attribute value can result in a logic bug:

UPDATE parts SET qty = qty - 1 WHERE partno = part_id;

check_parts; -- procedure call

IF SQL%NOTFOUND THEN -- dangerous!

...

END IF;

In this example, it is dangerous to rely on the IF condition because the

procedure "check_parts" might have changed the value of %NOTFOUND. You

can debug the code as follows:

UPDATE parts SET qty = qty - 1 WHERE partno = part_id;

sql_notfound := SQL%NOTFOUND;

check_parts;

IF sql_notfound THEN

...

END IF;

Before Oracle opens the SQL cursor, the implicit cursor attributes

yields NULL.

Cursor Attributes

%FOUND Attribute (PL/SQL)

================

When appended to the name of a cursor or cursor variable, the %FOUND

attribute returns useful information about the execution of a multi-row

query. When appended to the name of the SQL implicit cursor (SQL),

%FOUND returns useful information about the most recently executed

INSERT, UPDATE, DELETE, or SELECT INTO statement.

Explicit Cursors

----------------

%FOUND is the logical opposite of %NOTFOUND. After a cursor or cursor

variable is opened but before the first fetch, %FOUND yields NULL.

Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if

the last fetch failed to return a row.

In the following example, you use %FOUND to select an action:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%FOUND THEN -- fetch succeeded

...

ELSE -- fetch failed, so exit loop

EXIT;

END IF;

...

END LOOP;

If a cursor or cursor variable is not open, referencing it with %FOUND

raises the predefined exception INVALID_CURSOR.

Implicit Cursors

----------------

%FOUND is the logical opposite of %NOTFOUND. Until a SQL data

manipulation statement is executed, %FOUND yields NULL. Thereafter,

%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected

one or more rows or a SELECT INTO statement returned one or more rows.

Otherwise, %FOUND yields FALSE. In the following example, you use %FOUND

to insert a row if a deletion succeeds:

DELETE FROM emp WHERE empno = my_empno;

IF SQL%FOUND THEN -- delete succeeded

INSERT INTO new_emp VALUES (my_empno, my_ename, ...);

...

END IF;

When appended to the name of a cursor or cursor variable, the %NOTFOUND

attribute returns useful information about the execution of a multi-row

query. When appended to the name of the SQL implicit cursor (SQL),

%NOTFOUND returns useful information about the most recently executed

INSERT, UPDATE, DELETE, or SELECT INTO statement.

Explicit Cursors

----------------

When a cursor or cursor variable is opened, the rows that satisfy the

associated query are identified and form the active set. Before the

first fetch, %NOTFOUND yields NULL. Rows are fetched from the active set

one at a time. If the last fetch returned a row, %NOTFOUND yields FALSE.

If the last fetch failed to return a row, %NOTFOUND yields TRUE.

In the following example, you use %NOTFOUND to exit a loop when the

FETCH statement fails to return a row:

LOOP

FETCH c1 INTO my_ename, my_deptno;

EXIT WHEN c1%NOTFOUND;

...

END LOOP;

If a cursor or cursor variable is not open, referencing it with

%NOTFOUND raises the predefined exception INVALID_CURSOR.

Implicit Cursors

----------------

%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected

no rows or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND

yields FALSE. In the following example, you use %NOTFOUND to insert a new

row if an update fails:

UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;

IF SQL%NOTFOUND THEN -- update failed

INSERT INTO errors VALUES (...);

END IF;

%ISOPEN Attribute (PL/SQL)

=================

When appended to the name of a cursor or cursor variable, the %ISOPEN

attribute returns the status of the cursor or cursor variable.

Explicit Cursors

----------------

%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise,

%ISOPEN yields FALSE. In the following example, you use %ISOPEN to select

an action:

IF c1%ISOPEN THEN -- cursor is open

...

ELSE -- cursor is closed, so open it

OPEN c1;

END IF;

Implicit Cursors

----------------

Oracle closes the SQL cursor automatically after executing its

associated SQL statement. As a result, %ISOPEN always yields FALSE.

%ROWCOUNT Attribute (PL/SQL)

===================

When appended to the name of a cursor or cursor variable, the %ROWCOUNT

attribute returns useful information about the execution of a multi-row

query. When appended to the name of the SQL implicit cursor (SQL),

%ROWCOUNT returns useful information about the most recently executed

INSERT, UPDATE, DELETE, or SELECT INTO statement.

Explicit Cursors

----------------

When you open its cursor or cursor variable, %ROWCOUNT is zeroed. Before

the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of

rows fetched so far. The number is incremented if the last fetch returned

a row. In the next example, you use %ROWCOUNT to take action if more than

ten rows have been fetched:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%ROWCOUNT > 10 THEN

...

END IF;

...

END LOOP;

If a cursor or cursor variable is not open, referencing it with

%ROWCOUNT raises the predefined exception INVALID_CURSOR.

Implicit Cursors

----------------

%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or

DELETE statement or returned by a SELECT INTO statement. %ROWCOUNT

yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows or

a SELECT INTO statement returned no rows. In the following example, you

use %ROWCOUNT to take action if more than ten rows have been deleted:

DELETE FROM emp WHERE ...

IF SQL%ROWCOUNT > 10 THEN

...

END IF;

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In any DML statement if we want to know how many records are processed then SQL%ROWCOUNT attribute can be used.

In implicit cursor SQL%ROWCOUNT will give the total count of records.

In explicit cursor CUR%ROWCOUNT will give the incremental record count.

In Implicit cursor %ISOPEN is always FALSE.

What are exceptions? Different types of exceptions? Pragma exception in detail.

Three types of Exceptions are there

a) System Defined Exception When

b) User Defined Exception

c) Pragma Exception

Predefined Exceptions (PL/SQL)

=====================

An internal exception is raised implicitly whenever a 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, the predefined exception NO_DATA_FOUND is raised if a

SELECT INTO statement returns no rows.

PL/SQL declares predefined exceptions globally in package STANDARD,

which defines the PL/SQL environment. So, you need not declare them

yourself. You can write handlers for predefined exceptions using the

names shown in the table below. Also shown are the corresponding

Oracle error codes.

Exception Error Raised if ...

----------------------------------------------------------------

CURSOR_ALREADY_OPEN ORA-06511 you try to OPEN an already open

cursor; you must CLOSE a cursor

before you can reOPEN it

DUP_VAL_ON_INDEX ORA-00001 you try to INSERT or UPDATE

duplicate values in a UNIQUE

database column

INVALID_CURSOR ORA-01001 you try an illegal cursor

operation such as closing an

unopened cursor

INVALID_NUMBER ORA-01722 the conversion of a character

string to a number fails in a SQL statement

LOGIN_DENIED ORA-01017 you log on to Oracle with an

invalid username/password

NO_DATA_FOUND ORA-01403 a SELECT INTO returns no rows, or you refer to

an uninitialized row in a PL/SQL table

NOT_LOGGED_ON ORA-01012 your PL/SQL program issues a

database call without being logged on to Oracle

PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem such as exiting

a function that has no RETURN statement

ROWTYPE_MISMATCH ORA-06504 the host cursor variable and PL/SQL cursor

variable involved in an assignment have incompatible return types

STORAGE_ERROR ORA-06500 PL/SQL runs out of memory or memory is

corrupted

TIMEOUT_ON_RESOURCE ORA-00051 a timeout occurs while Oracle is waiting for a

resource

TOO_MANY_ROWS ORA-01422 a SELECT INTO returns more than one row

VALUE_ERROR ORA-06502 the conversion of a character string to a number

fails in a procedural statement, or an arithmetic, conversion, truncation, or constraint error occurs

ZERO_DIVIDE ORA-01476 you try to divide a number by zero

Redeclaring Predefined Exceptions

---------------------------------

Remember, PL/SQL declares predefined exceptions globally in package

STANDARD, so you need not declare them yourself. Redeclaring predefined

exceptions is error-prone because your local declaration overrides the

global declaration. For example, if you declare an exception named

"invalid_number" and then PL/SQL raises the predefined exception

INVALID_NUMBER internally, a handler written for INVALID_NUMBER will

not catch the internal exception. In such cases, you must use dot

notation to specify the predefined exception, as follows:

...

EXCEPTION

WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN

-- handle the error

...

END;

EXCEPTION_INIT Pragma (PL/SQL)

=====================

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. They do not affect the meaning of a program; they simply

convey information to the compiler.

The predefined pragma EXCEPTION_INIT tells the PL/SQL 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 part, as shown in the following example:

DECLARE

insufficient_privileges EXCEPTION;

PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);

-----------------------------------------------------

-- Oracle returns error number -1031 if, for example,

-- you try to UPDATE a table for which you have

only SELECT privileges

BEGIN

...

EXCEPTION

WHEN insufficient_privileges THEN

-- handle the error

...

END;

Hierarchy of exception? (when others should be last)

Scope Rules

-----------

You cannot declare an exception twice in the same block. You can,

however, declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and

global to all its sub-blocks. Because a block can reference only local

or global exceptions, enclosing blocks cannot reference exceptions

declared in a sub-block.

If you redeclare a global exception in a sub-block, the local

declaration prevails. So, the sub-block cannot reference the global

exception unless it was declared in a labeled block, in which case the

following syntax is valid:

block_label.exception_name

The next example illustrates the scope rules:

DECLARE

past_due EXCEPTION;

acct_num NUMBER;

BEGIN

...

---------------- beginning of sub-block ----------------

DECLARE

past_due EXCEPTION; -- this declaration prevails

acct_num NUMBER;

BEGIN

...

IF ... THEN

RAISE past_due; -- this is not handled

END IF;

...

END;

------------------- end of sub-block -------------------

EXCEPTION

WHEN past_due THEN -- does not handle RAISEd exception

...

END;

The enclosing block does not handle the RAISEd exception because the

declaration of "past_due" in the sub-block prevails. Though they share

the same name, the two "past_due" exceptions are different, just as

the two "acct_num" variables share the same name but are different

variables. Therefore, the RAISE statement and the WHEN clause refer

to different exceptions. To have the enclosing block handle the RAISEd

exception, you must remove its declaration from the sub-block or define

an OTHERS handler.

In emp table the fields are empno,sex,deptno. Find out the department no wise total count of employee, count of male, count of female.

                               select dept,  sum(decode(sex,'M',1,0)) MALE,

                                                    sum(decode(sex,'F',1,0)) FEMALE,

                                                    count(decode(sex,'M',1,'F',1)) TOTAL

                               from   my_emp_table

                               group  by dept;

Find out the 3^rd max salary in emp table.

               a)            SELECT *

                               FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)

                               WHERE  ROWNUM < 10;

               b)             SELECT *      FROM my_table a

                               WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)

                               FROM my_table b

                               WHERE b.maxcol >= a.maxcol)

                               ORDER BY maxcol DESC;

Find out the record between 70 and 80.

   a)            SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE

                                                ROWNUM < 101 )

                   WHERE  RN between 91 and 100 ;

               b)            SELECT rownum, f1 FROM t1

                               GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Update the salary of emp table as: salary between 1000-1999 1.2%sal, 2000- 2999 1.5% otherwise don’t update. In a single update statement.

               a)            select f2,

                                              sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",

                                              sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",

                                              sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"

                               from   my_table

                               group  by f2;

                   b)               select ename "Name", sal "Salary",

                                      decode( trunc(f2/1000, 0), 0, 0.0,

                                                                            1, 0.1,

                                                                               2, 0.2,

                                                                               3, 0.31) "Tax rate"

                                      from   my_table;

What is Public synonym? What’s the use?

CREATE SYNONYM command

PURPOSE:

To create a synonym. A synonym is an alternative name for a table,

view, sequence, procedure, stored function, package, snapshot, or

another synonym.

SYNTAX:

CREATE [PUBLIC] SYNONYM [schema.]synonym

FOR [schema.]object[@dblink]

where:

PUBLIC

creates a public synonym. Public synonyms are accessible to all

users. If you omit this option, the synonym is private and is

accessible only within its schema.

schema

is the schema to contain the synonym. If you omit schema, Oracle

creates the synonym in your own schema.

synonym

is the name of the synonym to be created.

FOR

identifies the object for which the synonym is created. If you do

not qualify object with schema, Oracle assumes that the object is in

your own schema. The object can be of these types:

* table

* view

* sequence

* stored procedure, function, or package

* snapshot

* synonym

The object cannot be contained in a package. Note that the object

need not currently exist and you need not have privileges to access

the object.

You can use a complete or partial dblink to create a synonym for an

object on a remote database where the object is located. If you

specify dblink and omit schema, the synonym refers to an object in

the schema specified by the database link. Oracle Corporation

recommends that you specify the schema containing the object in the

remote database.

If you omit dblink, Oracle assumes the object is located on the

local database.

PREREQUISITES:

To create a private synonym in your own schema, you must have CREATE

SYNONYM system privilege.

To create a private synonym in another user's schema, you must have

CREATE ANY SYNONYM system privilege. If you are using Trusted

Oracle in DBMS MAC mode, your DBMS label must dominate the creation

label of the owner of schema to contain the synonym.

To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM

system privilege.

How to tune a query? Explain Plan and TKPROF.

Oracle's Explain Plan

Whenever you read or write data in Oracle, you do so by issuing an SQL Statement. One of Oracle's task when it receives such a statement is to build a statement execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL Statement EXPLAIN PLAN to determine this.

The general syntax of EXPLAIN PLAN is

EXPLAIN PLAN [SET
STATEMENT_ID=<id>; [INTO <table>] FOR <here goes the sql
statement>

. If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the INTO <table> part of the EXPLAIN PLAN command. If you omit the INTO <table> clause, Oracle fills a default table: PLAN_TABLE.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL Statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

The fields (attributes) within the plan table

Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.

Possible values for operation are: DELETE STATEMENT, INSERT STATEMENT, SELECT STATEMENT, UPDATE STATEMENT, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT, DOMAIN INDEX, FILTER, FIRST ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION, MERGE JOIN, MINUS, NESTED LOOPS, PARTITION, REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION and VIEW.

Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

dbms_xplan

As if 9i, dbms_xplan can be used to format the plan table.

Operations

TABLE ACCESS

The following table is used to demonstrate EXPLAIN PLAN:

CREATE TABLE test_for_ep (a number, b varchar2(100));

Now, let's explain the plan for selecting everything on that table:

delete plan_table;

explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Displaying the execution plan

In order to view the explained plan, we have to query the plan table:

select

  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",

  object_name                                                              "Object"

from

  plan_table

start with id = 0

connect by prior id=parent_id;

Here's the output of the explain plan:

SELECT STATEMENT ()

 TABLE ACCESS (FULL)           TEST_FOR_EP

First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed.

Btw, sql*plus automatically explains the plan for you if autotrace is enabled.

Now, let's create an index on that table:

create index test_for_ep_ix on test_for_ep (a);

And do the same select statement again:

delete plan_table;

explain plan for select /*+ rule */ * from test_for_ep where a = 5;

The plan is now:

SELECT STATEMENT ()

 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP

  INDEX (RANGE SCAN)           TEST_FOR_EP_IX

Obviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a FULL table scan but rather by using the data's rowid.

INDEX

In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));

delete plan_table;

explain plan for select /*+ rule */ * from test_for_ep where a = 5;

The explained plan is:

SELECT STATEMENT ()

 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP

  INDEX (UNIQUE SCAN)          UQ_TP

INDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?

explain plan for select /*+ rule */ * from test_for_ep where a > 5;

Here, we see that the index is used, but for a RANGE SCAN:

SELECT STATEMENT ()

 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP

  INDEX (RANGE SCAN)           UQ_TP

If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));

delete plan_table;

explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;

Here's the execution plan. No table access anymore!

SELECT STATEMENT ()

 INDEX (RANGE SCAN)            UQ_TP

MERGE JOIN

Usually, you want to select data across multiple table which is referred to as to join the tables. MERGE JOIN is just one possible method of joining the data (the others are HASH JOIN, NESTED LOOPS and CLUSTER JOIN). A Sort Merge Join basically sorts all relevant rows in the first table by the join key (ba), and also sorts the relevant rows in the second table by the join key (aa), and then merges these sorted rows. Take an example! At a garage sale you can buy 400 books. The deal is to take all or none. You take all. Now, you have to find the books that you already have at home. How would you go about it? Probably, you'd do a merge join: first, you sort your books by the primary key (author, title), then you sort the 400 books by their primary key (auther, title). Now, you start at the top of both piles. If the value of the left piles primary key is higher, then you take a book from the right pile and vice versa. When both values are equal, then you have found a dublicate. To demonstrate a MERGE JOIN, two tables need to be created:

create table test_for_ep_a (aa number, ab varchar2(100));

create table test_for_ep_b (ba number, bb varchar2(100));

Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:

explain plan for

  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where

  a.aa=b.ba and a.aa > 5;

As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.

SELECT STATEMENT ()

 MERGE JOIN ()

  SORT (JOIN)

   TABLE ACCESS (FULL)         TEST_FOR_EP_B

  SORT (JOIN)

   TABLE ACCESS (FULL)         TEST_FOR_EP_A

The behaviour of MERGE JOINS is influenced by the init.ora parameters sort_area_size and db_file_mutliblock_read_count.

Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS

NESTED LOOPS

For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).

create table test_for_ep_a (aa number, ab varchar2(100));

create table test_for_ep_b (ba number, bb varchar2(100));

explain plan for

  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where

  a.aa > b.ba and a.aa > 5;

Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)

SELECT STATEMENT ()

 NESTED LOOPS ()

  TABLE ACCESS (FULL)          TEST_FOR_EP_B

  TABLE ACCESS (FULL)          TEST_FOR_EP_A

Now, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:

create table test_for_ep_a (aa number, ab varchar2(100));

create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba));

delete plan_table;

explain plan for

  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where

  a.aa > b.ba;

The plan is:

SELECT STATEMENT ()

 NESTED LOOPS ()

  TABLE ACCESS (FULL)          TEST_FOR_EP_A

  INDEX (RANGE SCAN)           UQ_BA

Interpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn't make sense anymore. This is when the Cost Based Optimizer comes into play.

Sorts

Aggregate Sorts

Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:

create table t_ep (

  w date,

  v number,

  x varchar2(40)

);

delete plan_table;

explain plan for select /*+ rule */ max(w) from t_ep where v=4;

SELECT STATEMENT ()

 SORT (AGGREGATE)

  TABLE ACCESS (FULL)          T_EP

Now: creating an index:

alter table t_ep add constraint uq_t_ep unique(v);

delete plan_table;

explain plan for select /*+ rule */ max(w) from t_ep where v=4;

SELECT STATEMENT ()

 SORT (AGGREGATE)

  TABLE ACCESS (BY INDEX ROWID) T_EP

   INDEX (UNIQUE SCAN)          UQ_T_EP

EXPLAIN PLAN command

PURPOSE:

To determine the execution plan Oracle follows to execute a

specified SQL statement. This command inserts a row describing each

step of the execution plan into a specified table. If you are using

cost-based optimization, this command also determines the cost of

executing the statement.

SYNTAX:

EXPLAIN PLAN

[SET STATEMENT ID = 'text']

[INTO [schema.]table[@dblink]]

FOR statement

where:

SET

specifies the value of the STATEMENT_ID column for the rows of the

execution plan in the output table. If you omit this clause, the

STATEMENT_ID value defaults to null.

INTO

specifies the schema, name, and database containing the output

table. This table must exist before you use the EXPLAIN PLAN

command. If you omit schema, Oracle assumes the table is in your

own schema.

The dblink can be a complete or partial name of a database link to a

remote Oracle7 database where the output table is located. You can

only specify a remote output table if you are using Oracle with the

distributed option. If you omit dblink, Oracle assumes the table is

on your local database.

If you omit the INTO clause altogether, Oracle assumes an output

table named PLAN_TABLE in your own schema on your local database.

FOR

specifies a SELECT, INSERT, UPDATE, or DELETE statement for which

the execution plan is generated.

PREREQUISITES:

To issue an EXPLAIN PLAN statement, you must have the privileges

necessary to insert rows into an existing output table that you

specify to hold the execution plan. For information on these

privileges, see the INSERT command.

You must also have the privileges necessary to execute the SQL

statement for which you are find the execution plan. If the SQL

statement accesses a view, you must have privileges to access any

tables and views on which the view is based. If the view is based

on another view that is based on a table, you must have privileges

to access both the other view and its underlying table.

To examine the execution plan produced by an EXPLAIN PLAN statement,

you must have the privileges necessary to query the output table.

For more information on these privileges, see the SELECT command.

If you are using Trusted Oracle in DBMS MAC mode, your DBMS label

must dominate the output table's creation label or you must satisfy

one of these criteria:

* If the output table's creation label is higher than your DBMS

label, you must have READUP and WRITEUP system privileges.

* If the output table's creation label and your DBMS label are

noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system

privileges.

EXPLAIN PLAN Usage

When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.

Plan Table

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:

SQL> @%ORACLE_HOME%\rdbms\admin\utlxplan.sql

SQL> GRANT select, insert, update, delete ON plan_table TO public;

SQL> CREATE PUBLIC SYNONYM plan_table FOR .plan_table;

AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON

SQL> SELECT * FROM dual;

1 row selected.

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics

----------------------------------------------------------

          0  recursive calls

          4  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        363  bytes sent via SQL*Net to client

        429  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time.

EXPLAIN PLAN

The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained:

SQL> EXPLAIN PLAN FOR

  2  SELECT * FROM dual;

Explained.

SQL>

Then the execution plan displayed:

SQL> @%ORACLE_HOME%\rdbms\admin\utlxpls.sql

Plan Table

----------------------------------------------------------------------------------------------------

| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop  |

-----------------------------------------------------------------------------------------------------

| SELECT STATEMENT   |                    |         |           |           |                    |            |

|  TABLE ACCESS FULL  |DUAL      |           |           |           |                    |            |

-----------------------------------------------------------------------------------------------------

5 rows selected.

SQL>

For parallel queries use the utlxplp.sql script instead of utlxpls.sql.

Statement ID

If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.

SQL> EXPLAIN PLAN STATEMENT_ID = 'TIM' FOR

  2  SELECT * FROM dual;

Explained.

SQL>

By default the Oracle scripts to not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.

Oracle's TKPROF

Enabling SQL TRACE

To be able to use TKPROF, you must first enable sql trace. This can be done for either the instance or the session. If you want to change it for the entire instance, set sql_trace=true into the init.ora file and restart the instance. However, usually, you'll want to turn on sql trace for a particular session only. This can be done like so:

alter session set sql_trace=true;

or, from another session with a

sys.dbms_system.set_sql_trace_in_session(session's id,serial number)

Timed Statistics

You can have Oracle include timing information into the sql trace files as well. Either set the timed_statistics parameter to truo or or issue an

alter system set timed_statistics=true;

alter session set timed_statistics=true;

Finding the SQL Trace file

The trace file will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value with select value from v$parameter where name = 'user_dump_dest'.

See find the trace file to see where the tk prof file goes.

Maximum size of the trace file

The init parameter max_dump_file_size allows to set the maximum size of the trace file. Valid values for this parameter are: unlimited, a number followed by a K or a M or a number. If the value is a number only, it indicates how many OS Blocks the file can grow to. K or M set the file's maximum size in kilo or mega bytes.

Using tkprof

Follow the following links:

· The meaning of count, cpu and elapsed

The meaning of count, cpu and elapsed in tkprof

Be sure to also take a look at Basics of tkprof.

In order to demonstrate the meaning of count, cpu and elapsed in tkprof, three different and simple SQL statements are executed. One of these SQL Statements is executed three times, one is executed twice and one is executed once. The only thing these statements actually do is call dbms_lock.sleep.

set feedback off

select spid from v,v where audsid= sys_context('userenv','sessionid') and addr=paddr;

alter session set sql_trace=true;

begin /* three times */

  dbms_lock.sleep(1);

end;

begin /* once */

  dbms_lock.sleep(1);

end;

begin /* three times */

  dbms_lock.sleep(1);

end;

begin /* twice */

  dbms_lock.sleep(2);

end;

begin /* twice */

  dbms_lock.sleep(2);

end;

begin /* three times */

  dbms_lock.sleep(1);

end;

alter session set sql_trace=false;

If these statements are run and the trace file is tkprof'ed, it produces the following output:

begin /* three times */

  dbms_lock.sleep(1);

end;

call       count      cpu   elapsed    disk    query  current      rows

-------     ------    -------- ---------- ---------- ---------- ----------  ----------

Parse        3        0.01       0.01          0          0          0           0

Execute    3        0.00       3.10          0          0          0           3

Fetch        0        0.00       0.00          0          0          0           0

-------    ------    -------- ---------- ---------- ---------- ----------  ----------

total          6        0.01       3.11          0          0          0           3

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 206

--------------------

begin /* twice */

  dbms_lock.sleep(2);

end;

call      count     cpu   elapsed      disk    query  current      rows

-------     ------  --------   ---------- ---------- ---------- ----------  ----------

Parse        2      0.00       0.01          0          0          0           0

Execute    2      0.00       4.09          0          0          0           2

Fetch        0      0.00       0.00          0          0          0           0

-------    ------   --------   ---------- ---------- ---------- ----------  ----------

total          4      0.00       4.10          0          0          0           2

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 206

--------------------

begin /* once */

  dbms_lock.sleep(1);

end;

call      count     cpu   elapsed     disk     query  current      rows

-------     ------  --------   ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.01          0          0          0           0

Execute    1      0.00       1.03          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0

-------    ------  -------- ---------- ---------- ---------- ----------  ----------

total          2      0.01       1.04          0          0          0           1

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 206

· The meaning of query and current

Query and current in tkprof

Be sure to also take a look at Basics of tkprof.

Setting up a demonstration environment

A table is created to demonstrate the use of TKPROF. This PL/SQL Script will take care of this.

Ok, now let's give it a try:

alter session set sql_trace=true;

alter session set timed_statistics=true;

select id, ob from test_for_tk_1 where nm='elit magna wisi nulla praesent possim';

When the statement returns, we go to the directory pointed to by user_dump_dest and do a:

tkprof ora01676.trc perf

Then, we open the file perf.prf which was created by tkprof and search for the statement:

select id, ob

from

 test_for_tk_1 where nm='elit magna wisi nulla praesent possim'

call      count      cpu   elapsed      disk       query     current      rows

-------    ------    --------  ----------   ----------    ----------   ----------  ----------

Parse        1      0.00       0.00          0              0             1           0

Execute    1      0.00       0.00          0              0              0           0

Fetch        2      2.49      30.56      37355      75405         4           1

-------    ------    --------  ----------    ----------    ----------  ----------  ----------

total          4      2.49      30.56      37355      75405          5           1

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 18

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL TEST_FOR_TK_1

How is this table to be interpreted? First, the statement was parsed and executed once.

query

Query: also known as consistent gets

The TKPROF output includes in his header the following explanation:
query = number of buffers gotten for consistent read

query means: blocks gotten in consistent mode. That could possibly entail reading the rollback segment to reconstruct the data.

Most often, query gets

current

current: also known as db block gets.

The TKPROF output includes in his header the following explanation:
current = number of buffers gotten in current mode (usually for update)

current mode means: the blocks are read as they are (either in the datafile or still in the buffer cache). Current gets might entail waiting for some data.

A column varchar having numbers and characters mixed. How will get only numbers from it. For e.g. 123AB4CD to 1234.

Select replace(translate(‘123AB4CD’,’ABCD’,’A’),’A’) from dual;

How will get the reverse of Ename column in employee. Suppose you have ename as vidhya output should be ayhdiv. Without using reverse funtion.

Can a package have only specification without body?

Yes. But you can’t have Body without Specification.

Can a package body be compiled separately?

Yes.

What is the difference between EXIST and IN?

Exist gives the value as True and False depends upon the query gives records or not.

You can give multiple value in the predicate ‘IN’ which will be true for the every value given as in the quotes.

Diiference between rownum and rowid? Why only < is allowed in rownum?

Rowid stores the physical address of the records as a hexadecimal number.

Rownum is psuedo column getting the number while query is executing.

Find out employee number and names from emp having salary greater than Jones salary.

Use of subquery.

How to convert an amount in number into amount in words?

select to_char(to_date('123','J'),'JSP') from dual;

What are purity levels in functions?

1.15 Calling PL/SQL Functions in SQL

Stored functions can be called from SQL statements in a manner similar to built-in functions like DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way. Unfortunately, there are a number of caveats and restrictions.

The most notable caveat is that stored functions executed from SQL are not guaranteed to follow the read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function will look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.

1.15.1 Syntax for Calling Stored Functions in SQL

The syntax for calling a stored function from SQL is the same as referencing it from PL/SQL:

   [schema_name.][pkg_name.]func_name[@db_link]

                   [parm_list]

schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is mandatory and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.

The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in the Section 1.14.1, "Overview of Package Structure " section:

-- Capture system events.

   INSERT INTO v_sys_event (timestamp ,event ,qty_waits)

   SELECT time_pkg.GetTimestamp ,event ,total_waits

   FROM v$system_event

-- Capture system statistics.

INSERT INTO v_sys_stat (timestamp,stat#,value)

   SELECT time_pkg.GetTimestamp ,statistic# ,value

   FROM v$sysstat;

1.15.2 Requirements and Restrictions on Stored Functions in SQL

There are a number of requirements for calling stored functions in SQL:

· The function must be a single-row function -- not one that operates on a column or group function.

· All parameters must be IN; no IN OUT or OUT parameters are allowed.

· The datatypes of the function's parameters and RETURN must be compatible with RDBMS datatypes. You cannot have arguments or RETURN types like BOOLEAN, programmer-defined record, index-by table, etc.

· The parameters passed to the function must use positional notation; named notation is not supported.

· Functions defined in packages must have a RESTRICT_REFERENCES pragma in the specification (Oracle8.0 and earlier).

· The function must be stored in the database, not a local program, Developer/2000 PL/SQL library, or Form.

1.15.3 Calling Packaged Functions in SQL

Prior to Oracle8i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.

The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:

PRAGMA RESTRICT_REFERENCES (program_name |

   DEFAULT, purity_level);

The keyword DEFAULT applies to all methods of an object type or all programs in a package. There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects. Side effects are listed in the following table with the purity levels they address.

Purity Level	Description	Restriction
WNDS	Write No Database State	Executes no INSERT, UPDATE, or DELETE statements.
RNDS	Read No Database State	Executes no SELECT statements.
WNPS	Write No Package State	Does not modify any package variables.
RNPS	Read No Package State	Does not read any package variables.
TRUST (Oracle8i)		Does not enforce the restrictions declared but allows the compiler to trust they are true.

The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:

· To be called from SQL, all stored functions must assert WNDS.

· All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.

· To be executed remotely, the function must assert WNPS and RNPS.

· To be executed in parallel, the function must assert all four purity levels or, in Oracle8i, use PARALLEL_ENABLED in the declaration.

· These functions must not call any other program that does not also assert the minimum purity level.

· If a package has an initialization section, it too must assert purity in Oracle7.

· If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.

Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited.

1.15.3.1 Column/function name precedence

If your function has the same name as a table column in your SELECT statement and the function has no parameter, then the column takes precedence over the function. To force the RDBMS to resolve the name to your function, prepend the schema name to it:

CREATE TABLE emp(new_sal NUMBER ...);

CREATE FUNCTION new_sal RETURN NUMBER IS ...;

SELECT new_sal FROM emp;      -- Resolves to column.

SELECT scott.new_sal FROM emp;-- Resolves to

                                 function.

What are the minimum arguments required in Decode?

Three.

A column has a constraint of value not exceeding 100. If try to insert value greater than 100 when will the constraint fire at insert statement or at commit.

By default the constraints are checked immediately after the insert (before commit) but you can make it to check after all the inserts and at the time of commit.

A column varchar having combination of number like 123ABC, 234XYZ. Want to have the max number.

Use of substring and to_number

Explain Materialized view in detail

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL>     CREATE MATERIALIZED VIEW mv_emp_pk

               REFRESH FAST START WITH SYSDATE

               NEXT  SYSDATE + 1/48

               WITH PRIMARY KEY

               AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid-materialized view on table emp located on a remote database:

SQL>     CREATE MATERIALIZED VIEW mv_emp_rowid

               REFRESH WITH ROWID

               AS SELECT * FROM emp@remote_db;

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery-materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW  mv_empdept

AS SELECT * FROM emp@remote_db e

WHERE EXISTS

     (SELECT * FROM dept@remote_db d

     WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE

[refresh [fast|complete|force]

               [on demand | commit]

               [start with date] [next date]

               [with {primary key|rowid}]]

The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed

Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations

Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

SQL>     CREATE MATERIALIZED VIEW mv_emp_pk

               REFRESH FAST

               START WITH SYSDATE

               NEXT  SYSDATE + 2

               WITH PRIMARY KEY

               AS SELECT * FROM emp@remote_db;

Materialized view created.

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes. Materialized Views can be refresh on commit or on request on base table using package for that M. View. (DBMS_MVIEW)

32. How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

       a) SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

Use two quotes for every one displayed. Example:

       b)  SELECT 'Franks''s Oracle site' FROM DUAL;

       c)  SELECT 'A ''quoted'' word.' FROM DUAL;

       d)  SELECT 'A ''''double quoted'''' word.' FROM DUAL;

33. How does one get the time difference between two date columns?

Look at this example query:

               select floor(((date1-date2)*24*60*60)/3600)

                              || ' HOURS ' ||

                                              floor((((date1-date2)*24*60*60) -

                                              floor(((date1-date2)*24*60*60)/3600)*3600)/60)

                                              || ' MINUTES ' ||

                                              round((((date1-date2)*24*60*60) -

                                              floor(((date1-date2)*24*60*60)/3600)*3600 -

                                              (floor((((date1-date2)*24*60*60) -

                                              floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))

                                              || ' SECS ' time_difference

                               from   ...

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

        select to_char(to_date('00:00:00','HH24:MI:SS') +

                    (date1 - date2), 'HH24:MI:SS') time_difference

        from ...

Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.

34. How does one add a day/hour/minute/second to a date value?

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

        SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

        SYSDATE                    SYSDATE+1/24            SYSDATE+1/1440        SYSDATE+1/86400

        --------------------             --------------------              --------------------              --------------------

        03-Jul-2002 08:32:12   03-Jul-2002 09:32:12    03-Jul-2002 08:33:12    03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

       select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

        NOW                           NOW_PLUS_30_SECS

        --------------------             --------------------

        03-JUL-2002 16:47:23  03-JUL-2002 16:47:53

35. Can one retrieve only the Nth row from a table?

Rupak Mohan provided this solution to select the Nth row from a table:

               SELECT * FROM t1 a

               WHERE  n = (SELECT COUNT(rowid)

                               FROM t1 b

                               WHERE a.rowid >= b.rowid);

Shaik Khaleel provided this solution:

                SELECT * FROM (

 SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )

                WHERE  RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

Ravi Pachalla provided these solutions:

               SELECT f1 FROM t1

               WHERE  rowid = (

                               SELECT rowid FROM t1

                               WHERE  rownum <= 10

                               MINUS

                               SELECT rowid FROM t1

                               WHERE  rownum < 10);

               SELECT rownum,empno FROM scott.emp a

               GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...

        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN

           (SELECT rowid FROM emp WHERE rownum < 10);

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

36. How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

        SELECT *

        FROM   emp

        WHERE  (ROWID,0) IN ( SELECT ROWID, MOD(ROWNUM,4)

                                              FROM   emp);

Method 2: Use dynamic views (available from Oracle7.2):

        SELECT *

        FROM   ( SELECT rownum rn, empno, ename FROM emp ) temp

        WHERE  MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla

               SELECT rownum, f1

               FROM t1

               GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n

Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

37. How does one select the TOP N rows from a table?

Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:

                       SELECT *  FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)

                                                      WHERE  ROWNUM < 10;

Use this workaround with prior releases:

                       SELECT *  FROM my_table a

                        WHERE 10 >= (    SELECT COUNT(DISTINCT maxcol)   FROM my_table b

                                                      WHERE b.maxcol >= a.maxcol)

                        ORDER BY maxcol DESC;

38. How does one code a tree-structured query?

Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:

               select  LEVEL, EMPNO, ENAME, MGR

               from  EMP

               connect by prior EMPNO = MGR

               start with MGR is NULL;

One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:

               select lpad(' ', LEVEL * 2) || ENAME ........

One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.

One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

39. How does one code a matrix report in SQL?

Look at this example query with sample output:

                       SELECT  * FROM  (SELECT job,

                                                                                  sum(decode(deptno,10,sal)) DEPT10,

                                                                    sum(decode(deptno,20,sal)) DEPT20,

                                                                  sum(decode(deptno,30,sal)) DEPT30,

                                                                  sum(decode(deptno,40,sal)) DEPT40

                        FROM scott.emp

                        GROUP BY job)

                         ORDER BY 1;

        JOB                DEPT10 DEPT20 DEPT30 DEPT40

        ---------           ----------   ----------   ----------   ----------

        ANALYST                                  6000

        CLERK                        1300                       1900                       950

        MANAGER       2450                 2975                       2850

        PRESIDENT     5000

        SALESMAN                               5600

40. How does one implement IF-THEN-ELSE in a select statement?

The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.

Some examples:

        select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')  from   employees;

        select a, b, decode( abs(a-b), a-b, 'a > b',

                                                      0,   'a = b',

                                                                     'a < b') from  tableX;

        select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...

        select decode( GREATEST(A,B),

                                 A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),

                                                'A NOT GREATER THAN B')...

Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.

From Oracle 8i one can also use CASE statements in SQL. Look at this example:

        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END

        FROM   emp;

41. How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)

        FROM tab1

        WHERE cond1 = val1;

        DUMP(COL1)

        -------------------------------------

        Typ=96 Len=4: 65,66,67,32

For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

42. Workaround for snapshots on tables with LONG columns

You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:

               COPY TO SCOTT/TIGER@REMOTE     -

               CREATE IMAGE_TABLE USING       -

                      SELECT IMAGE_NO, IMAGE  -

                      FROM   IMAGES;

Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.

43. Describe Create Table syntax

CREATE TABLE command

PURPOSE:

To create a table, the basic structure to hold user data, specifying this information:

* column definitions

* integrity constraints

* the table's tablespace

* storage characteristics

* an optional cluster

* data from an arbitrary query

SYNTAX:

CREATE TABLE [schema.]table

( { column datatype [DEFAULT expr] [column_constraint] ...

| table_constraint}

[, { column datatype [DEFAULT expr] [column_constraint] ...

| table_constraint} ]...)

[ [PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[ RECOVERABLE | UNRECOVERABLE ]

[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]

[ INSTANCES { integer | DEFAULT } ]

)

| NOPARALLEL ]

[ CACHE | NOCACHE ]

| [CLUSTER cluster (column [, column]...)] ]

[ ENABLE enable_clause

| DISABLE disable_clause ] ...

[AS subquery]

where:

schema

is the schema to contain the table. If you omit schema, Oracle

creates the table in your own schema.

table

is the name of the table to be created.

column

specifies the name of a column of the table. The number of columns

in a table can range from 1 to 254.

datatype

is the datatype of a column.

DEFAULT

specifies a value to be assigned to the column if a subsequent

INSERT statement omits a value for the column. The datatype of the

expression must match the datatype of the column. A DEFAULT

expression cannot contain references to other columns, the

pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants

that are not fully specified.

column_constraint

defines an integrity constraint as part of the column definition.

table_constraint

defines an integrity constraint as part of the table definition.

PCTFREE

specifies the percentage of space in each of the table's data blocks

reserved for future updates to the table's rows. The value of

PCTFREE must be a positive integer from 1 to 99. A value of 0

allows the entire block to be filled by inserts of new rows. The

default value is 10. This value reserves 10% of each block for

updates to existing rows and allows inserts of new rows to fill a

maximum of 90% of each block.

PCTFREE has the same function in the commands that create and alter

clusters, indexes, snapshots, and snapshot logs. The combination of

PCTFREE and PCTUSED determines whether inserted rows will go into

existing data blocks or into new blocks.

PCTUSED

specifies the minimum percentage of used space that Oracle maintains

for each data block of the table. A block becomes a candidate for

row insertion when its used space falls below PCTUSED. PCTUSED is

specified as a positive integer from 1 to 99 and defaults to 40.

PCTUSED has the same function in the commands that create and alter

clusters, snapshots, and snapshot logs.

The sum of PCTFREE and PCTUSED must be less than 100. You can use

PCTFREE and PCTUSED together use space within a table more

efficiently.

INITRANS

specifies the initial number of transaction entries allocated within

each data block allocated to the table. This value can range from 1

to 255 and defaults to 1. In general, you should not change the

INITRANS value from its default.

Each transaction that updates a block requires a transaction entry

in the block. The size of a transaction entry depends on your

operating system.

This parameter ensures that a minimum number of concurrent

transactions can update the block and helps avoid the overhead of

dynamically allocating a transaction entry.

The INITRANS parameter serves the same purpose in clusters, indexes,

snapshots, and snapshot logs as in tables. The minimum and default

INITRANS value for a cluster or index is 2, rather than 1.

MAXTRANS

specifies the maximum number of concurrent transactions that can

update a data block allocated to the table. This limit does not

apply to queries. This value can range from 1 to 255 and the

default is a function of the data block size. You should not change

the MAXTRANS value from its default.

If the number concurrent transactions updating a block exceeds the

INITRANS value, Oracle dynamically allocates transaction entries in

the block until either the MAXTRANS value is exceeded or the block

has no more free space.

The MAXTRANS parameter serves the same purpose in clusters,

snapshots, and snapshot logs as in tables.

TABLESPACE

specifies the tablespace in which Oracle creates the table. If you

omit this option, then Oracle creates the table in the default

tablespace of the owner of the schema containing the table.

STORAGE

specifies the storage characteristics for the table. This clause

has performance ramifications for large tables. Storage should be

allocated to minimize dynamic allocation of additional space.

RECOVERABLE

specifies that the index will be re-created as part of a database

recovery. This is the default in ARCHIVELOG mode. You cannot specify

RECOVERABLE when using NOARCHIVELOG mode.

UNRECOVERABLE

specifies that the index will not be re-created during recovery. For

more information on recoverable and unrecoverable objects, see

Oracle7 Server Administrator's Guide.

PARALLEL

DEGREE specifies the number of query server processes that can scan

the table in parallel. Either specify a positive integer or DEFAULT

which signifies to use the initialization parameter

INSTANCES specifies the minimum number of instances that need to be

of a Parallel Server. A positive integer specifies the number of

caches.

NOPARALLEL

specifies that queries on this table are not performed in parallel

by default. A hint in the query still causes the query to be

performed in parallel.

CACHE

specifies that blocks of this table are placed on the most recently

is performed.

This option is useful for small lookup tables.

NOCACHE

specifies that blocks of the table in the buffer cache follow the

standard LRU algorithm when a full table scan is performed.

CLUSTER

specifies that the table is to be part of the cluster. The columns

listed in this clause are the table columns that correspond to the

cluster's columns. Generally, the cluster columns of a table are

the column or columns that comprise its primary key or a portion of

its primary key.

Specify one column from the table for each column in the cluster

key. The columns are matched by position, not by name. Since a

clustered table uses the cluster's space allocation, do not use the

PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE

option, or the STORAGE clause in conjunction with the CLUSTER

option.

ENABLE

enables an integrity constraint.

DISABLE

disables an integrity constraint.

Constraints specified in the ENABLE and DISABLE clauses of a CREATE

TABLE statement must be defined in the statement. You can also

enable and disable constraints with the ENABLE and DISABLE keywords

of the CONSTRAINT clause. If you define a constraint but do not

explicitly enable or disable it, Oracle enables it by default.

You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE

statement to enable and disable triggers.

AS subquery

inserts the rows returned by the subquery into the table upon its

creation.

If you include this clause, the column definitions can only specify

column names, default values, and integrity constraints, not

datatypes. Oracle derives column datatypes and lengths from the

subquery. Oracle also automatically defines NOT NULL constraints on

columns in the new table if they existed on the corresponding

columns of the selected table and the subquery does not modify the

column value with a SQL function or operator. A CREATE TABLE

statement cannot contain both the AS clause and a referential

integrity constraint definition.

The number of columns must equal the number of expressions in the

subquery. If all expressions in the subquery are columns, you can

omit the columns from the table definition entirely. In this case,

the names of the columns of table are the same as the columns in the

subquery.

PREREQUISITES:

To create a table in your own schema, you must have CREATE TABLE

system privilege. To create a table in another user's schema, you

must have CREATE ANY TABLE system privilege. Also, the owner of the

schema to contain the table must have either space quota on the

tablespace to contain the table or UNLIMITED TABLESPACE system

privilege.

SEE:

ALTER TABLE, CONSTRAINT, CREATE CLUSTER, CREATE INDEX, CREATE

TABLESPACE, DISABLE, DROP TABLE, ENABLE, STORAGE

CREATE TABLESPACE command

PURPOSE:

To create a tablespace. A tablespace is an allocation of space in

the database that can contain objects.

SYNTAX:

CREATE TABLESPACE tablespace

    DATAFILE filespec

        [ AUTOEXTEND { OFF

                     | ON [NEXT integer [K|M]]

                          [MAXSIZE [UNLIMITED | integer [K|M]] } ]

        [, filespec ...]

    [DEFAULT STORAGE storage_clause]

    [ONLINE | OFFLINE]

where:

tablespace

    is the name of the tablespace to be created.

DATAFILE

    specifies the data file or files to comprise the tablespace.

    AUTOEXTEND

       enables or disables the autoextending of the size of the datafile in

       the tablespace.

       OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set

           to zero. Values for NEXT and MAXSIZE must be respecified in

           further ALTER TABLESPACE AUTOEXTEND commands.

        ON enable autoextend.

      NEXT the size in bytes of the next increment of disk space to be

           automatically allocated to the datafile when more extents are

           required. You can also use K or M to specify this size in

           in kilobytes or megabytes. The default is one data block.

    MAXSIZE maximum disk space allowed for automatic extension of the

           datafile.

    UNLIMITED set no limit on allocating disk space to the datafile.

DEFAULT STORAGE

    specifies the default storage parameters for all objects created in

    the tablespace.

ONLINE

    makes the tablespace available immediately after creation to users

    who have been granted access to the tablespace.

OFFLINE

    makes the tablespace unavailable after immediately after creation.

    If you omit both the ONLINE and OFFLINE options, Oracle creates the

    tablespace online by default.  The data dictionary view

    DBA_TABLESPACES indicates whether each tablespace is online or

    offline.

PREREQUISITES:

    You must have CREATE TABLESPACE system privilege.  Also, the SYSTEM

    tablespace must contain at least two rollback segments including the

    SYSTEM rollback segment.

44.  Explain Snapshots

    CREATE SNAPSHOT command

PURPOSE:

    To create a snapshot.  A snapshot is a table that contains the

    results of a query of one or more tables or views, often located on

    a remote database.

SYNTAX:

CREATE SNAPSHOT [schema.]snapshot

    [ [PCTFREE  integer] [PCTUSED  integer]

      [INITRANS integer] [MAXTRANS integer]

      [TABLESPACE tablespace]

      [STORAGE storage_clause]

    [ USING INDEX [  PCTFREE integer | TABLESPACE tablespace

                    | INITTRANS integer | MAXTRANS integer

                    | STORAGE storage_clause ] ...

    | [CLUSTER cluster (column [, column]...)] ]

    [ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]

    AS subquery

where:

schema

    is the schema to contain the snapshot.  If you omit schema, Oracle

    creates the snapshot in your schema.

snapshot

    is the name of the snapshot to be created.

    Oracle chooses names for the table, views, and index used to

    maintain the snapshot by prefixing the snapshot name.  To limit

    these names to 30 bytes and allow them to contain the entire

    snapshot name, Oracle Corporation recommends that you limit your

    snapshot names to 23 bytes.

PCTFREE

PCTUSED

INITRANS

MAXTRANS

    establishes values for these parameters for the internal table

    Oracle uses to maintain the snapshot's data.

TABLESPACE

    specifies the tablespace in which the snapshot is to be created.  If

    you omit this option, Oracle creates the snapshot in the default

    tablespace of the owner of the snapshot's schema.

STORAGE

    establishes storage characteristics for the table Oracle uses to

    maintain the snapshot's data.

USING INDEX

    specifies the storage characteristics for the index on a simple

    snapshot.  If the USING INDEX clause not specified, the index is

    create with the same tablespace and storage parameters as the

    snapshot.

CLUSTER

    creates the snapshot as part of the specified cluster.  Since a

    clustered snapshot uses the cluster's space allocation, do not use

    the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the

    TABLESPACE option, or the STORAGE clause in conjunction with the

    CLUSTER option.

REFRESH

    specifies how and when Oracle automatically refreshes the snapshot:

            FAST

                   specifies a fast refresh, or a refresh using only the

                   updated data stored in the snapshot log associated

                   with the master table.

            COMPLETE

                   specifies a complete refresh, or a refresh that re-

                   executes the snapshot's query.

            FORCE

                   specifies a fast refresh if one is possible or

                   complete refresh if a fast refresh is not possible.

                   Oracle decides whether a fast refresh is possible at

                   refresh time.

                   If you omit the FAST, COMPLETE, and FORCE options,

                   Oracle uses FORCE by default.

            START WITH

                   specifies a date expression for the first automatic

                   refresh time.

            NEXT

                   specifies a date expression for calculating the

                   interval between automatic refreshes.

    Both the START WITH and NEXT values must evaluate to a time in the

    future.  If you omit the START WITH value, Oracle determines the

    first automatic refresh time by evaluating the NEXT expression when

    you create the snapshot.  If you specify a START WITH value but omit

    the NEXT value, Oracle refreshes the snapshot only once.  If you

    omit both the START WITH and NEXT values or if you omit the REFRESH

    clause entirely, Oracle does not automatically refresh the snapshot.

AS subquery

    specifies the snapshot query.  When you create the snapshot, Oracle

    executes this query and places the results in the snapshot.  The

    select list can contain up to 253 expressions.  A snapshot query is

    subject to the same restrictions as a view query.

PREREQUISITES:

    To create a snapshot in your own schema, you must have CREATE

    SNAPSHOT system privilege.  To create a snapshot in another user's

    schema, you must have CREATE ANY SNAPSHOT system privilege.

    Before a snapshot can be created, the user SYS must run the SQL

    script DBMSSNAP.SQL on both the database to contain the snapshot and

    the database(s) containing the tables and views of the snapshot's

    query.  This script creates the package SNAPSHOT which contains both

    public and private stored procedures used for refreshing the

    snapshot and purging the snapshot log.  The exact name and location

    of this script may vary depending on your operating system.

    When you create a snapshot, Oracle creates a table, two views, and

    an index in the schema of the snapshot.  Oracle uses these objects

    to maintain the snapshot's data.  You must have the privileges

    necessary to create these objects.  For information on these

    privileges, see the CREATE TABLE, CREATE VIEW, and CREATE INDEX

    commands.

    The owner of the schema containing the snapshot must have either

    space quota on the tablespace to contain the snapshot or UNLIMITED

    TABLESPACE system privilege.  Also, both you (the creator) and the

    owner must also have the privileges necessary to issue the

    snapshot's query.

    To create a snapshot, you must be using Oracle with the procedural

    option.  To create a snapshot on a remote table or view, you must

    also be using the distributed option.

SEE:

    ALTER SNAPSHOT, CREATE SNAPSHOT LOG, DROP SNAPSHOT

    CREATE SNAPSHOT LOG command

PURPOSE:

    To create a snapshot log.  A snapshot log is a table associated with

    the master table of a snapshot.  Oracle stores changes to the master

    table's data in the snapshot log and then uses the snapshot log to

    refresh the master table's snapshots.

SYNTAX:

CREATE SNAPSHOT LOG ON [schema.]table

    [PCTFREE  integer]    [PCTUSED  integer]

    [INITRANS integer]    [MAXTRANS integer]

    [TABLESPACE tablespace]

    [STORAGE storage_clause]

where:

schema

    is the schema containing the snapshot log's master table.  If you

    omit schema, Oracle assumes the master table is contained in your

    own schema. Oracle creates the snapshot log in the schema of its

    master table.  You cannot create a snapshot log for a table in the

    schema of the user SYS.

table

    is the name of the master table for which the snapshot log is to be

    created.  You cannot create a snapshot log for a view.

    Oracle chooses names for the table and trigger used to maintain the

    snapshot log by prefixing the master table name.  To limit these

    names to 30 bytes and allow them to contain the entire master table

    name, Oracle Corporation recommends that you limit master table

    names to 24 bytes.

PCTFREE

PCTUSED

INITRANS

MAXTRANS

    establishes values for these parameters for the snapshot log.

TABLESPACE

    specifies the tablespace in which the snapshot log is to be created.

    If you omit this option, Oracle creates the snapshot log in the

    default tablespace the owner of the snapshot log's schema.

STORAGE

    establishes storage characteristics for the snapshot log.

PREREQUISITES:

    You must have the privileges necessary to create a table in the

    schema of the master table.  For information on these privileges,

    see the CREATE TABLE command.

    Before a snapshot log can be created, the user SYS must run the SQL

    script DBMSSNAP.SQL on the database containing the master table.

    This script creates the package SNAPSHOT which contains both public

    and private stored procedures used for refreshing the snapshot and

    urging the snapshot log.  The exact name and location of this script

    may vary depending on your operating system.

    You must also have the privileges to create a trigger on the master

    table.  For information on these privileges, see the CREATE TRIGGER

    command.

    If you are using Trusted Oracle in DBMS MAC mode, your DBMS label

    must dominate the label of the tablespace in which the snapshot log

    is to be stored.

45.  What are the Different types Parameters you can pass in Procedure and Function?

            CREATE PROCEDURE command

PURPOSE:

To create a stand-alone stored procedure. A procedure is a group

of PL/SQL statements that you can call by name.

SYNTAX:

CREATE [OR REPLACE] PROCEDURE [schema.]procedure

[ (argument [IN | OUT | IN OUT] datatype

[, argument [IN | OUT | IN OUT] datatype] ...)]

{IS | AS} pl/sql_subprogram_body

where:

OR REPLACE

recreates the procedure if it already exists. You can use this

option to change the definition of an existing procedure without

dropping, recreating, and regranting object privileges previously

granted on it. If you redefine a procedure, Oracle recompiles it.

For information on recompiling procedures, see the ALTER PROCEDURE

command.

Users who had previously been granted privileges on a redefined

procedure can still access the procedure without being regranted the

privileges.

CREATE FUNCTION command

PURPOSE:

To create a stand-alone stored function. A stored function is a set

of PL/SQL statements you can call by name. Stored functions are

very similar to procedures, except that a function returns a value

to the environment in which it is called.

SYNTAX:

CREATE [OR REPLACE] FUNCTION [schema.]function

[ (argument [IN] datatype

[, argument [IN] datatype] ...)]

RETURN datatype

{IS | AS} pl/sql_subprogram_body

where:

OR REPLACE

recreates the function if it already exists. You can use this

option to change the definition of an existing function without

dropping, recreating, and regranting object privileges previously

granted on the function. If you redefine a function, Oracle

recompiles it. For information on recompiling functions, see the

ALTER FUNCTION command.

Users who had previously been granted privileges on a redefined

function can still access the function without being regranted the

privileges.

schema

is the schema to contain the function. If you omit schema, Oracle

creates the function in your current schema.

function

is the name of the function to be created.

argument

is the name of an argument to the function. If the function does

not accept arguments, you can omit the parentheses following the

function name.

specifies that you must supply a value for the argument when calling

the function. This is always true for function arguments, so this

keyword is entirely optional.

A procedure, rather than a stored function, can accept arguments for

which the procedure passes a value back to the calling environment

after execution.

datatype

is the datatype of an argument. An argument can have any datatype

supported by PL/SQL.

The datatype cannot specify a length, precision, or scale. Oracle

derives the length, precision, or scale of an argument from the

environment from which the function is called.

RETURN datatype

specifies the datatype of the function's return value. Because

every function must return a value, this clause is required. The

return value can have any datatype supported by PL/SQL.

The datatype cannot specify a length, precision, or scale. Oracle

derives the length, precision, or scale of the return value from the

environment from which the function is called.

pl/sql_subprogram_body

is the definition of the function. Function definitions are written

in PL/SQL.

To embed a CREATE FUNCTION statement inside an Oracle Precompiler

program, you must terminate the statement with the keyword END-EXEC

followed by the embedded SQL statement terminator for the specific

language.

PREREQUISITES:

Before a stored function can be created, the user SYS must run the

SQL script DBMSSTDX.SQL. The exact name and location of this script

may vary depending on your operating system.

To create a function in your own schema, you must have CREATE

PROCEDURE system privilege. To create a function in another user's

schema, you must have CREATE ANY PROCEDURE system privilege.

If you are using Trusted Oracle in DBMS MAC mode, you can create a

function in another user's schema if your DBMS label dominates the

creation label of the other user.

46. Explain Packages

    CREATE PACKAGE command

PURPOSE:

To create the specification for a stored package. A package is an

encapsulated collection of related procedures, functions, and other

program objects stored together in the database. The specification

declares these objects.

SYNTAX:

CREATE [OR REPLACE] PACKAGE [schema.]package

{IS | AS} pl/sql_package_spec

where:

OR REPLACE

recreates the package specification if it already exists. You can

use this option to change the specification of an existing package

without dropping, recreating, and regranting object privileges

previously granted on the package. If you change a package

specification, Oracle recompiles it. For information on recompiling

package specifications, see the ALTER PROCEDURE command.

Users who had previously been granted privileges on a redefined

package can still access the package without being regranted the

privileges.

Schema

is the schema to contain the package. If you omit schema, Oracle

creates the package in your own schema.

package

is the name of the package to be created.

pl/sql_package_spec

is the package specification. The package specification can declare

program objects. Package specifications are written in PL/SQL.

To embed a CREATE PACKAGE statement inside an Oracle Precompiler

program, you must terminate the statement with the keyword END-EXEC

followed by the embedded SQL statement terminator for the specific

language.

PREREQUISITES:

Before a package can be created, the user SYS must run the SQL

script DBMSSTDX.SQL. The exact name and location of this script may

vary depending on your operating system.

To create a package in your own schema, you must have CREATE

PROCEDURE system privilege. To create a package in another user's

schema, you must have CREATE ANY PROCEDURE system privilege.

If you are using Trusted Oracle in DBMS MAC mode, you can only

create a package in another user's schema if your DBMS label

dominates the creation label of the other user.

To create a package, you must be using Oracle with the procedural

option.

SEE:

ALTER PACKAGE, CREATE FUNCTION, CREATE PACKAGE BODY, CREATE

PROCEDURE, DROP PACKAGE

CREATE PACKAGE BODY command

PURPOSE:

To create the body of a stored package. A package is an

encapsulated collection of related procedures, stored functions, and

other program objects stored together in the database. The body

defines these objects.

SYNTAX:

CREATE [OR REPLACE] PACKAGE BODY [schema.]package

{IS | AS} pl/sql_package_body

where:

OR REPLACE

recreates the package body if it already exists. You can use this

option to change the body of an existing package without dropping,

recreating, and regranting object privileges previously granted on

it. If you change a package body, Oracle recompiles it. For

information on recompiling package bodies, see the ALTER PACKAGE

BODY command.

Users who had previously been granted privileges on a redefined

package can still access the package without being regranted the

privileges.

schema

is the schema to contain the package. If you omit schema, Oracle

creates the package in your current schema.

package

is the name of the package to be created.

pl/sql_package_body

is the package body. The package body can declare and define

program objects. Package bodies are written in PL/SQL.

To embed a CREATE PACKAGE BODY statement inside an Oracle

Precompiler program, you must terminate the statement with the

keyword END-EXEC followed by the embedded SQL statement terminator

for the specific language.

PREREQUISITES:

Before a package can be created, the user SYS must run the SQL

script DBMSSTDX.SQL. The exact name and location of this script may

vary depending on your operating system.

To create a package in your own schema, you must have CREATE

PROCEDURE system privilege. To create a package in another user's

schema, you must have CREATE ANY PROCEDURE system privilege.

If you are using Trusted Oracle in DBMS MAC mode, you can only

create a package in another user's schema if your DBMS label

dominates the creation label of the other user.

To create a package, you must be using Oracle with the procedural

option.

Summary:

There are two types of functions/Procedure/Variable in packages

a) Public Function:- Functions define at package specification is called as Public Function.

b) Private Function:- Functions define at package Body is called as Private Function.

Function / Procedure Overloading:

A Function Name can be use within a same package with different signature.

47. Oracle Database Objects.

Table, Index, Views, Sequences, Stored Procedure, Functions, Packages, Snapshot, Synonyms, Types, Clusters, Role, Database Links, Jobs, Triggers, Partition.

48. Explain Indexes.

Types Of Indexes.

a) B-Tree Index: - Its Oracle default Index type.

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.

b) Bitmap Index: -

Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform. Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

c) Reverse Key: -

d) Function based Index: -

One of the largest problems with indexes is that the indexes are often suppressed by developers. Developers using the UPPER function can suppress an index on a column for a given query. In Oracle8i, there is now a way to combat this problem. Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions or may be a PL/SQL function or C callout. The following example shows an example of a function based index.

e) #key Index: -

f) Cluster Index: -

49. Is there a limit to the number of indexes that we can have on a table?
If most of the columns are selected often from a table is it advisable to define indexes on all the columns Besides what other performance issues can be created by indexes other than the overhead in insert operations.

No limit.

The number of columns is not so important. Analyze the query and the where clauses there to decide how menu indexes you need.

You must be careful when writing queries or you may unintentional suppress (turn off) an index that you may have intended on using. Any function that modifies the column name in a WHERE clause will suppress the corresponding index. In Oracle8I, there are function-based indexes that allow indexes to be built on functions like UPPER, SUBSTR and DECODE Many common functions that are used to suppress a standard index are listed below
·     NOT / IS NULL / != or <>
·     Comparing a number field to a character field
·     Any modification to the Indexed Column Name
·     (TO_CHAR, TO_DATE, +0, || '', SUBSTR, DECODE...)

Suppression Example; despite the intended hint to use the index, the SUBSTR function will suppress the index on the CUST_NO column below:
select      /*+ index(customer custidx) */ CUST_NO, ZIP_CODE
from      CUSTOMER
where     SUBSTR(CUST_NO,1,4) = '2502';
Execution Time - 280 seconds

The SUBSTR function was re-written with a LIKE instead and part of the index is used and the performance is substantially increased:
select      CUST_NO, ZIP_CODE
from      CUSTOMER
where     CUST_NO LIKE '2502%';
Execution Time - 3 seconds

Tip: Prior to Oracle 8.1, if a column is modified in anyway in the WHERE clause, the index on the column will not be used (it will be internally suppressed).

Comparing wrong datatypes
If you compare the wrong datatypes, your index may be suppressed internally. This is because Oracle will re-write the query so that the comparison is correct. This problem is at times difficult to track down.
Comparing Characters to Numbers:
where char_data = 123
could be rewritten to:
where To_Number(char_data) = 123

Comparing Numbers to Characters:
where num_data = ‘123’
could be rewritten lik:e
where To_Char(num_data) = ‘123’

Tip: Comparing mismatched datatypes could cause an internal index suppression that is difficult to track down. Oracle will often place a function on the column that fixes the mismatch, but suppresses the index.

Here are the limits on indexes:

# of indexes per table - unlimited
# of columns per index - 32 (30 if bitmapped index)

Total size of all indexed columns cannot be more than 40% of database block size. So, the limit could be less than 32 columns if you have large columns you are indexing.

If you're going to index all the columns, you may want to consider an INDEX ORGANIZED TABLE - this is a normal table except that the table itself does not physically exist - index(es) are created to hold all the data for the table. This is done with the ORGANIZATION INDEX clause on the CREATE TABLE statement. It requires a Primary Key.

50. What is TNS Stands for?

Transparent Network Substrate

51. Explain Constraints.

CONSTRAINT clause

PURPOSE:

To define an integrity constraint. An integrity constraint is a

rule that restricts the values for one or more columns in a table.

SYNTAX:

Column constraint:

[CONSTRAINT constraint]

{ [NOT] NULL

| {UNIQUE | PRIMARY KEY}

| REFERENCES [schema.]table [(column)]

[ON DELETE CASCADE]

| CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[PARALLEL [integer] | NOPARALLEL] ]

[ EXCEPTIONS INTO [schema.]table

| DISABLE }

Table constraint:

[CONSTRAINT constraint]

{ {UNIQUE | PRIMARY KEY} (column [,column] ...)

| FOREIGN KEY (column [,column] ...)

REFERENCES [schema.]table [(column [,column] ...)]

[ON DELETE CASCADE]

| CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[PARALLEL [integer] | NOPARALLEL] ]

[ EXCEPTIONS INTO [schema.]table[@dblink]

| DISABLE }

where:

CONSTRAINT

identifies the integrity constraint by the name constraint. Oracle

stores this name in the data dictionary along with the definition of

the integrity constraint. If you omit this identifier, Oracle

generates a name with this form:

SYS_Cn

where

is an integer that makes the name unique

within the database.

For the names and definitions of integrity constraints, query the

data dictionary.

NULL

specifies that a column can contain null values.

NOT NULL

specifies that a column cannot contain null values.

If you do not specify NULL or NOT NULL in a column definition, NULL

is the default.

UNIQUE

designates a column or combination of columns as a unique key.

PRIMARY KEY

designates a column or combination of columns as the table's primary

key.

FOREIGN KEY

designates a column or combination of columns as the foreign key in

a referential integrity constraint.

REFERENCES

identifies the primary or unique key that is referenced by a foreign

key in a referential integrity constraint.

ON DELETE CASCADE

specifies that Oracle maintains referential integrity by

automatically removing dependent foreign key values if you remove a

referenced primary or unique key value.

CHECK

specifies a condition that each row in the table must satisfy.

USING INDEX

    specifies parameters for the index Oracle uses to enforce a UNIQUE

or PRIMARY KEY constraint. The name of the index is the same as the

name of the constraint. You can choose the values of the INITRANS,

MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index.

For information on these parameters, see the CREATE TABLE command.

Only use this clause when enabling UNIQUE and PRIMARY KEY

constraints.

PARALLEL

specifies the number of processes that create the index in parallel.

You can only specify positive integer values greater than 1. If you

do not specify an integer, the degree of parallelism is based on

the parallelism specified in the table's definition.

NOPARALLEL

specifies that the index should not be created in parallel.

EXCEPTIONS INTO

identifies a table into which Oracle places information about rows

that violate an enabled integrity constraint. This table must exist

before you use this option. If you omit schema, Oracle assumes the

exception table is in your own schema. The exception table must be

on your local database.

DISABLE

disables the integrity constraint. If an integrity constraint is

disabled, Oracle does not enforce it.

If you do not specify this option, Oracle automatically enables the

integrity constraint.

You can also enable and disable integrity constraints with the

ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE

commands.

PREREQUISITES:

CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE

commands. To define an integrity constraint, you must have the

privileges necessary to issue one of these commands. See the CREATE

TABLE and ALTER TABLE commands.

Defining a constraint may also require additional privileges or

preconditions that depend on the type of constraint.

52. PL/SQL Tables

PL/SQL Tables (PL/SQL)

=============

Objects of type TABLE are called "PL/SQL tables," which are modelled

on (but not the same as) database tables. PL/SQL tables use a primary

key to give you array-like access to rows. The size of a PL/SQL table

is unconstrained. That is, the number of rows in a PL/SQL table can

increase dynamically.

PL/SQL tables can have one column and a primary key, neither of which

can be named. The column can have any scalar datatype, but the primary

key must have datatype BINARY_INTEGER.

Declaring PL/SQL Tables

-----------------------

PL/SQL tables must be declared in two steps. First, you define a TABLE

type, then declare PL/SQL tables of that type. You can declare TABLE

types in the declarative part of any block, subprogram, or package

using the syntax

TYPE type_name IS TABLE OF

{ column_type | variable%TYPE | table.column%TYPE } [NOT NULL]

INDEX BY BINARY_INTEGER;

where "type_name" is a type specifier used in subsequent declarations

of PL/SQL tables and "column_type" is any scalar datatype such as CHAR,

DATE, or NUMBER. You can use the %TYPE attribute to specify a column

datatype.

In this example, you declare a TABLE type called "EnameTabTyp":

DECLARE

TYPE EnameTabTyp IS TABLE OF CHAR(10)

INDEX BY BINARY_INTEGER;

You could have used %TYPE to provide the column datatype, as follows:

DECLARE

TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

Once you define type "EnameTabTyp," you can declare PL/SQL tables of

that type, as follows:

ename_tab EnameTabTyp;

The identifier "ename_tab" represents an entire PL/SQL table.

Like scalar variables, PL/SQL tables can be declared as the formal

parameters of procedures and functions. Some packaged examples follow:

PACKAGE emp_actions IS

TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

TYPE SalTabTyp IS TABLE OF emp.sal%TYPE

INDEX BY BINARY_INTEGER;

ename_tab EnameTabTyp;

sal_tab SalTabTyp;

...

PROCEDURE hire_batch

(ename_tab EnameTabTyp,

sal_tab SalTabTyp,

...);

PROCEDURE log_names

(ename_tab EnameTabTyp,

num BINARY_INTEGER);

...

END emp_actions;

Referencing PL/SQL Tables

-------------------------

To reference rows in a PL/SQL table, you specify a primary key value

using the array-like syntax

plsql_table_name(primary_key_value)

where "primary_key_value" has datatype BINARY_INTEGER. For example,

you reference the third row in PL/SQL table "ename_tab" as follows:

ename_tab(3) ...

You can assign the value of a PL/SQL expression to a specific row using

the following syntax:

plsql_table_name(primary_key_value) := plsql_expression;

In the next example, you assign the sum of variables "salary" and

"increase" to the fifth row in PL/SQL table "sal_tab":

sal_tab(5) := salary + increase;

Until a row is assigned a value, it does not exist. If you try to

reference an uninitialized row, PL/SQL raises the predefined exception

NO_DATA_FOUND. Consider the following example:

DECLARE

TYPE JobTabTyp IS TABLE OF CHAR(14)

INDEX BY BINARY_INTEGER;

job_tab JobTabTyp;

BEGIN

job_tab(1) := 'CLERK';

IF job_tab(2) = 'CLERK' THEN -- raises NO_DATA_FOUND

...

END IF;

...

EXCEPTION

WHEN NO_DATA_FOUND THEN

-- here because job_tab(2) does not exist

...

END;

Inserting/Fetching Rows

-----------------------

You must use a loop to INSERT values from a PL/SQL table into a database

column. Likewise, you must use a loop to FETCH values from a database

column into a PL/SQL table. For example, given the declarations

DECLARE

TYPE EmpnoTabTyp IS TABLE OF NUMBER(4)

INDEX BY BINARY_INTEGER;

TYPE EnameTabTyp IS TABLE OF CHAR(10)

INDEX BY BINARY_INTEGER;

...

empno_tab EmpnoTabTyp;

ename_tab EnameTabTyp;

you might use the following procedure to INSERT values from the PL/SQL

tables into the "emp" database table:

PROCEDURE insert_emp_data

(rows BINARY_INTEGER,

empno_tab EmpnoTabTyp,

ename_tab EnameTabTyp,

...) IS

BEGIN

FOR i IN 1..rows LOOP

INSERT INTO emp (empno, ename, ...)

VALUES (empno_tab(i), ename_tab(i), ...);

END LOOP;

END;

Conversely, you might use the next procedure to FETCH all rows from the

database table into PL/SQL tables "empno_tab" and "ename_tab":

PROCEDURE fetch_emp_data

(rows OUT BINARY_INTEGER,

empno_tab OUT EmpnoTabTyp,

ename_tab OUT EnameTabTyp,

...) IS

BEGIN

rows := 0;

FOR emprec IN (SELECT * FROM emp) LOOP

rows := rows + 1;

empno_tab(rows) := emprec.empno;

ename_tab(rows) := emprec.ename;

...

END LOOP;

END;

However, you cannot reference PL/SQL tables in the INTO clause. For

example, the following SELECT statement is illegal:

PROCEDURE fetch_emp_data

(rows OUT BINARY_INTEGER,

empno_tab OUT EmpnoTabTyp,

ename_tab OUT EnameTabTyp,

...) IS

BEGIN

SELECT empno, ename

INTO empno_tab, ename_tab -- illegal

FROM emp;

...

END;

Deleting Rows

-------------

The DELETE statement cannot specify PL/SQL tables, so you cannot

delete individual rows from a PL/SQL table. Assigning NULL to a row

as follows does not work because the row remains:

sal_tab(3) := NULL;

However, you can use a simple workaround to delete an entire PL/SQL

table. Simply assign NULL to it, as shown in the following example:

DECLARE

TYPE NumTabTyp IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

sal_tab NumTabTyp;

BEGIN

/* Load salary table. */

FOR i IN 1..50 LOOP

sal_tab(i) := i;

END LOOP;

...

sal_tab := NULL; -- deletes sal_tab table

IF sal_tab(3) ... -- raises NO_DATA_FOUND

53. Explain Cursors in PL/SQL

1.9 Cursors in PL/SQL

Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime. Static cursors are covered in greater detail in this section. Dynamic cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference, both from O'Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.

1.9.1 Explicit Cursors

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

1.9.1.1 Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:

· A cursor without parameters, such as:

      CURSOR company_cur

IS

         SELECT company_id FROM company;

· A cursor that accepts arguments through a parameter list:

      CURSOR company_cur (id_in IN NUMBER) IS

      SELECT name FROM company

      WHERE  company_id = id_in;

· A cursor header that contains a RETURN clause in place of the SELECT statement:

      CURSOR company_cur (id_in IN NUMBER)

      RETURN company%ROWTYPE IS

      SELECT * FROM company;

This technique can be used in packages to hide the implementation of the cursor in the package body. See the Section 1.14" section for more information.

1.9.1.2 Opening explicit cursors

To open a cursor, use the following syntax:

OPEN cursor_name [(argument [,argument ...])];

where cursor_name is the name of the cursor as declared in the declaration section. The arguments are required if the definition of the cursor contains a parameter list.

You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set.

When using a cursor FOR loop, the OPEN is implicit in the FOR statement. If you try to open a cursor that is already open, PL/SQL will raise an "ORA-06511: PL/SQL: cursor already open" exception.

1.9.1.3 Fetching from explicit cursors

The FETCH statement places the contents of the current row into local variables. To retrieve all rows in a result set, each row needs to be fetched. The syntax for a FETCH statement is:

FETCH cursor_name INTO record_or_variable_list;

where cursor_name is the name of the cursor as declared and opened.

1.9.1.4 Closing explicit cursors

The syntax of the CLOSE statement is:

CLOSE cursor_name;

where cursor_name is the name of the cursor declared and opened.

After all rows have been fetched, a cursor needs to be closed. Closing a cursor releases the private SQL area used by the cursor, freeing the memory used by that cursor.

If you declare a cursor in a local anonymous, procedure, or function block, that cursor will automatically close when the block terminates. Package-based cursors must be closed explicitly, or they stay open for the duration of your session. Closing a cursor that is not open raises an INVALID CURSOR exception.

1.9.1.5 Explicit cursor attributes

There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about the state of the cursor. The syntax for a cursor attribute is:

cursor_name%attribute

where cursor_name is the name of the explicit cursor.

The behaviors of the explicit cursor attributes are described in the following table.

Attribute	Description
%ISOPEN	TRUE if cursor is open. FALSE if cursor is not open.
%FOUND	INVALID_CURSOR is raised if cursor has not been OPENed. NULL before the first fetch. TRUE if record was fetched successfully. FALSE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd.
%NOTFOUND	INVALID_CURSOR is raised if cursor has not been OPENed. NULL before the first fetch. FALSE if record was fetched successfully. TRUE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd.
%ROWCOUNT	INVALID_CURSOR is raised if cursor has not been OPENed. The number of rows fetched from the cursor. INVALID_CURSOR if cursor has been CLOSEd.

Frequently a cursor attribute is checked as part of a WHILE loop that fetches rows from a cursor:

DECLARE

   caller_rec caller_pkg.caller_cur%ROWTYPE;

BEGIN

   OPEN caller_pkg.caller_cur;

   LOOP

      FETCH caller_pkg.caller_cur into caller_rec;

      EXIT WHEN caller_pkg.caller_cur%NOTFOUND

OR

               caller_pkg.caller_cur%ROWCOUNT > 10;

      UPDATE call

         SET caller_id = caller_rec.caller_id

         WHERE call_timestamp < SYSDATE;

   END LOOP;

   CLOSE caller_pkg.caller_cur;

END;

1.9.2 Implicit Cursors

Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd.

SELECT statements handle the %FOUND and %NOTFOUND attributes differently from explicit cursors. When an implicit SELECT statement does not return any rows, PL/SQL immediately raises the NO_DATA_FOUND exception and control passes to the exception section. When an implicit SELECT returns more than one row, PL/SQL immediately raises the TOO_MANY_ROWS exception and control passes to the exception section.

Implicit cursor attributes are referenced via the SQL cursor. For example:

BEGIN

   UPDATE activity SET last_accessed := SYSDATE

   WHERE UID = user_id;

   IF SQL%NOTFOUND THEN

      INSERT INTO activity_log (uid,last_accessed)

      VALUES (user_id,SYSDATE);

   END IF

END;

SQL Attributes	Description
%ISOPEN	Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed.
%FOUND	NULL before the statement. TRUE if one or more rows were inserted, updated, or deleted or if only one row was selected. FALSE if no row was selected, updated, inserted, or deleted.
%NOTFOUND	NULL before the statement. TRUE if no row was selected, updated, inserted, or deleted. FALSE if one or more rows were inserted, updated, or deleted.
%ROWCOUNT	The number of rows affected by the cursor.
%BULK_ROWCOUNT (Oracle8i)	A pseudo index-by table containing the numbers of rows affected by the statements executed in bulk bind operations. See the "Bulk Binds (Oracle8i)" section for more information on %BULK_ROWCOUNT.

Use the RETURNING clause in INSERT, UPDATE, and DELETE statements to obtain data modified by the associated DML statement. This clause allows you to avoid an additional SELECT statement to query the results of the DML statement. For example:

BEGIN

   UPDATE activity SET last_accessed := SYSDATE

   WHERE UID = user_id

   RETURNING last_accessed, cost_center

   INTO timestamp, chargeback_acct;

1.9.2.1 The SELECT FOR UPDATE clause

By default, the Oracle RDBMS locks rows as they are changed. To lock all rows in a result set, use the FOR UPDATE clause in your SELECT statement when you OPEN the cursor, instead of when you change the data. Using the FOR UPDATE clause does not require you to actually make changes to the data; it only locks the rows when opening the cursor. These locks are released on the next COMMIT or ROLLBACK. As always, these row locks do not affect other SELECT statements unless they, too, are FOR UPDATE. The FOR UPDATE clause is appended to the end of the SELECT statement and has the following syntax:

SELECT ...

  FROM ...

   FOR UPDATE [OF column_reference] [NOWAIT];

where column_reference is a comma-delimited list of columns that appear in the SELECT clause. The NOWAIT keyword tells the RDBMS to not wait for other blocking locks to be released. The default is to wait forever.

In the following example, only columns from the inventory (pet) table are referenced FOR UPDATE, so no rows in the dog_breeds (dog) table are locked when hounds_in_stock_cur is opened:

DECLARE

   CURSOR hounds_in_stock_cur IS

      SELECT pet.stock_no, pet.breeder, dog.size

        FROM dog_breeds dog ,inventory pet

       WHERE dog.breed = pet.breed

         AND dog.class = 'HOUND'

         FOR UPDATE OF pet.stock_no, pet.breeder;

BEGIN

1.9.2.2 The WHERE CURRENT OF clause

UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor. The syntax is:

[UPDATE | DELETE ] ...

   WHERE CURRENT OF cursor_name;

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement. For example:

DECLARE

   CURSOR wip_cur IS

      SELECT acct_no, enter_date FROM wip

       WHERE enter_date < SYSDATE -7

         FOR UPDATE;

BEGIN

   FOR wip_rec IN wip_cur

   LOOP

      INSERT INTO acct_log (acct_no, order_date)

         VALUES (wip_rec.acct_no, wip_rec.enter_

            date);

      DELETE FROM wip

         WHERE CURRENT OF wip_cur;

   END LOOP;

END;

1.9.3 Cursor Variables

A cursor variable is a data structure that points to a cursor object, which in turn points to the cursor's result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR

   [RETURN record_type];

If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. Cursor variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF CURSOR. A cursor variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor variable based on that type.

The following example shows the use of both weak and strong REF CURSORs:

DECLARE

   -- Create a cursor type based on the companies

      table.

   TYPE company_curtype IS REF CURSOR

      RETURN companies%ROWTYPE;

   -- Create the variable based on the REF CURSOR.

   company_cur company_curtype;

   -- And now the weak, general approach.

   TYPE any_curtype IS REF CURSOR;

   generic_curvar any_curtype;

The syntax to OPEN a cursor variable is:

OPEN cursor_name FOR select_statement;

FETCH and CLOSE a cursor variable using the same syntax as for explicit cursors. There are a number of restrictions on cursor variables:

· Cursor variables cannot be declared in a package since they do not have a persistent state.

· You cannot use the FOR UPDATE clause with cursor variables.

· You cannot assign NULLs to a cursor variable nor use comparison operators to test for equality, inequality, or nullity.

· Neither database columns nor collections can store cursor variables.

· You cannot use RPCs to pass cursor variables from one server to another.

· Cursor variables cannot be used with the dynamic SQL built-in package DBMS_SQL.

54. What is a cluster table?

A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database.

Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its

Cluster Keys

The columns defined by the CREATE CLUSTER command make up the cluster key. These cluster columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.

Types of Clusters

A cluster can be either an indexed cluster or a hash cluster.

Indexed Clusters

In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. This saves disk space and improves performance for many operations.

You may want to use indexed clusters in the following cases:

· Your queries retrieve rows over a range of cluster key values.

· Your clustered tables may grow unpredictably.

After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index.

A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.

Hash Clusters

In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. When you create a hash cluster, you can either specify a hash function or use the Oracle internal hash function. Hash values are not actually stored in the cluster, although cluster key values are stored for every row in the cluster.

You may want to use hash clusters in the following cases:

· Your queries retrieve rows based on equality conditions involving all cluster key columns.

· Your clustered tables are static or you can determine the maximum number of rows and the maximum amount of space required by the cluster when you create the cluster.

The hash function provides access to rows in the table based on the cluster key value. If you issue a SQL statement that locates a row in the cluster based on its cluster key value, Oracle applies the hash function to the given cluster key value and uses the resulting hash value to locate the matching rows. Because multiple cluster key values can map to the same hash value, Oracle must also check the row's cluster key value. This process often results in less I/O than the process for the indexed cluster, because the index search is not required.

Oracle's internal hash function returns values ranging from 0 to the value of HASHKEYS - 1. If you specify a column with the HASH IS clause, the column values need not fall into this range. Oracle divides the column value by the HASHKEYS value and uses the remainder as the hash value. The hash value for null is HASHKEYS - 1. Oracle also rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. This rounding reduces the likelihood of hash collisions, or multiple cluster key values having the same hash value.

You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key.

If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and performs row chaining to contain the rest of the row.

Cluster Size

Oracle uses the value of the SIZE parameter to determine the space reserved for rows corresponding to one cluster key value or one hash value. This space then determines the maximum number of cluster or hash values stored in a data block. If the SIZE value is not a divisor of the data block size, Oracle uses the next largest divisor. If the SIZE value is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.

Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. This does not apply to hash clusters because hash values are not actually stored in the cluster.

Although the maximum number of cluster and hash key values per data block is fixed on a per `-cluster basis, Oracle does not reserve an equal amount of space for each cluster or hash key value. Varying this space stores data more efficiently, because the data stored per cluster or hash key value is rarely fixed.

A SIZE value smaller than the space needed by the average cluster or hash key value may require the data for one cluster key or hash key value to occupy multiple data blocks. A SIZE value much larger results in wasted space.

When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters.

Adding Tables to a Cluster

You can add tables to an existing cluster by issuing a CREATE TABLE statement with the CLUSTER clause. A cluster can contain as many as 32 tables, although the performance gains of clustering are often lost in clusters of more than four or five tables.

All tables in the cluster have the cluster's storage characteristics as specified by the PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE, and STORAGE parameters.

Example I

The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel

( department_number NUMBER(2) )

SIZE 512

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp

(empno NUMBER PRIMARY KEY,

ename VARCHAR2(10) NOT NULL

CHECK (ename = UPPER(ename)),

job VARCHAR2(9),

mgr NUMBER REFERENCES scott.emp(empno),

hiredate DATE CHECK (hiredate >= SYSDATE),

sal NUMBER(10,2) CHECK (sal > 500),

comm NUMBER(9,0) DEFAULT NULL,

deptno NUMBER(2) NOT NULL )

CLUSTER personnel (deptno);

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(9),

loc VARCHAR2(9))

CLUSTER personnel (deptno);

The following statement creates the cluster index on the cluster key of PERSONNEL:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can insert rows into either the EMP or DEPT tables.

Example II

The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of size 512 bytes, and storage parameter values:

CREATE CLUSTER personnel

( department_number NUMBER )

SIZE 512 HASHKEYS 500

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);

Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.

Example III

The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:

CREATE CLUSTER personnel

( home_area_code NUMBER,

home_prefix NUMBER )

HASHKEYS 20

HASH IS MOD(home_area_code + home_prefix, 101);

Pages

Thursday, January 26, 2012

Oracle Interview Questions and Answers

Delete

In delete database trigger fires, if any.

Truncate

Cursor Attributes

Oracle's Explain Plan

The Plan Table

The fields (attributes) within the plan table

dbms_xplan

Operations

TABLE ACCESS

Displaying the execution plan

INDEX

MERGE JOIN

NESTED LOOPS

Sorts

Aggregate Sorts

EXPLAIN PLAN Usage

Plan Table

AUTOTRACE - The Easy Option?

EXPLAIN PLAN

Statement ID

Oracle's TKPROF

Enabling SQL TRACE

Timed Statistics

Finding the SQL Trace file

Maximum size of the trace file

Using tkprof

The meaning of count, cpu and elapsed in tkprof

Query and current in tkprof

Setting up a demonstration environment

query

current

1.15 Calling PL/SQL Functions in SQL

1.15.1 Syntax for Calling Stored Functions in SQL

1.15.2 Requirements and Restrictions on Stored Functions in SQL

1.15.3 Calling Packaged Functions in SQL

1.15.3.1 Column/function name precedence

REFRESH CLAUSE

Timing the refresh

Summary

32. How does one escape special characters when building SQL queries?

33. How does one get the time difference between two date columns?

34. How does one add a day/hour/minute/second to a date value?

35. Can one retrieve only the Nth row from a table?

36. How does one select EVERY Nth row from a table?

37. How does one select the TOP N rows from a table?

38. How does one code a tree-structured query?

39. How does one code a matrix report in SQL?

40. How does one implement IF-THEN-ELSE in a select statement?

41. How can one dump/ examine the exact content of a database column?

42. Workaround for snapshots on tables with LONG columns

CREATE TABLESPACE command

CREATE FUNCTION command

52. PL/SQL Tables

1.9 Cursors in PL/SQL

1.9.1 Explicit Cursors

1.9.1.1 Declaring explicit cursors

1.9.1.2 Opening explicit cursors

1.9.1.3 Fetching from explicit cursors

1.9.1.4 Closing explicit cursors

1.9.1.5 Explicit cursor attributes

1.9.2 Implicit Cursors

1.9.2.1 The SELECT FOR UPDATE clause

1.9.2.2 The WHERE CURRENT OF clause

1.9.3 Cursor Variables

No comments:

Post a Comment