Question

Locked

Oracle Forms on a keyless view

By john.a.wills ·
I have a view to look at a lot of people whose information is in 2 different tables. Neither table has a key (sorry, I didn't define the database). When I address the view with an Oracle Form the first person appears in the list but with the Oracle error FRM-40501:unable to reserve record for update or delete. The underlying SQL error is ORA-01445: canot select ROWID from, or sample, a join view without a key-preserved table. I do need the user to be able to update one of the tables in the view. How do I achieve this?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

These might help you understand the errors. And hopefully a fix..

Oracle error FRM-40501:
Pressing [Display Error] provides more information, if it is available. You can also try to update or delete this record later. If necessary, contact your DBA.

SQL error ORA-01445:
Have you checked to see if a primary key exists on these tables.

Usually that causes the error ORA-1445. Also check patch level as it may also be a bug in the specific
version of Oracle.

From Metalink #101349.1

*** fails with ORA-1445 ***
ERROR at line 11:
ORA-01445: cannot select ROWID from a join view without a key-preserved table


Solution Description
--------------------

Either create primary key constraints on the base tables, or create the
materialized view with BUILD IMMEDIATE option:

(1) create pk constraints on the base table

SQL> alter table ross1 add constraint pk_ross1 primary key (ross1_coas_code);
SQL> alter table ross2 add constraint pk_ross2 primary key (ross2_coas_code);

(2) or, create the materialized view with BUILD IMMEDIATE

Please post back if you have any more problems or questions.

Collapse -

I know I have no keys...

by john.a.wills In reply to These might help you unde ...

...on the tables, and I cannot help that. I do not understand BUILD IMMEDIATE - I use CREATE VIEW to create my view. What is a "materialized" view?

Collapse -

Update on Oracle here..

http://www.orafaq.com/forum/t/46039/0/

Please post back if you have any more problems or questions.

Collapse -

this is the view

by john.a.wills In reply to Oracle Forms on a keyless ...

CREATE or REPLACE VIEW fwcotin(fwcotin_rowid,
fwcotin_activity_date,
fwcotin_amount,
fwcotin_obligation_code,
fwcotin_pidm,
fwcotin_tax_yr,
fwcotin_user_id,
fwcotin_id,
fwcotin_full_name)
/* John A. Wills 2008.07.31
Interface to Form fwcotin. The row ID from fwcoto2 is needed as a
key for the Form to write back to fwcoto2.
*/
AS
SELECT fwcoto2.rowid,
fwcoto2_ACTIVITY_DATE,
fwcoto2_amount,
fwcoto2_obligation_code,
fwcoto2_pidm,
fwcoto2_tax_yr,
fwcoto2_USER_ID,
SPRIDEN_ID,
DECODE(SPRIDEN_MI, '',
(SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME),
(SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME
|| ' ' || SUBSTR(SPRIDEN_MI,1,1) || '.'))
FROM fwcoto2 LEFT JOIN spriden ON fwcoto2_pidm = SPRIDEN_PIDM
AND SPRIDEN_CHANGE_IND IS NULL

Collapse -

Update the tables in the view.. Try below or the links...

You're not able to update a view directly using SQL.
Use an INSTEAD OF trigger.

http://www.orafaq.com/forum/t/46039/0/
http://forums.oracle.com/forums/message.jspa?messageID=2458966
http://dba.ipbhost.com/lofiversion/index.php/t1719.html
http://www.mydatabasesupport.com/forums/oracle-tools/67085-forms-9i-frm-40501-when-deleting-record.html
What you can do is to do a update on the software in question.
Not much use in Oracle. Just got the info from other people in the similar situation as you. I hope the links will give you more insight to your problem than i can solve at this time.

Please post back if you have any more problems or questions.

Back to IT Employment Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums