首页 文章

oracle程序错误PLS-00103遇到符号“END”[关闭]

提问于
浏览 次
0

我收到以下错误：

58/6 PLS-00103：遇到以下其中一项时遇到符号“END”：begin function pragma procedure子类型当前游标删除存在先于

任何人都知道我错过了什么？

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) := 'WORK_SYSTEM@XXX.COM';
         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;
/
oracle plsql procedure

3 回答

  • 0

    你的 END IF; 之前有一个 END; 导致问题

    所以Oracle认为 IF 声明没有关闭 . 另外，它会忽略 END 之后的所有代码！

    回复于
  • 3
    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) := 'WORK_SYSTEM@XXX.COM';
     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

    问题是，在'DECLARE'之后，您需要在'END LOOP'之后添加'BEGIN'和'END' . 我希望你理解 . 这也让我想知道，你是否需要在'DECLARE'上方添加'BEGIN'？希望这能解决它:)

    回复于
  • 0

    您必须将 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) := 'WORK_SYSTEM@XXX.COM';
     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

    写这个：

    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) := 'WORK_SYSTEM@XXX.COM';
     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
    回复于

相关问题