Sunday, March 22, 2009

Create Operation for Master-Detail Relationship

Its very frequent requirement in Oracle ADF applications, when developer needs to implement Master-Detail relationship with Create functionality. Actually, its very easy to implement Master-Detail in ADF, the same is for Create operation. However, there is one thing you should know, especially if you are new to ADF. Let's say you are implementing functionality to allow users to insert data into Master and into Detail, its fine - you can just drag and drop Create or CreateInsert action from Data Control. But you will face a problem, when user will try to insert Master and Detail record during the same transaction:


There will be a problem, because framework will try to commit Detail record without any value for foreign key. There will be no value for foreign key, because Master record is still not committed and sequence number is not assigned for Master record primary key. When user will try to close transaction by committing new records from Master and Detail, integrity constraint violation error will be thrown by database:


From my practice, its quite common problem and many developers are facing this. However, there is one feature in Oracle ADF, that helps to solve described problem. You just need to open Entity Association wizard for those Entities involved into Master-Detail relationship:


And basically to select one checkbox in Behavior group, this checkbox is Composition Association. By selecting this option, you will force framework to use so called strong association between two Entities. With strong association, framework automatically will ensure correct foreign key value propagation for Detail record:


And there is no additional changes, Create functionality now will work for both - Master and Detail in the same transaction. Just make sure you didn't forgot to set DBSequence type for Primary Keys in your Entities. Both new records, Master and Detail, now can be commited together:


It works, but its important to know - when you are using Composite Association, you can't use Detail Entity without Master Entity in the same page. You can try to put Detail (in my case Departments Entity) into separate page and to invoke any CRUD operation, you will get Invalid Owner Exception:


More about this case you can read from Steve Muench blog - Why Do I Get the InvalidOwnerException?.

You can download and run my sample application - CreateMasterDetail.zip, developed with JDeveloper 11g for this post.

Spanish Summary:

El desarrollo de pantallas master-detail son comunes y sencillas de realizar usando ADF. Sin embargo, si se pretende grabar simultaneamente ambos registros, saldrá un error debido a que requerirá grabar primer la información que esta en cabecerá y luego la información que se encuentra en el detalle. Para evitar la salida de un error indecifrable, Andrejus nos muestra un tip muy útil en este post.

57 comments:

  1. Andrejus,

    I've been facing similar problems with creation, but in my case it's the composition association itself which causes problems (a TooManyObjectsException during commit).

    See here for the complete description.

    I've been able to consistently reproduce the problem with a sample application on a bare-bone schema with 2 tables using cascade delete. DBSequences and composition association always on, of course. The second commit after the first seems to work.

    Can you offer some help on why you don't seem to have this problem?

    thx,
    RickyS

    ReplyDelete
  2. ...also, when running your sample with a new test location and a new test dept exactly as you show in your screenshots, I get the following error when saving:

    ...
    ORA-01400: cannot insert NULL into ("HR"."LOCATIONS"."LOCATION_ID")
    ORA-06512: at line 1

    both the temporary keys (PK on Locations and FK on Deps) thus remain set to -4.

    ReplyDelete
  3. Hi,

    You are getting this error, because there are no Triggers in database to assign next sequence number. You need to create those triggers for Locations and Departments sequences.

    Regards,
    Andrej

    ReplyDelete
  4. Thx, I did forgot that HR has sequences but no triggers.

    Now I'm trying to make your example break as mine does. I've even enabled "Cascade Delete" and "Optimize for cascade delete" in the association in your app to make it behave like mine, but it still seems to work.
    This is puzzling: what are you doing that I'm not?

    Are you maybe using an internal build of ADF where a problem with this might have been fixed? Mine is plain 11.1.1.0.1 of course.

    ReplyDelete
  5. No, I'm using latest build available on OTN - 5188. Same as you I think.

    Regards,
    Andrejus

    ReplyDelete
  6. ok, it seems I've nailed my problems down by comparing *everything* in the 2 applications and spot and remove any significant differences.

    It likely was a combination of several things, with the main culprit being that I used commandToolbarButtons instead of commandButtons for the CreateInsert operation.
    Didn't realize they make submits behave differently (as well as PPR updates of the second table).

    Anyway, thanks for providing a working example, it was really helpful.

    Riccardo

    ReplyDelete
  7. Hi Andrejus.

    How can I do this operation with two "add form". One Form for master inputs and other Form for details in the same page???

    Thx and regards

    ReplyDelete
  8. Hi,

    You can use wizard to generate such kind of form, it supports Master Form and Detail Form as standard functionality.

    Regards,
    Andrej

    ReplyDelete
  9. This wizard (master form and deatil form) supports insert operation for both forms?? (as your example above)

    I have a little constrain problem when i tried to insert data into 2 tables (master and child).

    Regards and thanks foor your time..

    ReplyDelete
  10. Thanks Andrejus.

    It was exactly what I was looking for.

    Congratulations for the article.

    Regards,
    Garcia

    ReplyDelete
  11. Thanks, I'm happy it helped ;)

    Andrejus

    ReplyDelete
  12. Do you have any tips to make inserts easy to work for many-to-many associations? I'm asking because I have created a link for the association, and jdeveloper tries to insert the key of the master to the detail table instead of the intersection table.

    Thanks in advance.

    ReplyDelete
  13. Actually any way to make many-to-many associations work with inserts would be appreciated.

    ReplyDelete
  14. I am having a problem. What i am doing is that I am saving the Master and Detail through my service method, in which i assign ids to master and detail. But when i create both master and detail first time, i get no detail item in my service method but when i create first master and save it and then enter detail items, i get everything in my service method. What can be the problem? kindly help me

    ReplyDelete
  15. Hi Andrejus,

    Could you please give me a step by step example about master-detail Form where both the form will have create operation?

    Regards
    Rm.

    ReplyDelete
  16. For step by step, you can check Oracle tutorials... Or Grant Ronald book - Quick start development for Fusion Apps.

    I'm not focusing on step by step, but on problem solving.

    Andrejus

    ReplyDelete
  17. Hi Andrejus,
    My requirement is to add multiple model number and price for one product.like one product can have multiple model numbers.

    and I need to develop screen like
    Product ADD screen

    Product Name: I/p text box

    Product table:
    with column-- select radio, model no, price
    add button

    save button

    The Actor clicks the Add button. New row added to enter model and price for the product.

    with your application I can create row for detail but what i want is add product should be blank screen once user get into this he will enter new product name and below enter number of models and price for that product can you tell me how can i do this?

    ReplyDelete
  18. Hi I resolved this by using

    getViewObj().executeEmptyRowSet();

    ReplyDelete
  19. Hi Andrejus,
    I have a similar master detail table but couple of columns in that are cascading lov's and one column in child table is being populated from static lov.

    The issue that i am facing is that cascading lov's are not being filtered. Also when i create a couple of child rows, on partial refresh both rows in child column being populated from static lov are being reset to first static lov value. Could you kindly help.

    ReplyDelete
  20. The issue with child rows being refreshed to same value on ppr is really annoying.Is that a bug ?

    https://gist.github.com/1200854

    ReplyDelete
  21. Are you using ppr refresh setting for iterator inside bindings? i would recommend to set it to default and to ppr just in JSF.

    Andrejus

    ReplyDelete
  22. Hi andrejusb,
    No i have not set changeventpolicy to ppr. but still child rows are being refreshed to same value.

    ReplyDelete
  23. Then it might be View Link or Association is broken in ADF BC. Does it work in ADF BC tester?

    Andrejus

    ReplyDelete
  24. Yes, It is working there i have only changed the row state for master row to STATUS_NEW so that i do not receive the JBO exception. Is that the cause ?

    ReplyDelete
  25. May be you can send your sample to me. So I will look whats wrong.

    Andrejus

    ReplyDelete
  26. Hi,

    Sent the code to you on your mail

    ReplyDelete
  27. You should send sample application, I saw only some files - I can't test it.

    Andrejus

    ReplyDelete
  28. Dear Andrejus,

    thx for all of your article, i read it one by one. for about 4 month i'm using your article as reference in my study case, now i stuck. hahaha, seems funny when i'm stressing out.
    sorry, back to the case, i want to ask you how can i save multiple row in master detail with committ button ? when i tried it, it's just committing the first line. always and always. i'm a little frustated here.

    thx in advance for your time.
    JoeleZ

    ReplyDelete
  29. Hi,

    Typically it should commit all changes, from all rows. You should enable detail log (jbo.debugoutput=console) on View Controller. Restart WebLogic and see what SQL statements are executed.

    Andrejus

    ReplyDelete
  30. Thx a lot andrejus,

    i got it now,

    it's just inserting the first row,
    because when i click create insert nothing happen in database table, there is no sign initiating new row, but the UI showing new row.

    thats the problem. how can i resolvethis problem?

    thx before,
    JoeleZ

    ReplyDelete
  31. Its definitely something basic. Does my sample app from this post works for you, to insert multiple records?

    Andrejus

    ReplyDelete
  32. thx a lot andrejus.

    i Got it. may be the problem was the assoc or entity.

    it's solved now. i don't know what make my app error like before. but, when i try to dump my last app and creating a new one, it's work fine....

    thx guru... ^^
    this sample plus redeveloping summit adf from oracle giving me a lot of new technique.

    Yoel

    ReplyDelete
  33. Hi Andrejus,
    I would like to get help or guideline on hanlding my case with adf master detail relationship.
    Let us use order (master) and lineItem(detail) case. The first page in one task flow has a list of orders, selecting one row nagivates to the the second page in second task flow, which contains a list of related lineItem. Clicking on "Add" on the page pops up a dialog to create a
    lineItem. Popup page aslo has a taskflow.
    The orderId is passed along to the task flows. In the popup task, I need to create a lineItem object before displaying the default page for lineItem.
    1. What is the best way to create default lineItem with its parent order ? Use orderId to retrieve OrderView object, get LineItem iterator, and create the new lineItem. Where should the logics be ? Could you provide some example codes ?
    2. How is the lineItem object created in the step 1 binding to the page def to display in java codes ?
    I have spent quite bit of time to find a solution. I appreciate your help and guideline.
    Thank you very much for all your articles. I have read lots of them.
    David

    ReplyDelete
  34. If I understood correctly, you need to create default lineItem, before popup is opened. You can do this from popUpFetchListener method declared on popup. This method will be invoked by the framework before popup is rendered.

    As I understood, master Order VO is already set with current row - means is enough to get detail LineItem VO and insert default Line item.

    Regards,
    Andrejus

    ReplyDelete
  35. Hi Andrejus,
    Thnks for a wonderful post ,this is what I was looking for implementing in my app.
    I require to Call both the create insert (For parent & child) at the same time on press of a single button.
    I an using Jdev 11.1.2.1

    ReplyDelete
  36. Hi Andre,

    First of all Thank you very much for your posts. which are really helpful than the normal oracle books.

    There will be two creatInsert operations in the datacontrol right? One for maste rand one for detail. So whihc createInsert button do I need to drag on drop? This only button will create records for both master and detail. Please explain.

    ReplyDelete
  37. You need to drag and drop both - one for master, another for detail.

    Andrejus

    ReplyDelete
  38. Hi,

    How can we do this insert in BPM for there one table using DB Adapter as external reference.

    ReplyDelete
  39. I have a self referencing hierarchical table (tree) for which each row has a parent row unless it is a root node in which case the parent id can be null. My tree table is editable and supports drag and drop so I can move nodes around. I wanted to make it work so that I could insert multiple levels in the tree with one commit. I got this to work by setting the Composition Association however there is one problem in that I can no longer create root nodes that don't have a parent. Since the master entity is the same as the detail entity it won't let me insert a master record with no parent. (InvalidOwnerException) I can insert the root row externally but I might want to add multiple root nodes through the UI. Any idea how to solve this? My one thought is to create a separate entity association and view on the same table which doesn't have the composition setting and use that whenever I need to commit a root node. I think that will probably work but was wondering if you knew of an easier way. Thanks

    ReplyDelete
  40. Instead of trying to make the composite relationship work with my self referencing table I added code to the created method of the entity to retrieve the next sequence. That way the child rows will have a real sequence number from the parent when inserted. This will burn sequence numbers if for some reason I roll back or don't commit but that's not much of a concern.

    @Override
    protected void create(AttributeList attributeList) {
    super.create(attributeList);
    DBTransaction transaction = getDBTransaction();
    SequenceImpl sequence = new SequenceImpl("JURISDICTION_SEQ", transaction);
    this.setJurisdictionId(new DBSequence(sequence.getSequenceNumber()));
    }

    ReplyDelete
  41. Also if you want to avoid gaps in the sequence, you can use another temporary sequence in create method and then apply real sequence number in doDML. Probably I will post a blog about this soon.

    I'm glad you was able to resolve it without composite association.

    Regards,
    Andrejus

    ReplyDelete
  42. Hi Andrejus,

    Thanks for this post, it's a prickly subject without many good answers.

    What's your take on the situation where you have a references relationship between two tables and want to be able to, in separate situations, insert into the tables individually, but also insert into both as a single transaction?

    As I see it, the composition association makes things difficult in that situation, so one must use a references relationship and add code to the create() method of the entities to synchronise the PK and FK with values obtained directly from a sequence.

    I can't find a solution that I don't find cumbersome. I feel like the framework is getting in the way here rather than helping out.

    Your experience and advice would be much appreciated.

    Many thanks,
    Laurence.

    ReplyDelete
  43. Hi,

    You should override postChanges to control post order of Master-Detail: http://docs.oracle.com/cd/E16162_01/web.1112/e16182/bcentities.htm#ADFFD1152

    Andrejus

    ReplyDelete
  44. Thanks!! This was exactly what I was struggling with.

    ReplyDelete
  45. Hi,

    I have master and Child tables Order and Lines.

    I have view link between them based on the Source Order ID. When i use autoSubmit=true for Header, the source order Id get populate in Lines as well and we can commit at a time. But i don't want to use the autousubmit=true for header as it refreshes the whole header and may loose some data that is not committed. please let me know, how to populate the PK references in Lines.

    ReplyDelete
  46. Hi Andrejus,
    It seems that it's possible to insert both master and detail in the same page by adding :

    setNewRowState(Row.STATUS_NEW);

    on the current row of the master.
    I've tried it and it works

    Can you just confirm this to me

    ReplyDelete
  47. Hi,

    I dont think this is documented approach, better to override post method of detail and from there post master (if not already posted).

    Andrejus

    ReplyDelete
  48. I have a master form and detail table. I am using strong association and works fine. But I want to show more than one blank rows in detail table based on a value in one of the field in master form. I am getting client validation error when calling second creatinsert on detail table. Pls advice.

    ReplyDelete
  49. Hi Andrejus,

    I've been struggling with the same error and already had the composite relationship defined when I came across this post.

    I downloaded your example and it works as described. After some careful comparisons and testing I realised that my example doesn't have any mandatory fields in the master that the user must enter.

    If I do enter one of the non-mandatory fields the creation of the detail row works. I tested the same situation with your example by making the city optional in the DB and the entity and it shows the same behaviour, you still get the error when you create a new master then a detail without altering any fields in the master. It seems the master needs to be submitted for it to work.

    Have you seen that scenario before? Any thoughts on what might be causing it?

    Thanks,
    David

    ReplyDelete
  50. Are you using composition association? If no, then you should force posting of Master first programmatically, before Detail is posted.

    Regards,
    Andrejus

    ReplyDelete
  51. Hi Andrejus,

    Yep Definitely using composition.

    If you take your example and remove the not null constraint on the Locations city column in the DB and the mandatory on the EO. Then add a Location and straight away add a Department (don't enter any info in the new Locations record) you should see the same error (hopefully).

    David

    ReplyDelete
  52. Hi,

    Yes, I reproduced this error with JDEV 11.1.1.7 Seems like there is a bug in Composition Association. I didn't had much time to investigate it now. It works, if you override postChanges and post parent EO (if it is not posted) - then you don't need to use Composition Association. This will be executed during Commit only.

    Regards,
    Andrejus

    ReplyDelete
  53. Hi Andrejus,

    Turns out my issue is because of the old "new row state" problem that I found outlined by Chris Muir here: https://community.oracle.com/message/2580083. I added the code to stop the new row being set back to INITIALIZED by the binding layer and the composition works fine now.

    David H

    ReplyDelete
  54. Thanks David. Yes, I think this is the case, as it works as soon as you type something in the master row.

    Regards,
    Andrejus

    ReplyDelete
  55. hi,
    I use jdev 12.2.1 . I dont speak well english. i new in ADF my problem is this :
    I have master details relation (association and view link exist in project). Master primary key is generate by a method just before method dodml in master java class.
    How can i update details (foreign key) with new master key value

    ReplyDelete
  56. I think this is described in ADF developer guide, check it out.

    Regards,
    Andrejus

    ReplyDelete