我的一个预言机存储过程出错，出现错误 ORA:24344

Question

我在尝试创建存储过程时遇到一个 oracle 存储过程错误，出现错误 ORA：24344 我收到以下错误消息 ORA-24344：成功，但编译错误 PL/SQL：ORA-00927：缺少等号 PL/SQL：SQL 语句被忽略

CREATE OR REPLACE PROCEDURE sp_comms_update_stg (
ssms_key            IN VARCHAR2,
spolicyNumber       IN VARCHAR2,
sclientKey          IN VARCHAR2,
sclientReference    IN VARCHAR2,
sresult          OUT SYS_REFCURSOR
)
IS
BEGIN
UPDATE stg_update_email 
SET
sms_key := ssms_key, 
policy_number := spolicyNumber, 
client_key := sclientKey, 
process_status := 'Processed'
WHERE client_reference = sclientReference;
INSERT INTO EVENTLOG VALUES(seq_eventlog.NEXTVAL, spolicyNumber , (select 
to_date(sysdate) from dual),
to_char(sysdate,'HH24:MI:SS'), 101, null, 1, 'Updated stg_update_email', 
'stg_update_email successfully updated', 'P', var_Client, null, null);

sresult:= true;
COMMIT;
EXCEPTION
WHEN OTHERS Then
Rollback;
sresult := false;
RAISE_APPLICATION_ERROR (-20000,'ERROR IN EXECUTING PROCEDURE 
SP_UNDEL_UPD_STG - '|| chr(13)||chr(10) || UPPER(SQLERRM) || 
chr(13)||chr(10));
END;

Answer 1

ORA-24344：成功，但编译错误
PL/SQL：ORA-00927：缺少等号
PL/SQL：SQL 语句被忽略

这表明您在更新语句中使用了PL/SQL赋值运算符:=而不是SQL one=。但是你说尽管已经修复了你仍然无法编译你的过程。也许只是编译器不喜欢将布尔值分配给sys_refrcursor参数，可能还有更多。

我建议您运行此查询以查看编译器发现了哪些其他错误。

select * from user_errors 
where name = 'SP_COMMS_UPDATE_STG'

像Allable Automations PL/SQL Developer和Oracle SQL Developer这样的IDE会自动为我们执行此操作(在Orcale SQL Developer中，您需要单击"错误"选项卡(，但知道显式查询不会造成任何伤害。

Answer 2

请参阅评论; 解决这些问题应该使您的过程编译：

CREATE OR REPLACE PROCEDURE sp_comms_update_stg(
ssms_key            IN     VARCHAR2,
spolicyNumber       IN     VARCHAR2,
sclientKey          IN     VARCHAR2,
sclientReference    IN     VARCHAR2,
sresult                OUT boolean      /* based on your code, you probably need a BOOLEAN */
) IS
BEGIN
UPDATE stg_update_email
SET sms_key           = ssms_key,        /* = and not := */
policy_number     = spolicyNumber,   /* = and not := */
client_key        = sclientKey,      /* = and not := */
process_status    = 'Processed'      /* = and not := */
WHERE client_reference  = sclientReference;
INSERT INTO EVENTLOG
VALUES (
seq_eventlog.NEXTVAL,
spolicyNumber,
sysdate,    --(SELECT TO_DATE(SYSDATE) FROM DUAL),     /* let's simplify! also, to_date(sysdate) makes no sense: sysdate already is a date */
/* or even trunc(sysdate) if you don't want time informations*/
TO_CHAR(SYSDATE, 'HH24:MI:SS'),
101,
NULL,
1,
'Updated stg_update_email',
'stg_update_email successfully updated',
'P',
var_Client,                    /* where is var_Client defined ? */
NULL,
NULL
);

sresult    := TRUE;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
sresult    := FALSE;
RAISE_APPLICATION_ERROR(-20000, 'ERROR IN EXECUTING PROCEDURE SP_UNDEL_UPD_STG - '
|| CHR(13) || CHR(10) || UPPER(SQLERRM) || CHR(13) || CHR(10));
END;