3 Replies Latest reply: Feb 20, 2013 8:17 AM by inka RSS

    Insert Record into two tables

    inka
      Hello All,

      I have a form that I created from scratch and I am trying to enter data into two tables that form is based on.

      I am using Apex 4.2 and Oracle 11g.

      I have a process that runs the script below:

      declare
      l_database_id number := :P20_DATABASE_ID;
      l_application_id number := :P20_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 (
      :P20_SERVER_ID,
      :P20_GLOBAL_DATABASE_NAME,
      :P20_DATABASE_NAME,
      :P20_DBMS_RELEASE,
      :P20_DBMS_DESC,
      :P20_DB_ENVIRONMENT,
      :P20_TNS_PORT,
      :P20_CREATE_DATE,
      :P20_INTERNAL_DB_ID,
      :P20_INACTIVE_FLAG,
      :P20_UPDATE_DATE,
      :P20_UPDATE_USERID)
      returning database_id into l_database_id;

      :P20_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(
      :P20_APPLICATION_NAME,
      :P20_VENDOR_NAME,
      :P20_VENDOR_INFO,
      :P20_BCBEAR_URL,
      :P20_CURRENT_VERSION,
      :P20_CRITICAL_FLAG,
      :P20_APPLICATION_INACTIVE_FLAG,
      :P20_UPDATE_DATE1,
      :P20_UPDATE_USERID1)
      returning application_id into l_application_id;


      :P20_APPLICATION_ID := l_application_id;

      end;

      When I hit the submit button I get this error message:

      ORA-01400: cannot insert NULL into ("CFGMGR"."APPLICATION"."APPLICATION_ID")

      Not sure how to fix it.
        • 1. Re: Insert Record into two tables
          fac586
          inka wrote:

          I have a form that I created from scratch and I am trying to enter data into two tables that form is based on.

          I am using Apex 4.2 and Oracle 11g.

          I have a process that runs the script below:
          Always post code using <tt>\
          ...\
          </tt> tags as described in the FAQ.
          declare
          l_database_id number := :P20_DATABASE_ID;
          l_application_id number := :P20_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 (
          :P20_SERVER_ID,
          :P20_GLOBAL_DATABASE_NAME,
          :P20_DATABASE_NAME,
          :P20_DBMS_RELEASE,
          :P20_DBMS_DESC,
          :P20_DB_ENVIRONMENT,
          :P20_TNS_PORT,
          :P20_CREATE_DATE,
          :P20_INTERNAL_DB_ID,
          :P20_INACTIVE_FLAG,
          :P20_UPDATE_DATE,
          :P20_UPDATE_USERID)
          returning database_id into l_database_id;
          
          :P20_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(
          :P20_APPLICATION_NAME,
          :P20_VENDOR_NAME,
          :P20_VENDOR_INFO,
          :P20_BCBEAR_URL,
          :P20_CURRENT_VERSION,
          :P20_CRITICAL_FLAG,
          :P20_APPLICATION_INACTIVE_FLAG,
          :P20_UPDATE_DATE1,
          :P20_UPDATE_USERID1)
          returning application_id into l_application_id;
          
          
          :P20_APPLICATION_ID := l_application_id;
          
          end;
          When I hit the submit button I get this error message:

          ORA-01400: cannot insert NULL into ("CFGMGR"."APPLICATION"."APPLICATION_ID")

          Not sure how to fix it.
          The error message is pretty clear. The <tt>CFGMGR.APPLICATION.APPLICATION_ID</tt> column is defined as NOT NULL, but it is not included in your insert statement. Presumably it is intended that the <tt>APPLICATION_ID</tt> is to be generated by a trigger. That trigger is either missing, not firing, or is failing to provide an <tt>APPLICATION_ID</tt> value.
          • 2. Re: Insert Record into two tables
            inka
            I have created a trigger but not sure how to tie the trigger to the application_id.
            CREATE OR REPLACE TRIGGER  "APPLICATION_BIU" 
            BEFORE
            insert or update on "APPLICATION"
            for each row
            begin
            DECLARE
              app_id number;
            BEGIN
              if inserting then
                if :new.application_id is null then
                  select APPLICATION_APPLICATION_ID_SEQ.nextval
                    into app_id
                    from dual;
                  :new.application_id := app_id;
                end if;
              end if;
            END;
            end;
             
            • 3. Re: Insert Record into two tables
              inka
              I figured that out. But I have another question. 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?