How to use two tables in view? which is referred in the block of the forms

[shankar muthusamy]

Hi All,

I am new to Oracle forms, I have a question,

I create a view with two tables, which is referred in the block of the froms, if i try to query the data it will populate the values but if i try to update the data it will not allow. But i need to update the data.

Query is like

select a.col1,b.col1
from table1
, (select b.col1,b.col2 from table2 ) t2
where a.col2 = b.col2


Please help me to proceed further.


Regards
Shankar

[milonov]

hello, Shankar.

Usually stored procedures are used to make an update, or if you use oracle 9i or higher you can write trigger "instead of update" for this view .and put your logic in it.

example of "instead of update" trigger for view:

CREATE OR REPLACE TRIGGER your_view_vu_u
      INSTEAD OF UPDATE
      ON your_view
      FOR EACH ROW
      BEGIN                   
          -- We can update col3
          IF (:NEW.col3 <> :OLD.col3) THEN
           UPDATE b  
            SET    col3 = :NEW.dname,
                      WHERE col2 = :OLD.col2;
          END IF;
      END;
 
 

Regards,
Michael Milonov
http://www.snotratech.com

Hi All,

I am new to Oracle forms, I have a question,

I create a view with two tables, which is referred in the block of the froms, if i try to query the data it will populate the values but if i try to update the data it will not allow. But i need to update the data.

Query is like

select a.col1,b.col1
from table1
, (select b.col1,b.col2 from table2 ) t2
where a.col2 = b.col2


Please help me to proceed further.


Regards
Shankar

[shankar muthusamy]

Hi Michael,

I need to do the updation from the front end. If i refer the view from the block then i will not allow to edit any columns.


Regards
Shankar.M

[milonov]

Hello, Shankar.

IMHO the right solution is to create "view", for example
"create or replace view your_view_name as
select * from your tables.... "
and then write trigger "instead of update" for this view (see code in previous post). But of cource it is advice only.

Best Regards,
Michael Milonov
http://www.snotratech.com

Hi Michael,

I need to do the updation from the front end. If i refer the view from the block then i will not allow to edit any columns.


Regards
Shankar.M