当前位置：theitroad>oracle PLS-00103：在期待以下之一时遇到符号“END”

oracle PLS-00103：在期待以下之一时遇到符号“END”

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/6731876/
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

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 03:25:29 　来源:igfitidea点击:

PLS-00103: Encountered the symbol "END" when expecting one of the fo llowing

oraclestored-proceduresplsql

提问by JGJ

I get this error:

我收到此错误：

[sqlplus] Truncating dimension dw_DM_Application
[sqlplus] END;
[sqlplus] *
[sqlplus] ERROR at line 190:
[sqlplus] ORA-06550: line 190, column 1:
[sqlplus] PLS-00103: Encountered the symbol "END" when expecting one of the following:
[sqlplus] begin case declare exit for goto if loop mod null pragma
[sqlplus] raise return select update while with <an identifier>
[sqlplus] <a double-quoted delimited-identifier> <a bind variable> <<
[sqlplus] close current delete fetch lock insert open rollback
[sqlplus] savepoint set sql execute commit forall merge pipe

For the following code and I Have no idea what is causing this ugly error:

对于以下代码，我不知道是什么导致了这个丑陋的错误：

DECLARE
StartDate DATE;
EndDate DATE;

PROCEDURE DM_Application_Full_Refresh IS    

    v_RecordStatus VARCHAR2(10);    

BEGIN
v_RecordStatus:='Normal';

 INSERT INTO dw_DM_Application
     (
      dmApplicationkey,
      vRecordStatus,         
      nApplicationId,
      nPostingTargetId,
      nCandidateProfileId,
      nOpeningId,
      nJobselectionProcessId,
      vApplicationStatus,
      dApplicationDate,
      bManually,
      vOrigin,
      bArchived,
      dCreationDate,
      dCreationDateMM,
      dUpdateDate,
      dUpdateDateMM,
      bActiveApplication,
      vApplicationSrcType,
      vSrcChannelName,
      vSourcingMedium,
      nJobDeptlevel1Id,
      nJobDeptlevel2Id,
      nJobDeptlevel3Id,
      bJobIsArchived,
      bJobIsGeneralApp,
      nJobRecruitingUser1Id,
      nJobRecruitingUser2Id,
      nJobRecruitingUser3Id,
      vJobCountry,
      vJobRegion,                                
      vJobContractType,
      vCandType,                 
      vCandGender,

      dHireDate,

      dHireDateMM
     )
SELECT row_number() over (order by 1),
       v_RecordStatus,
       a.*
FROM (
 SELECT /*+ use_nl(a cp) INDEX(a IDX_RLSH_25_FK_) index (cp pk_candidateprofile) */
    a.nApplicationId,
    a.nPostingTargetId,
    a.nCandidateProfileId,
    a.nOpeningId,
    a.nSelectionProcessId nJobSelectionProcessId,
    a.vApplicationStatus,
    a.dApplicationDate,
    a.bManually,
    a.sOrigin vOrigin,
    0 bArchived,  -- Not archived
    TRUNC(a.dCreationDate) dCreationDate,
    TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
    TRUNC(a.dUpdateDate) dUpdateDate,
    TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
    a.bActiveApplication,
    a.vApplicationSrcType,
    NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
    a.vSourcingMedium,
    (Select nDepartmentid From Department where nlevel = 1
       Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
    (Select nDepartmentID From Department where nlevel = 2
       Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
    (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id,
    o.bArchived bJobIsArchived, 
    o.bSpontaneousOpening bJobIsGeneralapp,
    o.nRecruitingUserId nJobRecruitingUser1Id, 
    o.nRecruitingUserId2 nJobRecruitingUser2Id, 
    o.nRecruitingUserId3 nJobRecruitingUser3Id,
    o.vCountry JobCountry,
    o.vRegion JobRegion,     
    o.vContractType JobContractType,
    cp.vCandidateType,
    DECODE(cp.vSex, 'M', 'M', 'F', 'F', '-') CandGender,

    TRUNC(a.dUpdateDate) dHireDate,
    TRUNC(a.dUpdateDate, 'MM') dHireDateMM
  FROM Application a, Opening o, CandidateProfile cp
  WHERE a.nOpeningId=o.nOpeningId
    AND a.nCandidateProfileid=cp.nCandidateProfileid
 UNION ALL
  SELECT /*+ FIRST_ROWS */ 
   a.nApplicationIdH nApplicationId,
   a.nPostingTargetIdH nPostingTargetId,
   a.nCandidateProfileIdH nCandidateProfileId,
   a.nOpeningId,
   a.nSelectionProcessId nJobselectionProcessId,
   a.vApplicationStatus,
   a.dApplicationDate,
   a.bManually,
   a.sOrigin vOrigin,
   1 bArchived,  -- Archived
   TRUNC(a.dCreationDate) dCreationDate,
   TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
   TRUNC(a.dUpdateDate) dUpdateDate,
  TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
   a.bActiveApplication,
   a.vApplicationSrcType,
   NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
   a.vSourcingMedium,
   (Select nDepartmentid From Department where nlevel = 1
      Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
   (Select nDepartmentID From Department where nlevel = 2
      Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
   (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id, 
   o.bArchived bJobIsArchived, 
   o.bSpontaneousOpening bJobIsGeneralapp,
   o.nRecruitingUserId nJobRecruitingUser1Id, 
   o.nRecruitingUserId2 nJobRecruitingUser2Id, 
   o.nRecruitingUserId3 nJobRecruitingUser3Id,
   o.vCountry JobCountry,      
   o.vRegion JobRegion,                        
   o.vContractType JobContractType,
   cp.vCandidateType,
   DECODE(cp.vSex, 'M', 'M', 'F', 'F', NULL) CandGender,

   TRUNC(a.dUpdateDate) dHireDate,
  TRUNC(a.dUpdateDate, 'MM') dHireDateMM

FROM ApplicationH a, Opening o, CandidateProfileH cp
WHERE a.nOpeningId=o.nOpeningId
  AND a.nCandidateProfileidH=cp.nCandidateProfileidH
  AND cp.nCandidateProfileidH > 0) a;       
END;

BEGIN
SELECT SYSDATE
INTO StartDate
FROM DUAL;

 DM_Application_Full_Refresh;

 COMMIT;

 SELECT SYSDATE
 INTO EndDate
 FROM DUAL; 

 DBMS_OUTPUT.PUT_LINE('Full refresh of DW_DM_APPLICATION finished. Time: '|| TO_CHAR(ROUND((EndDate-StartDate)*3600*24)));

EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Errors occured: '||SUBSTR(SQLERRM(SQLCODE),1, 200));

 END;
 /

回答by Tony Andrews

You have two pieces of code there:

你有两段代码：

  1. Code to define a procedure
  2. Code to run that procedure
  1. 定义过程的代码
  2. 运行该过程的代码

However, you have run them together, which Oracle doesn't like. I'm not sure if you wanted to create a stored procedure, or just define a procedure temporarily and run it? If the latter then this will do it:

但是，您将它们一起运行，Oracle 不喜欢这种情况。我不确定您是想创建一个存储过程，还是只是临时定义一个过程并运行它？如果是后者，那么这将做到：

DECLARE
    PROCEDURE DM_Application_Full_Refresh IS    

        v_RecordStatus VARCHAR2(10);    

    BEGIN
    v_RecordStatus:='Normal';

     INSERT INTO dw_DM_Application
         (
          dmApplicationkey,
          vRecordStatus,         
          nApplicationId,
          nPostingTargetId,
          nCandidateProfileId,
          nOpeningId,
          nJobselectionProcessId,
          vApplicationStatus,
          dApplicationDate,
          bManually,
          vOrigin,
          bArchived,
          dCreationDate,
          dCreationDateMM,
          dUpdateDate,
          dUpdateDateMM,
          bActiveApplication,
          vApplicationSrcType,
          vSrcChannelName,
          vSourcingMedium,
          nJobDeptlevel1Id,
          nJobDeptlevel2Id,
          nJobDeptlevel3Id,
          bJobIsArchived,
          bJobIsGeneralApp,
          nJobRecruitingUser1Id,
          nJobRecruitingUser2Id,
          nJobRecruitingUser3Id,
          vJobCountry,
          vJobRegion,                                
          vJobContractType,
          vCandType,                 
          vCandGender,

          dHireDate,

          dHireDateMM
         )
    SELECT row_number() over (order by 1),
           v_RecordStatus,
           a.*
    FROM (
     SELECT /*+ use_nl(a cp) INDEX(a IDX_RLSH_25_FK_) index (cp pk_candidateprofile) */
        a.nApplicationId,
        a.nPostingTargetId,
        a.nCandidateProfileId,
        a.nOpeningId,
        a.nSelectionProcessId nJobSelectionProcessId,
        a.vApplicationStatus,
        a.dApplicationDate,
        a.bManually,
        a.sOrigin vOrigin,
        0 bArchived,  -- Not archived
        TRUNC(a.dCreationDate) dCreationDate,
        TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
        TRUNC(a.dUpdateDate) dUpdateDate,
        TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
        a.bActiveApplication,
        a.vApplicationSrcType,
        NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
        a.vSourcingMedium,
        (Select nDepartmentid From Department where nlevel = 1
           Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
        (Select nDepartmentID From Department where nlevel = 2
           Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
        (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id,
        o.bArchived bJobIsArchived, 
        o.bSpontaneousOpening bJobIsGeneralapp,
        o.nRecruitingUserId nJobRecruitingUser1Id, 
        o.nRecruitingUserId2 nJobRecruitingUser2Id, 
        o.nRecruitingUserId3 nJobRecruitingUser3Id,
        o.vCountry JobCountry,
        o.vRegion JobRegion,     
        o.vContractType JobContractType,
        cp.vCandidateType,
        DECODE(cp.vSex, 'M', 'M', 'F', 'F', '-') CandGender,

        TRUNC(a.dUpdateDate) dHireDate,
        TRUNC(a.dUpdateDate, 'MM') dHireDateMM
      FROM Application a, Opening o, CandidateProfile cp
      WHERE a.nOpeningId=o.nOpeningId
        AND a.nCandidateProfileid=cp.nCandidateProfileid
     UNION ALL
      SELECT /*+ FIRST_ROWS */ 
       a.nApplicationIdH nApplicationId,
       a.nPostingTargetIdH nPostingTargetId,
       a.nCandidateProfileIdH nCandidateProfileId,
       a.nOpeningId,
       a.nSelectionProcessId nJobselectionProcessId,
       a.vApplicationStatus,
       a.dApplicationDate,
       a.bManually,
       a.sOrigin vOrigin,
       1 bArchived,  -- Archived
       TRUNC(a.dCreationDate) dCreationDate,
       TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
       TRUNC(a.dUpdateDate) dUpdateDate,
      TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
       a.bActiveApplication,
       a.vApplicationSrcType,
       NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
       a.vSourcingMedium,
       (Select nDepartmentid From Department where nlevel = 1
          Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
       (Select nDepartmentID From Department where nlevel = 2
          Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
       (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id, 
       o.bArchived bJobIsArchived, 
       o.bSpontaneousOpening bJobIsGeneralapp,
       o.nRecruitingUserId nJobRecruitingUser1Id, 
       o.nRecruitingUserId2 nJobRecruitingUser2Id, 
       o.nRecruitingUserId3 nJobRecruitingUser3Id,
       o.vCountry JobCountry,      
       o.vRegion JobRegion,                        
       o.vContractType JobContractType,
       cp.vCandidateType,
       DECODE(cp.vSex, 'M', 'M', 'F', 'F', NULL) CandGender,

       TRUNC(a.dUpdateDate) dHireDate,
      TRUNC(a.dUpdateDate, 'MM') dHireDateMM

    FROM ApplicationH a, Opening o, CandidateProfileH cp
    WHERE a.nOpeningId=o.nOpeningId
      AND a.nCandidateProfileidH=cp.nCandidateProfileidH
      AND cp.nCandidateProfileidH > 0) a;       
    END;

BEGIN
    SELECT SYSDATE
    INTO StartDate
    FROM DUAL;

     DM_Application_Full_Refresh;

     COMMIT;

     SELECT SYSDATE
     INTO EndDate
     FROM DUAL; 

     DBMS_OUTPUT.PUT_LINE('Full refresh of DW_DM_APPLICATION finished. Time: '|| TO_CHAR(ROUND((EndDate-StartDate)*3600*24)));

    EXCEPTION
     WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Errors occured: '||SUBSTR(SQLERRM(SQLCODE),1, 200));

END;
/

If you did want a permanent stored procedure then do this:

如果您确实想要一个永久存储过程，请执行以下操作：

CREATE OR REPLACE
PROCEDURE DM_Application_Full_Refresh IS    

    v_RecordStatus VARCHAR2(10);    

BEGIN
v_RecordStatus:='Normal';

 INSERT INTO dw_DM_Application
     (
      dmApplicationkey,
      vRecordStatus,         
      nApplicationId,
      nPostingTargetId,
      nCandidateProfileId,
      nOpeningId,
      nJobselectionProcessId,
      vApplicationStatus,
      dApplicationDate,
      bManually,
      vOrigin,
      bArchived,
      dCreationDate,
      dCreationDateMM,
      dUpdateDate,
      dUpdateDateMM,
      bActiveApplication,
      vApplicationSrcType,
      vSrcChannelName,
      vSourcingMedium,
      nJobDeptlevel1Id,
      nJobDeptlevel2Id,
      nJobDeptlevel3Id,
      bJobIsArchived,
      bJobIsGeneralApp,
      nJobRecruitingUser1Id,
      nJobRecruitingUser2Id,
      nJobRecruitingUser3Id,
      vJobCountry,
      vJobRegion,                                
      vJobContractType,
      vCandType,                 
      vCandGender,

      dHireDate,

      dHireDateMM
     )
SELECT row_number() over (order by 1),
       v_RecordStatus,
       a.*
FROM (
 SELECT /*+ use_nl(a cp) INDEX(a IDX_RLSH_25_FK_) index (cp pk_candidateprofile) */
    a.nApplicationId,
    a.nPostingTargetId,
    a.nCandidateProfileId,
    a.nOpeningId,
    a.nSelectionProcessId nJobSelectionProcessId,
    a.vApplicationStatus,
    a.dApplicationDate,
    a.bManually,
    a.sOrigin vOrigin,
    0 bArchived,  -- Not archived
    TRUNC(a.dCreationDate) dCreationDate,
    TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
    TRUNC(a.dUpdateDate) dUpdateDate,
    TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
    a.bActiveApplication,
    a.vApplicationSrcType,
    NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
    a.vSourcingMedium,
    (Select nDepartmentid From Department where nlevel = 1
       Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
    (Select nDepartmentID From Department where nlevel = 2
       Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
    (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id,
    o.bArchived bJobIsArchived, 
    o.bSpontaneousOpening bJobIsGeneralapp,
    o.nRecruitingUserId nJobRecruitingUser1Id, 
    o.nRecruitingUserId2 nJobRecruitingUser2Id, 
    o.nRecruitingUserId3 nJobRecruitingUser3Id,
    o.vCountry JobCountry,
    o.vRegion JobRegion,     
    o.vContractType JobContractType,
    cp.vCandidateType,
    DECODE(cp.vSex, 'M', 'M', 'F', 'F', '-') CandGender,

    TRUNC(a.dUpdateDate) dHireDate,
    TRUNC(a.dUpdateDate, 'MM') dHireDateMM
  FROM Application a, Opening o, CandidateProfile cp
  WHERE a.nOpeningId=o.nOpeningId
    AND a.nCandidateProfileid=cp.nCandidateProfileid
 UNION ALL
  SELECT /*+ FIRST_ROWS */ 
   a.nApplicationIdH nApplicationId,
   a.nPostingTargetIdH nPostingTargetId,
   a.nCandidateProfileIdH nCandidateProfileId,
   a.nOpeningId,
   a.nSelectionProcessId nJobselectionProcessId,
   a.vApplicationStatus,
   a.dApplicationDate,
   a.bManually,
   a.sOrigin vOrigin,
   1 bArchived,  -- Archived
   TRUNC(a.dCreationDate) dCreationDate,
   TRUNC(a.dCreationDate, 'MM') dCreationDateMM,
   TRUNC(a.dUpdateDate) dUpdateDate,
  TRUNC(a.dUpdateDate, 'MM') dUpdateDateMM,
   a.bActiveApplication,
   a.vApplicationSrcType,
   NVL(a.sOrigin,DECODE(a.bManually,0,'-',1,'TalentLink',a.bManually)) SrcChannelName,
   a.vSourcingMedium,
   (Select nDepartmentid From Department where nlevel = 1
      Connect by prior nFatherDptID = nDepartmentID start with nDepartmentid = o.nDepartmentID) DeptLevel1Id, 
   (Select nDepartmentID From Department where nlevel = 2
      Connect by prior nFatherDptID = nDepartmentID start with nDepartmentID = o.nDepartmentID) DeptLevel2Id,
   (Select nDepartmentid FROM Department WHERE nDepartmentid = o.nDepartmentID AND nlevel = 3) DeptLevel3Id, 
   o.bArchived bJobIsArchived, 
   o.bSpontaneousOpening bJobIsGeneralapp,
   o.nRecruitingUserId nJobRecruitingUser1Id, 
   o.nRecruitingUserId2 nJobRecruitingUser2Id, 
   o.nRecruitingUserId3 nJobRecruitingUser3Id,
   o.vCountry JobCountry,      
   o.vRegion JobRegion,                        
   o.vContractType JobContractType,
   cp.vCandidateType,
   DECODE(cp.vSex, 'M', 'M', 'F', 'F', NULL) CandGender,

   TRUNC(a.dUpdateDate) dHireDate,
  TRUNC(a.dUpdateDate, 'MM') dHireDateMM

FROM ApplicationH a, Opening o, CandidateProfileH cp
WHERE a.nOpeningId=o.nOpeningId
  AND a.nCandidateProfileidH=cp.nCandidateProfileidH
  AND cp.nCandidateProfileidH > 0) a;       
END;
/

And then do this to run it:

然后执行以下操作来运行它：

BEGIN
SELECT SYSDATE
INTO StartDate
FROM DUAL;

 DM_Application_Full_Refresh;

 COMMIT;

 SELECT SYSDATE
 INTO EndDate
 FROM DUAL; 

 DBMS_OUTPUT.PUT_LINE('Full refresh of DW_DM_APPLICATION finished. Time: '|| TO_CHAR(ROUND((EndDate-StartDate)*3600*24)));

EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Errors occured: '||SUBSTR(SQLERRM(SQLCODE),1, 200));

END;
/

回答by Jon Heller

The code looks like it should compile and run.

代码看起来应该可以编译并运行。

The error message says line 190, but there aren't 190 lines in your sample. Are we not looking at the real code?

错误消息显示第 190 行，但您的示例中没有 190 行。我们不是在看真正的代码吗？

Also, you should remove the EXCEPTION block. SQL*Plus automatically displays all the debugging information you need.

此外，您应该删除 EXCEPTION 块。SQL*Plus 会自动显示您需要的所有调试信息。

相关推荐

oracle 将 sqlloader 连接到服务器时出现 ERROR 704 & 12154

oracle 将 sqlloader 连接到服务器时出现 ERROR 704 & 12154

如何在 Oracle 中重命名主键以便可以重用

如何在 Oracle 中重命名主键以便可以重用

检查 oracle sid 和数据库名称

检查 oracle sid 和数据库名称

oracle 如何比较 PL/SQL 中的字符串？

oracle 如何比较 PL/SQL 中的字符串？

相关推荐
最近更新
标签