Question on Updatable View!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Question on Updatable View!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Question on Updatable View!

    I have a view based on two tables joined by the id of a parent table with the same id as a foreign key in the child table. This is the select statement for my view.

    Code:
    SELECT L.AGENT_NUM, L.SCAC, L.LOI_DATE, L.LOI_REPL_DATE, F.AREA, L.LOI_CANCEL_DATE,
    L.BOOKING_AGENT, L.GBLOC
    FROM LOI_AUTHORITY L, FP_AREAS F
    WHERE L.FP_AREA_ID = F.ID
    This is the select on data dictionary to see what rows of views have update, insert and deletes.

    Code:
      1  SELECT column_name, updatable, insertable, deletable
      2   FROM user_updatable_columns
      3*  WHERE table_name = 'LOI_FP_AREAS_VIEW'
    oracle@CVLPL> /
    
    COLUMN_NAME                    UPD INS DEL
    ------------------------------ --- --- ---
    AGENT_NUM                      YES YES YES
    SCAC                               YES YES YES
    LOI_DATE                         YES YES YES
    LOI_REPL_DATE                 YES YES YES
    AREA                               NO  NO  NO
    LOI_CANCEL_DATE             YES YES YES
    BOOKING_AGENT               YES YES YES
    GBLOC                             YES YES YES
    
    8 rows selected.
    My question is when I use the view in my Oracle Forms 6i and try to do an update it does not allow me. I'm using Oracle 8i on windows NT. Does anyone has any clue as to what I am missing? Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    What error exactly?

    Forms may be trying to update the entire row which I doubt would be allowed because you have non-updateable columns.

    Have a look at the Block Property "Update Changed Columns Only".

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    Yes. You're right padders. When I took off the AREA field of parent and added the foreign key of child table in my view instead, it let me allowed to do an update and now I can use the foreign key to do a join. I had to create another data block to do a join between the foreign key in child and that parent data block. Since, I have a tabular style form it's not query for all the rows only for the first one.

    Anyways, I deleted the block to get the area and then had to write a select in POST-QUERY Trigger to get the value for each row. I know it's confusing but I'm just trying to do my best. If someone has a better way please let me know. Thanks.

    P.S. Thanks man. Changing the UPDATE CHANGED COLUMN ONLY PROPERTY ON BLOCK allows me to do an update.
    Last edited by see_one; 08-04-2005 at 12:42 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width