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?
sharan_it Messages: 131 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
rajavu1 Messages: 586 Registered: May 2005 Location: Bangalore
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>
sharan_it Messages: 131 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...
saadatahmad Messages: 356 Registered: March 2005 Location: Kuwait
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.
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........
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....