RE: Have you heard about constraint type 'O'
- From: Jose Luis Delgado <joseluis_delgado@xxxxxxxxx>
- To: "'tpandian@xxxxxxxxx'" <tpandian@xxxxxxxxx>, Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 28 Jan 2005 12:17:29 -0800 (PST)
Hey Thiru ...
Here you have some examples
for constraints type: O & V
HTH
JL
Read Only Constraint - Type O
Read Only on a view CREATE OR REPLACE VIEW <view_name>
AS
<select statement>
WITH READ ONLY;
CREATE OR REPLACE VIEW person_reg_view AS
SELECT first_name, last_name, ssn
FROM person;
SELECT first_name FROM person;
UPDATE person_reg_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel';
SELECT first_name FROM person;
CREATE OR REPLACE VIEW person_ro_view AS
SELECT first_name, last_name, ssn
FROM person
WITH READ ONLY;
UPDATE person_ro_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel';
Check Option - Type V
Check option Specify WITH CHECK OPTION to indicate
that Oracle prohibits any changes to the table or view
that would produce rows that are not included in the
subquery.
CREATE OR REPLACE VIEW <view_name> AS
<select statement with WHERE clause>
WITH CHECK OPTION;
CREATE OR REPLACE VIEW insertable_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10000;
SELECT person_id, first_name, last_name FROM person;
INSERT INTO insertable_view
SELECT 7, 'Allen', 'Richards', dob, ssn
FROM person
WHERE ROWNUM = 1;
INSERT INTO insertable_view
SELECT 77777, 'Richard', 'Allen', dob, ssn
FROM person
WHERE ROWNUM = 1;
SELECT person_id, first_name, last_name FROM person;
CREATE OR REPLACE VIEW checkoption_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10
WITH CHECK OPTION;
INSERT INTO checkoption_view
SELECT 8, 'Natalie', 'Dressed', dob, ssn
FROM person
WHERE ROWNUM = 1;
INSERT INTO checkoption_view
SELECT 88, 'Rich', 'Poorly', dob, ssn
FROM person
WHERE ROWNUM = 1;
SELECT person_id, first_name, last_name FROM person;
--- "Powell, Mark D" <mark.powell@xxxxxxx> wrote:
> Type "O" is for READ ONLY on a view per the Oracle
> 10g Reference Manual
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
> Thiru Pandian
> Sent: Friday, January 28, 2005 2:55 PM
> To: Oracle-L@xxxxxxxxxxxxx
> Subject: Have you heard about constraint type 'O'
>
>
> I was browsing through Oracle 11i applsys schema and
> i found there is
> a constraint type called O . I saw all the
> constraint belongs to this
> type for AQ tables. I do not see any oracle
> documentation for this
> type. Any body know what for this used?.
> --
> Thiru Pandian
> Sr. Oracle DBA
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
