在做一个比较任重道远的事情，结果在过程中总是被其他事情打断，前几天又是被打断了，说是我写的一个报表的查询效能不好，所以要求改善，在Sql/plus 里面执行我的Sql发现效能也就在1秒以内，可是到程序里面就有问题了，所以怀疑是iis server的问题，于是想将程序搬到另一台server也就是后来我碰到标题问题的这台上测试，又因为oracle9i 的odbc有问题，所以将这台server上的9i client删除装了oracle817测试完了后，发现我要做的任重道远的事情的东西不能用了，select 的时候总是提示:ora-06550 PLS-00553 PL/SQL: Compilation unit analysis terminated 困扰我好几天，在这片文章中找到了答案.....（连自己都佩服自己的耐性，因为这篇文章的最后面才会有一句话提醒了我，解决了问题，我都一个个看过来了）

[@more@]

timestamp datatype [message #138929]

Sun, 25 September 2005 21:16

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?

Report message to a moderator

Re: timestamp datatype [message #138997 is a reply to message #138929 ]

Mon, 26 September 2005 05:00

saadatahmad
Messages: 356
Registered: March 2005
Location: Kuwait

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

Report message to a moderator

Re: timestamp datatype [message #139011 is a reply to message #138929 ]

Mon, 26 September 2005 05:17

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

see type is not supporting (This is in Oracle 9i only)

Report message to a moderator

Re: timestamp datatype [message #139014 is a reply to message #138929 ]

Mon, 26 September 2005 05:24

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

Report message to a moderator

Re: timestamp datatype [message #139027 is a reply to message #138929 ]

Mon, 26 September 2005 05:54

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>

Rajuvan

Report message to a moderator

Re: timestamp datatype [message #139036 is a reply to message #138929 ]

Mon, 26 September 2005 06:17

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...

Report message to a moderator

Re: timestamp datatype [message #139053 is a reply to message #139036 ]

Mon, 26 September 2005 07:38

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.

Regards

Report message to a moderator

Re: timestamp datatype [message #139063 is a reply to message #139036 ]

Mon, 26 September 2005 08:02

Art Metzer
Messages: 2477
Registered: January 2003

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.".

Report message to a moderator

Re: timestamp datatype [message #139222 is a reply to message #138929 ]

Mon, 26 September 2005 21:00

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 ......

Report message to a moderator

Re: timestamp datatype [message #139250 is a reply to message #138929 ]

Tue, 27 September 2005 00:48

sharan_it
Messages: 131
Registered: July 2005
Location: Chennai

Senior Member

Noo am not using any db links

Report message to a moderator

Re: timestamp datatype [message #186361 is a reply to message #139250 ]

Mon, 07 August 2006 12:52

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.

Report message to a moderator

Re: timestamp datatype [message #186365 is a reply to message #138929 ]

Mon, 07 August 2006 13:18

Bill B
Messages: 426
Registered: February 2005

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....

Report message to a moderator

Re: timestamp datatype [message #186391 is a reply to message #186361 ]

Mon, 07 August 2006 15:16

joy_division
Messages: 1322
Registered: February 2005
Location: NY

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).

ITPub博客

ora-06550 PLS-00553 PL/SQL: Compilation unit analysis terminated

最新文章