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

在尝试改善报表查询效能的过程中,发现在Sql/plus中查询正常,但在程序中出现ora-06550 PLS-00553错误。经过排查,问题出现在将程序转移到新服务器并安装了不同版本的Oracle客户端后。通过阅读一篇ITPUB博客文章,最终在文章末尾找到解决方案,成功解决了这个PL/SQL编译问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在做一个比较任重道远的事情,结果在过程中总是被其他事情打断,前几天又是被打断了,说是我写的一个报表的查询效能不好,所以要求改善,在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
yahoo.gif
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
yahoo.gifmsnm.gif
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
yahoo.gifmsnm.gif
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
yahoo.gif
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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-917495/

### ORA-06550 PLS-00394 PL/SQL FETCH INTO 值数量错误解决方案 在处理 `PL/SQL` 的 `FETCH INTO` 语句时,如果遇到 `ORA-06550` 或 `PLS-00394` 错误提示,通常是因为目标变量的数量与查询返回的结果列数不匹配。以下是详细的分析和解决方法: #### 错误原因 当执行 `FETCH INTO` 操作时,Oracle 需要确保游标中的每一列都有对应的变量接收其值。如果定义的目标变量数量少于或多余实际查询结果的列数,则会触发此错误。 例如,在以下代码片段中: ```plsql DECLARE v_col1 NUMBER; BEGIN OPEN my_cursor FOR SELECT col1, col2 FROM my_table; FETCH my_cursor INTO v_col1; -- 这里只提供了一个变量,而查询有两列 END; ``` 上述代码会导致 `PLS-00394` 错误,因为查询返回了两个列 (`col1`, `col2`),但仅有一个变量用于存储数据[^1]。 #### 解决方案 为了修复该问题,可以采取以下措施之一: ##### 方法一:调整目标变量数量 确保 `INTO` 子句中的变量数量与查询返回的列数一致。例如: ```plsql DECLARE v_col1 NUMBER; v_col2 VARCHAR2(100); BEGIN OPEN my_cursor FOR SELECT col1, col2 FROM my_table; FETCH my_cursor INTO v_col1, v_col2; -- 添加额外变量以匹配查询列数 END; ``` ##### 方法二:减少查询列数 如果不需要所有列的数据,可以通过修改查询来减少返回的列数。例如: ```plsql DECLARE v_col1 NUMBER; BEGIN OPEN my_cursor FOR SELECT col1 FROM my_table; -- 只选择需要的一列 FETCH my_cursor INTO v_col1; END; ``` ##### 方法三:使用记录类型 (Record Type) 通过声明一个记录类型的变量,简化多列映射的过程。例如: ```plsql DECLARE TYPE my_record_type IS RECORD ( col1_value NUMBER, col2_value VARCHAR2(100) ); v_my_record my_record_type; BEGIN OPEN my_cursor FOR SELECT col1, col2 FROM my_table; FETCH my_cursor INTO v_my_record; -- 将整个记录赋给单个变量 END; ``` 这种方法不仅减少了手动指定多个变量的需求,还提高了代码可读性和维护性[^3]。 ##### 方法四:动态 SQL 绑定集合 对于更复杂的场景,可以考虑使用动态 SQL 来绑定集合。这允许程序灵活地处理不同结构的查询结果。例如: ```plsql DECLARE TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; v_nums num_tab; BEGIN EXECUTE IMMEDIATE 'SELECT col1 FROM my_table' BULK COLLECT INTO v_nums; END; ``` 这种方式适用于批量操作或多行数据提取的情况。 --- ### 总结 以上提供了四种不同的策略来解决 `PL/SQL FETCH INTO` 中因值数量不匹配引发的错误。具体采用哪种方法取决于业务需求以及查询复杂度。无论选择何种方式,都应严格校验目标变量与查询列之间的对应关系,从而避免类似的运行时异常。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值