Snippet Name: Oracle Exception Handling
Description: In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.
You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.
When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.
To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.
In the example given, we calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically.
An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the list on the right.
====================
DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; EXCEPTION -- exception handlers begin WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL); COMMIT; ... WHEN OTHERS THEN -- handles all other errors ROLLBACK; END; -- exception handlers and block end here The LAST example illustrates EXCEPTION handling, NOT the effective USE OF INSERT statements. FOR example, a better way TO DO the INSERT follows: INSERT INTO stats (symbol, ratio) SELECT symbol, DECODE(earnings, 0, NULL, price / earnings) FROM stocks WHERE symbol = 'XYZ'; -- In the example above, a subquery supplies values to the -- INSERT statement. If earnings are zero, the function DECODE -- returns a null. Otherwise, DECODE returns the price-to-earnings -- ratio. /* Predefined PL/SQL Exceptions: Error (Oracle Error / SQLCODE Value) ACCESS_INTO_NULL (ORA-06530 / -6530) Your program attempts to assign values to the attributes of an uninitialized (atomically null) object. CASE_NOT_FOUND (ORA-06592 / -6592) None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL (ORA-06531 / -6531) Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. CURSOR_ALREADY_OPEN (ORA-06511 / -6511) Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. Your program cannot open that cursor inside the loop. DUP_VAL_ON_INDEX (ORA-00001 / -1) Your program attempts to store duplicate values in a database column that is constrained by a unique index. INVALID_CURSOR (ORA-01001 / -1001) Your program attempts an illegal cursor operation such as closing an unopened cursor. INVALID_NUMBER (ORA-01722 / -1722) In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. LOGIN_DENIED (ORA-01017/ -1017) Your program attempts to log on to Oracle with an invalid username and/or password. NO_DATA_FOUND (ORA-01403 / +100) A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised. NOT_LOGGED_ON (ORA-01012 / -1012) Your program issues a database call without being connected to Oracle. PROGRAM_ERROR (ORA-06501 / -6501) PL/SQL has an internal problem. ROWTYPE_MISMATCH (ORA-06504 / -6504) The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. SELF_IS_NULL (ORA-30625 / -30625) Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null. STORAGE_ERROR (ORA-06500 / -6500) PL/SQL runs out of memory or memory has been corrupted. SUBSCRIPT_BEYOND_COUNT (ORA-06533 / -6533) Your program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT (ORA-06532 / -6532) Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. SYS_INVALID_ROWID (ORA-01410 / -1410) The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. TIMEOUT_ON_RESOURCE (ORA-00051 / -51) A time-out occurs while Oracle is waiting for a resource. TOO_MANY_ROWS (ORA-01422 / -1422) A SELECT INTO statement returns more than one row. VALUE_ERROR (ORA-06502 / -6502) An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) ZERO_DIVIDE (ORA-01476 / -1476) Your program attempts to divide a number by zero. */
============
Snippet Name: SQL Code and SQL Erroor Messages
Description: Captures Oracle error codes and their associated messages.
example:
SQL Code NUMBER OF the most recent EXCEPTION raised BY PL/SQL. 0 IF none SET serveroutput ON BEGIN DBMS_OUTPUT.put_line(SQLCODE); END; / SQL Errm Error message associated WITH the specified code SET serveroutput ON BEGIN DBMS_OUTPUT.put_line(SQLERRM); END; /============================================
Description: Returns a Value if the Expression IS NULL
example:
1) programe to print the no's from 1 to 100 ?
declare
a number(6):=1;
begin
loop
print(a);
a:=a+1;
exit when a>100;
if mod(a,10)=0
then
dbms_output.new_line;
end if;
end loop;
end;
/
=======
2) programe to print the no's from 1 to 25 ?
declare
a number(8):=1;
begin
loop
print(a);
a:=a+1;
exit when a>25;
end loop;
end;
/
=====
3) program to print to accept two no's and print the odd number
declare
a number(5):=&a;
b number(6):=&b;
begin
loop
exit when a>b;
if mod(a,2)=1
then
print(a);
a:=a+2;
else a:=a+1;
end if;
end loop;
end;
/
======
example for WHILE LOOP
4)WAP to accept a date and print all the seven days of a given week from the given day alog with the date value
declare
dt date:='&dt';
dt1 date;
begin
dt1:=dt+6;
while dt<=dt1
loop
print(to_char(dt,'day,dd/mon/yyyy'));
dt:=dt+1;
end loop;
end;
/
=====
5) WAP to accept a 4 digt no (consider it as a year) and rint the dates of all sundays also print totsl no of sundays of that year
declare
year number(4):=&year;
dt1 date;
dt2 date;
cnt number(4):=0;
begin
dt1:='01-jan-'||year;
dt2:='31-dec-'||year;
while (dt1<=dt2)
loop
if to_char(dt1,'d')=1;
then
print(to_char(dt1,'day,dd.mon.yyyy'));
cnt:=cnt+1;
end if;
dt1:=next(day(dt1,'sun');
end loop;
print(total sundays of the year:'||year||''='||cnt);
end;
/
/
Snippet Name: INNER JOIN example and syntax
Description: The INNER JOIN keyword return rows when there is at least one match in both tables.
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
INNER JOIN Sales
ON Person.P_Id=Sales.P_Id
ORDER BY Person.LastName
===
Snippet Name: LEFT JOIN example and syntax
Description: The LEFT JOIN (also called LEFT OUTER JOIN) keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
example:
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
-- or
SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
LEFT JOIN Sales
ON Person.P_Id=SalesP_Id
ORDER BY Person.LastName
========
Snippet Name: RIGHT JOIN example and syntax
Description: The RIGHT JOIN (or RIGHT OUTER JOIN) keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
-- or
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
RIGHT JOIN Sales
ON Persons.P_Id=Sales.P_Id
ORDER BY Person.LastName
==========
Snippet Name: FULL JOIN example and syntax
Description: The FULL JOIN keyword return rows when there is a match in one of the tables.
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
-- or
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
FULL JOIN Sales
ON Person.P_Id=Sales.P_Id
ORDER BY Person.LastName
-- alternate syntax:
SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
======
Snippet Name: Self-join example and syntax
Description: A self-join is a way of joining a table to itself.
The example show could be the result of a query written to find all pairings of two employees in the same country, where all of the employee information is contained within a single large table.
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
-- FOR this example, note that:
-- F and S are aliases FOR the first and second copies of the
-- employee table.
-- The condition F.Country = S.Country excludes pairings between
-- employees in different countries. The example question only
-- wanted pairs of employees in the same country.
-- The condition F.EmployeeID < S.EmployeeID excludes pairings
-- where the EmployeeIDs are the same.
-- F.EmployeeID < S.EmployeeID also excludes duplicate pairings.
-- The effect of outer joins can also be obtained using
-- correlated subqueries. FOR example:
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- this can also be written AS:
SELECT employee.LastName, employee.DepartmentID,
(SELECT department.DepartmentName
FROM department
WHERE employee.DepartmentID = department.DepartmentID )
FROM employee
=========
Snippet Name: ANSI Joins: INNER JOIN
Description: Examples of an ANSI-style INNER JOIN.
CREATE TABLE table_one (
col_one NUMBER,
col_two NUMBER
);
CREATE TABLE table_two (
col_one NUMBER,
col_two NUMBER
);
INSERT INTO table_one VALUES ( 1, 1);
INSERT INTO table_one VALUES ( 3, 5);
INSERT INTO table_one VALUES ( 5, 9);
INSERT INTO table_two VALUES ( 4, 5);
INSERT INTO table_two VALUES ( 6, 3);
INSERT INTO table_two VALUES ( 5, 5);
SELECT * FROM
table_one t1 inner join
table_two t2 ON t1.col_one = t2.col_two;
COL_ONE COL_TWO COL_ONE COL_TWO
---------- ---------- ---------- ----------
5 9 4 5
3 5 6 3
5 9 5 5
SELECT * FROM
table_one t1 inner join
table_two t2 using (col_two);
-- Note: col_two is only returned once here instead of twice
-- when using is used instead of on. This is because it must
-- be the same value:
COL_TWO COL_ONE COL_ONE
---------- ---------- ----------
5 3 4
5 3 5
=========
Snippet Name: ANSI Joins: CROSS JOIN
Description: Example of an ANSI-style CROSS JOIN.
CREATE TABLE table_one (
col_one NUMBER,
col_two VARCHAR2(10)
);
CREATE TABLE table_two (
col_three NUMBER,
col_four VARCHAR2(10)
);
INSERT INTO table_one VALUES ( 1, 'one');
INSERT INTO table_one VALUES ( 2, 'two');
INSERT INTO table_two VALUES (10, 'ten');
INSERT INTO table_two VALUES (20, 'twenty');
INSERT INTO table_two VALUES ( 5, 'five');
SELECT * FROM
table_one cross join
table_two;
-- Each row from table_one is returned together
-- with each row from table_two:
COL_ONE COL_TWO COL_THREE COL_FOUR
---------- ---------- ---------- ----------
1 one 10 ten
1 one 20 twenty
1 one 5 five
2 two 10 ten
2 two 20 twenty
2 two 5 five
===========
Snippet Name: ANSI Joins: OUTER JOIN
Description: Example of an ANSI-style OUTER JOIN
CREATE TABLE table_one (
col_one NUMBER,
col_two CHAR(1)
);
CREATE TABLE table_two (
col_one NUMBER,
col_two CHAR(1)
);
INSERT INTO table_one VALUES (1, 'a');
INSERT INTO table_one VALUES (2, 'b');
INSERT INTO table_one VALUES (3, 'c');
INSERT INTO table_two VALUES (2, 'B');
INSERT INTO table_two VALUES (3, 'C');
INSERT INTO table_two VALUES (4, 'D');
SELECT * FROM
table_one t1 left outer join
table_two t2 ON t1.col_one = t2.col_one;
COL_ONE C COL_ONE C
---------- - ---------- -
2 b 2 B
3 c 3 C
1 a
SELECT * FROM
table_one t1 right outer join
table_two t2 ON t1.col_one = t2.col_one;
COL_ONE C COL_ONE C
---------- - ---------- -
2 b 2 B
3 c 3 C
4 D
SELECT * FROM
table_one t1 full outer join
table_two t2 ON t1.col_one = t2.col_one;
COL_ONE C COL_ONE C
---------- - ---------- -
2 b 2 B
3 c 3 C
1 a
4 D
==============
Snippet Name: ANSI Joins: FULL JOIN
Description: Example of an ANSI-style FULL JOIN.
CREATE TABLE left_tbl (
id NUMBER,
txt VARCHAR2(10)
);
CREATE TABLE right_tbl (
id NUMBER,
txt VARCHAR2(10)
);
INSERT INTO left_tbl VALUES (1, 'one' );
INSERT INTO left_tbl VALUES (2, 'two' );
INSERT INTO left_tbl VALUES (3, 'three' );
--insert into left_tbl values (4, 'four' );
INSERT INTO left_tbl VALUES (5, 'five' );
INSERT INTO right_tbl VALUES (1, 'uno' );
--insert into right_tbl values (2, 'dos' );
INSERT INTO right_tbl VALUES (3, 'tres' );
INSERT INTO right_tbl VALUES (4, 'cuatro');
INSERT INTO right_tbl VALUES (5, 'cinco' );
-- A full join returns the records of both tables
-- (that satisfy a [potential] where condition). In
-- the following example, 4 cuatro and 2 two are returned,
-- although the ids 4 and 2 are not present in both tables:
SELECT
id,
l.txt,
r.txt
FROM
left_tbl l full join
right_tbl r using(id)
id;
ID TXT TXT
---------- ---------- ----------
1 one uno
2 two
3 three tres
4 cuatro
5 five cinco
DROP TABLE left_tbl;
DROP TABLE right_tbl;
=============
Snippet Name: TKPROF (transient kernel profiler)
Description: TKPROF stands for "transient kernel profiler". TKPROF is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis.
To use TKPROF you must first enable sql trace. This can be done for either the instance or the session. If you want to change it for the entire instance, set sql_trace=true into the init.ora file and restart the instance. However, usually, you'll want to turn on sql trace for a particular session only.
The trace files will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value with select value from v$parameter where name = 'user_dump_dest'.
ALTER session SET sql_trace=TRUE;
-- or, from another session with a
sys.dbms_system.set_sql_trace_in_session(session's id,serial number, true)
-- include timing information. Set the timed_statistics parameter
-- to true or issue one of these commands:
alter system set timed_statistics=true;
-- or
alter session set timed_statistics=true;
========
Snippet Name: Oracle SQL Hints
Description: All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.
There should be no schema names in hints. Hints must use aliases if alias names are used for table names.
Why bother to use hints?
When the Oracle optimizer is working properly, no hints should really be required at all.
However, there are time when the characteristics of the data in the database are changing often or quickly so that the optimizer's statistics are out of date or inaccurate. In this case a hint could improve performance and/or efficiency.
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ index(table_alias f_name) */ ... FROM TABLE.test table_alias
-- Hint List:
/*+ ALL_ROWS */
Explicitly chooses the cost-based approach TO optimize
a statement block WITH a goal OF best throughput (that
IS, minimum total resource consumption)
/*+ CHOOSE */
Causes the optimizer TO choose BETWEEN the rule-based approach
AND the cost-based approach FOR a SQL statement based ON the
presence OF statistics FOR the tables accessed BY the statement
/*+ FIRST_ROWS */
Explicitly chooses the cost-based approach TO optimize a statement
block WITH a goal OF best response TIME (minimum resource usage TO
RETURN FIRST ROW). It will also force the optimizer TO make USE OF
INDEX, IF available. There are other versions OF FIRST_ROWS hints.
This hint IS useful IN an OLTP environment WHEN the USER cannot
wait till the LAST ROW IS fetched. This IS mainly used IN JAVA
lookup screens. IF there are some calculations THEN this hint should
NOT be used.
Test your PL/SQL knowledge, Which code runs faster?
/*+ RULE */
Explicitly chooses rule-based optimization FOR a statement block
/*+ AND_EQUAL(table index) */
Explicitly chooses an execution plan that uses an access PATH that
merges the scans ON several single-column indexes
/*+ CLUSTER(table) */
Explicitly chooses a CLUSTER scan TO access the specified TABLE
/*+ FULL(table) */
Explicitly chooses a full TABLE scan FOR the specified TABLE
/*+ HASH(table) */
Explicitly chooses a hash scan TO access the specified TABLE
/*+ HASH_AJ(table) */
Transforms a NOT IN sub query INTO a hash anti join TO access the
specified TABLE
/*+ HASH_SJ (table) */
Transforms a NOT IN sub query INTO a hash anti-join TO access the
specified TABLE
/*+ INDEX(table index) */
Explicitly chooses an INDEX scan FOR the specified TABLE
/*+ INDEX_ASC(table index) */
Explicitly chooses an ascending-RANGE INDEX scan FOR the specified
TABLE
/*+ INDEX_COMBINE(table index) */
IF no indexes are given AS arguments FOR the INDEX_COMBINE hint, the
optimizer uses whatever BOOLEAN combination OF bitmap indexes has the
best cost estimate. IF particular indexes are given AS arguments, the
optimizer tries TO USE some BOOLEAN combination OF those particular
bitmap indexes.
/*+ INDEX_DESC(table index) */
Explicitly chooses a descending-RANGE INDEX scan FOR the specified TABLE
/*+ INDEX_FFS(table index) */
Causes a fast full INDEX scan TO be performed rather than a full
TABLE scan
/*+ MERGE_AJ (table) */
Transforms a NOT IN sub query INTO a merge anti-join TO access the
specified TABLE
/*+ MERGE_SJ (table) */
Transforms a correlated EXISTS sub query INTO a merge semi-join TO
access the specified TABLE
/*+ ROWID(table) */
Explicitly chooses a TABLE scan BY ROWID FOR the specified TABLE
/*+ USE_CONCAT */
Forces combined OR conditions IN the WHERE clause OF a query TO be
transformed INTO a compound query using the
UNION ALL SET OPERATOR
/*+ ORDERED */
Causes Oracle TO join tables IN the ORDER IN which they appear IN
the FROM clause
/*+ STAR */
Forces the large TABLE TO be joined using a nested-LOOP join ON the INDEX
/*+ DRIVING_SITE (table) */
Forces query execution TO be done AT a different site FROM that selected
BY Oracle
/*+ USE_HASH (table) */
Causes Oracle TO join each specified TABLE WITH another ROW source
WITH a hash join
/*+ USE_MERGE (table) */
Causes Oracle TO join each specified TABLE WITH another ROW source
WITH a sort-merge join
/*+ USE_NL (table) */
Causes Oracle TO join each specified TABLE TO another ROW source
WITH a nested-loops join using the specified TABLE AS the inner TABLE
/*+ APPEND */ , /*+ NOAPPEND */
Specifies that data IS simply appended (OR NOT) TO a TABLE; existing
free SPACE IS NOT used. USE these hints only following the INSERT keyword.
/*+ NOPARALLEL(table) */
Disables parallel scanning OF a TABLE, even IF the TABLE was created
WITH a PARALLEL clause
/*+ PARALLEL(table, instances) */
This allows you TO specify the desired NUMBER OF concurrent slave processes
that can be used FOR the operation. DELETE, INSERT, AND UPDATE operations
are considered FOR parallelization only IF the session IS IN a PARALLEL DML
enabled MODE. (USE ALTER SESSION PARALLEL DML TO enter this MODE.)
/*+ PARALLEL_INDEX */
Allows you TO parallelize fast full INDEX scan FOR partitioned AND
non-partitioned indexes that have the PARALLEL attribute
/*+ NOPARALLEL_INDEX */
Overrides a PARALLEL attribute setting ON an INDEX
/*+ CACHE */
Specifies that the blocks retrieved FOR the TABLE IN the hint are placed
AT the most recently used END OF the LRU list IN the buffer cache WHEN a
full TABLE scan IS performed
/*+ NOCACHE */
Specifies that the blocks retrieved FOR this TABLE are placed AT the
LEAST recently used END OF the LRU list IN the buffer cache WHEN a full
TABLE scan IS performed
/*+ MERGE (table) */
Causes Oracle TO evaluate complex views OR sub queries before the
surrounding query
/*+ NO_MERGE (table) */
Causes Oracle NOT TO merge mergeable views
/*+ PUSH_JOIN_PRED (table) */
Causes the optimizer TO evaluate, ON a cost basis, whether OR NOT TO push
individual join predicates INTO the VIEW
/*+ NO_PUSH_JOIN_PRED (table) */
Prevents pushing OF a join predicate INTO the VIEW
/*+ PUSH_SUBQ */
Causes non merged sub queries TO be evaluated AT the earliest possible
place IN the execution plan
/*+ STAR_TRANSFORMATION */
Makes the optimizer USE the best plan IN which the transformation has been used.
Free
Oracle Magazine
Subscriptions
and Oracle White Papers
SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.
======
Snippet Name: Data Types
Description: Oracle offers many data types in character, numeric, and date/time groups.
Character Data Types
CHAR(size) Maximum size OF 2000 bytes.
nchar(size) Maximum size OF 2000 bytes.
nvarchar2(size) Maximum size OF 4000 bytes
VARCHAR2(size) Maximum size OF 4000 bytes
LONG Maximum size OF 2GB
RAW Maximum size OF 2000 bytes
LONG RAW Maximum size OF 2GB
Numeric Data Types
NUMBER(p,s) Precision can RANGE FROM 1 TO 38.
numeric(p,s) Precision can RANGE FROM 1 TO 38
dec(p,s) Precision can RANGE FROM 1 TO 38
DECIMAL(p,s) Precision can RANGE FROM 1 TO 38integer
int
SMALLINT
REAL
double precision
DATE/TIME Data Types
DATE A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD. A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD. A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD.
TIMESTAMP (fractional seconds precision) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds.
TIMESTAMP (fractional seconds precision) WITH TIME ZONE fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds; WITH a TIME ZONE displacement VALUE.
TIMESTAMP (fractional seconds precision) WITH local TIME ZONE fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds; WITH a TIME ZONE expressed AS the session TIME ZONE.
INTERVAL YEAR (YEAR precision) TO MONTH YEAR precision IS the NUMBER OF digits IN the YEAR. (DEFAULT IS 2)
Large Object (LOB) Datatypes
bfile Maximum file size OF 4GB.
blob Store up TO 4GB OF binary data.
clob Store up TO 4GB OF character data.
nclob Store up TO 4GB OF character text data.
INTERVAL DAY
(DAY precision)
TO SECOND (fractional seconds precision) DAY precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 2)
fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6)
Free
Oracle Magazine
Subscriptions
and Oracle White Papers
==========
Snippet Name: Distinct
Description: The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.
The syntax FOR the DISTINCT clause IS:
SELECT DISTINCT columns
FROM tables
WHERE predicates;
Let's take a look at a very simple example.
SELECT DISTINCT city
FROM suppliers;
This SQL statement would return all unique cities from the suppliers table.
===========
Snippet Name: Declaring Variables
Description: Variables are very useful in PL/SQL, indeed in all programming languages. They allow you to set a values once and use it many times throughout the code.
The syntax FOR declaring variables IS:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
FOR example:
Declaring a variable:
LDescription VARCHAR2(40);
Declaring a CONSTANT:
LTotal CONSTANT numeric(8,1) := 8363934.1;
Declaring a variable WITH an initial VALUE (NOT a CONSTANT):
LType VARCHAR2(10) := 'Example';
=============
Snippet Name: AND Condition
Description: The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.
-- The syntax for the AND condition is:
SELECT columns
FROM tables
WHERE column1 = 'value1'
AND column2 = 'value2';
-- The AND condition requires that each condition be must
-- be met for the record to be included in the result set.
-- In this case, column1 has to equal 'value1' and column2
-- has to equal 'value2'.
-- Example #1
-- The first example that we'll take a look at involves a very
-- simple example using the AND condition.
SELECT *
FROM suppliers
WHERE city = 'New York'
AND TYPE = 'PC Manufacturer';
-- This would return all suppliers that reside in New York
-- and are PC Manufacturers. Because the * is used in the select,
-- all fields from the supplier table would appear in the result
-- set.
--Example #2
-- Our next example demonstrates how the AND condition can be
-- used to "join" multiple tables in an SQL statement.
SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
AND suppliers.supplier_name = 'IBM';
-- This would return all rows where the supplier_name is IBM.
-- And the suppliers and orders tables are joined on supplier_id.
-- You will notice that all of the fields are prefixed with the
-- table names (ie: orders.order_id). This is required to
-- eliminate any ambiguity as to which field is being referenced;
-- as the same field name can exist in both the suppliers and
-- orders tables.
============
Snippet Name: IS NOT NULL
Description: In PLSQL to check if a value is not null, you must use the "IS NOT NULL" syntax.
IN PLSQL TO CHECK IF a VALUE IS NOT NULL, you must USE the "IS NOT NULL" syntax.
FOR example,
IF Lvalue IS NOT NULL THEN
...
END IF;
IF Lvalue does NOT contain a NULL VALUE, the "IF" expression will evaluate TO TRUE.
You can also USE "IS NOT NULL" IN an SQL statement. FOR example:
SELECT * FROM suppliers
WHERE supplier_name IS NOT NULL;
This will RETURN ALL records FROM the suppliers TABLE WHERE the supplier_name does NOT contain a NULL VALUE.
====
Snippet Name: OR Condition
Description: The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - select, insert, update, or delete.
The syntax FOR the OR condition IS:
SELECT columns
FROM tables
WHERE column1 = 'value1'
OR column2 = 'value2';
The OR condition requires that ANY OF the conditions be must be met FOR the RECORD TO be included IN the result SET. IN this CASE, column1 has TO equal 'value1' OR column2 has TO equal 'value2'.
Example #1
The FIRST example that we'll take a look at involves a very simple example using the OR condition.
SELECT *
FROM suppliers
WHERE city = 'NEW York'
or city = 'Newark';
This would return all suppliers that reside in either New York or Newark. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
Example #2
The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.
SELECT supplier_id
FROM suppliers
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';
This SQL statement would return all supplier_id values where the supplier's name IS either IBM, Hewlett Packard OR Gateway.
======
Snippet Name: Combining the AND and OR Conditions
Description: The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.
-- When combining these conditions, it is important to use brackets
-- so that the database knows what order to evaluate each condition.
-- Example #1
-- The first example that we'll take a look at an example that
-- combines the AND and OR conditions.
SELECT *
FROM suppliers
WHERE (city = 'New York' AND name = 'IBM')
OR (city = 'Newark');
-- This would return all suppliers that reside in New York whose
-- name is IBM and all suppliers that reside in Newark. The brackets
-- determine what order the AND and OR conditions are evaluated in.
-- Example #2
-- The next example takes a look at a more complex statement.
-- For example:
SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
OR (name = 'Hewlett Packard' AND city = 'Atlantic City')
OR (name = 'Gateway' AND status = 'Active' AND city = 'Burma');
-- This SQL statement would return all supplier_id values where the
-- supplier's name is IBM or the name is Hewlett Packard and the
-- city is Atlantic City or the name is Gateway, the status is
-- Active, and the city is Burma.
===========
Snippet Name: LIKE Condition
Description: The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.
The patterns that you can choose FROM are:
% allows you TO match ANY string OF ANY LENGTH (including zero LENGTH)
_ allows you TO match ON a single character
Examples using % wildcard
The FIRST example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
You can also using the wildcard multiple times within the same string. For example,
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
In this example, we are looking for all suppliers whose name contains the characters 'bob'.
You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,
SELECT * FROM suppliers
WHERE supplier_name not like 'T%';
By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.
Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ IS looking FOR only one character.
FOR example,
SELECT * FROM suppliers
WHERE supplier_name LIKE 'Sm_th';
This SQL statement would RETURN ALL suppliers whose name IS 5 characters LONG, WHERE the FIRST two characters IS 'Sm' AND the LAST two characters IS 'th'. FOR example, it could RETURN suppliers whose name IS 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
Here IS another example,
SELECT * FROM suppliers
WHERE account_number LIKE '12317_';
You might find that you are looking FOR an account NUMBER, but you only have 5 OF the 6 digits. The example above, would retrieve potentially 10 records back (WHERE the missing VALUE could equal anything FROM 0 TO 9). FOR example, it could RETURN suppliers whose account numbers are:
123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.
Examples using Escape Characters
Next, IN Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
Please note that you can define an escape character as a single character (length of 1) ONLY.
For example,
SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';
This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.
Here is another more complicated example:
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';
This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.
You can also use the Escape character with the _ character. For example,
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';
This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
=============
Snippet Name: IN Function
Description: The IN function helps reduce the need to use multiple OR conditions.
-- The syntax for the IN function is:
SELECT columns
FROM tables
WHERE column1 IN (value1, value2, .... value_n);
-- This SQL statement will return the records where column1 is
-- value1, value2..., or value_n. The IN function can be used
-- in any valid SQL statement - select, insert, update, or delete.
-- Example #1
-- The following is an SQL statement that uses the IN function:
SELECT *
FROM suppliers
WHERE supplier_name IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
-- This would return all rows where the supplier_name is either
-- IBM, Hewlett Packard, or Microsoft. Because the * is used in
-- the select, all fields from the suppliers table would appear
-- in the result set.
-- It is equivalent to the following statement:
SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
-- As you can see, using the IN function makes the statement
-- easier to read and more efficient.
-- Example #2
-- You can also use the IN function with numeric values.
SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);
-- This SQL statement would return all orders where the order_id
-- is either 10000, 10001, 10003, or 10005.
-- It is equivalent to the following statement:
SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;
-- Example #3 using "NOT IN"
-- The IN function can also be combined with the NOT operator.
-- For example,
SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard',
'Microsoft');
-- This would return all rows where the supplier_name is neither
-- IBM, Hewlett Packard, or Microsoft. Sometimes, it is more
-- efficient to list the values that you do not want, as opposed
-- to the values that you do want.
==========
Snippet Name: BETWEEN Condition
Description: The BETWEEN condition allows you to retrieve values within a range.
-- The syntax for the BETWEEN condition is:
SELECT columns
FROM tables
WHERE column1 BETWEEN value1 AND value2;
-- This SQL statement will return the records where column1
-- is within the range of value1 and value2 (inclusive). The
-- BETWEEN function can be used in any valid SQL statement -
-- select, insert, update, or delete.
-- Example #1 - Numbers
-- The following is an SQL statement that uses the BETWEEN
-- function:
SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;
-- This would return all rows where the supplier_id is between
-- 5000 and 5010, inclusive. It is equivalent to the following SQL
-- statement:
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
-- Example #2 - Dates
-- You can also use the BETWEEN function with dates.
SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');
-- This SQL statement would return all orders where the
-- order_date is between Jan 1, 2003 and Dec 31, 2003
-- (inclusive).
-- It would be equivalent to the following SQL statement:
SELECT *
FROM orders
WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2003/12/31','yyyy/mm/dd');
-- Example #3 - NOT BETWEEN
-- The BETWEEN function can also be combined with the NOT
-- operator. For example:
SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;
-- This would be equivalent to the following SQL:
SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
-- In this example, the result set would exclude all supplier_id
-- values between the range of 5000 and 5500 (inclusive).
=======
Snippet Name: CLUSTER_ID
Description: CLUSTER_ID returns the cluster identifier of the predicted cluster with the highest probability for the set of predictors specified in the mining_attribute_clause. The value returned is an Oracle NUMBER.
The mining_attribute_clause behaves as described for the PREDICTION function.
This function is for use with clustering models that have been created using the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.
This example, and the prerequisite data mining operations, including the creation of the dm_sh_clus_sample model and the dm_sh_sample_apply_prepared view, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql.
CLUSTER_ID(<schame.model> <mining_attribute_clause>)
SELECT CLUSTER_ID(km_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt
FROM km_sh_sample_apply_prepared
GROUP BY CLUSTER_ID(km_sh_clus_sample USING *)
ORDER BY cnt DESC
==========
Snippet Name: CLUSTER_PROBABILITY
Description: CLUSTER_PROBABILITY returns a measure of the degree of confidence of membership of an input row in a cluster associated with the specified model.
This function is for use with clustering models that have been created with the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.
This example can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql.
CLUSTER_PROBABILITY(<schema.model>, <cluster_id> <mining_attribute_clause>)
CLUSTER_PROBABILITY ( [ schema . ] model
[ , cluster_id ] mining_attribute_clause )
SELECT *
FROM (SELECT cust_id, CLUSTER_PROBABILITY(km_sh_clus_sample, 2 USING *) prob
FROM km_sh_sample_apply_prepared
ORDER BY prob DESC)
WHERE ROWNUM < 11;
/*
For cluster_id, specify the identifier of the cluster in the
model. The function returns the probability for the specified
cluster. If you omit this clause, then the function returns
the probability associated with the best predicted cluster.
You can use the form without cluster_id in conjunction with
the CLUSTER_ID function to obtain the best predicted pair of
cluster ID and probability.
The mining_attribute_clause behaves as described for the
PREDICTION function.
*/
==========
Snippet Name: CLUSTER_SET
Description: CLUSTER_SET returns a varray of objects containing all possible clusters that a given row belongs to. Each object in the varray is a pair of scalar values containing the cluster ID and the cluster probability. The object fields are named CLUSTER_ID and PROBABILITY, and both are Oracle NUMBER.
For the optional topN argument, specify a positive integer. Doing so restricts the set of predicted clusters to those that have one of the top N probability values. If you omit topN or set it to NULL, then all clusters are returned in the collection. If multiple clusters are tied for the Nth value, the database still returns only N values.
For the optional cutoff argument, specify a positive integer to restrict the returned clusters to those with a probability greater than or equal to the specified cutoff. You can filter only by cutoff by specifying NULL for topN and the desired cutoff value for cutoff.
You can specify topN and cutoff together to restrict the returned clusters to those that are in the top N and have a probability that passes the threshold.
This example, and the prerequisite data mining operations, including the creation of the dm_sh_clus_sample model and the views and type, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql.
CLUSTER_SET(<schema.model>, <top N>, <cutoff>
<mining_attribute_clause>)
WITH
clus_tab AS (
SELECT id,
A.attribute_name aname,
A.conditional_operator op,
NVL(A.attribute_str_value,
ROUND(DECODE(A.attribute_name, N.col,
A.attribute_num_value * N.scale + N.shift,
A.attribute_num_value),4)) val,
A.attribute_support support,
A.attribute_confidence confidence
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('km_sh_clus_sample')) T,
TABLE(T.rule.antecedent) A,
km_sh_sample_norm N
WHERE A.attribute_name = N.col (+) AND A.attribute_confidence > 0.55
),
clust AS (
SELECT id,
CAST(COLLECT(Cattr(aname, op, TO_CHAR(val), support, confidence))
AS Cattrs) cl_attrs
FROM clus_tab
GROUP BY id
),
custclus AS (
SELECT T.cust_id, S.cluster_id, S.probability
FROM (SELECT cust_id, CLUSTER_SET(km_sh_clus_sample, NULL, 0.2 USING *) pset
FROM km_sh_sample_apply_prepared
WHERE cust_id = 101362) T,
TABLE(T.pset) S
)
SELECT A.probability prob, A.cluster_id cl_id,
B.attr, B.op, B.val, B.supp, B.conf
FROM custclus A,
(SELECT T.id, C.*
FROM clust T,
TABLE(T.cl_attrs) C) B
WHERE A.cluster_id = B.id
ORDER BY prob DESC, cl_id ASC, conf DESC, attr ASC, val ASC;
PROB CL_ID ATTR OP VAL SUPP CONF
------- ---------- --------------- --- --------------- ---------- -------
.7873 8 HOUSEHOLD_SIZE IN 9+ 126 .7500
.7873 8 CUST_MARITAL_ST IN Divorc. 118 .6000
ATUS
.7873 8 CUST_MARITAL_ST IN NeverM 118 .6000
ATUS
.7873 8 CUST_MARITAL_ST IN Separ. 118 .6000
ATUS
.7873 8 CUST_MARITAL_ST IN Widowed 118 .6000
ATUS
.2016 6 AGE >= 17 152 .6667
.2016 6 AGE <= 31.6 152 .6667
.2016 6 CUST_MARITAL_ST IN NeverM 168 .6667
ATUS
8 rows selected.
=======
Snippet Name: FEATURE_ID
Description: FEATURE_ID returns an Oracle NUMBER that is the identifier of the feature with the highest value in the row.
Used with feature extraction models that have been created using the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.
This example and the prerequisite data mining operations, including creation of the nmf_sh_sample model and nmf_sh_sample_apply_prepared view, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmnmdemo.sql.
FEATURE_ID(<schame.model> <mining_attribute_clause>)
SELECT FEATURE_ID(nmf_sh_sample USING *) AS feat, COUNT(*) AS cnt
FROM nmf_sh_sample_apply_prepared
GROUP BY FEATURE_ID(nmf_sh_sample USING *)
ORDER BY cnt DESC;
FEAT CNT
---------- ----------
7 1443
2 49
3 6
1 1
6 1
=========
No comments:
Post a Comment