oracle 程序错误 PLS-00103 遇到符号“END”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20376152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle procedure error PLS-00103 Encountered the symbol "END"
提问by user3065825
I get the following error:
我收到以下错误：
58/6 PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function pragma procedure subtype type
current cursor delete
exists prior
58/6 PLS-00103：在预期以下情况之一时遇到符号“END”：
begin 函数 pragma procedure subtype type
当前游标删除
之前存在
Anyone have any idea what I am missing?
任何人都知道我缺少什么？
CREATE OR REPLACE PROCEDURE VALIDATE_BI_JOB_COMPLETE_PROC AS
msg SYS.XMLTYPE;
msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
msg_id RAW(16);
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
rec_count INTEGER;
/******************************************************************************
NAME: VALIDATE_BI_JOB_COMPLETE_PROC
*******************************************************************************
BEGIN
INSERT INTO JOB_LOG
(JOB_NAME, JOB_SEQUENCE, RUN_DATE, LINE_SEQ_NO, LOG_TXT)
VALUES
($$PLSQL_UNIT, 1, SYSDATE, 1, 'Job Started at ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
COMMIT;
rec_count := 0;
SELECT COUNT(*) INTO rec_count
FROM SCHEDULED_JOBS
WHERE JOB_NAME IN ('bi_get_transactional_data', 'bi_get_reference_data') AND
CURRENTLY_PROCESSING_FLG = 'Y';
IF rec_count > 0 THEN
BEGIN
DECLARE CURSOR email IS
SELECT EMAIL_ID
FROM ERROR_EMAIL_NOTIFICATION
WHERE ACTIVE = 'Y' AND
SEVERITY_CD = 'ERROR';
vFROM VARCHAR2(30) := '[email protected]';
vTYPE VARCHAR2(30) := 'text/plain; charset=us-ascii';
msg_body VARCHAR2(4000) := 'BI jobs are still running, please investigate.
bi_get_transactional_data, bi_get_reference_data)';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
FOR email_rec IN email
LOOP
utl_mail.send(vFROM, email_rec.EMAIL_ID, NULL, NULL, ora_database_name || ': ' ,
msg_body, vTYPE, NULL);
END LOOP;
END;
END IF;
INSERT INTO JOB_LOG
(JOB_NAME, JOB_SEQUENCE, RUN_DATE, LINE_SEQ_NO, LOG_TXT)
VALUES
($$PLSQL_UNIT, 2, SYSDATE, 1, 'Job Ended at ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS') ||
'. Records sent to JSSO: ' || rec_processed);
COMMIT;
-- exception processing goes here
EXCEPTION
WHEN OTHERS THEN
LOG_ERROR(
p_APP_ID => 'ORACLE',
p_SEVERITY_CD => 'ERROR',
p_ROUTINE_NAME => $$PLSQL_UNIT,
p_BACKTRACE => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
p_SQL_CODE => SQLCODE,
p_LOG_TXT => SQLERRM,
p_HOST_ID => SYS_CONTEXT('userenv', 'host'),
p_USER_ID => SYS_CONTEXT('userenv', 'session_user'),
p_SESSION_ID => SYS_CONTEXT('userenv', 'sid'));
INSERT INTO JOB_LOG
(JOB_NAME, JOB_SEQUENCE, RUN_DATE, LINE_SEQ_NO, LOG_TXT)
VALUES
($$PLSQL_UNIT, 2, SYSDATE, 1, 'Job ABENDED at ' || to_char(sysdate, 'MM/DD/YYYY
HH:MI:SS') || '. Error condtion.');
COMMIT;
END;
/
回答by Alen Oblak
You must move your BEGINfrom the 28 line to the line 41. Instead of this:
你必须将你的BEGIN从 28 行移动到 41 行。而不是这个：
IF rec_count > 0 THEN
BEGIN <----------------------------------------- THIS IS WRONG
DECLARE CURSOR email IS
SELECT EMAIL_ID
FROM ERROR_EMAIL_NOTIFICATION
WHERE ACTIVE = 'Y' AND
SEVERITY_CD = 'ERROR';
vFROM VARCHAR2(30) := '[email protected]';
vTYPE VARCHAR2(30) := 'text/plain; charset=us-ascii';
msg_body VARCHAR2(4000) := 'BI jobs are still running, please investigate.
bi_get_transactional_data, bi_get_reference_data)';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
FOR email_rec IN email
LOOP
Write this:
写这个：
IF rec_count > 0 THEN
DECLARE CURSOR email IS
SELECT EMAIL_ID
FROM ERROR_EMAIL_NOTIFICATION
WHERE ACTIVE = 'Y' AND
SEVERITY_CD = 'ERROR';
vFROM VARCHAR2(30) := '[email protected]';
vTYPE VARCHAR2(30) := 'text/plain; charset=us-ascii';
msg_body VARCHAR2(4000) := 'BI jobs are still running, please investigate.
bi_get_transactional_data, bi_get_reference_data)';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
BEGIN <-------------------------------------------- THIS IS OK
FOR email_rec IN email
LOOP
回答by Tom Thomas
IF rec_count > 0 THEN
BEGIN
DECLARE
CURSOR email IS
SELECT EMAIL_ID
FROM ERROR_EMAIL_NOTIFICATION
WHERE ACTIVE = 'Y' AND
SEVERITY_CD = 'ERROR';
vFROM VARCHAR2(30) := '[email protected]';
vTYPE VARCHAR2(30) := 'text/plain; charset=us-ascii';
msg_body VARCHAR2(4000) := 'BI jobs are still running, please investigate.
bi_get_transactional_data, bi_get_reference_data)';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
BEGIN
FOR email_rec IN email
LOOP
utl_mail.send(vFROM, email_rec.EMAIL_ID, NULL, NULL, ora_database_name || ': ' ,
msg_body, vTYPE, NULL);
END LOOP;
END;
END;
END IF;
--rest of the code
The problem was that, after the 'DECLARE' you need to add a 'BEGIN', and an 'END' following the 'END LOOP'. I hope you understood. Also that makes me wonder, do you need the 'BEGIN' you have added just above the 'DECLARE' there? Hope this solves it :)
问题是，在“DECLARE”之后，您需要在“END LOOP”之后添加“BEGIN”和“END”。我希望你明白。这也让我想知道，您是否需要在“DECLARE”上方添加的“BEGIN”？希望这能解决它:)
回答by Mike
You have an END;just before your END IF;which is causing the problem
你END;之前有一个END IF;导致问题的
So Oracle thinks the IFstatement is not closed. Plus it will ignore all the code after the END!
所以Oracle认为该IF语句没有关闭。此外，它将忽略END!之后的所有代码。
