Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question, SANJAY.
Asked: January 28, 2017 - 10:11 pm UTC
Last updated: January 29, 2017 - 5:39 pm UTC
Version: 11.2
Viewed 10K+ times! This question is
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
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>
A reader, July 30, 2018 - 9:46 am UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library