SUBQUERIES AND EXISTS
SUBQUERIES
Ø Nesting of queries, one within the other
is termed as a subquery.
Ø A statement containing a subquery is
called a parent query.
Ø Subqueries are used to retrieve data from
tables that depend on the values in the table itself.
TYPES
Ø
Single
row subqueries
Ø
Multi
row subqueries
Ø
Multiple
subqueries
Ø
Correlated
subqueries
SINGLE ROW
SUBQUERIES
In single row subquery, it will return one value.
Ex:
SQL> select
* from emp where sal > (select sal from emp where empno = 7566);
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ----------
--------- ---------- ------------
------- ---------- ----------
7788 SCOTT
ANALYST 7566 19-APR-87
3000 20
7839 KING
PRESIDENT
17-NOV-81 5000 10
7902 FORD
ANALYST 7566 03-DEC-81
3000 20
MULTI ROW
SUBQUERIES
In multi row subquery, it will return more than one value. In
such cases we should include operators like any, all, in or not in between the
comparision operator and the subquery.
Ex:
SQL> select
* from emp where sal > any (select sal from emp where sal between 2500
and 4000);
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ----------
--------- ---------- -----------
-------- ---------- ----------
7566 JONES
MANAGER 7839 02-APR-81 2975 20
7788 SCOTT
ANALYST 7566 19-APR-87 3000 20
7839 KING
PRESIDENT 17-NOV-81 5000 10
7902 FORD
ANALYST 7566 03-DEC-81 3000 20
SQL> select * from emp where sal > all (select sal from emp where
sal between 2500
and 4000);
EMPNO ENAME JOB
MGR HIREDATE SAL
COMM DEPTNO
---------- ----------
--------- ---------- -------------
------ ---------- ----------
7839 KING
PRESIDENT
17-NOV-81 5000 10
MULTIPLE
SUBQUERIES
There is no limit on the number of subqueries included in a
where clause. It allows nesting of a query within a subquery.
Ex:
SQL> select
* from emp where sal = (select max(sal) from emp where sal < (select
max(sal) from emp));
EMPNO ENAME
JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ----------
--------- ---------- ------------
------- ---------- ----------
7788 SCOTT
ANALYST 7566 19-APR-87
3000 20
7902 FORD
ANALYST 7566 03-DEC-81
3000 20
CORRELATED
SUBQUERIES
A subquery is evaluated once for the entire parent statement
where as a correlated subquery is evaluated once for every row processed by the
parent statement.
Ex:
SQL> select
distinct deptno from emp e where 5 <= (select count(ename) from emp
where e.deptno = deptno);
DEPTNO
----------
20
30
EXISTS
Exists
function is a test for existence. This is a logical test for the return of rows
from a query.
Ex:
Suppose we want to display the department
numbers which has more than 4
employees.
SQL> select
deptno,count(*) from emp group by deptno having count(*) > 4;
DEPTNO COUNT(*)
--------- ----------
20 5
30 6
From the above query
can you want to display the names of employees?
SQL> select deptno,ename, count(*) from emp group by deptno,ename
having count(*)
> 4;
no rows selected
The above query
returns nothing because combination of deptno and ename never
return more than one
count.
The solution is to
use exists which follows.
SQL> select deptno,ename from emp e1 where exists (select * from emp
e2
where e1.deptno=e2.deptno group by
e2.deptno having count(e2.ename) > 4)
order by deptno,ename;
DEPTNO ENAME
---------- ----------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
NOT
EXISTS
SQL> select deptno,ename from emp e1 where not
exists (select * from emp e2
where e1.deptno=e2.deptno group by
e2.deptno having count(e2.ename) > 4) order
by deptno,ename;
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
WALKUP TREES AND INLINE
VIEW
WALKUP
TREES
Using hierarchical queries, you can retrieve data based on a
natural hierarchical relationship between rows in a table. However, where a
hierarchical relationship exists between the rows of a table, a process called
tree walking enables the hierarchy to be constructed.
Ex:
SQL> select
ename || '==>' || prior ename, level
from emp start with ename = 'KING'
connect by prior empno=mgr;
ENAME||'==>'||PRIORENAM LEVEL
------------------------------------ --------
KING==> 1
JONES==>KING 2
SCOTT==>JONES 3
ADAMS==>SCOTT 4
FORD==>JONES 3
SMITH==>FORD 4
BLAKE==>KING 2
ALLEN==>BLAKE 3
WARD==>BLAKE 3
MARTIN==>BLAKE 3
TURNER==>BLAKE 3
JAMES==>BLAKE 3
CLARK==>KING 2
MILLER==>CLARK 3
In
the above
Start
with clause specifies the root row of the table.
Level
pseudo column gives the 1 for root , 2 for child and so on.
Connect
by prior clause specifies the columns which has parent-child relationship.
INLINE
VIEW OR TOP-N ANALYSIS
In
the select statement instead of table name, replacing the select statement is
known as inline view.
Ex:
SQL> Select ename, sal, rownum rank from (select *from emp order by
sal);
ENAME SAL RANK
---------- ---------- ----------
SMITH 800 1
JAMES 950 2
ADAMS 1100 3
WARD 1250 4
MARTIN 1250 5
MILLER
1300 6
TURNER 1500 7
ALLEN 1600 8
CLARK 2450 9
BLAKE 2850 10
JONES 2975 11
SCOTT 3000 12
FORD 3000 13
KING 5000 14
LOCKS
Locks are the mechanisms used to prevent destructive interaction
between users accessing same resource simultaneously. Locks provides high
degree of data concurrency.
TYPES
Ø
Row
level locks
Ø
Table
level locks
ROW LEVEL
LOCKS
In the row level lock a row is locked exclusively so that other
cannot modify the row until the transaction holding the lock is committed or
rolled back. This can be done by using select..for update clause.
Ex:
SQL> select
* from emp where sal > 3000 for update of comm.;
TABLE
LEVEL LOCKS
A table level lock will protect table data thereby guaranteeing
data integrity when data is being accessed concurrently by multiple users. A
table lock can be held in several modes.
Ø
Share
lock
Ø
Share
update lock
Ø
Exclusive
lock
SHARE LOCK
A share lock locks the table allowing other users to only query
but not insert, update or delete rows in a table. Multiple users can place
share locks on the same resource at the same time.
Ex:
SQL> lock
table emp in share mode;
SHARE
UPDATE LOCK
It locks rows that are to be updated in a table. It permits
other users to concurrently query, insert , update or even lock other rows in
the same table. It prevents the other users from updating the row that has been
locked.
Ex:
SQL> lock table emp in share update mode;
EXCLUSIVE
LOCK
Exclusive lock is the most restrictive of tables locks. When
issued by any user, it allows the other user to only query. It is similar to
share lock but only one user can place exclusive lock on a table at a time.
Ex:
SQL> lock
table emp in share exclusive mode;
NOWAIT
If one user locked the table without nowait then another user
trying to lock the same table then he has to wait until the user who has
initially locked the table issues a commit or rollback statement. This delay
could be avoided by appending a nowait clause in the lock table command.
Ex:
SQL> lock table emp in exclusive mode nowait.
DEADLOCK
A deadlock occurs when tow users have a lock each on separate
object, and they want to acquire a lock on the each other’s object. When this
happens, the first user has to wait for the second user to release the lock,
but the second user will not release it until the lock on the first user’s
object is freed. In such a case, oracle detects the deadlock automatically and
solves the problem by aborting one of the two transactions.
INDEXES
Index is typically a listing of keywords accompanied by the
location of information on a subject. We can create indexes explicitly to speed
up SQL statement execution on a table. The index points directly to the
location of the rows containing the value.
WHY INDEXES?
Indexes are most useful on larger tables, on columns that are
likely to appear in where clauses as simple equality.
TYPES
Ø Unique index
Ø Non-unique index
Ø Btree index
Ø Bitmap index
Ø Composite index
Ø Reverse key index
Ø Function-based index
Ø Descending index
Ø Domain index
Ø Object index
Ø Cluster index
Ø Text index
Ø Index organized table
Ø Partition index
v Local index
ü Local prefixed
ü Local non-prefixed
v Global index
ü Global prefixed
ü Global non-prefixed
UNIQUE INDEX
Unique indexes guarantee that no two rows of a table have
duplicate values in the columns that define the index. Unique index is
automatically created when primary key or unique constraint is created.
Ex:
SQL> create unique index stud_ind on student(sno);
NON-UNIQUE INDEX
Non-Unique indexes do not impose the above restriction on the
column values.
Ex:
SQL> create index stud_ind on student(sno);
BTREE INDEX or ASCENDING INDEX
The default type of index used in an oracle database is the
btree index. A btree index is designed to provide both rapid access to
individual rows and quick access to groups of rows within a range. The btree
index does this by performing a succession of value comparisons. Each
comparison eliminates many of the rows.
Ex:
SQL> create index stud_ind on student(sno);
BITMAP INDEX
This can be used for low cardinality columns: that is columns in
which the number of distinct values is snall when compared to the number of the
rows in the table.
Ex:
SQL> create bitmap index stud_ind on student(sex);
COMPOSITE INDEX
A composite index also called a concatenated index is an index
created on multiple columns of a table. Columns in a composite index can appear
in any order and need not be adjacent columns of the table.
Ex:
SQL> create bitmap index stud_ind on student(sno, sname);
REVERSE KEY INDEX
A reverse key index when compared to standard index, reverses
each byte of the column being indexed while keeping the column order. When the
column is indexed in reverse mode then the column values will be stored in an
index in different blocks as the starting value differs. Such an arrangement
can help avoid performance degradations in indexes where modifications to the
index are concentrated on a small set of blocks.
Ex:
SQL> create index stud_ind on student(sno, reverse);
We can rebuild a reverse key index into normal index using the
noreverse keyword.
Ex:
SQL> alter index stud_ind rebuild noreverse;
FUNCTION BASED INDEX
This will use result of the function as key instead of using
column as the value for the key.
Ex:
SQL> create index stud_ind on student(upper(sname));
DESCENDING INDEX
The order used by B-tree indexes has been ascending order. You
can categorize data in B-tree index in descending order as well. This feature
can be useful in applications where sorting operations are required.
Ex:
SQL> create index stud_ind on student(sno desc);
TEXT INDEX
Querying text is different from querying data because words have
shades of meaning, relationships to other words, and opposites. You may want to
search for words that are near each other, or words that are related to thers.
These queries would be extremely difficult if all you had available was the
standard relational operators. By extending SQL to
include text indexes, oracle text permits you to ask very complex questions
about the text.
To use oracle text, you need to create a text index on the column in which the text is stored. Text index is
a collection of tables and indexes that store information about the text stored
in the column.
TYPES
There are several different types of indexes available in oracle
9i. The first, CONTEXT is supported in oracle 8i as well as
oracle 9i. As of oracle 9i, you can use the CTXCAT text
index fo further enhance your text index management and query capabilities.
Ø CONTEXT
Ø CTXCAT
Ø CTXRULE
The CTXCAT index type supports the transactional
synchronization of data between the base table and its text index. With CONTEXT indexes, you need to manually tell oracle to update the values
in the text index after data changes in base table. CTXCAT index types do not generate score values during the text
queries.
HOW TO CREATE TEXT INDEX?
You can create a text index via a special version of the create
index comman. For context index, specify the ctxsys.context index type and for
ctxcat index, specify the ctxsys.ctxcat index type.
Ex:
Suppose you have a table called BOOKS with the following columns
Title, Author, Info.
SQL> create
index book_index on books(info) indextype is ctxsys.context;
SQL> create
index book_index on books(info) indextype is ctxsys.ctxcat;
TEXT QUERIES
Once a text index is created on the info column of BOOKS table, text-searching capabilities increase dynamically.
CONTAINS & CATSEARCH
CONTAINS
function takes two parameters – the column name and the search string.
Syntax:
Contains(indexed_column, search_str);
If you create a CTXCAT index, use the CATSEARCH function in place of CONTAINS. CATSEARCH takes three parameters – the column name, the search string and
the index set.
Syntax:
Contains(indexed_column, search_str, index_set);
HOW A TEXT QEURY WORKS?
When a function such as CONTAINS or CATSEARCH is used in query, the text portion of the query is processed by
oracle text. The remainder of the query is processed just like a regular query
within the database. The result of the text query processing and the regular
query processing are merged to return a single set of records to the user.
SEARCHING FOR AN EXACT MATCH OF A WORD
The following queries will search for a word called ‘prperty’
whose score is greater than zero.
SQL> select
* from books where contains(info, ‘property’) > 0;
SQL> select
* from books where catsearch(info, ‘property’, null) > 0;
Suppose if you want to know the score of the ‘property’ in each
book, if score values for individual searches range from 0 to 10 for each
occurrence of the string within the text then use the score function.
SQL> select
title, score(10) from books where contains(info, ‘property’, 10) > 0;
SEARCHING FOR AN EXACT MATCH OF MULTIPLE WORDS
The following queries will search for two words.
SQL> select
* from books where contains(info, ‘property AND
harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property AND
harvests’, null) > 0;
Instead of using AND you could hae used an ampersand(&).
Before using this method, set define off so the & character will not be
seen as part of a variable name.
SQL> set
define off
SQL> select
* from books where contains(info, ‘property & harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property
harvests’, null) > 0;
The following queries will search for more than two words.
SQL> select
* from books where contains(info, ‘property AND
harvests AND workers’) > 0;
SQL> select
* from books where catsearch(info, ‘property harvests workers’, null) > 0;
The following queries will search for either of the two words.
SQL> select
* from books where contains(info, ‘property OR
harvests’) > 0;
Instead of OR you can use a vertical line (|).
SQL> select
* from books where contains(info, ‘property |
harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property |
harvests’, null) > 0;
In the following queries the ACCUM(accumulate)
operator adds together the scores of the individual searches and compares the
accumulated score to the threshold value.
SQL> select
* from books where contains(info, ‘property ACCUM
harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property ACCUM harvests’,
null) > 0;
Instead of OR you can use a comma(,).
SQL> select
* from books where contains(info, ‘property , harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property , harvests’, null) > 0;
In the following queries the MINUS
operator subtracts the score of the second term’s search from the score of the
first term’s search.
SQL> select
* from books where contains(info, ‘property MINUS
harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property NOT harvests’,
null) > 0;
Instead of MINUS you can use – and instead of NOT you can use ~.
SQL> select
* from books where contains(info, ‘property -
harvests’) > 0;
SQL> select
* from books where catsearch(info, ‘property ~ harvests’,
null) > 0;
SEARCHING FOR AN EXACT MATCH OF A PHRASE
The following queries will search for the phrase. If the search
phrase includes a reserved word within oracle text, the you must use curly
braces ({}) to enclose text.
SQL> select
* from books where contains(info, ‘transactions {and} finances’) > 0;
SQL> select
* from books where catsearch(info, ‘transactions {and} finances’, null) > 0;
You can enclose the entire phrase within curly braces, in which
case any reserved words within the phrase will be treated as part of the search
criteria.
SQL> select
* from books where contains(info, ‘{transactions and finances}’) > 0;
SQL> select
* from books where catsearch(info, ‘{transactions and finances}’, null) > 0;
SEARCHING FOR WORDS THAT ARE NEAR EACH OTHER
The following queries will search for the words that are in
between the search terms.
SQL> select
* from books where contains(info, ‘workers NEAR
harvests’) > 0;
Instead of NEAR you can use ;.
SQL> select
* from books where contains(info, ‘workers ; harvests’) > 0;
In CONTEXT index queries, you can specify the
maximum number of words between the search terms.
SQL> select
* from books where contains(info, ‘NEAR((workers,
harvests),10)’ > 0;
USING WILDCARDS DURING SEARCHES
You can use wildcards to expand the list of valid search terms
used during your query. Just as in regular text-string wildcard processing, two
wildcards are available.
% - percent sign; multiple-character
wildcard
_ - underscore; single-character wildcard
SQL> select
* from books where contains(info, ‘worker%’) > 0;
SQL> select
* from books where contains(info, ‘work___’) > 0;
SEARCHING FOR WORDS THAT SHARE THE SAME STEM
Rather than using wildcards, you can use stem-expansion
capabilities to expand the list of text strings. Given the ‘stem’ of a word,
oracle will expand the list of words to search for to include all words having
the same stem. Sample expansions are show here.
Play - plays playing played playful
SQL> select
* from books where contains(info, ‘$manage’) > 0;
SEARCHING FOR FUZZY MATCHES
A fuzzy match expands the specified search term to include words
that are spelled similarly but that do not necessarily have the same word stem.
Fuzzy matches are most helpful when the text contains misspellings. The
misspellings can be either in the searched text or in the search string
specified by the user during the query.
The following queries will not return anything because its
search does not contain the word ‘hardest’.
SQL> select
* from books where contains(info, ‘hardest’) > 0;
It does, however, contains the word ‘harvest’. A fuzzy match
will return the books containing the word ‘harvest’ even though ‘harvest’ has a
different word stem thant the word used as the search term.
To use a fuzzy match, precede the search term with a question
mark, with no space between the question mark and the beginning of the search
term.
SQL> select
* from books where contains(info, ‘?hardest’) > 0;
SEARCHING FOR WORDS THAT SOUND LIKE OTHER WORDS
SOUNDEX, expands search terms based on how the word sounds. The
SOUNDEX expansion method uses the same text-matching logic available via the
SOUNDEX function in SQL.
To use the SOUNDEX option, you must precede the search term with
an exclamation mark(!).
SQL> select
* from books where contains(info, ‘!grate’) > 0;
INDEX SYNCHRONIZATION
When using CONTEXT indexes, you need to manage the text
index contents; the text indexes are not updated when the base table is
updated. When the table was updated, its text index is out of sync with the
base table. To sync of the index, execute the SYNC_INDEX
procedure of the CTX_DDL package.
SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);
INDEX SETS
Historically, problems with queries of text indexes have
occurred when other criteria are used alongside text searches as part of the where
clause. To improve the mixed query capability, oracle features index sets. The
indexes within the index set may be structured relational columns or on text
columns.
To create an index set, use the CTX_DDL
package to create the index set and add indexes to it. When you create a text
index, you can then specify the index set it belongs to.
SQL> exec CTX_DDL.CREATE_INDEX_SET(‘books_index_set’);
The add non-text indexes.
SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’, ‘title_index’);
Now create a CTXCAT text index. Specify ctxsys.ctxcat as the
index type, and list the index set in the parameters clause.
SQL> create
index book_index on books(info) indextype is ctxsys.ctxcat
parameters(‘index
set books_index_set’);
INDEX-ORGANIZED TABLE
An index-organized table keeps its data sorted according to the
primary key column values for the table. Index-organized tables store their
data as if the entire table was stored in an index.
An index-organized table allows you to store the entire table’s
data in an index.
Ex:
SQL> create table student (sno number(2),sname varchar(10),smarks
number(3)
constraint pk
primary key(sno) organization index;
PARTITION INDEX
Similar to partitioning tables, oracle allows you to partition
indexes too. Like table partitions,
index partitions could be in different tablespaces.
LOCAL INDEXES
Ø Local keyword tells oracle to create a
separte index for each partition.
Ø In the local prefixed index the partition
key is specified on the left prefix. When the underlying table is partitioned
baes on, say two columns then the index can be prefixed on the first column
specified.
Ø Local prefixed indexes can be unique or
non unique.
Ø Local indexes may be easier to manage
than global indexes.
Ex:
SQL> create index stud_index on student(sno) local;
GLOBAL INDEXES
Ø A global index may contain values from
multiple partitions.
Ø An index is global prefixed if it is
partitioned on the left prefix of the index columns.
Ø The global clause allows you to create a
non-partitioned index.
Ø Global indexes may perform uniqueness
checks faster than local (partitioned) indexes.
Ø You cannot create global indexes for hash
partitions or subpartitions.
Ex:
SQL> create index stud_index on student(sno) global;
Similar to table partitions, it is possible to move them from
one device to another. But unlike table partitions, movement of index
partitions requires individual reconstruction of the index or each partition
(only in the case of global index).
Ex:
SQL> alter index stud_ind rebuild partition p2
Ø Index partitions cannot be dropped
manually.
Ø They are dropped implicitly when the data
they refer to is dropped from the partitioned table.
MONITORING USE OF INDEXES
Once you turned on the monitoring the use of indexes, then we
can check whether the table is hitting the index or not.
To monitor the use of index use the follwing syntax.
Syntax:
alter index index_name monitoring usage;
then check for the details in V$OBJECT_USAGE view.
If you want to stop monitoring use the following.
Syntax:
alter index index_name nomonitoring usage;
DATA MODEL
Ø
ALL_INDEXES
Ø
DBA_INDEXES
Ø
USER_INDEXES
Ø
ALL_IND-COLUMNS
Ø
DBA-IND_COLUMNS
Ø
USER_IND_COLUMNS
Ø
ALL_PART_INDEXES
Ø
DBA_PART_INDEXES
Ø
USER_PART_INDEXES
Ø
V$OBJECT_USAGE
SQL*PLUS COMMNANDS
These
commands does not require statement terminator and applicable to the sessions ,
those will be automatically cleared when session was closed.
BREAK
This
will be used to breakup the data depending on the grouping.
Syntax:
Break or bre [on <column_name>
on report]
COMPUTE
This
will be used to perform group functions on the data.
Syntax:
Compute or comp [group_function of column_name on breaking_column_name
or
report]
TTITLE
This
will give the top title for your report. You can on or off the ttitle.
Syntax:
Ttitle or ttit [left | center | right] title_name skip n other_characters
Ttitle or ttit [on or off]
BTITLE
This
will give the bottom title for your report. You can on or off the btitle.
Syntax:
Btitle or btit [left | center | right] title_name skip n other_characters
Btitle or btit [on or off]
Ex:
SQL> bre on
deptno skip 1 on report
SQL> comp
sum of sal on deptno
SQL> comp
sum of sal on report
SQL> ttitle
center 'EMPLOYEE DETAILS' skip1 center '----------------'
SQL> btitle
center '** THANKQ **'
SQL> select
* from emp order by deptno;
Output:
EMPLOYEE
DETAILS
-----------------------
EMPNO
ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- -------
-------------- --------
---------- ----------
7782 CLARK
MANAGER 7839 09-JUN-81
2450 10
7839 KING
PRESIDENT
17-NOV-81 5000
7934 MILLER CLERK 7782 23-JAN-82
1300
---------- **********
8750 sum
7369 SMITH
CLERK 7902 17-DEC-80 800 20
7876 ADAMS
CLERK 7788 23-MAY-87 1100
7902 FORD
ANALYST 7566 03-DEC-81 3000
7788 SCOTT
ANALYST 7566 19-APR-87 3000
7566 JONES
MANAGER 7839 02-APR-81 2975
---------- **********
10875 sum
7499
ALLEN SALESMAN 7698
20-FEB-81 1600 300 30
7698
BLAKE MANAGER 7839
01-MAY-81 2850
7654
MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7900
JAMES CLERK 7698 03-DEC-81 950
7844
TURNER SALESMAN 7698 08-SEP-81 1500 0
7521
WARD SALESMAN 7698
22-FEB-81 1250 500
---------- **********
9400 sum
----------
sum
29025
** THANKQ **
CLEAR
This
will clear the existing buffers or break or computations or columns formatting.
Syntax:
Clear or cle buffer | bre | comp | col;
Ex:
SQL> clear
buffer
Buffer cleared
SQL> clear
bre
Breaks cleared
SQL> clear
comp
Computes cleared
SQL> clear
col
Columns cleared
CHANGE
This
will be used to replace any strings in SQL
statements.
Syntax:
Change
or c/old_string/new_string
If
the old_string repeats many times
then new_string replaces the first
string only.
Ex:
SQL> select * from det;
select * from det
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
c/det/dept
1* select * from dept
SQL> /
DEPTNO DNAME LOC
----------
---------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH ALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
COLUMN
This
will be used to increase or decrease the width of the table columns.
Syntax:
Column or col <column_name>
format <num_format|text_format>
Ex:
SQL> col
deptno format 999
SQL> col
dname format a10
SAVE
This
will be used to save your current SQL statement as SQL Script file.
Syntax:
Save or sav <file_name>.[extension]
replace or rep
If
you want to save the filename with existing filename the you have to use
replace option.
By
default it will take sql as the
extension.
Ex:
SQL> save
ss
Created file ss.sql
SQL> save
ss replace
Wrote file ss.sql
EXECUTE
This
will be used to execute stored subprograms or packaged subprograms.
Syntax:
Execute or exec <subprogram_name>
Ex:
SQL> exec
sample_proc
SPOOL
This
will record the data when you spool on, upto when you say spool off. By default
it will give lst as extension.
Syntax:
Spool on | off | out | <file_name>.[Extension]
Ex:
SQL> spool
on
SQL>
select * from dept;
DEPTNO DNAME
LOC
--------- --------------
----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> spool
off
SQL> ed
on.lst
SQL>
select * from dept;
DEPTNO DNAME
LOC
--------- --------------
----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
spool off
LIST
This
will give the current SQL statement.
Syntax:
List or li [start_line_number]
[end_line_number]
Ex:
SQL> select
2 *
3 from
4 dept;
SQL>
list
1 select
2 *
3 from
4* dept
SQL> list 1
1* select
SQL> list 3
3* from
SQL> list 1
3
1
select
2
*
3* from
INPUT
This
will insert the new line to the current SQL
statement.
Syntax:
Input or in <string>
Ex:
SQL> select
*
SQL> list
1* select *
SQL> input
from dept
SQL> list
1 select *
2* from dept
APPEND
This
will adds a new string to the existing string in the SQL statement without any space.
Syntax:
Append or app <string>
Ex:
SQL> select
*
SQL> list
1* select *
SQL>
append from dept
1* select * from dept
SQL> list
1* select * from dept
DELETE
This
will delete the current SQL statement lines.
Syntax:
Delete or del <start_line_number>
[<end_line_number>]
Ex:
SQL> select
2 *
3 from
4 dept
5 where
6 deptno
7 >10;
SQL> list
1 select
2 *
3 from
4 dept
5 where
6 deptno
7* >10
SQL> del 1
SQL> list
1 *
2 from
3 dept
4 where
5 deptno
6* >10
SQL> del 2
SQL> list
1 *
2 dept
3 where
4 deptno
5* >10
SQL> del 2
4
SQL> list
1
*
2* >10
SQL> del
SQL> list
1
*
VARIABLE
This
will be used to declare a variable.
Syntax:
Variable or var <variable_name>
<variable_type>
Ex:
SQL>
var dept_name varchar(15)
SQL> select
dname into dept_name from dept where deptno = 10;
PRINT
This
will be used to print the output of the variables that will be declared at SQL level.
Syntax:
Print <variable_name>
Ex:
SQL> print
dept_name
DEPT_NAME
--------------
ACCOUNTING
START
This
will be used to execute SQL scripts.
Syntax:
start <filename_name>.sql
Ex:
SQL>
start ss.sql
SQL>
@ss.sql -- this will execute sql script files only.
HOST
This
will be used to interact with the OS
level from SQL.
Syntax:
Host [operation]
Ex:
SQL>
host
SQL>
host dir
SHOW
Using
this, you can see several commands that use the set command and status.
Syntax:
Show all | <set_command>
Ex:
SQL> show
all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT
statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
SQL> sho
verify
verify OFF
RUN
This
will runs the command in the buffer.
Syntax:
Run | /
Ex:
SQL> run
SQL> /
STORE
This
will save all the set command statuses in a file.
Syntax:
Store set <filename>.[extension] [create] | [replace] |
[append]
Ex:
SQL> store
set my_settings.scmd
Created file my_settings.scmd
SQL> store
set my_settings.cmd replace
Wrote file my_settings.cmd
SQL> store
set my_settings.cmd append
Appended file to my_settings.cmd
FOLD_AFTER
This
will fold the columns one after the other.
Syntax:
Column <column_name>
fold_after [no_of_lines]
Ex:
SQL> col
deptno fold_after 1
SQL> col
dname fold_after 1
SQL> col
loc fold_after 1
SQL> set
heading off
SQL> select
* from dept;
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
FOLD_BEFORE
This
will fold the columns one before the other.
Syntax:
Column <column_name>
fold_before [no_of_lines]
DEFINE
This
will give the list of all the variables currently defined.
Syntax:
Define [variable_name]
Ex:
SQL> define
DEFINE _DATE =
"16-MAY-07" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oracle" (CHAR)
DEFINE _USER =
"SCOTT" (CHAR)
DEFINE _PRIVILEGE =
"" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR =
"Notepad" (CHAR)
DEFINE _O_VERSION =
"Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 – Production With the Partitioning, OLAP and
Data Mining options" (CHAR)
DEFINE _O_RELEASE =
"1001000200" (CHAR)
No comments:
Post a Comment