This discussion is archived
4 Replies Latest reply: Feb 20, 2009 5:23 AM by 453921 RSS

Working with multiple tables

453921 Newbie
Currently Being Moderated
Hello:

I want to create an update form and a create form. I have three tables I need to use: TableA, TableB, and TableC. I have data in each of these tables that need to be updated. I cannot seem to figure out a way to create an update statement which will allow me to update all three tables.

I'm having the same issue with inserting into multiple tables.

When creating an APEX page, what type of page should I be selecting: form, report, etc.?
Or, what kind of process do I need to create in order to write an update or insert statement which will allow me to udpate/insert my records?

When I want to create a View only report, no problem here, I write a SQL statement joining all my tables.

I cannot figure out an efficient way to udpate and insert into multiple tables.

Can someone help?

Thanks.
  • 1. Re: Working with multiple tables
    438381 Guru
    Currently Being Moderated
    Hello:

    You will need to do one of the following

    Create a form based on a view and then write instead of triggers on the view to do the DML
    Create a form based on a pl/sql procedure which has the code to do the DML
    Create a form manually and create pl/sql page processes to do the DML

    Varad
  • 2. Re: Working with multiple tables
    453921 Newbie
    Currently Being Moderated
    Hello:

    I'm a beginner with PL/SQL so I'm taking things step-by-step. Would something like this be correct, at least for an insert statement with two tables?

    I created a PL/SQL anonymous block as a process. This is the code I wrote:
    declere
      l_dept number;
    begin
     insert into test1
      (deptno, deptname, location)
     values
      (:P17_deptno, :P17_deptname, :P17_location)
    returning deptno into l_dept;
    
     insert into test2
      (empid, deptid, empname)
     values
      (:P17_empid, l_dept, :P17_empname);
    end;
    If I wanted to add a third table to the insert statement, would I just add another insert statement? Would I need to declare another variable similar to l_dept?


    And for an update statement, would this be correct?
    declere
      l_dept number;
    begin
     update test1
      set deptno=:P17_deptno, 
           deptname=:P17_deptname,
           location=:P17_location)
    returning deptno into l_dept;
    
     update test2
      set empid=:P17_empid,
           deptid=l_dept,
          empname=:P17_empname;
    end;
    Thank you for the help.
  • 3. Re: Working with multiple tables
    438381 Guru
    Currently Being Moderated
    Hello:

    What you have is a good start save for a few syntax errors.
    If your form is going to provide a value for deptno then the 'l_dept' declaration is not needed.
    You want create one page process for each DML operation. This will make it easier to control the execution of the page processes.
    declare
      l_dept number;
    begin
     insert into test1
      (deptno, deptname, location)
     values
      (:P17_deptno, :P17_deptname, :P17_location)
    returning deptno into l_dept;
     
     insert into test2
      (empid, deptid, empname)
     values
      (:P17_empid, l_dept, :P17_empname);
    end;
    declare
      l_dept number;
    begin
     update test1
      set deptno=:P17_deptno, 
           deptname=:P17_deptname,
           location=:P17_location
    returning deptno into l_dept;
     
     update test2
      set empid=:P17_empid,
           deptid=l_dept,
          empname=:P17_empname;
    end;
    Varad
  • 4. Re: Working with multiple tables
    453921 Newbie
    Currently Being Moderated
    Hi,

    The test1 table will contain the deptno, deptname, and location for all company departments. The deptno is the primary key in this table. There will be times where new departments will be added as well as existing department information may change.

    The test2 table will contain information on all employees which include the department in which they are working. There will be times where an employee can move from one department to another and I would need to udpate their information to reflect the new department id.

    I can't remember why I declared l_dept. I thought that I needed to declare some kind of variable to hold the "new" department to which the employee will be assigned. I guess I don't have to do that. So, I should delete the declare piece as well as the anything reference the declared variable. Correct?

    Also, I can't seem to find any of other syntax errors.

    Thanks for the help.

Legend

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