oracle-databaseoracle11gvarchar2systimestamp

Varchar2 datatype allows systimestamp

I have one table with varchar2 datatypes like below

create table t11 (aa varchar2(100),bb varchar2(100));

Now, if I trying to insert systimestamp into above, values are getting inserted:

insert into t11 values (systimestamp,systimestamp);
commit;

Question is why this conversion is allowed in Oracle.

I am using Oracle 11g.

Solution

  • Your column type is varchar2 and the return type of systimestamp is timestamp. timestamp can not be stored directly into varchar2 column, Oracle implicitly convert the timestamp value into varchar2 with the rule specified in the init parameter, NLS_TIMESTAMP_FORMAT.

    You can read Data Conversion Rules and NLS_TIMESTAMP_FORMAT for more detail.