This discussion is archived
4 Replies Latest reply: Jun 27, 2011 7:05 AM by OraclePledge RSS

Most efficient way to insert form data into multiple tables and w/autonum

OraclePledge Newbie
Currently Being Moderated
Hi all, this is my first post here. I am new to APEX and PL/SQL. My background is ColdFusion and SQL, and I'm used to performing CRUD operations within my application files in CF tags. From what I've read so far, it appears it is more efficient to perform most CRUD operations on the database side, so if this is correct I'm hoping for some tips on the best way to accomplish what I am about to ask - similarly if what I'm trying to do is better off existing within my new APEX app I'm open to that information as well.

I have a form whose data should reside in two tables. Table A is a one-to-one relationship table and stores general information for each request. Table B is a one-to-many comments table, and should store unique records for each type of comment entered on the form, of which there are 3 comments fields on the form, so I would end up with 3 records in Table B for the 1 in Table A. I am generating an autonumber for Table A using a sequence and trigger. What I want to do is then take the autonumber I just created and use it for insertion into Table B for each insertion I have to do. This is where I'm stuck.

I'd like some input on 1) where I should put all of these operations, and 2) how best to go about coding what I want to do. In ColdFusion it was as simple as inserting the record into Table A, immediately querying Table A for the newest record and storing the ID (auto) in a variable, then using that var for any other table insertions I had to do. I know it has to be at least somewhat as simple in the Oracle world, I just need a nudge in the right direction. Thanks!
  • 1. Re: Most efficient way to insert form data into multiple tables and w/autonum
    Tyson Jouglet Expert
    Currently Being Moderated
    Mr. Anonymous Numbers,

    Are you using automatic row processing in APEX? or did you write your own procedure to update this information?

    Generally re querying the database to get the the primary key is a bad practice. What happens when two records are inserted at the same time? I can think of two ways to handle this:

    1. Use the returning clause.
    2. Do not blindly update the PK with the trigger. Only update the PK with the sequence if it is null. And get the value of the nextval of the sequence before the insert.


    starting code:
    CREATE table "DEMO_TABLE" (
        "PK_COL"     NUMBER NOT NULL,
        "MESSAGE"    VARCHAR2(20),
        constraint  "DEMO_TABLE_PK" primary key ("PK_COL")
    )
    /
    
    CREATE sequence "DEMO_TABLE_SEQ" 
    /
    
    CREATE trigger "BI_DEMO_TABLE"  
      before insert on "DEMO_TABLE"              
      for each row 
    begin  
      if :NEW."PK_COL" is null then
        select "DEMO_TABLE_SEQ".nextval into :NEW."PK_COL" from dual;
      end if;
    end;
    /   
    Example 1: using the returning clause
    declare
      demo_table_pk demo_table.pk_col%type;
    begin
        insert into demo_table(message) values('Hello World')
        returning pk_col into demo_table_pk;
    
        insert into demo_table(message) values('the last id was:' || demo_table_pk);
        commit;
    end;
    Example 2: Selecting the next sequence value before insert.
    declare
      demo_table_pk demo_table.pk_col%type;
    begin
        select demo_table_seq.nextval 
          into demo_table_pk
          from dual;
    
        insert into demo_table(pk_col, message) values(demo_table_pk, 'Hello World');
    
        insert into demo_table(message) values('the last id was:' || demo_table_pk);
        commit;
    end;
    Cheers,
    Tyson Jouglet

    Edited by: Tyson Jouglet on Jun 22, 2011 12:23 PM
  • 2. Re: Most efficient way to insert form data into multiple tables and w/autonum
    OraclePledge Newbie
    Currently Being Moderated
    Sorry, wasn't my intent to be Mr. Anonymous Numbers. I picked a screen name but it hasn't refreshed with it yet. I will study and implement your solution, thanks for the feedback. What I put together to get things working after I posted is below, and I put it in an anonymous block process on my APEX page. I have the autonumber trigger and sequence on the db side. I will get started on re-working it.
    declare
    v_ID number;
    begin
    
    insert into sch.tbl_main(cust_lname,cust_fname,acct_num)
    values(:P3_CUST_LNAME,:P3_CUST_FNAME,:P3_ACCT_NUM);
    
    select "SCH"."TBL_MAIN_SEQ".currval 
          into
          v_ID
          from dual;
    
    if :P3_COMPLAINT_COMMENTS is not null THEN
    insert into sch.tbl_comments (COMPL_ID,comment_type,comments,dt_created,created_by)
    VALUES(v_ID,'Complaint',:P3_COMPLAINT_COMMENTS,sysdate,:APP_USER);
    end if;
    
    if :P3_GENERAL_COMMENTS is not null THEN
    insert into sch.tbl_comments (COMPL_ID,comment_type,comments,dt_created,created_by)
    VALUES(v_ID,'General',:P3_GENERAL_COMMENTS,sysdate,:APP_USER);
    end if;
    
    if :P3_RESOLUTION_COMMENTS is not null THEN
    insert into sch.tbl_comments (COMPL_ID,comment_type,comments,dt_created,created_by)
    VALUES(v_ID,'Resolution',:P3_RESOLUTION_COMMENTS,sysdate,:APP_USER);
    end if;
    
    end;
    Signed,
    (hopefully soon to be) OraclePledge
  • 3. Re: Most efficient way to insert form data into multiple tables and w/autonum
    Tyson Jouglet Expert
    Currently Being Moderated
    OraclePledge,

    You are pretty close for not having worked with oracle much.

    This section is backwards:
    insert into sch.tbl_main(cust_lname,cust_fname,acct_num)
    values(:P3_CUST_LNAME,:P3_CUST_FNAME,:P3_ACCT_NUM);
     
    select "SCH"."TBL_MAIN_SEQ".currval 
          into
          v_ID
          from dual;
    While it will work if you only have one user, it is a potential bug with two or more users. Select the key first (if it is first use nextval instead of currval) then use it in the insert statement.

    Cheers,
    Tyson Jouglet

    Edited by: Tyson Jouglet on Jun 23, 2011 10:48 AM
  • 4. Re: Most efficient way to insert form data into multiple tables and w/autonum
    OraclePledge Newbie
    Currently Being Moderated
    Tyson,

    Thank you for that information, and sorry for the delay in response. I have implemented the change you suggested. I think I'm good now!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points