|
 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 & | 
