IT Employment



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.

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..

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,
/* 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.
SELECT fwcoto2.rowid,
|| ' ' || SUBSTR(SPRIDEN_MI,1,1) || '.'))
FROM fwcoto2 LEFT JOIN spriden ON fwcoto2_pidm = SPRIDEN_PIDM

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.
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.

Related Discussions

Related Forums