Error Inserting Record with SYSDATE as Default Value

jfudickar · Post by **jfudickar** » Thu 21 Dec 2006 09:28

Hi

i've got the follwing error(stack) when i try to insert a record in a table:
exception class : EConvertError
exception message : 'sysdate' is not a valid date and time.

main thread ($748):
0040a8f5 +019 SysToolsAdmin.exe SysUtils ConvertErrorFmt
0040fe7f +027 SysToolsAdmin.exe SysUtils StrToDateTime
005bd9ef +043 SysToolsAdmin.exe DB TDateTimeField.SetAsString
005e681d +00d SysToolsAdmin.exe MemDS TMemDataSet.SetDefaultExpressionValue
005e686e +046 SysToolsAdmin.exe MemDS TMemDataSet.DoOnNewRecord
005f4fd9 +0c1 SysToolsAdmin.exe DBAccess TCustomDADataSet.DoOnNewRecord
005c7e8f +027 SysToolsAdmin.exe DB TDataSet.EndInsertAppend
005c7b0d +089 SysToolsAdmin.exe DB TDataSet.Insert

The Table Definition is:
--
-- OraTool Pro v 2.9.1.0 BETA DEBUG - (c) by Softwaredevelopment Jens Fudickar
-- Softwareentwicklung Jens Fudickar - 0000000001 (Unlimited)
--
-- Generator : Create Object
-- Objecttype : TABLE
-- Generated at : 21.12.2006
-- Generated by : IF_KSC
--

--##############################################################
-- TABLE SYST_PARAM_VALUE
--##############################################################
--
-- Description
--
-- Parameter-Werte-Tabelle
-- In dieser Tabelle sind alle Parameterwerte abgespeichert.
-- Über das Feld SYST_PARAM_PARAMETER.PARAM_CREATE_DEFAULT
-- kann gesteuert werden, ob Werte die dem Default-Wert
-- entsprechen in dieser Tabelle generiert werden sollen oder
-- nicht.
--
-- Columns
--
-- PVAL_ID
-- Primary Key
-- PVAL_2_PARAM_ID
-- Foreign Key auf die Tabelle SYST_PARAM_PARAMETER
-- PVAL_DETAIL
-- Zusätzliches Unterscheidungskriterium zum Namen des
-- Parameters.Hiermit können z.B. verschiedene Werte
-- für einen Parameter für unterschiedliche Tabellen
-- generiert werden.
-- PVAL_VALUE
-- Eigentlicher Parameterwert
--

PROMPT DROP TABLE SYSTOOLS.SYST_PARAM_VALUE
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('DROP', 'TABLE');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PARAM_VALUE'); END;
/
DROP TABLE SYST_PARAM_VALUE CASCADE CONSTRAINTS;

PROMPT CREATE TABLE SYSTOOLS.SYST_PARAM_VALUE
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE', 'TABLE');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PARAM_VALUE'); END;
/
CREATE TABLE SYST_PARAM_VALUE (
PVAL_ID NUMBER NOT NULL ,
PVAL_2_PARAM_ID NUMBER NOT NULL ,
PVAL_ACTIVE VARCHAR2 (1) DEFAULT 'Y' NOT NULL ,
PVAL_DETAIL VARCHAR2 (1000) ,
PVAL_VALUE VARCHAR2 (4000) ,
PVAL_AUDIT_INSERT_USER VARCHAR2 (100) DEFAULT user ,
PVAL_AUDIT_INSERT_TIMESTAMP DATE DEFAULT sysdate ,
PVAL_AUDIT_UPDATE_USER VARCHAR2 (100) ,
PVAL_AUDIT_UPDATE_TIMESTAMP DATE ,
PVAL_COMMENT VARCHAR2 (4000) ,
PVAL_VALUE_NUMBER NUMBER ,
PVAL_VALUE_DATE DATE
)
INITRANS 1
MAXTRANS 255
TABLESPACE SYSTOOLS_TAB
LOGGING
STORAGE
(INITIAL 1 M NEXT 1 M MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 0 FREELISTS 0 BUFFER_POOL DEFAULT)
PCTFREE 10
PCTUSED 0
MONITORING
PARALLEL (DEGREE 1 INSTANCES 1)
;

----------------------------------------------------------------
-- Constraints
----------------------------------------------------------------

PROMPT CREATE CHECK CONSTRAINT CONSTRAINT SYSTOOLS.SYST_PVAL_ACTIVE_CHECK
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE CHECK CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PVAL_ACTIVE_CHECK'); END;
/
ALTER TABLE SYST_PARAM_VALUE
ADD CONSTRAINT SYST_PVAL_ACTIVE_CHECK CHECK (PVAL_ACTIVE IN ('Y', 'N'))
;

PROMPT CREATE PRIMARY KEY CONSTRAINT CONSTRAINT SYSTOOLS.SYST_PVAL_PK
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE PRIMARY KEY CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PVAL_PK'); END;
/
ALTER TABLE SYST_PARAM_VALUE
ADD CONSTRAINT SYST_PVAL_PK PRIMARY KEY
(PVAL_ID)
NOT DEFERRABLE INITIALLY IMMEDIATE
USING INDEX
INITRANS 2
MAXTRANS 255
TABLESPACE SYSTOOLS_IND
STORAGE
(INITIAL 1 M NEXT 1 M MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 0 FREELISTS 0 BUFFER_POOL DEFAULT)
PCTFREE 10
;

PROMPT CREATE FOREIGN KEY CONSTRAINT CONSTRAINT SYSTOOLS.SYST_PVAL_2_PARAM_FK
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE FOREIGN KEY CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PVAL_2_PARAM_FK'); END;
/
ALTER TABLE SYST_PARAM_VALUE
ADD CONSTRAINT SYST_PVAL_2_PARAM_FK FOREIGN KEY
(PVAL_2_PARAM_ID)
REFERENCES SYST_PARAM_PARAMETER
(PARAM_ID)
ON DELETE CASCADE
NOT DEFERRABLE INITIALLY IMMEDIATE
;

PROMPT CREATE UNIQUE CONSTRAINT CONSTRAINT SYSTOOLS.SYST_PVAL_UNQ
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE UNIQUE CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SYST_PVAL_UNQ'); END;
/
ALTER TABLE SYST_PARAM_VALUE
ADD CONSTRAINT SYST_PVAL_UNQ UNIQUE
(PVAL_2_PARAM_ID,
PVAL_DETAIL)
NOT DEFERRABLE INITIALLY IMMEDIATE
USING INDEX
INITRANS 2
MAXTRANS 255
TABLESPACE SYSTOOLS_IND
STORAGE
(INITIAL 1 M NEXT 1 M MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 0 FREELISTS 0 BUFFER_POOL DEFAULT)
PCTFREE 10
;

----------------------------------------------------------------
-- Sequencees
----------------------------------------------------------------

PROMPT DROP SEQUENCE SYSTOOLS.SEQ_SYST_PARAM_VALUE
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('DROP', 'SEQUENCE');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYSTOOLS.SEQ_SYST_PARAM_VALUE'); END;
/
DROP SEQUENCE SEQ_SYST_PARAM_VALUE;

CREATE SEQUENCE SEQ_SYST_PARAM_VALUE
MINVALUE 1
MAXVALUE 1E27
INCREMENT BY 1
NOCYCLE
CACHE 20
;

----------------------------------------------------------------
-- Trigger
----------------------------------------------------------------

PROMPT CREATE OR REPLACE TRIGGER SYST_PVAL_B_IUD_R_CHECK_PRIVS
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYST_PVAL_B_IUD_R_CHECK_PRIVS'); END;
/
CREATE OR REPLACE TRIGGER SYST_PVAL_B_IUD_R_CHECK_PRIVS
BEFORE INSERT OR UPDATE OR DELETE ON SYST_PARAM_VALUE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF inserting THEN
pkg_syst_parameter_internal.check_current_user_has_priv (
i_param_id => :NEW.PVAL_2_PARAM_ID,
i_privilege => pkg_syst_privilege.c_priv_insert,
i_object => 'SYST_PARAM_VALUE');
ELSIF updating THEN
IF UPDATING ('PVAL_2_PARAM_ID') THEN
pkg_syst_parameter_internal.check_current_user_has_priv (
i_param_id => :OLD.PVAL_2_PARAM_ID,
i_privilege => pkg_syst_privilege.c_priv_update,
i_object => 'SYST_PARAM_VALUE');
END IF;
pkg_syst_parameter_internal.check_current_user_has_priv (
i_param_id => :NEW.PVAL_2_PARAM_ID,
i_privilege => pkg_syst_privilege.c_priv_update,
i_object => 'SYST_PARAM_VALUE');
ELSIF deleting THEN
pkg_syst_parameter_internal.check_current_user_has_priv (
i_param_id => :OLD.PVAL_2_PARAM_ID,
i_privilege => pkg_syst_privilege.c_priv_delete,
i_object => 'SYST_PARAM_VALUE');
END IF;
END SYST_PVAL_B_IUD_R_CHECK_PRIVS;
/

PROMPT CREATE OR REPLACE TRIGGER SYST_PVAL_B_IU_R_AUDIT
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYST_PVAL_B_IU_R_AUDIT'); END;
/
CREATE OR REPLACE TRIGGER SYST_PVAL_B_IU_R_AUDIT
BEFORE INSERT OR UPDATE ON SYST_PARAM_VALUE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.PVAL_AUDIT_INSERT_USER := USER||' @ '||PKG_SYST_TOOLS.GET_CURRENT_OSUSER;
:NEW.PVAL_AUDIT_INSERT_TIMESTAMP := SYSDATE;
ELSIF UPDATING THEN
:NEW.PVAL_AUDIT_INSERT_USER := :OLD.PVAL_AUDIT_INSERT_USER;
:NEW.PVAL_AUDIT_INSERT_TIMESTAMP := :OLD.PVAL_AUDIT_INSERT_TIMESTAMP;
:NEW.PVAL_AUDIT_UPDATE_USER := USER||' @ '||PKG_SYST_TOOLS.GET_CURRENT_OSUSER;
:NEW.PVAL_AUDIT_UPDATE_TIMESTAMP := SYSDATE;
END IF;
END SYST_PVAL_B_IU_R_AUDIT;
/

PROMPT CREATE OR REPLACE TRIGGER SYST_PVAL_B_IU_R_CHECK_FIELDS
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYST_PVAL_B_IU_R_CHECK_FIELDS'); END;
/
CREATE OR REPLACE TRIGGER SYST_PVAL_B_IU_R_CHECK_FIELDS
BEFORE INSERT OR UPDATE ON SYST_PARAM_VALUE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_check_result NUMBER;
BEGIN
:NEW.PVAL_DETAIL := UPPER(:NEW.PVAL_DETAIL);
v_check_result := PKG_SYST_PARAMETER_INTERNAL.CHECK_PARAM_VALUE (
i_param_id => :NEW.PVAL_2_PARAM_ID,
io_pval_value => :NEW.PVAL_VALUE,
io_pval_value_number => :NEW.PVAL_VALUE_NUMBER,
io_pval_value_date => :NEW.PVAL_VALUE_DATE);
IF v_check_result = PKG_SYST_PARAMETER_INTERNAL.c_pval_chk_datatype_missmatch THEN
RAISE_APPLICATION_ERROR (pkg_syst_tools.c_e_common_systools_exception , 'Parameter-Wert-Datatype-Error: "'||:NEW.PVAL_VALUE||'" kann nicht in den Parameter-Datentyp konvertiert werden.');
ELSIF v_check_result = PKG_SYST_PARAMETER_INTERNAL.c_pval_chk_constraint_violated THEN
RAISE_APPLICATION_ERROR (pkg_syst_tools.c_e_common_systools_exception , 'Parameter-Wert-Constraint-Error: "'||:NEW.PVAL_VALUE||'" entspricht nicht dem Parameter-Constraint.');
ELSIF v_check_result = PKG_SYST_PARAMETER_INTERNAL.c_pval_chk_no_error THEN
NULL;
ELSE
RAISE_APPLICATION_ERROR (pkg_syst_tools.c_e_common_systools_exception , 'Unknown Parameter-Value-Error: "'||:NEW.PVAL_VALUE||'".');
END IF;

END SYST_PARAM_B_IU_R_CHECK_FIELDS;
/

PROMPT CREATE OR REPLACE TRIGGER SYST_PVAL_B_I_R_KEYGEN
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYST_PVAL_B_I_R_KEYGEN'); END;
/
CREATE OR REPLACE TRIGGER SYST_PVAL_B_I_R_KEYGEN
BEFORE INSERT ON SYST_PARAM_VALUE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW WHEN (NEW.PVAL_ID IS NULL)
BEGIN
SELECT SEQ_SYST_PARAM_VALUE.NEXTVAL INTO :NEW.PVAL_ID FROM DUAL;
END;
/

I'm using Oracle Data Access Components 5.70.1.38

Any ideas?

Greetings
Jens

Plash · Post by **Plash** » Fri 22 Dec 2006 08:46

In ODAC you can use a constant only as a value for the TField.DefaultExpression property. For example, you can set the value '21.12.2006' (without quotes) to the DefaultExpression property of a date field.

jfudickar · Post by **jfudickar** » Fri 22 Dec 2006 10:52

I don't use any constant's in my delphi app.

The default is only defined on the oracle table.

Greetings
Jens

Plash · Post by **Plash** » Mon 25 Dec 2006 08:27

If you set the DefaultValues option of the dataset to True, DefaultExpression properties of the dataset fields are filled automatically with default values that are set for a database table. So if your table has default values that are not constants, you should set dataset's DefaultValues option to False.

jfudickar · Post by **jfudickar** » Tue 26 Dec 2006 20:40

Is it possible to change this?

This is realy ugly. What about executing it with "SELECT FROM DUAL" instead of using the constant.

Greetings
Jens

Plash · Post by **Plash** » Thu 28 Dec 2006 09:51

We'll consider possibility to implement this feature in either of the next ODAC versions.

jfudickar · Post by **jfudickar** » Thu 28 Dec 2006 11:29

Hopefully you will.

For me that will be a realy important feature, and i think it's not an unusual case.

Greetings
Jens

jfudickar · Post by **jfudickar** » Sun 11 Feb 2007 23:42

WIll there be a chance to see this in the next release???

Greetings and thanks
Jens

Plash · Post by **Plash** » Tue 13 Feb 2007 15:13

We are planning to support this feature in ODAC 6 release.

jfudickar · Post by **jfudickar** » Tue 13 Feb 2007 17:12

Great

Thanks
Jens