Tom,
I have problem while updating or even selecting if the statement contains FOR UPDATE OF clause against a view where instead of trigger perform insert, update, delete. One of view column is a calculated column which contains decode function and display 2 char prod id and update 4 char data into table by prefixing "11".
Here the FOR UPDATE OF query cannot be changed as it is generated from an old application for which we dont have source code. Our requirement is user should not hit the table directly. Is there way to get rid of this error Oracle ?
I have a table Product and view p_product on table product and it will give all columns as such but product id will returned as
2 byte data if table having 4 byte data. Table have 4 char prod_id but application know only 2 char prod id.
CREATE TABLE ABC.PRODUCT
(PROD_CATEGORY VARCHAR2(10),
PROD_ID VARCHAR2(4));
CREATE OR REPLACE FORCE VIEW ABC.P_PRODUCT
(PROD_CATEGORY,
PROD_ID)
AS
SELECT
PROD_CATEGORY,
CAST (
DECODE (LENGTH (PROD_ID),
4, SUBSTR (PROD_ID, 3),
PROD_ID) AS VARCHAR2 (2))
FROM ABC.PRODUCT;
Instead of trigger T_product on P_view will just update the value into table as 4 byte prod_id if value is 2 byte.
The below query is accessing the view for update.
SELECT PROD_CATEGORY,
PROD_ID
FROM P_PRODUCT
FOR UPDATE OF
PROD_CATEGORY,
PROD_ID;
Error:
ORA-01733: virtual column not allowed here
Regards
Sanjay.P
You'll need to give us a complete test case
SQL>
SQL> CREATE TABLE PRODUCT
2 (PROD_CATEGORY VARCHAR2(10),
3 PROD_ID VARCHAR2(4));
Table created.
SQL>
SQL> insert into product values ('x','y');
1 row created.
SQL>
SQL> CREATE OR REPLACE VIEW P_PRODUCT
2 (PROD_CATEGORY,
3 PROD_ID)
4 AS
5 SELECT
6 PROD_CATEGORY,
7 CAST (
8 DECODE (LENGTH (PROD_ID),
9 4, SUBSTR (PROD_ID, 3),
10 PROD_ID) AS VARCHAR2 (2))
11 FROM PRODUCT;
View created.
SQL>
SQL>
SQL> create or replace
2 trigger trg
3 instead of update
4 on p_product
5 for each row
6 begin
7 null;
8 end;
9 /
Trigger created.
SQL>
SQL> update p_product
2 set prod_id = '1';
1 row updated.
SQL>
SQL> create or replace
2 trigger trg
3 instead of update
4 on p_product
5 for each row
6 begin
7 update product set prod_id = :new.prod_id;
8 end;
9 /
Trigger created.
SQL>
SQL> update p_product
2 set prod_id = '1';
1 row updated.
SQL>
SQL>