Home » Developer & Programmer » Application Express & MOD_PLSQL » Populate three tables (Apex 4.2, Oracle 11g)
Populate three tables [message #577789] Wed, 20 February 2013 10:49 Go to next message
inka
Messages: 14
Registered: February 2013
Junior Member
I have an intersection table that has database_id and application_id. I need to add a record into this intersection table once I add a record into the application table and database table.

declare
     l_database_id number := :P21_DATABASE_ID;
     l_application_id number := :P21_APPLICATION_ID;
begin
 
    -- Create New Customer
         insert into DATABASES (
            SERVER_ID,
            global_database_name,
            database_name,
            dbms_release,
            dbms_desc,
            db_environment,
            tns_port,
            create_date,
            internal_db_id,
            inactive_flag,
            update_date,
            update_userid)
          values (
            :P21_SERVER_ID,
            :P21_GLOBAL_DATABASE_NAME,
            :P21_DATABASE_NAME,
            :P21_DBMS_RELEASE,
            :P21_DBMS_DESC,
            :P21_DB_ENVIRONMENT,
            :P21_TNS_PORT,
            :P21_CREATE_DATE,
            :P21_INTERNAL_DB_ID,
            :P21_INACTIVE_FLAG,
            :P21_UPDATE_DATE,
            :P21_UPDATE_USERID)
          returning database_id into l_database_id;
 
          :P21_DATABASE_ID := l_database_id;
          
          insert into APPLICATION (
            APPLICATION_NAME,
            VENDOR_NAME,
            VENDOR_INFO,
            BCBEAR_URL,
            CURRENT_VERSION,
            CRITICAL_FLAG,
            INACTIVE_FLAG,
            UPDATE_DATE,
            UPDATE_USERID)
          values(
            :P21_APPLICATION_NAME,
            :P21_VENDOR_NAME,
            :P21_VENDOR_INFO,
            :P21_BCBEAR_URL,
            :P21_CURRENT_VERSION,
            :P21_CRITICAL_FLAG,
            :P21_APPLICATION_INACTIVE_FLAG,
            :P21_UPDATE_DATE1,
            :P21_UPDATE_USERID1)
    returning application_id into l_application_id;
 
          
          :P21_APPLICATION_ID := l_application_id;
 
        insert into APPLICATION_DATABASE_INT(
          application_id,
          database_id,
          update_date,
          update_userid)
        values(
          l_database_id,
          l_application_id,
          systimestamp, 
          upper(:APP_USER));
 
end;

Once I hit submit button I am getting this error message:

ORA-02291: integrity constraint (CFGMGR.APP_DB_INT_APP_ID_FK) violated - parent key not found

How to fix it?


[EDITED by LF: applied [code] tags]

[Updated on: Wed, 20 February 2013 12:51] by Moderator

Report message to a moderator

Re: Populate three tables [message #577797 is a reply to message #577789] Wed, 20 February 2013 12:50 Go to previous message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Insert parent key values (a master table) before inserting child records (a detail table).

P.S. Find out which INSERT raises the error. The simplest way is, probably, to run a form in debug mode.

[Updated on: Wed, 20 February 2013 12:55]

Report message to a moderator

Previous Topic: developing new application
Next Topic: Populating table
Goto Forum:
  


Current Time: Wed Oct 01 07:04:53 CDT 2014

Total time taken to generate the page: 0.16745 seconds