存储过程一个奇怪的问题
我被一个很奇怪的问题困惑了很久
数据库中有这样一个表
CREATE TABLE MAILLOG2
(
TIMESTAMP DATE NOT NULL,
ID VARCHAR2(4000 BYTE),
"SIZE" NUMBER(19),
FROM_ADDRESS VARCHAR2(4000 BYTE),
TO_ADDRESS VARCHAR2(4000 BYTE),
SUBJECT VARCHAR2(4000 BYTE),
CLIENTIP VARCHAR2(4000 BYTE),
ARCHIVE VARCHAR2(4000 BYTE),
ISSPAM NUMBER(3) DEFAULT 0,
ISHIGHSPAM NUMBER(3) DEFAULT 0,
ISSASPAM NUMBER(3) DEFAULT 0,
ISRBLSPAM NUMBER(3) DEFAULT 0,
SPAMWHITELISTED NUMBER(3) DEFAULT 0,
SPAMBLACKLISTED NUMBER(3) DEFAULT 0,
SASCORE NUMBER(7,2) DEFAULT 0,
SPAMREPORT VARCHAR2(4000 BYTE),
VIRUSINFECTED NUMBER(3) DEFAULT 0,
NAMEINFECTED NUMBER(3) DEFAULT 0,
OTHERINFECTED NUMBER(3) DEFAULT 0,
REPORT VARCHAR2(4000 BYTE),
HOSTNAME VARCHAR2(4000 BYTE),
"DATE" DATE,
TIME DATE,
HEADERS VARCHAR2(4000 BYTE)
)新建一个存储过程,里面仅有一天insert 语句 ,这天insert语句单独执行没问题。
CREATE OR REPLACE PROCEDURE maillog1 (
p_to in varchar2)as
--tmpVar NUMBER;
BEGIN
insert into maillog2 (timestamp,from_address,to_address,subject,clientip,isspam,ishighspam,issaspa,isrblspam,spamwhitelisted,spamblacklisted,sascore,spamreport,virusinfected,nameinfected,otherinfected,report,hostname,headers) values(to_date('2007-8-22','YYYY-MM-DD'),'mazq','zou','p_subject','p_clientip', 1,1,1,1,0,0,10.2,'report',1,1,0,'p_reports','p_hostname','p_headers'); END maillog1;
编译,提示下列错误
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
高手们,能看出错误在那么?还是这个存储过程,向数据库中的其他表插入数据没有问题。
我被一个很奇怪的问题困惑了很久
数据库中有这样一个表
CREATE TABLE MAILLOG2
(
TIMESTAMP DATE NOT NULL,
ID VARCHAR2(4000 BYTE),
"SIZE" NUMBER(19),
FROM_ADDRESS VARCHAR2(4000 BYTE),
TO_ADDRESS VARCHAR2(4000 BYTE),
SUBJECT VARCHAR2(4000 BYTE),
CLIENTIP VARCHAR2(4000 BYTE),
ARCHIVE VARCHAR2(4000 BYTE),
ISSPAM NUMBER(3) DEFAULT 0,
ISHIGHSPAM NUMBER(3) DEFAULT 0,
ISSASPAM NUMBER(3) DEFAULT 0,
ISRBLSPAM NUMBER(3) DEFAULT 0,
SPAMWHITELISTED NUMBER(3) DEFAULT 0,
SPAMBLACKLISTED NUMBER(3) DEFAULT 0,
SASCORE NUMBER(7,2) DEFAULT 0,
SPAMREPORT VARCHAR2(4000 BYTE),
VIRUSINFECTED NUMBER(3) DEFAULT 0,
NAMEINFECTED NUMBER(3) DEFAULT 0,
OTHERINFECTED NUMBER(3) DEFAULT 0,
REPORT VARCHAR2(4000 BYTE),
HOSTNAME VARCHAR2(4000 BYTE),
"DATE" DATE,
TIME DATE,
HEADERS VARCHAR2(4000 BYTE)
)新建一个存储过程,里面仅有一天insert 语句 ,这天insert语句单独执行没问题。
CREATE OR REPLACE PROCEDURE maillog1 (
p_to in varchar2)as
--tmpVar NUMBER;
BEGIN
insert into maillog2 (timestamp,from_address,to_address,subject,clientip,isspam,ishighspam,issaspa,isrblspam,spamwhitelisted,spamblacklisted,sascore,spamreport,virusinfected,nameinfected,otherinfected,report,hostname,headers) values(to_date('2007-8-22','YYYY-MM-DD'),'mazq','zou','p_subject','p_clientip', 1,1,1,1,0,0,10.2,'report',1,1,0,'p_reports','p_hostname','p_headers'); END maillog1;
编译,提示下列错误
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
高手们,能看出错误在那么?还是这个存储过程,向数据库中的其他表插入数据没有问题。
我把列名date 改成 date1 后,编译即可通过
如果date是表的最后一列,存储过程没问题
否则就报错误
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-488: 无效的变量说明: 对象 'DATE' 必须是一个类型或子类型
--
哪位高人给解释一下