Alter table to add columns with default value
ALTER TABLE EMP
ADD EMP_STATUS VARCHAR2(1) DEFAULT ‘D’ NOT NULL
In Oracle 11g, a table can be set as READ ONLY.
In READ ONLY mode, a table can only be queried;
DML and DDL (Truncate and Alter) operations are restricted on such tables.
At any point of time in a session,
the table mode can be switched from READ WRITE or READ ONLY mode
and vice versa. Note that the table can be created in READ WRITE mode only.
ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]
IGNORE_ROW_ON_DUPKEY_INDEX hint
During a direct loading Insert process, unique key conflict in data can result into process failure. To make this check passive during the process, Oracle 11g furnishes a new hint IGNORE_ROW_ON_DUPKEY_INDEX.
Enhancements to improve upon work around solutions and enhance usability
1. Sequence usage
In Oracle 11g, sequence value assignment can be used as a PL/SQL construct. Earlier, sequence value was used to be fetched through a SELECT statement, which added up the overhead of context switch between SQL and PL/SQL engines.
Example Code
Demonstration of Sequence assignment as a PL/SQL construct.
DECLARE
L_ID NUMBER;
BEGIN
L_ID:=EMP_SEQ.NEXTVAL;
END;
Named and Mixed notation
Oracle 11g SQL now supports the function call with both named and positioned parameters. This is known as mixed notation of a function call in an SQL statement.
A function F_PRIMECOUNT in Example Code [6] accepts two parameters to count the numbers prime numbers between the ranges.
SQL> SELECT F_PRIMECOUNT (P_A => 10, 20) FROM DUAL;
ALTER TABLE EMP
ADD EMP_STATUS VARCHAR2(1) DEFAULT ‘D’ NOT NULL
In Oracle 11g, a table can be set as READ ONLY.
In READ ONLY mode, a table can only be queried;
DML and DDL (Truncate and Alter) operations are restricted on such tables.
At any point of time in a session,
the table mode can be switched from READ WRITE or READ ONLY mode
and vice versa. Note that the table can be created in READ WRITE mode only.
ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]
IGNORE_ROW_ON_DUPKEY_INDEX hint
During a direct loading Insert process, unique key conflict in data can result into process failure. To make this check passive during the process, Oracle 11g furnishes a new hint IGNORE_ROW_ON_DUPKEY_INDEX.
Enhancements to improve upon work around solutions and enhance usability
1. Sequence usage
In Oracle 11g, sequence value assignment can be used as a PL/SQL construct. Earlier, sequence value was used to be fetched through a SELECT statement, which added up the overhead of context switch between SQL and PL/SQL engines.
Example Code
Demonstration of Sequence assignment as a PL/SQL construct.
DECLARE
L_ID NUMBER;
BEGIN
L_ID:=EMP_SEQ.NEXTVAL;
END;
Named and Mixed notation
Oracle 11g SQL now supports the function call with both named and positioned parameters. This is known as mixed notation of a function call in an SQL statement.
A function F_PRIMECOUNT in Example Code [6] accepts two parameters to count the numbers prime numbers between the ranges.
SQL> SELECT F_PRIMECOUNT (P_A => 10, 20) FROM DUAL;
