One PLSQL error

最新推荐文章于 2022-08-26 14:47:02 发布

原创最新推荐文章于 2022-08-26 14:47:02 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#compilation #null #insert #sql #table #solaris

ORACLE11G 专栏收录该内容

96 篇文章

订阅专栏

本文记录了在Oracle 10G中使用TIMESTAMP作为字段名时遇到的PL/SQL编译错误PLS-00320，并提供了解决方案。当字段名与数据类型名称相同时，会导致编译失败。

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

Everything is OK in 11G but get an error when run it in 10G

SQL> conn / as sysdba
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> @p.sql

Warning: Procedure created with compilation errors.

SQL> show errors
No errors.
SQL> conn scott/tiger
Connected.
SQL> desc snmp_specific_data
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
STARTTIME                                 NOT NULL TIMESTAMP(6)
ENDTIME                                   NOT NULL TIMESTAMP(6)
TIMESTAMP                                 NOT NULL TIMESTAMP(6)
GROUP_ID                                  NOT NULL NUMBER
APP_ID                                    NOT NULL NUMBER
ORG_ID                                    NOT NULL VARCHAR2(255)
CI_UUID                                   NOT NULL VARCHAR2(255)
TOTAL                                     NOT NULL NUMBER
SUCCESS                                   NOT NULL NUMBER
FAILURE                                   NOT NULL NUMBER
ADDITION1                                          VARCHAR2(4000)
ADDITION2                                          VARCHAR2(4000)

SQL> desc snmp_specific_data_raw
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
STARTTIME                                 NOT NULL TIMESTAMP(6)
ENDTIME                                   NOT NULL TIMESTAMP(6)
TIMESTAMP                                 NOT NULL TIMESTAMP(6)
GROUP_ID                                  NOT NULL NUMBER
APP_ID                                    NOT NULL NUMBER
ORG_ID                                    NOT NULL VARCHAR2(255)
CI_UUID                                   NOT NULL VARCHAR2(255)
TOTAL                                     NOT NULL NUMBER
SUCCESS                                   NOT NULL NUMBER
FAILURE                                   NOT NULL NUMBER
ADDITION1                                          VARCHAR2(4000)
ADDITION2                                          VARCHAR2(4000)

SQL>@p.sql

SQL> Warning: Procedure created with compilation errors

SQL> show error
No errors.

Check the MOS found:[ID 416303.1]

PL/SQL - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to
Information in this document applies to any platform.

Bug:6331062






 PLS-00320 if a column is called "TIMESTAMP"







              Component: PLSQL







              Fixed Ver(s): 10205 11107 112







               Symptom(s):







                - Compiling a library unit which refers to a table containing a column whose name is the same as its 







                  datatype fails with PLS-320.







                  For example: 







                   Have a column called TIMESTAMP of type TIMESTAMP.







                  







                   create table test_timestamp2 (timestamp timestamp); 







                   insert into test_timestamp2 values (systimestamp); 







                   commit;







                   create or replace procedure test_timestamp2_proc







                   IS







                   BEGIN







                    insert into test_timestamp2 values (systimestamp);







                   END;







                   /







                   ^







                   Does not compile:







                    ORA-06552: PL/SQL: Compilation unit analysis terminated







                    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed







               Available Workaround(s): 







               					Use a different column name from type name.







               References:







                 Note:6331062.8






 Bug 6331062 - PLS-320 if a column is called "TIMESTAMP"






But the example provided by docs is not same with mine.



SQL> create table test_timestamp2 (timestamp timestamp); 







                   insert into test_timestamp2 values (systimestamp); 











Table created.







SQL> SQL> 



1 row created.







                   commit;



SQL> SQL> 



Commit complete.







SQL> 



SQL>                    create or replace procedure test_timestamp2_proc



  2  



  3                     IS



  4  



  5                     BEGIN



  6  



  7                      insert into test_timestamp2 values (systimestamp);



  8  



  9                     END;



 10  



 11                     /











Warning: Procedure created with compilation errors.







SQL> SQL> 



SQL> show error



Errors for PROCEDURE TEST_TIMESTAMP2_PROC:







LINE/COL ERROR



-------- -----------------------------------------------------------------



7/21     PL/SQL: SQL Statement ignored



7/33     PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated



         ORA-06553: PLS-320: the declaration of the type of this



         expression is incomplete or malformed

But modify the column name then every thing become OK again.