ITPub博客

首页 > 数据库 > Oracle > ora-06550 PLS-00553 PL/SQL: Compilation unit analysis terminated

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

原创 Oracle 作者:playwawa 时间:2007-06-05 14:56:44 0 删除 编辑

在做一个比较任重道远的事情,结果在过程中总是被其他事情打断,前几天又是被打断了,说是我写的一个报表的查询效能不好,所以要求改善,在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 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: 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
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: 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)
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: 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
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: 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> 


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: 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...
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: 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
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: 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.".
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: 131
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: 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....
Re: timestamp datatype [message #186391 is a reply to message #186361 ]Mon, 07 August 2006 15:16 Go to previous message
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博客 ” ,链接:http://blog.itpub.net/202861/viewspace-917495/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 事件 ID 1000
下一篇: 不明白
请登录后发表评论 登录
全部评论
  • 博文量
    105
  • 访问量
    1173511