Time type

[filipe26]

Hi How can i define a time field in the create table?Thanks

 

[sem]

DATE Oracle type contains both date and time part.

Regards, Dima

http://seminihin.narod.ru

 

[filipe26]

Ok.But how can i define the default date or time with the system and how to check in a constraint if a date field is greater than the system one?

 

[oradba101]

hi, filepe26

Perhaps the SYSDATE() function?

Regards,

William Chadbourne
Oracle DBA

 

[filipe26]

Yes but i have:

constraint ck_data check(data>=SYSDATE)

and gives me an error

 

[Turkbear]

Hi,
What error message?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Please post a copy-and-paste of the actual constraint definition and the error message, then we can be more helpful in solving your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[filipe26]

i have this:

CREATE TABLE Agenda(
.....
Data Date default(SYSDATE) NOT NULL
......
constraint ck_Agenda_Data check(Data>=SYSDATE)
);

and i receive this ERROR:

ORA-02436:"This text is in my language that is Portuguese" but are saying that system variable or no specific incorrect date on check restriction.

Thanks a lot

 

[karluk]

Most interesting. You have apparently run into a rather unexpected limitation of check constraints. My Oracle 9i documentation lists the following restrictions, which include a ban on using the sysdate function in your constraint definition. It looks as if you will have to write a trigger to accomplish the same thing.

Restrictions on Check Constraints:

You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
Conditions of check constraints cannot contain the following constructs:
Subqueries and scalar subquery expressions
Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
Calls to user-defined functions
Dereferencing of REF columns (for example, using the DEREF function)
Nested table columns or attributes
The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
Date constants that are not fully specified

 

[SantaMufasa]

The reasons that you cannot use SYSDATE (and other non-deterministic functions) in an Oracle constraint are:

1) An Oracle constraint must guarantee that all values in the constrainted column obey the constraint at all times.
2) SYSDATE is a constantly changing (non-deterministic) value. Oracle cannot (and should not) attempt to validate all existing values against a moving target.

As an example, using filipe's definitions, one second following an insertion of a row using the default value, SYSDATE, that value violates the constraint "check(Data>=SYSDATE)".

This is why you must enforce such a restriction using an "INSERT" trigger (one-time check) versus a constraint (which must enforce constant compliance).

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.