Re: Forms 6.0 and interaction with table/column with default value

Matt,

Unfortunately, when you do and insert or an update from forms then all the database
fields of the block of the inserted/changed record take part in these operations even they are nulls or not changed.
So, if you have two database fields MY_DATA and INSERT_DT_TIME and you specify only MY_DATE
and leave INSERT_DT_TIME and commit (hit F10) then this operations will generate such an insert:

INSERT INTO my_table(MY_DATA,INSERT_DT_TIME) VALUES (:1,:2),

which will produce an error because INSERT_DT_TIME can't be null.

You can solve this problem in two ways (from my point of view of course):

1. You can set the Initial Value property of the INSERT_DT_TIME field to $$DBDATETIME$$.
2. You can use On-Insert trigger.

IF :my_table.INSERT_DT_TIME IS NULL THEN   INSERT INTO my_table(MY_DATA)
  VALUES(:my_table.MY_DATA);
ELSE
  Insert_Record;
END IF; I hope this helps.

-- 
Regards,

Zbigniew Sliwa
Oracle Programmer
Poland
email: zibi_at_at_yahoo.com

"Matt B." wrote:


> 

> I have a table in the database that has a default value on a particular column:


> 

> Example (not my actual table but I'm keeping the example simple):

> 

> CREATE TABLE MY_TABLE

> (MY_DATA VARCHAR2(50) NOT NULL,

> INSERT_DT_TIME DATE DEFAULT SYSDATE NOT NULL);

> 

> If I insert something into the table outside of Forms (like in SQL*Plus), this

> works fine:

> 

> INSERT INTO MY_TABLE(MY_DATA)

> VALUES('STUFF');

> 

> It populates INSERT_DT_TIME for me with no problem even though I didn't specify

> it.

> 

> However, I now have a form where MY_TABLE is the base table for a block.  I

> give the user the items MY_DATA and INSERT_DT_TIME, but I leave INSERT_DT_TIME

> as optional in the form since I'm expecting the default at the table level to

> populate it if the user doesn't.

> 

> When I commit a record in the form with a null INSERT_DT_TIME, I get an

> ORA-1400 error on the INSERT_DT_TIME column.  Why is this?  Shouldn't the

> table's default value of SYSDATE populate that column for me when I don't do it

> ahead of time?

> 

> Thanks,

> 

> Matt