Home » SQL & PL/SQL » SQL & PL/SQL » timestamp datatype
timestamp datatype [message #138929] Sun, 25 September 2005 21:16 Go to next message
lavanya_pvn
Messages: 11
Registered: September 2005
Junior Member

hi....

when we create a table with timestamp datatype ....and the column name also as timestamp its created and we can perform all the operations on the table i.e,

create table a (timestamp timestamp);

but if we use this table in a trigger it is giving 'trigger created with compilation errors' , why?
Re: timestamp datatype [message #138997 is a reply to message #138929] Mon, 26 September 2005 05:00 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
Please read the sticky. You should also provide the code if you'r getting the error message and the version of Oracle database.

Check this:
saadat@anba> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Elapsed: 00:00:00.02
saadat@anba> CREATE TABLE test
  2  ( a TIMESTAMP);

Table created.

Elapsed: 00:00:00.00
saadat@anba> CREATE OR REPLACE TRIGGER trg_test
  2  AFTER INSERT ON test
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Trigger fired successfully');
  5  END;
  6  /

Trigger created.

Elapsed: 00:00:00.00
saadat@anba> INSERT INTO test
  2  VALUES('09-JAN-2006');
Trigger fired successfully

1 row created.

Elapsed: 00:00:00.00
saadat@anba> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
saadat@anba> SELECT * FROM test;

A
---------------------------------------------------------------------------
09-JAN-20 06.00.00.000000 AM

Elapsed: 00:00:00.00
saadat@anba>

It doesn't give the error. You have to check the error you'r receiving by the command SHOW ERRORS

Regards
Re: timestamp datatype [message #139011 is a reply to message #138929] Mon, 26 September 2005 05:17 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member
C2K@FXUT> create table a (timestamp timestamp);

Table created.

C2K@FXUT> insert into a values(sysdate);

1 row created.

C2K@FXUT> select * from a;
select * from a
*
ERROR at line 1:
ORA-03115: unsupported network datatype or representation


C2K@FXUT> desc a;
Name Null? Type
------------------------------- -------- ----
TIMESTAMP UNDEFINED


see type is not supporting (This is in Oracle 9i only)
Re: timestamp datatype [message #139014 is a reply to message #138929] Mon, 26 September 2005 05:24 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member

1 CREATE OR REPLACE TRIGGER trg_test
2 AFTER INSERT ON a
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Trigger fired successfully');
5* END;
6 /
AFTER INSERT ON a
*
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed


C2K@FXUT> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

C2K@FXUT> desc a;
Name Null? Type
------------------------------- -------- ----
TIMESTAMP UNDEFINED
Re: timestamp datatype [message #139027 is a reply to message #138929] Mon, 26 September 2005 05:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi sharan,

See what happens...

SQL> connect
Enter user-name: scott
Enter password: ************
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> create table a (timestamp timestamp);

Table created.

SQL> insert into a values(sysdate);

1 row created.

SQL> select * from a;

TIMESTAMP
---------------------------------------------------------------------------
26-SEP-05 04.25.06.000000 PM

SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIMESTAMP                                          TIMESTAMP(6)

SQL> CREATE OR REPLACE TRIGGER trg_test
  2  AFTER INSERT ON a
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Trigger fired successfully');
  5   END;
  6  /
AFTER INSERT ON a
                *
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed


SQL> alter table a rename column timestamp to ts;

Table altered.

SQL>  CREATE OR REPLACE TRIGGER trg_test
  2   AFTER INSERT ON a
  3   BEGIN
  4   DBMS_OUTPUT.PUT_LINE('Trigger fired successfully');
  5    END;
  6  /

Trigger created.

SQL> set serverout on
SQL> insert into a values(sysdate);
Trigger fired successfully

1 row created.

SQL> 


Thumbs Up
Rajuvan
Re: timestamp datatype [message #139036 is a reply to message #138929] Mon, 26 September 2005 06:17 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member

C2K@FXUT> create table a(timestamp timestamp);

Table created.

C2K@FXUT> insert into a values(sysdate);

1 row created.


C2K@FXUT> select * from a;
select * from a
*
ERROR at line 1:
ORA-03115: unsupported network datatype or representation


C2K@FXUT> desc a;
Name Null? Type
------------------------------- -------- ----
TIMESTAMP UNDEFINED

C2K@FXUT> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

why am only getting error???? I tried to do the same u did...
Re: timestamp datatype [message #139053 is a reply to message #139036] Mon, 26 September 2005 07:38 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Sep 26 15:20:30 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: scott/tiger@anba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

scott@anba> DROP TABLE test;

Table dropped.

Elapsed: 00:00:05.06
scott@anba> CREATE TABLE test
  2  (timestamp TIMESTAMP);

Table created.

Elapsed: 00:00:00.04
scott@anba> INSERT INTO test
  2  VALUES(sysdate);

1 row created.

Elapsed: 00:00:00.00
scott@anba> SELECT * FROM test;

TIMESTAMP
---------------------------------------------------------------------------
26-SEP-05 03.21.41.000000 PM

Elapsed: 00:00:00.00
scott@anba> DESC test
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------
 TIMESTAMP                                                                        TIMESTAMP(6)

scott@anba> CREATE OR REPLACE TRIGGER trg_test
  2  AFTER INSERT ON test
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Trigger Fired Successfully');
  5  END;
  6  /
AFTER INSERT ON test
                *
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed


Elapsed: 00:00:00.02
scott@anba> ALTER TABLE test
  2  RENAME COLUMN timestamp TO ts;

Table altered.

Elapsed: 00:00:00.06
scott@anba> CREATE OR REPLACE TRIGGER trg_test
  2  AFTER INSERT ON test
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Trigger Fired Successfully');
  5  END;
  6  /

Trigger created.

Elapsed: 00:00:00.04
scott@anba> INSERT INTO test
  2  VALUES(sysdate);
Trigger Fired Successfully

1 row created.

Elapsed: 00:00:00.01
scott@anba> desc test
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------
 TS                                                                               TIMESTAMP(6)

scott@anba> SELECT * FROM test;

TS
---------------------------------------------------------------------------
26-SEP-05 03.21.41.000000 PM
26-SEP-05 03.24.55.000000 PM

Elapsed: 00:00:00.00
scott@anba> DROP TABLE test;

Table dropped.

Elapsed: 00:00:00.02

TO be honest, I don't have an idea why you'r getting this behaviour. May be some other person can explain.

Regards
Re: timestamp datatype [message #139063 is a reply to message #139036] Mon, 26 September 2005 08:02 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Are you using database links in any way?

Did you check out what the documentation had to say about the error message?
Quote:

ORA-03115 unsupported network datatype or representation

Cause: A user bind or define, or an Oracle function, is not supported by this heterogeneous Net8 connection.

Action: Upgrade the older version of Oracle and try again.
Please also read this AskTom thread, "accessing a higher version database from lower.".
Re: timestamp datatype [message #139222 is a reply to message #138929] Mon, 26 September 2005 21:00 Go to previous messageGo to next message
lavanya_pvn
Messages: 11
Registered: September 2005
Junior Member

thank u guys ......
but can any one tell me why a trigger cannot support a table with same column name and same date type......more over only timestamp is the one, using which we can create such table we can't even create such table with others datatypes........

anyway thanks guys for trying ......

Re: timestamp datatype [message #139250 is a reply to message #138929] Tue, 27 September 2005 00:48 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member
Noo am not using any db links
Re: timestamp datatype [message #186361 is a reply to message #139250] Mon, 07 August 2006 12:52 Go to previous messageGo to next message
plsql
Messages: 1
Registered: August 2006
Junior Member
Guess:may be you are using 8i client to connect the 9i database.
Solution: To solve this problem connect the database using 9i client and works fine.
Re: timestamp datatype [message #186365 is a reply to message #138929] Mon, 07 August 2006 13:18 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Oracle reserved words may NOT be used to name objects such as tables, views, triggers, procedures, columns, constraints...etc. Timestamp is a reserved word and you may NOT use it for a column name. Reserved words are things like timestamp, date, integer, varchar2, char....
Re: timestamp datatype [message #186391 is a reply to message #186361] Mon, 07 August 2006 15:16 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
I hope OP didn't hold their breath for the 11 months it took between the last answer and today (07-AUG-2006).
Previous Topic: count()
Next Topic: partitions
Goto Forum:
  


Current Time: Wed Dec 07 08:44:42 CST 2016

Total time taken to generate the page: 0.14888 seconds