This discussion is archived
7 Replies Latest reply: May 3, 2013 11:56 AM by Jayson Hanes RSS

ORA -1400 cannot insert null into table

inka Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

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