Oracle Reserved Words

List of Oracle Reserved Words

Oracle Reserved Words and Keywords
ACCESS	ACCOUNT	ACTIVATE
ADD	ADMIN	ADVISE
AFTER	ALL	ALL_ROWS
ALLOCATE	ALTER	ANALYZE
AND	ANY	ARCHIVE
ARCHIVELOG	ARRAY	AS
ASC	AT	AUDIT
AUTHENTICATED	AUTHORIZATION	AUTOEXTEND
AUTOMATIC	BACKUP	BECOME
BEFORE	BEGIN	BETWEEN
BFILE	BITMAP	BLOB
BLOCK	BODY	BY
CACHE	CACHE_INSTANCES	CANCEL
CASCADE	CAST	CFILE
CHAINED	CHANGE	CHAR
CHAR_CS	CHARACTER	CHECK
CHECKPOINT	CHOOSE	CHUNK
CLEAR	CLOB	CLONE
CLOSE	CLOSE_CACHED_OPEN_CURSORS	CLUSTER
COALESCE	COLUMN	COLUMNS
COMMENT	COMMIT	COMMITTED
COMPATIBILITY	COMPILE	COMPLETE
COMPOSITE_LIMIT	COMPRESS	COMPUTE
CONNECT	CONNECT_TIME	CONSTRAINT
CONSTRAINTS	CONTENTS	CONTINUE
CONTROLFILE	CONVERT	COST
CPU_PER_CALL	CPU_PER_SESSION	CREATE
CURRENT	CURRENT_SCHEMA	CURREN_USER
CURSOR	CYCLE
DANGLING	DATABASE	DATAFILE
DATAFILES	DATAOBJNO	DATE
DBA	DBHIGH	DBLOW
DBMAC	DEALLOCATE	DEBUG
DEC	DECIMAL	DECLARE
DEFAULT	DEFERRABLE	DEFERRED
DEGREE	DELETE	DEREF
DESC	DIRECTORY	DISABLE
DISCONNECT	DISMOUNT	DISTINCT
DISTRIBUTED	DML	DOUBLE
DROP	DUMP	EACH
ELSE	ENABLE	END
ENFORCE	ENTRY	ESCAPE
EXCEPT	EXCEPTIONS	EXCHANGE
EXCLUDING	EXCLUSIVE	EXECUTE
EXISTS	EXPIRE	EXPLAIN
EXTENT	EXTENTS	EXTERNALLY
FAILED_LOGIN_ATTEMPTS	FALSE	FAST
FILE	FIRST_ROWS	FLAGGER
FLOAT	FLOB	FLUSH
FOR	FORCE	FOREIGN
FREELIST	FREELISTS	FROM
FULL	FUNCTION	GLOBAL
GLOBALLY	GLOBAL_NAME	GRANT
GROUP	GROUPS	HASH
HASHKEYS	HAVING	HEADER
HEAP	IDENTIFIED	IDGENERATORS
IDLE_TIME	IF	IMMEDIATE
IN	INCLUDING	INCREMENT
INDEX	INDEXED	INDEXES
INDICATOR	IND_PARTITION	INITIAL
INITIALLY	INITRANS	INSERT
INSTANCE	INSTANCES	INSTEAD
INT	INTEGER	INTERMEDIATE
INTERSECT	INTO	IS
ISOLATION	ISOLATION_LEVEL	KEEP
KEY	KILL	LABEL
LAYER	LESS	LEVEL
LIBRARY	LIKE	LIMIT
LINK	LIST	LOB
LOCAL	LOCK	LOCKED
LOG	LOGFILE	LOGGING
LOGICAL_READS_PER_CALL	LOGICAL_READS_PER_SESSION	LONG
MANAGE	MASTER	MAX
MAXARCHLOGS	MAXDATAFILES	MAXEXTENTS
MAXINSTANCES	MAXLOGFILES	MAXLOGHISTORY
MAXLOGMEMBERS	MAXSIZE	MAXTRANS
MAXVALUE	MIN	MEMBER
MINIMUM	MINEXTENTS	MINUS
MINVALUE	MLSLABEL	MLS_LABEL_FORMAT
MODE	MODIFY	MOUNT
MOVE	MTS_DISPATCHERS	MULTISET
NATIONAL	NCHAR	NCHAR_CS
NCLOB	NEEDED	NESTED
NETWORK	NEW	NEXT
NOARCHIVELOG	NOAUDIT	NOCACHE
NOCOMPRESS	NOCYCLE	NOFORCE
NOLOGGING	NOMAXVALUE	NOMINVALUE
NONE	NOORDER	NOOVERRIDE
NOPARALLEL	NOPARALLEL	NOREVERSE
NORMAL	NOSORT	NOT
NOTHING	NOWAIT	NULL
NUMBER	NUMERIC	NVARCHAR2
OBJECT	OBJNO	OBJNO_REUSE
OF	OFF	OFFLINE
OID	OIDINDEX	OLD
ON	ONLINE	ONLY
OPCODE	OPEN	OPTIMAL
OPTIMIZER_GOAL	OPTION	OR
ORDER	ORGANIZATION	OSLABEL
OVERFLOW	OWN	PACKAGE
PARALLEL	PARTITION	PASSWORD
PASSWORD_GRACE_TIME	PASSWORD_LIFE_TIME	PASSWORD_LOCK_TIME
PASSWORD_REUSE_MAX	PASSWORD_REUSE_TIME	PASSWORD_VERIFY_FUNCTION
PCTFREE	PCTINCREASE	PCTTHRESHOLD
PCTUSED	PCTVERSION	PERCENT
PERMANENT	PLAN	PLSQL_DEBUG
POST_TRANSACTION	PRECISION	PRESERVE
PRIMARY	PRIOR	PRIVATE
PRIVATE_SGA	PRIVILEGE	PRIVILEGES
PROCEDURE	PROFILE	PUBLIC
PURGE	QUEUE	QUOTA
RANGE	RAW	RBA
READ	READUP	REAL
REBUILD	RECOVER	RECOVERABLE
RECOVERY	REF	REFERENCES
REFERENCING	REFRESH	RENAME
REPLACE	RESET	RESETLOGS
RESIZE	RESOURCE	RESTRICTED
RETURN	RETURNING	REUSE
REVERSE	REVOKE	ROLE
ROLES	ROLLBACK	ROW
ROWID	ROWNUM	ROWS
RULE	SAMPLE	SAVEPOINT
SB4	SCAN_INSTANCES	SCHEMA
SCN	SCOPE	SD_ALL
SD_INHIBIT	SD_SHOW	SEGMENT
SEG_BLOCK	SEG_FILE	SELECT
SEQUENCE	SERIALIZABLE	SESSION
SESSION_CACHED_CURSORS	SESSIONS_PER_USER	SET
SHARE	SHARED	SHARED_POOL
SHRINK	SIZE	SKIP
SKIP_UNUSABLE_INDEXES	SMALLINT	SNAPSHOT
SOME	SORT	SPECIFICATION
SPLIT	SQL_TRACE	STANDBY
START	STATEMENT_ID	STATISTICS
STOP	STORAGE	STORE
STRUCTURE	SUCCESSFUL	SWITCH
SYS_OP_ENFORCE_NOT_NULL$	SYS_OP_NTCIMG$	SYNONYM
SYSDATE	SYSDBA	SYSOPER
SYSTEM	TABLE	TABLES
TABLESPACE	TABLESPACE_NO	TABNO
TEMPORARY	THAN	THE
THEN	THREAD	TIMESTAMP
TIME	TO	TOPLEVEL
TRACE	TRACING	TRANSACTION
TRANSITIONAL	TRIGGER	TRIGGERS
TRUE	TRUNCATE	TX
TYPE	UB2	UBA
UID	UNARCHIVED	UNDO
UNION	UNIQUE	UNLIMITED
UNLOCK	UNRECOVERABLE	UNTIL
UNUSABLE	UNUSED	UPDATABLE
UPDATE	USAGE	USE
USER	USING	VALIDATE
VALIDATION	VALUE	VALUES
VARCHAR	VARCHAR2	VARYING
VIEW	WHEN	WHENEVER
WHERE	WITH	WITHOUT
WORK	WRITE	WRITEDOWN
WRITEUP	XID	YEAR
ZONE

Oracle Pivot

Oracle / PLSQL: PIVOT Clause

This Oracle tutorial explains how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in Oracle/PLSQL is:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

IN ( expr1, expr2, ... expr_n )

A list of values for column2 to pivot into headings in the cross-tabulation query results.

subquery

It can be used instead of a list of values. In this case, the results of the subquery would be used to determine the values for column2 to pivot into headings in the cross-tabulation query results.

Applies To

The PIVOT clause can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g

Example

Let's look at how to use the PIVOT clause in Oracle.

We will base our example on a table called orders with the following definition:

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

To show you the data for this example, we will select the records from the orderstable with the following SELECT statement:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

These are the records in the orders table. We will be using these records to demonstrate how the PIVOT clause works:

order_id	customer_ref	product_id
50001	SMITH	10
50002	SMITH	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	SMITH	20
50008	SMITH	10
50009	SMITH	20

Now, let's create our cross-tabulation query using the following PIVOT clause:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

In this example, the PIVOT clause would return the following results:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Fields to Include

First, we want to specify what fields to include in our cross tabulation. In this example, we want to include the customer_ref and product_id fields. This is done by the following portion of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)

You can list the columns to be included in any order.

Specify Aggregate Function

Next, we need to specify what aggregate function to use when creating our cross-tabulation query. You can use any aggregate such as SUM, COUNT, MIN,MAX, or AVG functions.

In this example, we are going to use the COUNT function. This will count the number of product_id values that meet our criteria. This is done by the following portion of the statement:

PIVOT
(
  COUNT(product_id)

Specify Pivot Values

Finally, we need to specify what pivot values to include in our results. These will be used as the column headings in our cross-tabulation query. You can use either be a list of values enclosed in parentheses or a subquery to specify the pivot values.

In this example, we are going to return only the following product_id values: 10, 20, 30. These values will become our column headings in our cross-tabulation query. Also, note that these values are a finite list of the product_id values and will not necessarily contain all possible values.

This is done by the following portion of the statement:

  FOR product_id IN (10, 20, 30)
)

Now when we put it all together, we get the following pivot table:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

analytic query

analytic query to got number of rows 

select row_number() over ( order by branch ) as rnum, a.*
  from student
 where name like '%ram%'

Oracle sql script list of all countries of the world

Oracle sql script list of all countries of the world

only 5$

yasser.hassan@yandex.com

Sql list of all cities and Governorates in Egypt

Here I have Oracle Sql script for list of all cities and Governorates in Egypt
it is cost 5$
email me to got it
yasser.hassan@yandex.com

Oracle Delete dublicated rows

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

character set problems

You just installed Oracle Database XE version (10g or 11g) and try to connect with forms 6i. When you put the connection string and hit ENTER to connect then Forms/Reports builder hang and "Forcefully Closed".

What happen ?

It's just about the CHARACTER SET mismatch.  Typically XE database version most surelly 
11g XE use Unicode CHARACTER SET, which is "AL32UTF8".

Forms/Reports 6i doesn't support this and supports ("UTF8","WE8MSWIN1252")

Moreover,

Oracle Database 11g Express Edition has no provisions to change/use other charactersets than AL32UTF8 as NLS_CHARACTERSET and AL16UTF16 as NLS_NCHAR_CHARACTERSET
The NLS_CHARACTERSET is used for CHAR, VARCHAR2, LONG and CLOB columns;
The NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2 and NCLOB columns.

So, if you migrate data from any previous version of Database like 10g with special character, you will see the bellow problem when importing. 

SQL> create table t( c varchar2(3) );

SQL> insert into t values( 'abç' );
insert into t values( 'abç' )
*
ERROR at line 1:
ORA-12899: value too large for column "SCH"."T"."C" (actual: 4, maximum: 3)


What to do ?
Simply alter the CHARACTER SET to previously supported one.

[Script]

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>CD C:\oraclexe\app\oracle\product\11.2.0\server\bin

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SET ORACLE_HOME=C:\oraclexe\app
\oracle\product\11.2.0\server

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SET ORACLE_SID=XE

C:\oraclexe\app\oracle\product\11.2.0\server\bin>echo %ORACLE_SID%
XE

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SQLPLUS/NOLOG

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 23 19:53:42 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> CONN SYS/SYSTEM11g AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             272633544 bytes
Database Buffers          255852544 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;

Database altered.

SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             272633544 bytes
Database Buffers          255852544 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.
SQL>SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8MSWIN1252

NLS_NCHAR_CHARACTERSET
AL16UTF16