Update data in view

Advertisement
I created a view in program unit (procedure).
when new form instance I call this
and created a block base with this view
it has detail block
I have one column chk varchar2(1)
it is a check box
I need when I check this box chk will be 'Y'
I mean update the view . if I check the box 4 records all the records chk must be 'Y' in view
I write a code on-update trigger;
if :challan1_view.chk = 'Y' then
update challan_view
set chk ='Y';
end if;but not saving CHK ='Y'
Edited by: Kame on Aug 17, 2009 11:15 PM
Advertisement

Replay

Dear Francois
Answers: 1. this is a procedure
PROCEDURE so_view IS
BEGIN
---------- Creating First view----------------
declare
     V_COMMAND  varchar2(5000);
begin
V_COMMAND := 'Create or replace view Challan_view as
           select  qry1.dcno,qry1.dcdate,qry1.customername,qry1.perscode,qry1.persname,qry1.commission,qry1.netrate,
        qry1.itemcode,qry1.itemname,qry1.itemunit,qry1.dcrate,qry1.dcqty,qry1.disc_pers,qry1.disc_amount,
        qry1.tax_perc,qry1.tax_amount,qry1.netamount,qry1.chk,
        qry2.idcqty, nvl(qry1.dcqty,0) - nvl(qry2.idcqty,0) Remaining
from
(select a.dcno,a.dcdate,a.customername,a.perscode,a.persname,a.commission,a.netrate,
        b.itemcode,b.itemname,b.itemunit,b.dcrate,b.dcqty,b.disc_pers,b.disc_amount,
        b.tax_perc,b.tax_amount,b.netamount,b.chk
        from masterdc a , dc_detail b
        where a.dcno = b.dcno) qry1,
(select b.dcno, b.dcdate,b.itemcode,b.itemname,
        b.dcqty IDCQTY
        from masterinv a , inv_detail b
        where a.invno = b.invno) qry2
where qry1.dcno = qry2.dcno (+)
--and qry1.itemcode = qry2.itemcode(+)
and nvl(qry2.idcqty,0) < nvl(qry1.dcqty,0)';
FORMS_DDL(V_COMMAND);
end;
declare
     V_COM  varchar2(5000);
begin
V_COM := 'Create or replace view challan1_view as
            select dcno,dcdate,customername,perscode,persname,commission,netrate from challan_view
          group by dcno,dcdate,customername,perscode,persname,commission,netrate';
FORMS_DDL(V_COM);
end;
END;Answer 2:
I have check in list of table/views its created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
CHALLAN1_VIEW                  VIEW
CHALLAN_VIEW                   VIEW
CITY                           TABLE
CUSTOMER                       TABLE
DEPT                           TABLE
DETAILDC_VIEW                  VIEW
DETAILSO_VIEW                  VIEW
ITEM                           TABLE
MASTERDC_VIEW                  VIEW
MASTERSO_VIEW                  VIEW
SOVIEW_DETAIL                  VIEW
USERS                          TABLE
SQL> after that I created a block from views.