|
 How to pass an array to a stored
procedure
Author of this Tip: Thomas Kyte
You want to declare an array EMPARRY like
the following ...
(emp_no number,
emp_dept varchar2
emp_salary number
emp_title varchar2)
.... and use it as a parameter in a stored proc like:
procedure emp_report(emp_arr EMPARRY)
To accomplish this, you just need to declare new types. You can have types that
are tables of a RECORD or types that are tables of SCALARS. PL/SQL use collection
types such as arrays, bags, lists, nested tables, sets, and trees. To support these
techniques in database applications, PL/SQL provides the datatypes TABLE and VARRAY,
which allow you to declare index-by tables, nested tables and variable-size arrays.
To create collections, you define a collection type, then declare variables of that
type. You can define TABLE and VARRAY types in the declarative part of any PL/SQL block,
subprogram, or package.
For nested tables, use the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
- type_name - is a type specifier used later to declare collections. For
nested tables declared within PL/SQL, element_type is any PL/SQL datatype except:
REF CURSOR
- element type - nested tables declared globally in SQL have additional restrictions
on the element type. They cannot use the following element types:
BINARY_INTEGER, PLS_INTEGERBOOLEANLONG, LONG RAWNATURAL, NATURALNPOSITIVE, POSITIVENREF
CURSORSIGNTYPESTRING
Create a Package demo_pkg with the overloaded procedure emp_report. One
uses EMPARRY as argument,
the other CHARARRY.
CREATE OR REPLACE PACKAGE demo_pkg
AS
-- Emparry keeps Rows of the Table EMP
TYPE emparray IS TABLE OF emp%rowtype INDEX BY
BINARY_INTEGER;
-- Chararray keeps just VARCHARs
TYPE chararray IS TABLE OF VARCHAR2(255) INDEX BY
BINARY_INTEGER;
-- Declare emp_report to use emparry as Argument
PROCEDURE emp_report(emp_arr IN emparray);
-- Overloaded emp_report to use chararray as
Argument
PROCEDURE emp_report(p_empno IN chararray,
p_deptno IN chararray,
p_sal IN chararray,
p_job IN chararray);
END;
/
CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
-- Define emp_report to use emparry as Argument
PROCEDURE emp_report(emp_arr IN emparray)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('output from: emp_report(emp_arr in
emparray)');
-- Loop through emparry
FOR I IN 1 .. emp_arr.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( 'EMPNO = ' ||
emp_arr(i).empno || ' DEPTNO = ' ||
emp_arr(i).deptno);
END LOOP;
END;
-- Overloaded emp_report to use chararray as
Argument
PROCEDURE emp_report(p_empno IN chararray,
p_deptno IN chararray,
p_sal IN chararray,
p_job IN chararray)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('output from: emp_report(p_empno in chararray,
...)');
-- Loop through chararray
FOR I IN 1 .. P_EMPNO.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( 'EMPNO = ' || P_EMPNO(I) || ' DEPTNO
= ' || P_DEPTNO(I) );
END LOOP;
END;
END;
/
Initializing and Referencing Collections
Until you initialize it, a nested table or varray is atomically null:
the collection itself is null, not its elements.
SET SERVEROUTPUT ON
DECLARE my_data demo_pkg.emparray;
BEGIN
-- Row 1 in emparray
my_data(1).empno := 1234;
my_data(1).deptno := 10;
-- Row 2 in emparray
my_data(2).empno := 4567;
my_data(2).deptno := 20;
demo_pkg.emp_report(my_data);
END;
/
output from: emp_report(emp_arr in emparray)
EMPNO = 1234 DEPTNO = 10
EMPNO = 4567 DEPTNO = 20
DECLARE
my_empnos demo_pkg.chararray;
my_deptno demo_pkg.chararray;
empty demo_pkg.chararray;
BEGIN
my_empnos(1) := 1234;
my_deptno(1) := 10;
my_empnos(2) := 4567;
my_deptno(2) := 20;
demo_pkg.emp_report(my_empnos,my_deptno,empty,empty);
END;
/
output from: emp_report(p_empno in chararray,
...)
EMPNO = 1234 DEPTNO = 10
EMPNO = 4567 DEPTNO = 20
The following collection methods help generalize code, make collections
easier to use, and make your applications easier to maintain:
- EXISTS
- COUNT
- LIMIT
- FIRST and LAST
- PRIOR and NEXT
- EXTEND
- TRIM
- DELETE
A collection method is a built-in function or procedure that operates
on collections and is called using dot notation. The syntax follows:
collection_name.method_name[(parameters)]
Collection methods cannot be called from SQL statements. Also, EXTEND
and TRIM cannot be used with associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST,
PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures. EXISTS, PRIOR,
NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts,
which are usually integers but can also be strings for associative arrays.
Only EXISTS can be applied to atomically null collections. If you apply another method to
such collections, PL/SQL raises COLLECTION_IS_NULL.
For more Information see: PL/SQL User's Guide and Reference
Understanding
Associative Arrays (Index-By Tables) in Oracle 9.2
Associative arrays are new in Oracle 9.2, they help you
represent data sets of arbitrary size, with fast lookup for an individual element
without knowing its position within the array and without having to loop through all
the array elements. It is like a simple version of a SQL table where you can retrieve
values based on the primary key. For simple temporary storage of lookup data, associative
arrays let you avoid using the disk space and network operations required for SQL
tables.
Because associative arrays are intended for temporary data rather than
storing persistent data, you cannot use them with SQL statements such as INSERT and
SELECT INTO. You can make them persistent for the life of a database session by
declaring the type in a package and assigning the values in a package body.
For example, here is the declaration of an associative array type, and one array of
that type, using keys that are strings:
SET SERVEROUTPUT ON
DECLARE
TYPE TyCity IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
l_city TyCity;
l_pop NUMBER;
l_first VARCHAR2(64);
l_last VARCHAR2(64);
BEGIN
-- Initialize Associative Array
l_city('Bern') := 20000;
l_city('Olten') := 750000;
-- Get Population for Olten
l_pop := l_city('Olten');
DBMS_OUTPUT.PUT_LINE('Olten has: ' || l_pop || ' habitants');
-- Insert more Value Pairs in Associative
Array
l_city('Thun') := 15000;
l_city('Zürich') := 2000000;
-- Get first City Name
l_first := l_city.FIRST;
DBMS_OUTPUT.PUT_LINE('First City is: ' || l_first);
-- Get last City Name
l_last := l_city.LAST;
DBMS_OUTPUT.PUT_LINE('Last City is: ' || l_last);
-- Get Population for last City
l_pop := l_city(l_city.LAST);
DBMS_OUTPUT.PUT_LINE('Last City has: ' || l_pop || ' habitants');
END;
/
Olten has: 750000 habitants
First City is: Bern
Last City is: Zürich
Last City has: 2000000 habitants
PL/SQL procedure successfully completed.
This example shows how to load a lookup table from the database into an
Associative Array which is fully kept in the memory for fast random access. First we
create the following table:
SQL> desc city
Name
Null? Type
-----------------------------------
PLZ
VARCHAR2(12)
NAME_D NOT NULL VARCHAR2(80)
We create the Package AssArrDemo with the Procedure
load_data to initialize the Associative Array. The Function check_plz
checks if there is a zip code (PLZ) for a given city name. Note that the index in the
Associative Array TyCity is of type city.name_d%TYPE which is a
VARCHAR(80). Here is the Package:
CREATE OR REPLACE PACKAGE AssArrDemo
AS
-- Declare Associative Array using CITY.NAME_D as the
index
-- Note that city.name_d%TYPE is a VARCHAR2 !!!!
TYPE TyCity IS TABLE OF city.name_d%TYPE INDEX BY
city.name_d%TYPE;
-- Variable of type Associative Array
AsCity TyCity;
-- Procedure and Function Declaration
PROCEDURE load_data;
FUNCTION check_plz (pCityName city.name_d%TYPE) RETURN city.plz%TYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY AssArrDemo
AS
-- Procedure to load Database Data into the Associative
Array
PROCEDURE load_data IS
CURSOR cLoad IS
SELECT plz, name_d FROM city;
BEGIN
FOR rLoad IN cLoad LOOP
AsCity(rLoad.name_d)
:= rLoad.plz;
END LOOP;
END load_data;
-- Function to lookup PLZ for given city name
FUNCTION check_plz (pCityName city.name_d%TYPE) RETURN city.plz%TYPE IS
BEGIN
RETURN AsCity(pCityName);
EXCEPTION
WHEN no_data_found THEN
RETURN ('PLZ not found for City: <' || pCityName ||
'>');
END check_plz;
END;
/
SET SERVEROUTPUT ON
DECLARE
BEGIN
AssArrDemo.load_data;
dbms_output.put_line('PLZ for Seftigen is: ' ||
AssArrDemo.check_plz('Seftigen'));
END;
/
PLZ for Seftigen is: 3136
PL/SQL procedure successfully completed.
How to use an OUTER join to replace
slow IN condition
A membership condition (IN, NOT IN) tests for
membership in a list or subquery. It's easy to implement, but usually the execution plan
chosen by the query optimizer is not as optimal as it can be if you replace the query by
an OUTER join.
An outer join extends the result of a simple
join. An outer join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other satisfy the
join condition.
The following example shows, how you can replace an
IN condition by an OUTER join. First create the following table and insert some
values;
DROP TABLE stock;
--
CREATE TABLE stock (
stock_id NUMBER(5) NOT NULL,
selled_year NUMBER(4),
customer_id NUMBER(5));
STOCK_ID SELLED_YEAR
CUSTOMER_ID
---------- ----------- -----------
1
1995 1
1
1995 2
1
1995 1
1
1995 2
3
1995 3
3
1995 3
4
1965 1
4
1965 1
5
2000 2
6
2000 1
7
2001 4
8
1995 1
Apply the following conditions to the above result set:
Show only those records with SELLED_YEAR >= 1993 and
CUST_ID's 1,2,3.
SELECT *
FROM stock st
WHERE (selled_year >= 1993)
AND(customer_id IN (1,2,3));
STOCK_ID SELLED_YEAR
CUSTOMER_ID
---------- ----------- -----------
1
1995 1
1
1995 2
3
1995 3
1
1995 1
1
1995 2
3
1995 3
5
2000 2
6
2000 1
8
1995 1
From this result set, do not show any records for
CUST_ID = 1 and
SELLED_YEAR = 1995.
SELECT *
FROM stock
WHERE selled_year = 1995
AND customer_id = 1;
STOCK_ID SELLED_YEAR
CUSTOMER_ID
---------- ----------- -----------
1
1995 1
1
1995 1
8
1995 1
If you subtract the result set in Condition 2 from the result set
in Condition 1 you will get the following final result set. The question is now, how to
get this result set in one single Step.
STOCK_ID
SELLED_YEAR CUSTOMER_ID
---------- ----------- -----------
3
1995 3
5
2000 2
6
2000 1
SQL> set autotrace on explain
SELECT DISTINCT *
FROM stock st
WHERE (selled_year >= 1993)
AND (customer_id IN (1,2,3))
AND stock_id NOT IN
(SELECT stock_id
FROM stock
WHERE selled_year = 1995
AND customer_id
= 1);
Oracle 9.2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 FILTER
3 2 TABLE ACCESS
(FULL) OF 'STOCK'
4 2 TABLE ACCESS
(FULL) OF 'STOCK'
SQL Server 2000
Execution Plan
----------------------------------------------------------
|--Sort(DISTINCT ORDER BY)
|--Nested Loops
|--Table
Scan(stock)
|--Table
Scan(stock)
SELECT DISTINCT st.*
FROM stock st
LEFT OUTER JOIN (SELECT stock_id
FROM stock
WHERE selled_year = 1995
AND customer_id = 1
)su
ON su.stock_id = st.stock_id
WHERE selled_year >= 1993
AND customer_id IN (1,2,3)
AND su.stock_id IS NULL;
Oracle 9.2
Execution Plan
------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 FILTER
3 2 HASH JOIN
(OUTER)
4 3 TABLE
ACCESS (FULL) OF 'STOCK'
5 3 TABLE
ACCESS (FULL) OF 'STOCK'
SQL Server 2000
set showplan_all on
Execution Plan
------------------------------------------------
|--Sort(DISTINCT ORDER BY)
|--Filter
|--Nested Loops
|--Table Scan(stock)
|--Table Scan(stock)
Both Databases Oracle and SQL-Server 2000 do not
generate the same execution
plan for the two SQL-Statements which have
of course the same result set. Usually the IN and NOT IN solutions are slower - but this
must be verified with real data, not only with a few records as shown in this
example. However it is worth to spend
the time to verify an measure the execution time.
How to randomly selecting
rows ?
If you need to randomly select one or more rows from one of your
tables, then you can use the following query.
As an example we want to show an employee, randomly selected from
the EMP table:
SELECT *
FROM (SELECT empno, ename
FROM emp
WHERE ename like '%'
ORDER BY DBMS_RANDOM.VALUE)
WHERE rownum <= 1;
EMPNO ENAME
---------- ----------
7566 JONES
If you need two employees, use:
SELECT *
FROM (SELECT empno, ename
FROM emp
WHERE ename like '%'
ORDER BY DBMS_RANDOM.VALUE)
WHERE rownum <= 2;
EMPNO ENAME
---------- ----------
7499 ALLEN
7844 TURNER
Displaying
the maximum value for every record
You want to display the maximum value for every row for some
columns in a table.
GREATEST returns the greatest of the list of exprs. All exprs after the first are
implicitly converted to the datatype of the first expr before the comparison. Oracle
compares the exprs using nonpadded comparison semantics. Character comparison is based on
the value of the character in the database character set. One character is greater than
another if it has a higher character set value. If the value returned by this function is
character data, then its datatype is always VARCHAR2.
Create the following table with some
values:
DROP TABLE tcol;
CREATE TABLE tcol (
recno NUMBER(2),
col1 NUMBER(2),
col2 NUMBER(2),
col3 NUMBER(2)
);
INSERT INTO tcol (recno,col1,col2,col3) VALUES (1,20,40,22);
INSERT INTO tcol (recno,col1,col2,col3) VALUES (2,50,10,25);
INSERT INTO tcol (recno,col1,col2,col3) VALUES (3,10,44,12);
INSERT INTO tcol (recno,col1,col2,col3) VALUES (4,22,90,65);
COMMIT;
Now you can create the following view:
DROP VIEW vcol;
CREATE VIEW vcol AS
SELECT recno, GREATEST(col1,col2,col3) max_val FROM tcol;
SELECT * FROM
vcol;
RECNO MAX_VAL
---------- ----------
1 40
2 50
3 44
4 90
Or, you can use an Inline View:
SELECT * FROM (
SELECT recno, GREATEST(col1,col2,col3) max_val
FROM tcol
);
RECNO
MAX_VAL
---------- ----------
1 40
2 50
3 44
4 90
Create your own Password
«Encryption» Function
For applications dealing with highly sensitive data, Oracle provides the
DBMS_OBFUSCATION_TOOLKIT PL/SQL package to encrypt and decrypt data, including string
inputs and raw inputs. The function is limited to selected algorithms, such as the Data
Encryption Standard (DES). Developers may not plug in their own encryption algorithms,
and the key length is also fixed.
Another solution is to hash or digest the data using the
GET_HASH_VALUE Function of the DBMS_UTILITY package.
This function computes a hash value for the given
string.
Syntax
DBMS_UTILITY.GET_HASH_VALUE (
name VARCHAR2,
base NUMBER,
hash_size NUMBER)
RETURN NUMBER;
Parameters
| name |
String to be hashed. |
| base |
Base value for the returned hash value to start
at. |
| hash_size |
Desired size of the hash table. |
Returns
A hash value based on the input string. For example,
to get a hash value on a string where the hash value should be between 1000 and 3047, use
1000 as the base value and 2048 as the hash_size value. Using a power of 2 for the
hash_size parameter works best.
select DBMS_UTILITY.GET_HASH_VALUE (
'zahn', 1000, POWER(2,11)
) "Hash Val" FROM dual;
Hash Val
----------
1154
To validate a username/password we take them, and hash it. This
results in a fixed length string of some bytes of data. We compare that to the stored
hash and if they match -- you are in, if not -- you are not.
So, for our password check function, we would simply glue the
USERNAME together with the supplied PASSWORD.
CREATE OR REPLACE FUNCTION hashit
(p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN
LTRIM (
TO_CHAR (
DBMS_UTILITY.GET_HASH_VALUE (
UPPER(p_username)||'/'||
UPPER(p_password),
1000000000,
POWER(2,30)
),
RPAD('X',29,'X')||'X'
)
);
END hashit;
/
So, we have a function digest that takes a username
and password, hashes it into 1 of 1073741824 different numeric values, adds 1000000000 to
it (to make it big) and turns it into HEX. This is what we would store in the database --
not the password. Now when the user presents a username/password, we digest it and
compare -- if they match, you get in, if not you do not.
SELECT hashit ('zahn','martin') FROM
dual;
HASHIT('ZAHN','MARTIN')
------------------------
3C5525AA
Getting Rows N through M of a Result
Set
Author of this Tip: Thomas Kyte
You would like to fetch data and sort it based on some field. As this query results into approx 100 records,
you would like to cut the result set into 4, each of 25
records and you would like to give
sequence number to each record.
SELECT *
FROM (SELECT A.*, ROWNUM rnum
FROM (your query including the
order by) A
WHERE ROWNUM <= MAX_ROWS )
WHERE rnum >= MIN_ROWS
/
Example
SELECT *
FROM (SELECT A.*, ROWNUM rnum
FROM (SELECT ename,dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
ORDER BY E.ENAME) A
WHERE ROWNUM <= 5)
WHERE rnum >= 2
/
ENAME
DNAME
RNUM
---------- -------------- ----------
ALLEN
SALES
2
BLAKE
SALES
3
CLARK
ACCOUNTING
4
FORD
RESEARCH
5
Configuring OS Authentication
on W2K to connect to Oracle
OS authentication is a very useful feature of Oracle. If you are unfamiliar with it,
basically what it does is allow the users to connect to the database by authenticating
their W2K username in the database. No password is associated
with an OPS$ account since it is assumed that OS authentication is sufficient. There are
many benifits to taking advantage of this:
- The user does not have to keep track of multiple ID’s and
passwords.
|
- The user can be forced to change his W2K password periodically
|
- The Oracle DBA does not have to keep track of
password changes.
|
- You can run scripts locally on your workstation, or on the server through
SQL*PLUS that do not contain ID and password.
|
Of course there must be disadvantages of this method. If the password for an NT
account becomes known, oracle access is then granted without another level of security.
The other disadvantage is that your configuration may not be set up correctly to support
this.
The Windows native authentication adapter works with Windows authentication protocols
to enable access to your Oracle9i database. Kerberos is the default authentication
protocol for Windows 2000.
If the user is logged on as a Windows 2000 domain user from a Windows 2000 computer,
then Kerberos is the authentication mechanism used by the NTS adapter.
If authentication is set to NTS on a standalone Windows 2000 or Windows NT 4.0
computer, ensure that Windows Service NT LM Security Support Provider is started.
If this service is not started on a standalone Windows 2000 or Windows NT 4.0 computer,
then NTS authentication fails. This issue is applicable only if you are running Windows
2000 or Windows NT 4.0 in standalone mode.
Client computers do not need to specify an authentication protocol when attempting a
connection to an Oracle9i database. Instead, Oracle9i database determines the protocol to
use, completely transparent to the user. The only Oracle requirement is to ensure that
parameter SQLNET.AUTHENTICATION_SERVICES contains nts in the SQLNET.ORA file located in
ORACLE_HOME\network\admin on both the client and database server:
SQLNET.AUTHENTICATION_SERVICES = (nts)
The W2K User «curia» on the Windows Client
Computer ARKUM wants to connect to Oracle 9i Database located on the W2K Server XEON
using Operating System Authentication handled by ARKUM.
Users need not enter a login-ID and password when using this
type of login.
The user account on the client ARKUM must match the user
account on the server XEON. The OPS$ account in the
database must be in the format OPS$username.
| 1. |
Create a user account «curia» on the Windows NT Client
ARKUM . |
| 2. |
Create an OPS$ account in the Oracle Database on XEON.
create user OPS$curia identified
externally;
grant connect, resource to OPS$curia;
alter user OPS$curia default tablespace tab;
alter user OPS$curia temporary tablespace temp;
|
| 3. |
By default you cannot connect through SQL NET, to establish this
feature you must set the INIT.ORA file
parameter REMOTE_OS_AUTHENT=TRUE on XEON
which will allow authentication of remote clients with
the value of OS_AUTHENT_PREFIX.
os_authent_prefix = "OPS$"
remote_os_authent = true
You might want to caution that "only people who do not
care about their data" would use this. The reason is that with REMOTE_OS_AUTHENT=TRUE, all I need to do
is plop a machine down on the network, create a named
account on this machine and I'm in.
We use REMOTE_OS_AUTHENT=TRUE in Oracle but the way we set it up is:
- Only low level user accounts use it.
- Use the protcol.ora file to restrict inbound
IP connects to the database to come only from specified data center
machines.
For information on protocol.ora read Understanding SQL*Net Release 2.3 Part
No. A424841. In appendix A they describe the protcol.ora file and tell how to
set up Validnode Verification. This allows you to specify from which hosts a
listener will accept inbound connections from.
|
| 4. |
Setup the TNSNAMES.ORA configuration file on ARKUM that contains service names to connect descriptors. This
file is used for the local naming method. The tnsnames.ora file typically resides
in $ORACLE_HOME/network/admin on UNIX platforms and ORACLE_HOME\network\admin
on Windows.
XEO2.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = xeon)(Port = 1522))
(CONNECT_DATA =
(SERVICE_NAME = XEO2)
(SERVER = DEDICATED)
)
)
|
Now run SQLPLUS on the client and login to the database as
follows:
How to kill
Oracle Process on Win NT/2000
Win NT/2000 is a thread based Operating System and not process based Operating
System like Unix. Oracle user and background processes are not multiple processes but,
threads with in oracle.exe process. 'orakill' is oracle provided utility to kill a
particular resource intensive thread under oracle.exe process.
For help type in at the DOS prompt
C:\>orakill /?
The following query can be used to get the sid, thread id of a
particular resource intensive thread.
select sid, spid as thread, osuser, s.program
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr;
SID
THREAD OSUSER PROGRAM
---------- ------------ -------- ------------
1
1264 SYSTEM ORACLE.EXE
2
1268 SYSTEM ORACLE.EXE
3
1272 SYSTEM ORACLE.EXE
4
1276 SYSTEM ORACLE.EXE
5
1296 SYSTEM ORACLE.EXE
6
1324 SYSTEM ORACLE.EXE
7
1396 SYSTEM ORACLE.EXE
8
1408 SYSTEM ORACLE.EXE
9
1484 SYSTEM ORACLE.EXE
10
1504 SYSTEM ORACLE.EXE
16
2160 zahn
sqlplusw.exe
11
1400 zahn
sqlplusw.exe
Say the query from user zahn is consuming lot
resources and it can be killed by running orakill utility as follows.
C:\>orakill 11 1400
Primary Keys and Unique
Columns in Oracle and
SQL-Server
Overview
The SQL-92 standard requires that all values in a primary key be
unique and that the column not allow null values. Both Oracle and Microsoft SQL
Server enforce uniqueness by automatically creating unique indexes whenever a PRIMARY KEY
or UNIQUE constraint is defined. Additionally, primary key columns are automatically
defined as NOT NULL. Only one primary key is allowed per table.
A SQL Server clustered index is created by default for a primary
key, though a nonclustered index can be requested. The Oracle index on primary keys can
be removed by either dropping or disabling the constraint, whereas the SQL Server index
can be removed only by dropping the constraint.
In either RDBMS, alternate keys can be defined with a UNIQUE
constraint. Multiple UNIQUE constraints can be defined on any table. UNIQUE constraint
columns are nullable. In SQL Server, a nonclustered index is created by default, unless
otherwise specified.
UNIQUE Indexes and NULL's
When migrating your application, it is important to note that
SQL Server allows only one row to contain the value NULL for the complete unique
key (single or multiple column index), and Oracle allows any number of rows to contain
the value NULL for the complete unique key.
Example
Let's make a small example to verify this
situation.
DROP TABLE departement
GO
CREATE TABLE departement (
dept INTEGER NOT NULL,
dname VARCHAR(30) NULL,
CONSTRAINT departement_pk
PRIMARY KEY CLUSTERED (dept),
CONSTRAINT dname_unique
UNIQUE NONCLUSTERED (dname)
)
GO
INSERT INTO departement (dept,dname) VALUES (1,'Sales')
INSERT INTO departement (dept,dname) VALUES (2,'Informatik')
INSERT INTO departement (dept,dname) VALUES (3,'Support')
INSERT INTO departement (dept,dname) VALUES (4,NULL)
INSERT INTO departement (dept,dname) VALUES (5,NULL)
GO
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'dname_unique'.
Cannot insert duplicate key in object 'departement'.
The statement has been terminated.
DROP TABLE departement;
CREATE TABLE departement (
dept INTEGER NOT NULL,
dname VARCHAR2(30) NULL,
CONSTRAINT departement_pk
PRIMARY KEY (dept)
USING INDEX TABLESPACE tab
PCTFREE 0 STORAGE (
INITIAL 10K NEXT 10K
MINEXTENTS 1 MAXEXTENTS UNLIMITED),
CONSTRAINT dname_unique
UNIQUE (dname)
USING INDEX TABLESPACE tab
PCTFREE 0 STORAGE (
INITIAL 10K NEXT 10K
MINEXTENTS 1 MAXEXTENTS UNLIMITED)
);
INSERT INTO departement (dept,dname) VALUES (1,'Sales');
INSERT INTO departement (dept,dname) VALUES (2,'Informatik');
INSERT INTO departement (dept,dname) VALUES (3,'Support');
INSERT INTO departement (dept,dname) VALUES (4,NULL);
INSERT INTO departement (dept,dname) VALUES (5,NULL);
COMMIT;
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
Conclusion
Oracle is NOT SQL-Server .... SQL-Server is NOT
Oracle.
Display Exact Match
First
Overview
In an application, someone may ask to display an exact match first. For example,
suppose that the following SQL:
SELECT * FROM tubes
WHERE prod LIKE '%Tube%' ORDER BY prod;
Returns the following Results:
ID PROD
---------- -------------------------------------------------
1 51H Tube Joint Compound
2 5X12 Flex Tube
3 725-4B / Hose-Male Tube
4 90 Deg Male Elbow Tube OD 3/8,
Tube Thread 1/4
7 Tail Tube
8 Tail Tube Strap
5 Tube
6 Tube Cutter Hose / Plastic
9 Wrench 12in Tube
But you want the exact match first:
ID PPROD
---------- ------------------------------------------------
5 Tube
1 51H Tube Joint Compound
2 5X12 Flex Tube
3 725-4B / Hose-Male Tube
4 90 Deg Male Elbow Tube OD 3/8,
Tube Thread 1/4
7 Tail Tube
8 Tail Tube Strap
6 Tube Cutter Hose / Plastic
9 Wrench 12in Tube
Here is a (nearly perfect) solution
SELECT id, DECODE(prod,'Tube', '
'||prod, prod) pprod
FROM tubes
WHERE prod LIKE '%Tube%' ORDER
BY pprod;
We get a space in front of 'Tube' with this
solution, but in many applications this can be filtered with TRIM later.
Reorganize very large
tables with the NOLOGGING Option
Overview
The Oracle NOLOGGING clause is a wonderful tool since it
often halves run times, but you need to remember the danger. For example, a common
practice is to reorganize very large tables is to use CREATE TABLE AS SELECT (CTAS)
commands:
SQL> CREATE TABLE
2 new_emp
3 TABLESPACE
4 tab
5 NOLOGGING
6 AS
7 SELECT * FROM emp;
Table created.
SQL> DROP TABLE emp;
Table dropped.
SQL> RENAME new_emp TO emp;
Table renamed.
However, you must be aware that a roll-forward through this operation is not possible,
since there are no images in the archived redo logs for this operation. Hence, you MUST
take a full backup after performing any NOLOGGING operation.
Watch out for the UNRECOVERABLE and NOLOGGING clause
Be very careful using UNRECOVERABLE clause (Oracle7) and the NOLOGGING
clause (Oracle8) when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS)
commands.
The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the
redo logs (this information is needed in the data dictionary), but all rows loaded into
the table during the operation are NOT sent to the redo logs.
With NOLOGGING in Oracle8, although you can set the NOLOGGING attribute for a table,
partition, index, or tablespace, NOLOGGING mode does not apply to every operation
performed on the schema object for which you set the NOLOGGING attribute.
Only the following operations can make use of the NOLOGGING option:
- alter table...move partition
- alter table...split partition
- alter index...split partition
- alter index...rebuild
- alter index...rebuild partition
- create table...as select
- create index
- direct load with SQL*Loader
- direct load INSERT
Many Oracle professionals use NOLOGGING because the actions runs fast because the
Oracle redo logs are bypassed. However, this can be quite dangerous if you need to
roll-forward through this time period during a database recovery.
It is not possible to roll forward through a point in time when an NOLOGGING operation
has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING,
or an NOLOGGING table load.
Oracle 10g new features
Overview
With all of the hoopla surrounding the impending release of Oracle10g, it is sometimes
difficult to differentiate between the truly valuable features and the marginal new
features.
On one hand, Oracle10g is more complex and robust than previous database versions with
 its wealth of new tuning options, sophisticated tuning tools and
enhanced tuning parameters. On the other hand, it is simpler than any Oracle database
before it. At your option, you can disable much of the flexibility (and complexity)
of Oracle and create an easy to maintain system that requires little experienced human
intervention.
This dichotomy is an amazing new feature and allows Oracle10g to be either robust or
simple. Much of the new 10g features address this issue.
10g provides a wealth of features that can be used to automate almost every
aspect of its database administration. It is important to note that these automation
features are optional, and they are not intended to replace standard DBA activities.
Rather, the Oracle10g automation features are aimed at shops that do not have the
manpower or expertise to manually perform the tasks.
So, if your 10g database does not require detailed, expert tuning, then the automated
features might be a good choice. They are targeted at these market segments:
Small shops: Small installations that can't afford a trained Oracle DBA. Shops with
over-worked DBAs: Large shops with hundreds of instances where the DBA does not have time
to properly tune each system.
Let's take a closer look at the 10g automation features.
Automation Features
Automatic Workload Repository defaults to a collection interval every 30 minutes and
collects data that is the foundation for all of the other self-tuning features. AWR is
very much like STATSPACK, especially the level-5 STATSPACK collection mechanism where top
SQL is collected every hour, based on your rolling thresholds for high-use SQL. In
addition to the SQL, AWR collects detailed run-time statistics on the top SQL (disk
reads, executions, consistent gets) and uses this information to adjust the rolling
collection threshold. This technique ensures that AWR always collects the most
resource-intensive SQL.
The Automatic Maintenance Tasks automate the routine tasks of refreshing statistics
when they become stale, and rebuilding indexes when they become sub-optimal. The AMTs are
scheduled for execution inside the new Oracle10g Unified Scheduler (US), providing
a primitive DBA functionality.
The Automatic Database Diagnostic Monitor analyzes the AWR data, much the same as a
human DBA would analyze a STATSPACK report. ADDM searches for lock-and-latch contention,
file I/O bottlenecks and SGA shortages just like a human DBA. The ADDM then triggers
automatic reconfiguration using the Automatic Storage Management (ASM) and Automatic
Memory Management (AMM) components.
The Automated Memory Management component manages the RAM
inside the System Global Area, much the same way as the automatic PGA management feature
in Oracle9i (pga_aggregate_target) automates the sort and hash areas with PGA RAM.
The ASM uses real-time workload data from AWR and changes the sizes of the shared pool
and data buffers according to the current workload.
The Automatic Storage Management feature allows for the automatic stripe-and-mirror
everywhere approach to be used to automatically load balance the disk I/O subsystem and
remove the need for the DBA to specify physical file locations when allocating a
tablespace.
Other Features
The SQL Tuning Advisor works with the Automatic Tuning Optimizer (ATO) to analyze
historical SQL workload (using data from the AWR), and generates recommendations for new
indexes and materialized views that will reduce the disk I/O associated with troublesome
SQL statements.
Server Generated Alerts (SGA) interfaces with the US to send e-mail messages when an
external problem is impeding Oracle performance. External problems might include a UNIX
mount point that is full, causing a failure of ASM files to extend or a RAM shortage with
the System Global Area.
Benefits to .NET
Oracle Database 10g is an enterprise-class, cluster-capable,
grid-ready database that supports all new features equally across all platforms,
including Windows server environments. In addition to the server side of the database
itself, Oracle has worked over the years to facilitate application-development
integration for Microsoft developers, especially those working in the .NET world. To this
end, Oracle provided Oracle Data Provider for .NET (ODP.NET), which provides
connectivity between the Oracle database and .NET applications. Many enhancements in the
latest release of ODP.NET further extend that support by providing significant
integration and performance improvements between .NET and Oracle Database 10g. This
"grid-aware" version of ODP.NET means .NET developers can take advantage of Oracle
Database 10g's grid capabilities.
ODP.NET now has enhanced support for Oracle XML DB. "That's
important," he says, "because many .NET developers use XML heavily in their applications.
Developers who are using .NET will be able to work with XML data much more easily,
because of our integration with XML DB through the new APIs and a new XMLType datatype in
ODP.NET."
ODP.NET now lets you manipulate XML data more easily within .NET,
using the XMLType that's been native to the Oracle database since Oracle Database
9i. It also lets you access relational or object-relational data as XML data in an Oracle
database instance from the Microsoft .NET environment—using Visual Studio .NET
development tools, such as Visual Basic, Visual C++, or Visual C#, for example—and
process the XML by using the facilities of the Microsoft .NET framework and saving any
changes back into the Oracle database as XML data.
ODP.NET includes many other new capabilities, including support for
nested cursors and PL/SQL associative arrays, allowing .NET developers to gain the
benefits of working in Oracle's own programming constructs without losing the ability to
use any part of .NET's functionality. For example, PL/SQL associative arrays (formerly
known as PL/SQL Index-By Tables), comparable to hash tables in some other programming
languages, are sets of key-value pairs that can represent data sets of arbitrary size;
associative arrays can provide fast lookup of individual elements in an array without
knowing their positions within the array or having to loop through all the array
elements.
Another improvement in Oracle Database 10g available to ODP.NET
developers is the new IEEE-compliant datatypes FLOAT and DOUBLE. Supported in ODP.NET,
they take up less storage space, are faster than the other number datatypes available in
Oracle, and facilitate more-direct handling of FLOAT and DOUBLE datatypes between host
variables and stored data.
Finally, there are numerous other performance improvements,
in terms of both performance and data retrieval—particularly in
retrieving number data and LOBs. For example, in prior releases, developers had limited
control over retrieving LOB data, "but with this release, there's a new property in
ODP.NET, InitialLOBfetchSize, that allows you to optimize LOB retrieval to suit
your needs. For example, a European government agency's geographic information system
(GIS) application that accesses tens of thousands of rows of LOB data gained a 17-fold
improvement in its application speed, simply by replacing the ODP.NET driver with the
newer version.
Overview
We have tested Gentoo Linux / Oracle 9.2 at work. Gentoo is a pretty nice distribution,
and provides a useful mechanism called "portage" for compiling software from source and
keeping things up to date. In general, the linux machine seems a lot faster now compared
to the previous Redhat binary image. You have a lot of control over what's installed by
default, so it's also using far less disk space.
Although Gentoo is not an officially supported Linux distro for
Oracle, it works without problems. We did run into a few issues during
installation.
Installation
Install libcompat. You can emerge this like so (as user
root):
emerge sys-libs/lib-compat
gcc 2.95 has to be the default gcc version in order to install
successfully. Using a later version resulted in the following error: Error in invoking
target ioracle of makefile
/opt/product/oracle/9.2.0/ctx/lib/in_rdbms.mk.
Verify the version you're using with:
gcc --version
We got returned
gcc (GCC) 3.2.3 20030422
(Gentoo Linux
1.4.3.2.3-r2, propolice)
To get the Oracle installation to complete, we installed gcc
2.95
cd /usr/portage/sys-devel/gcc
Check for the exact emerge name of gcc 2.95
emerge
/usr/portage/sys-devel/gcc/gcc-2.95.3-r8.ebuild
Then temporarily changed /usr/bin/gcc into a symbolic
link
mv /usr/bin/gcc /usr/bin/gcc-backup
ln -s /usr/i686-pc-linux-gnu/gcc-bin/2.95/gcc \
/usr/bin/gcc
After installation was complete, restore the original
version:
rm /usr/bin/gcc
mv /usr/bin/gcc-backup /usr/bin/gcc
During installation, we got the following error: Error in invoking
target install of makefile /opt/oracle/product/9.2.0/ctx/lib/ins_ctx.mk. It's documented
on Metalink as Note 191587.1, and you can work around it like this:
Modify the file $ORACLE_HOME/ctx/lib/ins_ctx.mk changing
ctxhx: $(CTXHXOBJ)
$(LINK) $(CTXHXOBJ) $(INSO_LINK)
to
ctxhx: $(CTXHXOBJ)
$(LINK) -ldl $(CTXHXOBJ) $(INSO_LINK)
After fixing the above, installation was still failing with an
error in ins_ctx.mk. It turns out this is caused by bug 2037255 in Oracle Text. If you
don't need Oracle Text, just click Ignore on the error dialog.
The whole rest of the installation is exactly the same as on any
other Linux Distribution. A short installation guide can be found here.
Author of this Tip: Thomas Kyte
Overview
If you write
shell scripts that invoke SQL*Plus and other tools from the
command line, you put the username
and password on the command line. We don't like
the fact that the "ps" command can display the command line of the process to other users, since it will reveal the username and
password for this account! How can we mask this
information from "ps"?
OS Authentication
The favorite solution to this is to use an
"identified externally" account.
For example, we have set:
NAME
TYPE VALUE
------------------------------ ------- --------------------
os_authent_prefix
string ops$
in the INIT.ORA, we then:
create user ops$zahn identified
externally;
This lets us:
$ id
uid=400(zahn) gid=400(dba)
$ sqlplus /
SQL> show user
USER is "OPS$ZAHN"
We do not need a username password anymore
(we can still use them but we can
always use / to log in). This is perfect for cron jobs, at jobs and sysadmin accounts, but not for client application connections.
Environment Variables
This is useful for Scripts, look at the following
example:
#!/bin/bash
# Let this be you env variables.
export APP_USER=scott
export APP_PASS=tiger
export APP_SID=GEN1
# Here is the script with a execute
permission.
sqlplus << END_OF_SQL
${APP_USER}/${APP_PASS}@${APP_SID}
select * from user_tables;
END_OF_SQL
exit $?
If you are
on solaris, fire up your script and then go:
/usr/ucb/ps
-auxwwee | grep APP_PASS
and see what you see ... ps can dump the ENVIRONMENT as
well. (very handy trick to know, can be useful to see a processes environment --
but for you -- it exposes the password.
Overview
Normally we use FOR UPDATE
NOWAIT to acquire a lock on rows. This statement either locks all the selected
rows or the control is returned without acquiring any lock (i.e. even on rows which are
available for locking) after throwing an exception.
For Update SKIP
LOCKED
But there is an feature in Oracle Database, the clause FOR
UPDATE SKIP LOCKED, which can be used to lock rows that are available for locking
and skip the rows that have been locked by other sessions. This statement returns the
control back without throwing an exception, even if all the rows are locked by another
session.
To illustrate, we open two sessions. In the first
session, we lock the row with deptno as 10 using FOR UPDATE
NOWAIT.
SELECT * FROM dept
WHERE deptno = 10
FOR UPDATE NOWAIT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
NEW YORK
In the second session, we try to lock two rows
(deptno 10 and 20) from the table dept using FOR UPDATE NOWAIT. An exception is thrown
after executing the following statement because one of the row (i.e. deptno 10) out of
the selected list is already locked by session 1.
SELECT * FROM dept
WHERE deptno IN (10,20)
FOR UPDATE NOWAIT;
SELECT * FROM dept WHERE deptno IN (10,20)
FOR UPDATE NOWAIT
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Now we again try to lock two rows (deptno(s) 10 and 20) from
the table dept but using the clause FOR UPDATE SKIP LOCKED instead of FOR UPDATE NOWAIT.
As you can see the following statement has
1. returned the control without throwing an exception
2. acquired lock on the row (i.e. deptno 20) which is available for locking
3. skipped the row (i.e. deptno 10) that has been locked already by session 1
SELECT * FROM dept
WHERE deptno IN (10,20)
FOR UPDATE SKIP LOCKED;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH
DALLAS
Overview
Although you can enumerate each field of a PL/SQL record when inserting or updating
rows in a table, the resulting code is not especially readable or maintainable. Instead,
you can use PL/SQL records directly in these statements. The most convenient technique is
to declare the record using a %ROWTYPE attribute, so that it has exactly the same fields
as the SQL table.
Example
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
emp_rec.eno := 1500;
emp_rec.ename := 'Steven Hill';
emp_rec.sal := '40000';
--
-- A %ROWTYPE value can fill
-- in all the row fields.
--
INSERT INTO emp VALUES emp_rec;
--
-- The fields of a %ROWTYPE can
-- completely replace the table columns.
--
UPDATE emp SET ROW = emp_rec WHERE eno = 100;
END;
/
Overview
In complex queries that process the same subquery multiple times, you might be tempted
to store the subquery results in a temporary table and perform additional queries against
the temporary table. The WITH clause lets you factor out the subquery, give it a
name, then reference that name multiple times within the original complex query.
This technique lets the optimizer choose how to deal with the subquery results --
whether to create a temporary table or inline it as a view.
For example, the following query joins two tables and computes the aggregate SUM(SAL)
more than once. The bold text represents the parts of the query that are repeated.
The WITH clause
SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING
SUM(sal) >
(
SELECT SUM(sal) * 1/3
FROM emp, dept
WHERE emp.deptno = dept.deptno
)
ORDER BY SUM(sal) DESC;
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 10875
SALES
9400
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 SORT (GROUP
BY)
4 3 MERGE
JOIN
5
4 SORT (JOIN)
6
5 TABLE ACCESS
(FULL) OF 'DEPT'
7
4 SORT (JOIN)
8
7 TABLE ACCESS
(FULL) OF 'EMP'
9 2 SORT
(AGGREGATE)
10 9 MERGE
JOIN
11 10 SORT
(JOIN)
12
11 TABLE ACCESS
(FULL) OF 'DEPT'
13 10 SORT
(JOIN)
14
13 TABLE ACCESS
(FULL) OF 'EMP'
You can improve the query by doing the subquery once, and referencing it at the
appropriate points in the main query. The bold text represents the common parts of the
subquery, and the places where the subquery is referenced.
WITH
summary AS
(
SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname
)
SELECT dname, dept_total
FROM summary
WHERE dept_total >
(
SELECT SUM(dept_total) * 1/3
FROM summary
)
ORDER BY dept_total DESC;
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 10875
SALES
9400
Execution Plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 2 RECURSIVE EXECUTION OF
'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2 SORT (ORDER BY)
4 3 FILTER
5 4
VIEW
6
5 TABLE ACCESS (FULL) OF
'SYS_TEMP'
7 4 SORT
(AGGREGATE)
8
7 VIEW
9
8 TABLE ACCESS
(FULL) OF 'SYS_TEMP'
Overview
When you update a column in joined tables, the value of some
records are changed into the value NULL, even if some records should not be updated. To
demonstrate this behaviour with Oracle9i, look at the following example:
DROP TABLE t1;
CREATE TABLE t1 (
key INT,
col1 VARCHAR2(25)
);
DROP TABLE t2;
CREATE TABLE t2 (
key INT,
value VARCHAR2(25),
col2 INT
);
INSERT INTO t1 VALUES (100, 'Original Data');
INSERT INTO t1 VALUES (200, 'Original Data');
INSERT INTO t2 VALUES (100, 'New Data', 1 );
COMMIT;
SELECT * FROM t1;
KEY COL1
---------- -------------------------
100 Original Data
200 Original Data
VARIABLE other_value NUMBER
EXEC :other_value := 1
UPDATE t1 B
SET col1 = (SELECT value
FROM t2
O
WHERE B.key =
O.key
AND
O.col2 = :other_value);
2 rows updated.
<==== !
SELECT * FROM t1;
KEY COL1
---------- -------------------------
100 New Data
200
The first row (KEY = 100) was updated as desired,
but the second row (KEY = 200) was updated with NULL, not as expected, why?
Key Preserved Concept
in Oracle
In the example above we update a join. We can
only modify the columns in one of the tables ( T1 ) and the other tables (
T2 ) we are NOT modifying must be "key preserved".
That is - we must be able to verify that at most one
record will be returned when we join T1 to this other table T2. In order to
do that, key in T2 must either be a primary key or have a unique constraint
applied to it.
DROP TABLE t2;
CREATE TABLE t2 (
key INT PRIMARY KEY,
value VARCHAR2(25),
col2 INT
);
Drop and recreate the tables T1 and T2 and insert
the same values, then apply the following UPDATE using an inline view:
UPDATE
(SELECT col1, value
FROM t1, t2
WHERE t1.key = t2.key
AND t2.col2 = :other_value)
SET col1 = value
/
1 row updated. <====
!
SELECT * FROM t1;
KEY COL1
---------- -------------------------
100 New Data
200 Original Data
Row 2 (KEY = 200) is now untouched and only the rows
we wanted are updated.
Another solution
will work with no constraints on anything -- you do not need the primary key/unique
constraint on T2 (but you
better be sure the subquery returns 0 or 1 records!). It is very much like our update,
just has a where clause so that only rows that we find matches for are actually
updated.
DROP TABLE t1;
CREATE TABLE t1 (
key INT,
col1 VARCHAR2(25)
);
DROP TABLE t2;
CREATE TABLE t2 (
key INT,
value VARCHAR2(25),
col2 INT
);
INSERT INTO t1 VALUES (100, 'Original Data');
INSERT INTO t1 VALUES (200, 'Original Data');
INSERT INTO t2 VALUES (100, 'New Data', 1 );
COMMIT;
UPDATE t1
SET col1 = (SELECT value
FROM t2
WHERE t1.key = t2.key
AND col2 = :other_value)
WHERE EXISTS (SELECT value
FROM t2
WHERE t1.key = t2.key
AND col2 = :other_value)
/
1 row updated.
SELECT * FROM t1;
KEY COL1
---------- -------------------------
100 New Data
200 Original Data
Conclusion
The concept of a key-preserved table is fundamental
to understanding the restrictions on modifying joins (join views). A table is key
preserved if every key of the table can also be a key of the result of the join. So, a
key-preserved table has its keys preserved through a join.
More Information about this concept can be found in
the
Oracle Database Administrator's Guide.
Overview
Some applications make extensive use of hierarchical data such as an
organization chart, a bill of material in a manufacturing and assembly plant, or a family
tree. These types of information are most conveniently represented in a tree structure.
However, such data can be easily fit into a relational table by using a self-referential
relationship.
Oracle provides some useful extensions to ANSI SQL to manipulate hierarchical data
represented in a relational table. Up to Oracle9i, Oracle’s hierarchical extensions
include the START WITH … CONNECT BY clause, the PRIOR operator, and the LEVEL
pseudo-column. The following example lists the employees in a hierarchical order and
indents the subordinates under an employee:
column EmpName format a30
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
/
EMPNAME
EMPNO MGR
------------------------------ ---------- ----------
KING
7839
JONES
7566 7839
SCOTT
7788 7566
ADAMS
7876 7788
FORD
7902 7566
SMITH
7369 7902
BLAKE
7698 7839
ALLEN
7499 7698
WARD
7521 7698
MARTIN
7654 7698
TURNER
7844 7698
JAMES
7900 7698
CLARK
7782 7839
MILLER
7934 7782
New Features
The new hierarchical query features in Oracle Database 10g are:
New Operator
CONNECT_BY_ROOT
New Pseudocolumns
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
New Function
SYS_CONNECT_BY_PATH
(Oracle9i)
New Keywords
NOCYCLE
SIBLINGS (Oracle9i)
CONNECT_BY_ROOT
The CONNECT_BY_ROOT operator, when applied to a column, returns the value for that
column for the root row. The following example illustrates how the CONNECT_BY_ROOT
operator is used:
SELECT ename, CONNECT_BY_ROOT ename "Top Manager"
FROM emp
START WITH mgr = 7839
CONNECT BY PRIOR empno = mgr
/
ENAME Top Manager
---------- -----------
JONES JONES
SCOTT JONES
ADAMS JONES
FORD JONES
SMITH JONES
BLAKE BLAKE
ALLEN BLAKE
WARD BLAKE
MARTIN BLAKE
TURNER BLAKE
JAMES BLAKE
CLARK CLARK
MILLER CLARK
In this example, the organization tree is built by starting with the rows that have
MGR = 7839. This means that anyone whose manager is 7839 will
be considered a root for this query. Now, all the employees who come under the
organizations under these roots will be displayed in the result set of this query along
with the name of their top-most manager in the tree. The CONNECT_BY_ROOT operator
determines the top-most node in the tree for a given row.
NOCYCLE
Cycles are not allowed in a true tree structure. But some hierarchical data may
contain cycles. In a hierarchical structure, if a descendant is also an ancestor, it is
called a cycle. It is sometimes difficult to identify cycles in hierarchical data. The
hierarchical construct “START WITH … CONNECT BY … PRIOR” will
report an error if there is a cycle in the data.
To allow the “START WITH … CONNECT BY … PRIOR” construct to
work properly even if cycles are present in the data, Oracle Database 10g provides a new
keyword, NOCYCLE. If there are cycles in the data, you can use the NOCYCLE keyword in the
CONNECT BY clause, and you will not get the error mentioned earlier.
The test data we have in the EMP table doesn’t have a
cycle. To test the NOCYCLE feature, let’s introduce a cycle into the existing
EMP data, by updating the MGR column
of the top-most employee (KING with EMPNO=7839) with the
EMPNO of one of the lowest level employees (MARTIN with
EMPNO = 7654).
UPDATE emp
SET mgr = 7654
WHERE mgr IS NULL;
COMMIT;
column EmpName format a30
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
/
EMPNAME
EMPNO MGR
------------------------------ ---------- ----------
KING
7839 7654
JONES
7566 7839
SCOTT
7788 7566
ADAMS
7876 7788
FORD
7902 7566
SMITH
7369 7902
BLAKE
7698 7839
ALLEN
7499 7698
WARD
7521 7698
MARTIN
7654 7698
KING
7839 7654
JONES
7566 7839
SCOTT
7788 7566
ADAMS
7876 7788
FORD
7902 7566
ERROR:
ORA-01436: CONNECT BY loop in user data
Besides the error, note that the whole tree starting with KING starts
repeting under MARTIN. This is erroneous and confusing. The NOCYCLE keyword can be used
in the CONNECT BY clause to get rid of this error:
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH empno = 7839
CONNECT BY NOCYCLE PRIOR empno = mgr
/
EMPNAME EMPNO MGR
------------------------------ ---------- ----------
KING 7839 7654
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
The above query recognizes that there is a cycle and ignore the cycle (as an impact of
the NOCYCLE keyword), and returns the rows as if there were no cycle.
CONNECT_BY_ISCYCLE
It is sometimes difficult to identify cycles in hierarchical data. Oracle 10g’s
new pseudocolumn CONNECT_BY_ISCYCLE can help you identify the cycles in the data easily.
The CONNECT_BY_ISCYCLE can be used only in conjunction with the NOCYCLE keyword in a
hierarchical query. The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has
a child which is also its ancestor; otherwise it returns 0.
column EmpName format a15
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr,
CONNECT_BY_ISCYCLE
FROM emp
START WITH empno = 7839
CONNECT BY NOCYCLE PRIOR empno = mgr
/
EMPNAME EMPNO MGR CONNECT_BY_ISCYCLE
--------------- ---------- ---------- ------------------
KING 7839 7654 0
JONES 7566 7839 0
SCOTT 7788 7566 0
ADAMS 7876 7788 0
FORD 7902 7566 0
SMITH 7369 7902 0
BLAKE 7698 7839 0
ALLEN 7499 7698 0
WARD 7521 7698 0
MARTIN 7654 7698 1
TURNER 7844 7698 0
JAMES 7900 7698 0
CLARK 7782 7839 0
MILLER 7934 7782 0
CONNECT_BY_ISLEAF
For correct results in the subsequent queries, we should revert the data back to its
original state by rolling back (if you have not committed) the earlier change we did to
force a cycle in the data. If you have already committed the change, then update the
MGR for KING to NULL.
In a tree structure, the nodes at the lowest level of the tree are referred to as leaf
nodes. Leaf nodes have no children. CONNECT_BY_ISLEAF is a pseudocolumn that returns 1 if
the current row is a leaf, and returns 0 if the current row is not a leaf.
UPDATE emp
SET mgr = NULL
WHERE empno = 7839;
COMMIT;
column EmpName format a15
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr, CONNECT_BY_ISLEAF
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
/
EMPNAME
EMPNO MGR CONNECT_BY_ISLEAF
--------------- ---------- ---------- -----------------
KING
7839
0
JONES
7566
7839
0
SCOTT
7788
7566
0
ADAMS
7876
7788
1
FORD
7902
7566
0
SMITH
7369
7902
1
BLAKE
7698
7839
0
ALLEN
7499
7698
1
WARD
7521
7698
1
MARTIN
7654
7698
1
TURNER
7844
7698
1
JAMES
7900
7698
1
CLARK
7782
7839
0
MILLER
7934
7782
1
SYS_CONNECT_BY_PATH
The SYS_CONNECT_BY_PATH function was introduced in Oracle9i. However, it makes sense
to discuss it along with the enhancements in Oracle Database 10g. The SYS_CONNECT_BY_PATH
is function takes two arguments — a column name, and a character string — and
returns the value of the column from the root node to each node, separated by the
character string.
SELECT SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
/
Path
-----------------------------
KING
/KING/JONES
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/JAMES
/KING/CLARK
/KING/CLARK/MILLER
ORDER SIBLINGS BY
The SIBLINGS keyword was introduced in Oracle9i. However, it makes sense to discuss it
along with the enhancements in Oracle Database 10g. A hierarchical query with a
“START WITH … CONNECT BY … PRIOR … “ construct displays the
results in an arbitrary order, as shown in the following example:
column EmpName format a30
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
/
EMPNAME
EMPNO MGR
------------------------------ ---------- ----------
KING
7839
JONES
7566 7839
SCOTT
7788 7566
ADAMS
7876 7788
FORD
7902 7566
SMITH
7369 7902
BLAKE
7698 7839
ALLEN
7499 7698
WARD
7521 7698
MARTIN
7654 7698
TURNER
7844 7698
JAMES
7900 7698
CLARK
7782 7839
MILLER
7934 7782
As always, you can use an ORDER BY clause to order the result rows in the way you
want. However, in this case, an ORDER BY clause can destroy the hierarchical
layers of the displayed data, as shown in the following example:
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY ename
/
EMPNAME
EMPNO MGR
--------------- ---------- ----------
ADAMS
7876 7788
ALLEN
7499 7698
BLAKE
7698 7839
CLARK
7782 7839
FORD
7902 7566
JAMES
7900 7698
JONES
7566 7839
KING
7839
MARTIN
7654 7698
MILLER
7934 7782
SCOTT
7788 7566
SMITH
7369 7902
TURNER
7844 7698
WARD
7521 7698
As you can see from the above output, it is impossible to identify the hierarchical
relationship between the rows. To resolve this problem, Oracle Database 10g has
introduced a new keyword SIBLINGS, that you can use in an ORDER BY clause, and order the
result set properly.
SELECT RPAD(' ',2*LEVEL,' ')||ename EmpName, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename
/
EMPNAME
EMPNO MGR
--------------- ---------- ----------
KING
7839
BLAKE
7698 7839
ALLEN
7499 7698
JAMES
7900 7698
MARTIN
7654 7698
TURNER
7844 7698
WARD
7521 7698
CLARK
7782 7839
MILLER
7934 7782
JONES
7566 7839
FORD
7902 7566
SMITH
7369 7902
SCOTT
7788 7566
ADAMS
7876 7788
In the above output, BLAKE, CLARK and JONES are siblings, and they are displayed in
the ascending order. So are BLAKE’s children – ALLEN, JAMES, MARTIN, TURNER
and WARD.
Conclusion
Oracle Database 10g enhances the already powerful hierarchical query features of the
Oracle database. Among the new features are the easy ways to identify leafs and cycles in
the data. The ordering of siblings provides a great way to improve the readability of the
result sets. Developers who are familiar with Oracle’s hierarchical query
constructs will find these features very useful.
Overview
The pseudocolumn ROWNUM is available since Oracle
versions 7 and it often leads to wrong results in combination with ORDER BY. For each row
returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in
which Oracle selects the row from a table or set of joined rows. The first row selected
has a ROWNUM of 1, the second has 2, and so on.
You can use the ROWNUM pseudocolumn to limit the number of rows returned by a query to
5:
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6;
EMPNO
ENAME JOB
MGR HIREDATE SAL
---------- -------- --------- ------- --------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600
7521 WARD
SALESMAN 7698 22-FEB-81
1250
7566 JONES
MANAGER 7839 02-APR-81
2975
7654 MARTIN SALESMAN
7698 28-SEP-81 1250
ORDER BY and ROWNUM - Wrong Result!
If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by
the ORDER BY clause.
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6
ORDER BY Sal;
EMPNO
ENAME JOB
MGR HIREDATE SAL
---------- -------- --------- ------- --------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800
7521 WARD
SALESMAN 7698 22-FEB-81
1250
7654 MARTIN SALESMAN
7698 28-SEP-81 1250
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600
7566 JONES
MANAGER 7839 02-APR-81
2975
Use Inline Views - Correct Result!
Hups - Because the ROWNUM is assigned upon
retrieval, it is assigned prior to any sorting! This is opposite to the result you
would get in SQL Server using the TOP clause. In order to select employees with the
highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is
the syntax for a top-N query where N = 5.
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate,
Sal
FROM Emp
ORDER BY NVL(Sal, 0) DESC)
WHERE ROWNUM < 6;
EMPNO
ENAME JOB
MGR HIREDATE SAL
---------- -------- --------- ------- --------- ----------
7839 KING
PRESIDENT
17-NOV-81 5000
7788 SCOTT
ANALYST 7566 09-DEC-82
3000
7902 FORD
ANALYST 7566 03-DEC-81
3000
7566 JONES
MANAGER 7839 02-APR-81
2975
7698 BLAKE
MANAGER 7839 01-MAY-81
2850
We used the NVL() function to sort the expression because
sorting just by Emp_Salary would have put all records with NULL salary before those with
the highest salaries, and that's not what we wanted to achieve.
ROWNUM instead of Subquery
Sometimes it is
worth to rewrite a query to get a better performance - one such
example shows the power of ROWNUM, to eliminate a subquery. Create the following table
and fill it with some random data.
CREATE TABLE bigtable (
id NUMBER,
weight NUMBER,
adate DATE
);
INSERT INTO bigtable (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
Now look at the following query, which uses a
subquery
SELECT MAX (weight) weight
FROM bigtable
WHERE id = 345
AND adate =
(SELECT MAX (adate)
FROM bigtable
WHERE id = 345);
WEIGHT
----------
1650589959
The same result can be accomplish using ROWNUM and an inline
view.
SELECT weight FROM (SELECT weight
FROM bigtable
WHERE id = 345
ORDER BY id DESC,
adate DESC,
weight DESC)
WHERE ROWNUM = 1;
WEIGHT
----------
1650589959
Conclusion
The inline view is a construct in Oracle SQL where you can place a query in the SQL
FROM, clause, just as if the query was a table name. A common use for in-line views in
Oracle SQL is to simplify complex queries by removing join operations and condensing
several separate queries into a single query.
The ROWNUM pseudocolumn returns a number indicating the
order in which Oracle selects the row from a table or set of joined rows.
The combination of ROWNUM and inline views is often a solution for
an alternative way to perform a query.
Overview
The inline view is a construct in Oracle SQL where
you can place a query in the SQL FROM clause, just as if the query was a table
name.
A common use for in-line views in Oracle SQL is to
simplify complex queries by removing join operations and condensing several separate
queries into a single query.
In ANSI standard SQL, it is quite difficult to
compare two result sets that are summed together in a single query, and this is a
common problem with Oracle SQL where specific values must be compared to a summary.
Without the use of an in-line view, several separate SQL queries would need to be
written, one to compute the sums from each view and another to compare the intermediate
result sets.
Inline Views
As an example of
an in-line view look at the following SELECT statement
to show the amount of free space and used space within all Oracle
tablespaces. Let’s take a close look at this SQL to see how it works. Carefully
note that the FROM clause in this SQL query specifies two sub-queries that perform
summations and grouping from two standard views, DBA_DATA_FILES, and DBA_FREE_SPACE.
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING
"%|Used"
COLUMN name FORMAT
a16 HEADING "Tablespace Name"
COLUMN total FORMAT 999,999,999 HEADING "Total"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
BREAK ON report
COMPUTE sum OF total ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc
total,
kbytes_alloc-NVL(kbytes_free,0)
used,
NVL(kbytes_free,0)
free,
((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100
pct_used
FROM ( SELECT SUM(bytes)/1024 Kbytes_free,
tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a,
( SELECT SUM(bytes)/1024 Kbytes_alloc,
tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
/
%
Tablespace Name
Total
Used Free Used
---------------- ------------ ------------ ------------ ------
IDX
819,264 1,600
817,664 .2
SYSAUX
819,200 243,648
575,552 29.7
SYSTEM
819,200 191,808
627,392 23.4
TAB
819,264 14,400
804,864 1.8
UNDO
204,800 8,896
195,904 4.3
USERS
10,304
3,136 7,168 30.4
------------ ------------ ------------
sum
3,492,032 463,488 3,028,544
The statement compares the sum of the total space within
each tablespace to the sum of the free space within each tablespace. Note, that the summation is done in the Inline View.
Overview
If you're in an environment where you want to manage
all of your Oracle databases from one place and not have to log on to each host, you must
do it via a network connection. For example to remotely administer RMAN through a network
connection, you need such an environment.
Enable remote Administration
You have to to two things
-
Create a password file on each target
database.
-
Enable remote logins for password file
users.
To create the password file, as the Oracle software
owner or as a member of the DBA group:
$ orapwd file=sidname
password=password entries=n
There are three user-provided variables in this
example:
-
sidname: The SID of the target
instance
-
password: The password to be used when
you connect a user SYS with SYSDBA privilege.
-
n: The maximum number of schemas allowed
in the password files.
For example, say that you have an instance named
AKI1, that you want the password to be goofi, and that you want at the most
30 entries in the password file. Logon to the remote database machine an
enter:
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwAKI1 password=goofi entries=30
The resulting password file is named
orapwAKI1 and is in the $ORACLE_HOME/dbs directory.
After you create a password file, you need to enable
remote logins. To do this, set the instance's REMOTE_LOGIN_PASSWORDFILE initialization
parameter in INIT.ORA to exclusive, as shown:
remote_login_passwordfile =
exclusive
Setting this parameter to exclusive signifies that
only one database can use the password file and that users other than sys and internal
can reside in it. You can now use a network connection to connect to your target
database as SYSDBA.
Test the connection, try to connect from a PC to the remote
database as SYS with SYSDBA privileges:
$ sqlplus "sys/goofi@AKI1 as
sysdba"
Overview
If you substract two Date Values in Oracle, then the returning the result is in Days (such
as 2.23456). However you would the Result in Days, Hours, Minutes and Seconds. Why not
ask the Oracle Database to do that with Oracle Precision and speed.
Example
We will use a leap year date, 01/01/2000 for
example, for temporary purposes. This date will provide accurate calculation for most
cases.
First, substract the two Dates:
SELECT
TO_DATE('10/29/04-23:28:30','MM/DD/YY-HH24:MI:SS') -
TO_DATE('10/29/04-20:52:04','MM/DD/YY-HH24:MI:SS')
DateDiff
FROM dual;
DATEDIFF
----------
.108634259
Now convert it to Years, Months, Days, Hours, Minutes and Seconds
using the following Statement:
DEFINE DateDay = .108634259
SELECT
TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);
YEARS MONTHS DAYS HO MI
SE
---------- ---------- ---------- -- -- --
0
0 0 02 36 26
The Result of .108634259 Days is: 2 Hours, 36 Minutes and 26
Seconds.
Overview
For applications dealing with highly sensitive data,
Oracle provides the DBMS_OBFUSCATION_TOOLKIT PL/SQL package to encrypt and decrypt data,
including string inputs and raw inputs. The function is limited
to selected algorithms, such as the Data Encryption Standard (DES). Developers may not
plug in their own encryption algorithms, and the key length is also fixed. The function
prohibits making multiple passes of encryption; that is, you cannot nest encryption
calls, thereby encrypting an encrypted value. These restrictions are required by U.S.
laws governing the export of cryptographic products.
Another solution to encrypt strings, for example passwords you can
store them HASHED or DIGESTED. For example, in Oracle, the password is
not encrypted (that would imply there is a decrypt but there is not). For example, to validate a username/password we take them, plus some magic
"salt" and hash it. This results in a fixed length string of some bytes of data. We
compare that to the stored hash and if they match -- you are in,
if not -- you are not.
Computes a Hash
Value for the given String
So, to write a password check function, one would simply glue the USERNAME together with the supplied
PASSWORD. You would call
DBMS_UTILITY.GET_HASH_VALUE to generate some hashes.
This function computes a hash value for the given string.
DBMS_UTILITY.GET_HASH_VALUE (
name VARCHAR2,
base NUMBER,
hash_size NUMBER)
RETURN NUMBER;
Parameters:
| name |
String to be hashed |
| base |
Base value for the returned hash value to start at |
| hash_size |
Desired size of the hash table |
Returns:
A hash value based on the input string. For example, to get a hash
value on a string where the hash value should be between 1000 and 3047, use 1000 as the
base value and 2048 as the hash_size value. Using a power of 2 for the hash_size
parameter works best.
Here is the digest Function
CREATE OR REPLACE FUNCTION digest (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN
LTRIM (
TO_CHAR (
DBMS_UTILITY.GET_HASH_VALUE (
UPPER(p_username)||'/'||UPPER(p_password),
1000000000,
POWER(2,30)
),
RPAD('X',29,'X')||'X'
)
);
END digest;
/
SQL> EXECUTE
DBMS_OUTPUT.PUT_LINE(digest('SCOTT','TIGER'));
4307767C
The function digest takes a username and password,
hashes it into 1 of 1073741824 different numeric values, adds 1000000000 to it (to make
it big) and turns it into HEX. This is what we would
store in the database -- not the password (which we really
don't ever need to know). Now when the user presents a username/password, we digest it and compare -- if they match, you get in,
if not you do not.
Author of this Tip: Thomas Kyte
Overview
The setup for SQL*Plus is amazingly easy. In fact, it should
already be done. Every client software installation
has it, and every server installation has it too. On windows, there
are two versions of SQL*Plus: a GUI one (the sqlplusw.exe program) and a character based one (the sqlplus.exe program). The character-mode SQL*Plus is 100% compatible with
SQL*Plus on every other platform on which Oracle
is delivered. The GUI SQL*Plus, which offers no real functional
benefit over the character mode - after all, it is a character-mode tool running
in a window - is different enough to be confusing and isn't flexible as the command-line
version. Additionally, it is already officially deprecated in the next release of Oracle,
so it won't be around for long.
Store Settings for SQL*PLUS (login.sql and
glogin.sql)
Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and
the containing statements executed. This allows to store settings (such as linesize)
accross SQL*PLUS sessions. New in Oracle 10g: Oracle also reads
glogin.sql and login.sql at a connect in sql*plus.
Additionally, after reading glogin.sql, sql*plus also looks for a file named login.sql in the directory from where
SQL*PLUS was started and in the
directory that the environment variable SQLPATH points to and reads it and
executes it. Settings from the login.sql take precedence
over settings from glogin.sql.
A common login.sql file
REM turn off the terminal output - make it so
SQLPlus does not
REM print out anything when we log in
set termout off
REM default your editor here. SQLPlus has many
REM individual settings.
REM This is one of the most important ones
define _editor=vi
REM serveroutput controls whether your DBMS_OUTPUT.PUT_LINE
calls
REM go into the bit bucket (serveroutput off) or get displayed
REM on screen. I always want serveroutput set on and as big
REM as possible - this does that. The format wrapped elements
REM causes SQLPlus to preserve leading whitespace - very useful
set
serveroutput on size 1000000 format wrapped
REM Here I set some default column widths for commonly queried
REM columns - columns I find myself setting frequently,
REM day after day
column object_name format
a30
column segment_name format a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped
column plan_plus_exp format a100
REM by default, a spool file is a fixed width file with lots of
REM trailing blanks. Trimspool removes these trailing blanks
REM making the spool file significantly smaller
set trimspool
on
REM LONG controls how much of a LONG or CLOB sqlplus displays
REM by default. It defaults to 80 characters which in general
REM is far too small. I use the first 5000 characters by default
set long 5000
REM This sets the default width at which sqlplus wraps output.
REM I use a telnet client that can go upto 135 characters wide
-
REM hence this is my preferred setting.
set linesize 135
REM SQLplus will print column headings every N lines of output
REM this defaults to 14 lines. I find that they just clutter my
REM screen so this setting effectively disables them for all
REM intents and purposes - except for the first page of course
set pagesize
9999
REM here is how I set my signature prompt in sqlplus to
REM username@database> I use the NEW_VALUE concept to format
REM a nice prompt string that defaults to IDLE (useful for those
REM of you that use sqlplus to startup their databases - the
REM prompt will default to idle> if your database isn't started)
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr( global_name, 1, decode( dot,
0, length(global_name),
dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot
from global_name );
set sqlprompt '&gname> '
REM and lastly, we'll put termout back on so sqlplus prints
REM to the screen
set termout on
Overview
Suppose you want to convert an Oracle
table:
(id, sum1,
sum2, sum3)
into another table:
(id, '1',
sum1)
(id, '2', sum2)
(id, '3', sum3)
That means converting 1 row from
the first table into 3 rows in the other table. Of course,
this can be done by scanning the
source table 3 times, one for each «sum» column, but if the first table
is pretty large (~50 million rows), we need
another, faster approach.
Solution
Using an Inline View with
the UNION ALL operator, all can be done in one single step.
CREATE TABLE t1 (
id NUMBER PRIMARY KEY,
sum1 NUMBER,
sum2 NUMBER,
sum3 NUMBER
);
INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;
select * from t1;
ID
SUM1 SUM2
SUM3
---------- ---------- ---------- ----------
1
20
40 50
2
30
20 25
3
15
60 55
CREATE TABLE t2 AS
SELECT id, num, DECODE(num,'1',sum1,'2',sum2,'3',sum3) data
from t1, (SELECT '1' num FROM dual UNION ALL
SELECT '2' num FROM
dual UNION ALL
SELECT '3' num FROM
dual)
/
select * from t2 order by id;
ID N
DATA
---------- - ----------
1
1 20
1
2 40
1
3 50
2
1 30
2
3 25
2
2 20
3
1 15
3
3 55
3
2 60
Overview
You have a result set with positive, negative and neutral values
like this:
select * from result;
KEY
VAL
---------- ----------
first -1.222
second -.03
third -.02
fourth
0
fifth 1.2
sixth
.03
Now, you want to count how many negative, postitive and neutral
values you have.
- 3 negative
- 1 no change
- 2 positive
Solution
Use the Oracle SIGN ( n ) function which returns -1 if n <
0. If n = 0, then the function returns 0. If n > 0, then
SIGN returns 1.
create table result (
key varchar2(10),
val number
);
insert into result (key,val) values ('first',-1.222);
insert into result (key,val) values ('second',-.03);
insert into result (key,val) values ('third',-.02);
insert into result (key,val) values ('fourth',0);
insert into result (key,val) values ('fifth',1.2);
insert into result (key,val) values ('sixth',.03);
select count(decode(sign(val),-1,-1)) neg,
count(decode(sign(val),0,0)) zero,
count(decode(sign(val),1,1)) pos
from result;
NEG ZERO
POS
---------- ---------- ----------
3
1 2
You may notice, that you cannot install an additional Option
(Spatial, InterMedia, etc) or you are not able to create the Enterprise Manager
Repository. For example you get the following errors if you create the Repository
with:
emca - config dbcontrol db -repos create
No errors.
No errors.
Warning: Package Body created with compilation errors.
No errors.
No errors.
.....
and later:
Recompile invalid objects...
ERROR:
ORA-24344: success with compilation error
ORA-06512: at line 38
Potential Solution
1. Clean up the failed respository creation
a. SQL> drop user sysman
cascade;
b. SQL> drop role MGMT_USER;
c. SQL> drop user MGMT_VIEW cascade;
d. SQL> drop public synonym
MGMT_TARGET_BLACKOUTS;
e. SQL> drop public synonym SETEMVIEWUSERCONTEXT;
2. Ensure that the execute permission has been granted
on the following packages:
SQL> grant execute on utl_smtp to
public;
SQL> grant execute on utl_tcp to
public;
SQL> grant execute on utl_file to
public;
3. Run the catproc.sql script from
ORACLE_HOME/rdbms/admin
SQL> @catproc.sql
3. Retry the emca utility or create the desired Option
emca -config dbcontrol db -repos create
Check-Pointing
Check-pointing is an important Oracle activity which records the highest system change
number (SCN,) so that all data blocks less than or equal to the SCN are known to be
written out to the data files. If there is a failure and then subsequent cache recovery,
only the redo records containing changes at SCN(s) higher than the checkpoint need to be
applied during recovery.
As we are aware, instance and crash recovery occur in two steps - cache recovery
followed by transaction recovery. During the cache recovery phase, also known as the
rolling forward stage, Oracle applies all committed and uncommitted changes in the redo
log files to the affected data blocks. The work required for cache recovery processing is
proportional to the rate of change to the database and the time between checkpoints.
Mean time to recover (MTTR)
Fast-start recovery can greatly reduce the mean time to recover (MTTR), with minimal
effects on online application performance. Oracle continuously estimates the recovery
time and automatically adjusts the check-pointing rate to meet the target recovery
time.
With 10g, the Oracle database can now self-tune check-pointing to achieve good
recovery times with low impact on normal throughput. You no longer have to set any
checkpoint-related parameters.
This method reduces the time required for cache recovery and makes the recovery
bounded and predictable by limiting the number of dirty buffers and the number of redo
records generated between the most recent redo record and the last checkpoint.
Administrators specify a target (bounded) time to complete the cache recovery phase of
recovery with the FAST_START_MTTR_TARGET initialization parameter, and Oracle
automatically varies the incremental checkpoint writes to meet that target.
The TARGET_MTTR field of V$INSTANCE_RECOVERY contains the MTTR target in effect. The
ESTIMATED_MTTR field of V$INSTANCE_RECOVERY contains the estimated MTTR should a crash
happen right away.
Example
SELECT TARGET_MTTR,
ESTIMATED_MTTR,
CKPT_BLOCK_WRITES
FROM V$INSTANCE_RECOVERY;
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
214
12 269880
Whenever you set FAST_START_MTTR_TARGET to a nonzero value,
then set the following parameters to
0.
LOG_CHECKPOINT_TIMEOUT = 0
LOG_CHECKPOINT_INTERVAL = 0
FAST_START_IO_TARGET = 0
Because these initialization parameters either override
FAST_START_MTTR_TARGET or potentially drive checkpoints more aggressively than
FAST_START_MTTR_TARGET does, they can interfere with the simulation.
Overview
Historically database columns which hold alphanumeric data have
been defined using the number of bytes they store. This approach was fine as the number
of bytes equated to the number of characters when using single-byte character sets. With
the increasing use of multibyte character sets to support globalized databases comes the
problem of bytes no longer equating to characters.
The VARCHAR2 datatype specifies a variable-length character string.
When you create a VARCHAR2 column, you supply the maximum number of bytes or
characters of data that it can hold.
Oracle subsequently stores each value in the column exactly as you
specify it, provided the value does not exceed the column's maximum length of the column.
If you try to insert a value that exceeds the specified length, then Oracle returns an
ORA-12899 error.
A character is technically a code point of the database character
set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS
parameter, which has a default of bytes. The maximum length of VARCHAR2 data is 4000
bytes.
Current Setting of NLS_LENGTH_SEMANTICS
select * from nls_database_parameters;
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.3.0
Example
CREATE TABLE test (
t1 VARCHAR2(10 BYTE),
t2 VARCHAR2(10 CHAR),
t3 VARCHAR2(10)
);
DESC test;
Name
Null?
Type
---------------------------------------------------
T1
VARCHAR2(10)
T2
VARCHAR2(10 CHAR)
T3
VARCHAR2(10)
OK, we can see how the table was created.
INSERT INTO TEST (t1, t2) VALUES
('1234567890','äöüߧèáàâö');
INSERT INTO TEST (t1, t2) VALUES
('äää','ääääääääää');
COMMIT;
1 row created.
1 row created.
Commit complete.
INSERT INTO VARCHAR_TEST (t1) VALUES
('ääääää');
ERROR at line 1:
ORA-12899: value too large for column "TEST"."T1" (actual: 12,
maximum: 10)
The default character semantics of the database or session can be
altered using the NLS_LENGTH_SEMANTICS parameter.
alter system set nls_length_semantics=char;
alter session set nls_length_semantics=char;
The INSTR, LENGTH and SUBSTR functions always deal with characters,
regardless of column definitions and the character sets. For times when you specifically
need to deal in bytes Oracle provides the INSTRB, LENGTHB and SUBSTRB
functions.
|
Oracle Char Semantics and
Globalization