Re: TIMESTAMP datatype in Oracle 9i

From: Frank <fbortel_at_nescape.net>
Date: Wed, 17 Dec 2003 22:05:22 +0100
Message-ID: <brqftk$t5q$1@news3.tilbu1.nb.home.nl>

Hong wrote:

> Hi,
>
> How can I create a table with TIMESTAMP datatype field inside Orcle
> Enterprise Manager Console, Standalone (Version 9.2.0.1.0)?
>
> The problem is there is no TIMESTAMP in the Datatype drop-down list in
> the Manager Console. If I choose Date datatype, I can't input
> nanoseconds, for example, '07-14-2002 11:07:00.123456789 AM', in the
> Table Editor. (The Table Editor is invoked by right-clicking the table
> and then choose Edit/View Contents...).
>
> Then I tried to do it in Oracle SQL*Plus. I issued:
>
> SQL> create table HR.test1 ( x TIMESTAMP);
>
> Table created.
>
> SQL> insert into HR.test1 VALUES('07-14-2002 11:07:00.123456789 AM');
>
> 1 row created.
>
> SQL> select * from HR.test1;
>
> X
> ---------------------------------------------------------------------------
> 07-14-2002 11:07:00.123457 AM
>
>
> Go back to Orcle Enterprise Manager Console, Standalone, click
> HR.TEST1. The Datatype for Column X is TIMESTAMP(6). When I tried to
> view its contents in the Table Editor, error message:
> Oracle Enterprise Manager
> VTOS-2011: Cannot display the contents of the table with columns
> of type LOB, RAW, LONG and OBJECT.
>
>
> If I query the table through JDBC, the result is empty. There is also
> an error if through ODBC: Invalid identifier in statement.
>
> I checked the documentation that comes with the database, but didn't
> find too much.
>
> Any help is appreciated.
>
> Many thanks in advance,
> Hong

Well - it's in there: timestamps default to 6 digits:

SQL> create table dummy (col1 timestamp(9)); Table created.

SQL> insert into dummy values('17-DEC-03 09.45.46.226134000'); 1 row created.

SQL> insert into dummy values('17-DEC-03 09.45.46.226134123'); 1 row created.

SQL> select * from dummy;
COL1

---

17-DEC-03 09.45.46.226134000 PM
17-DEC-03 09.45.46.226134123 AM SQL> create table dum (col1 timestamp);
Table created.

SQL> insert into dum values('17-DEC-03 09.45.46.226134000'); 1 row created.

SQL> insert into dum values('17-DEC-03 09.45.46.226134123'); 1 row created.

SQL> select * from dum;
COL1

---

17-DEC-03 09.45.46.226134 AM
17-DEC-03 09.45.46.226134 AM

-- 
Regards, Frank van Bortel

Received on Wed Dec 17 2003 - 15:05:22 CST