7 Replies Latest reply: May 3, 2013 1:56 PM by Jayson Hanes RSS

    ORA -1400 cannot insert null into table

    inka
      Hello All,

      I am running Apex 4.1 and Oracle 11g. I have a form where an item's value is being passed from another page.

      "Item: P31_DATABASE_ID
      Source type: SQL Query
      Souce: select database_name from databases
      where database_name = :P20_DATABASE_NAME"

      When I click on the Create button I get this error:

      ORA -1400 cannot insert null into table .......

      How can I fix it?
        • 1. Re: ORA -1400 cannot insert null into table
          Jayson Hanes
          fix it by either:

          a) not allowing that column to be null (set it to something?: ex. default value or create a validation process to display it as an error (thus allowing the user a chance to enter something for that item),
          or
          b) create a process prior to insert/update that actually assigns a default value to that item (lower sequence #)
          or by
          c) modifying the structure of the table so that it doesn't have a "not null" constraint on that column..
          • 2. Re: ORA -1400 cannot insert null into table
            Howard (... in Training)
            inka,

            Re:
            I have a form where an item's value is being passed from another page.
            "Item: P31_DATABASE_ID
            Source type: SQL Query
            Souce: select database_name from databases
            where database_name = :P20_DATABASE_NAME">
            A few more thoughts. Do you still have this problem.

            I'll assume for now that the problem is that P31_DATABASE_ID is NULL. Since you seem to be on page 31 (P31_DATABASE_ID) and this items gets its value from querying with ":P20_DATABASE_NAME", I'll suppose ":P20_DATABASE_NAME" is NULL or invalid.

            Maybe the ":P20_DATABASE_NAME" value was retrieved/set on p. 20 but was never placed in the Session State? Does ":P20_DATABASE_NAME" show in the Session State on p. 31? Can you display ":P20_DATABASE_NAME" on page 31 so you can verify exactly what value it has?

            Howard
            • 3. Re: ORA -1400 cannot insert null into table
              Tom Petrus
              inka wrote:
              "Item: P31_DATABASE_ID
              Source type: SQL Query
              Souce: select database_name from databases
              where database_name = :P20_DATABASE_NAME"
              Why do you mention this? This item won't change anything. You get an ora-1400, not 1403 or 1422. The item does not have source type "database column", so it won't be applied to the database when you're using the built in DML process.
              When I click on the Create button I get this error:

              ORA -1400 cannot insert null into table .......
              Do you use the apex DML process or do you use a PLSQL process you've created yourself? What table is the insert happening on? One of the columns of the record you are inserting is defined as "not null" on the database, and you are trying to put a "null" value into it. You'll have to check to see which column this is.
              • 4. Re: ORA -1400 cannot insert null into table
                inka
                Yes I can. P20_database_name shows the value on page 31. Somehow when I enter the record into the table I have to assign p20_database_id to p31_database_id and I don't know how to do that.
                • 5. Re: ORA -1400 cannot insert null into table
                  Howard (... in Training)
                  inka,

                  Jayson and Tom's comments are revelant: Specifically, how is the data getting written to the database? Are you using ARP (automated (automagic?) row processing) or are doing your own inserts and updates?

                  Generally, a column set up in a tabular form would be retrieved and updated by APEX ARP code. In your "After Header" process on the page, you would see an ARP ("Fetch Row from ...") and there would be a process under Page Processing ("Process Row of ..."). From what you have written here, ":P31_DATABASE_ID" does not appear to be a column in a tabular form. If it were, there would be Tabular Form Attributes section mapping the column variable back to the database column.

                  Tell us more. How is the data on p. 31 getting written to the database?

                  Howard
                  • 6. Re: ORA -1400 cannot insert null into table
                    inka
                    It is written using APEX ARP. It works when P31_DATABASE_ID is setup as text field but when I change it to display only field then I get ORA-1400 error. I need for this field to be display only. Is there any way to make the display only field behave as text?
                    • 7. Re: ORA -1400 cannot insert null into table
                      Jayson Hanes
                      How about this.. create another item, such as P31_DISPLAY_DATABASE_ID, make THAT item display only, leave the other defaults, then assign it's value the value of P31_DATABASE_ID (make this item hidden) and set it's value using on-demand process onload, etc. That way "what's being displayed but never modified" doesn't have anything to to with the automagic at all