Solved

Oracle Forms6i: Update in a block based on updatable view.

Posted on 2001-07-26
12
2,846 Views
Last Modified: 2007-11-27

Hi

I'm trying to update a record in block that I based on an updatable view.

I can update the record using SQL*Plus.

I get
FRM-40602: Cannot insert into or update data in a view

Is it not possible to update, insert, or delete records from the detail table of a view based on a master-detail relationship?

Thanks
Huzaifa
0
Comment
Question by:HuzaifaMerchant
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 5

Expert Comment

by:ser6398
ID: 6324009
Does the view encompass a single table, or multiple tables?

If it encompasses multiple tables, then there are some complex rules to determine if you can insert into the separate tables through the view.  Try the following: make sure every NOT NULL column of each table is SELECTED by the view, make sure you have fields for this columns in your forms block, and make sure you are sending values for every NOT NULL column in your Insert statement (you can do this with the Forms debug tools).
0
 

Author Comment

by:HuzaifaMerchant
ID: 6324074
Thanks

I tried this out.

I have pasted the view and table definitions here.

I included the d.deptno to include all NOT NULL columns. But does not help.

I am also setting the QUERY ONLY property for d.deptno and d.dname to YES. But does not help.

Any ideas
Huzaifa

create or replace view emp_dept as
select e.empno, e.ename, e.deptno, d.deptno dept_no, d.dname
from emp e, dept d
where e.deptno = d.deptno;

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(2)

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
0
 

Author Comment

by:HuzaifaMerchant
ID: 6324094
To add...

The error message comes up at the point i try to edit the column.
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6324176
What are the INSERT_ALLOWED and UPDATE_ALLOWED property settings for the Blocks?  What are the settings for that specific column?
0
 

Author Comment

by:HuzaifaMerchant
ID: 6324202
BLOCK
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES

Columns
EMPNO
   QUERY_ONLY = NO
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES

ENAME
   QUERY_ONLY = NO
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES

EMPNO
   QUERY_ONLY = NO
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES

DEPTNO
   QUERY_ONLY = NO
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES


DEPT_NO (This one is from the DEPT table)
   QUERY_ONLY = YES
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES

DNAME (This is again from the DEPT table)
   QUERY_ONLY = YES
   INSERT_ALLOWED = YES
   UPDATE_ALLOWED = YES


I changed the INSERT_ALLOWED and UPDATE_ALLOWED to NO for DEPT_NO and DNAME. No luck.

Thanks
Huzaifa
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6324440
Hmm. I can't see anything wrong with what you have.  I'll pop back in if I think of anything else.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:mnarayan
ID: 6324498
I will try to simulate this on my machine.  
As a workaround you can write your own ON triggers like "ON-INSERT"
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6325678
anyway, press shift-f1 after you get the error to force forms to show the statement which is sent to the server.

maybe it shows, what is going wrong

(could be that a not database-item in the block is already marked as database-item, which is then included into the statement, which causes an invalid column error)
0
 
LVL 47

Accepted Solution

by:
schwertner earned 100 total points
ID: 6325693
If you want to INSERT, UPDATE, DELETE records based on views you have two ways. The FIRST one is to provide DB triggers of type "Instead of" to do these actions.

Example:
create or replace view CAND_PGRS_VIEW
as select
cp.id, cp.to_employee, cp.progress_type, cp.planned_on_date,
cp.held_on_date, cp.result, cp.remark,
cpt.name, cpt.place, cpt.in_use
from CANDIDATE_PROGRESS cp, CANDIDATE_PROGRESS_TYPE cpt
where cp.progress_type = cpt.id;


CREATE OR REPLACE TRIGGER candidate_progress_update
INSTEAD OF UPDATE  ON cand_pgrs_view
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
   UPDATE staff.candidate_progress
   SET  planned_on_date = :new.planned_on_date,
        held_on_date    = :new.held_on_date,
        result          = :new.result,
        remark          = :new.remark
   WHERE id = :new.id;
END candidate_progress_update;
/


CREATE OR REPLACE TRIGGER candidate_progress_input
INSTEAD OF INSERT  ON cand_pgrs_view
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
   INSERT INTO staff.candidate_progress
           (id, to_employee, progress_type, planned_on_date,
            held_on_date, result, remark )
   VALUES  (:new.id, :new.to_employee, :new.progress_type, :new.planned_on_date,
            :new.held_on_date, :new.result, :new.remark );
EXCEPTION
   WHEN duplicate_info THEN
   RAISE_APPLICATION_ERROR (
    num => -21107,
    msg => 'Duplicated Candidate Progress Record');
END candidate_progress_input;
/

In Forms you have a SECOND way. The functionality of the "Instead of" triggers may be implemented in ON-INSERT, ON-UPDATE triggers on block level which replace the standard Forms functionality at commot phase.

Good luck!
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6325833
when you select * from user_updatable_columns
you can see what the properties are of your view; if you can IUD in which columns
0
 

Author Comment

by:HuzaifaMerchant
ID: 6337442
I was reading about updatable views in the Oracle Advanced Forms and Reports book. The book says that if you have an updatable view, you do not need instead of triggers. But I could not get that working. Once I added the instead of triggers it works fine.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 6340137
Not every view is updatable. There are some restrictions. Read the article in the last but one issue of Oracle magazine. The problem is discussed very good there.

Good Luck!

Joseph Schwertner
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now