Question

  • Creator
    Topic
  • #2154199

    Oracle Forms on a keyless view

    Locked

    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?

All Answers

  • Author
    Replies
    • #2922162

      Clarifications

      by john.a.wills ·

      In reply to Oracle Forms on a keyless view

      Clarifications

    • #2922147

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

      by Anonymous ·

      In reply to Oracle Forms on a keyless view

      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.

    • #2922142

      Update on Oracle here..

      by Anonymous ·

      In reply to Oracle Forms on a keyless view

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

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

    • #2922100

      this is the view

      by john.a.wills ·

      In reply to Oracle Forms on a keyless view

      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

    • #2922099

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

      by Anonymous ·

      In reply to Oracle Forms on a keyless view

      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.

Viewing 4 reply threads