Forum Stats

  • 3,874,135 Users
  • 2,266,687 Discussions
  • 7,911,732 Comments

Discussions

one form to add data to multiple tables

User_1EUWL
User_1EUWL Member Posts: 3 Green Ribbon
edited May 6, 2022 8:04PM in APEX Discussions

Hi,

I'm new with oracle apex.

for a school draft I have a normalized database (country, city, municipality, correspondent, Person as spin off of the correspondent and institution as spin off of the correspondent table).

the database is fine, but the second part is to create forms to fill the database.

i want to have one form where i can type in the information about te person.

when I submit, I want to write the filled in in formation to the different database tables example by elk sql procedure. how can I do that in apex?


i have started creating a form whit items that shown or hide based on the type of correspondent you can fill in person details or institution details.

Answers

  • InoL
    InoL Member Posts: 10,195 Blue Diamond

    Your regions Correspondent, Details, Adres seem to only have items from one table. If not, I guess your datamodel has some problems.

    Data from the other tables (country, city, municipality) are only used for lookup LOV's. So I don't really see why you need "one form to add data to multiple tables".

    If I'm wrong in my assumption, you'll have to provide more details about your data model.

  • User_1EUWL
    User_1EUWL Member Posts: 3 Green Ribbon

    Hi InoL,


    thanx for responding! I had add the table overview so I hope it make more sense.

    wat I found out yesterday was that I can make a button on the form with a dynamical function to run PL SQL.

    when I click on the button the procedure runs, but the page items I want to use in the update statement are empty.


    this is the PLSQL I used:


    DECLARE

    v_mncpCode varchar2(255) := nvl(:MNCP_CODE,'9999-AA');

    v_mncpStreet varchar2(255):= nvl(:MNCP_STREET,'hhhh');

    v_mncpCityCode varchar2(255) := 'EN';


    BEGIN

    INSERT INTO MUNICIPALITY ("MNCP_CODE","MNCP_STREET","MNCP_CITYCODE") VALUES (v_mncpCode,v_mncpStreet,v_mncpCityCode);

    END;


    when I query the MUNICIPALITY table, it shows only the nvl values.

  • InoL
    InoL Member Posts: 10,195 Blue Diamond

    it shows only the nvl values.

    I doubt that :MNCP_CODE really references your page item. (Almost) everybody adheres to the APEX standard Px_MNCP_CODE, where x is the page number.

    Having said that, your data model suggests lookup values for postal code, and postal code determines the street and city. So, you should not even be able to add any new postal code, street and city in this form.

    A better solution is to have separate pages for postal codes, streets, cities and countries.

    Since this is a school assignment, you can ask your teacher or mentor what is required.

  • User_1EUWL
    User_1EUWL Member Posts: 3 Green Ribbon

    Hi Inol,

    after day's of research I've found my solution!

    i had to create a view on the tables whites I would insert data to.

    after that I had to create a trigger that run's an update or insert statement to save the data from the single form to the multiple tables.

    this was my solution: https://www.oracletutorial.com/plsql-tutorial/oracle-instead-of-triggers/

    thnx for your input to helping me!