pls-00103: encountered the symbol ";" when expecting one of the following: loop

Question

I have written an SP to insert into parent and child table. I am receiving the error

pls-00103: encountered the symbol ";" when expecting one of the following: loop.

When I am running through oracle worksheet it is inserting. It is throwing error when running through my code.

The below I have mentioned the SP and its execution

CREATE OR REPLACE PROCEDURE SP_INSERT_RPAEXCEPTION
(
  CONTRACT_CODE IN VARCHAR2  
, EFFECTIVE_DATE IN DATE   
, QUOTE_LINE_ID IN VARCHAR2  
, TXN_ID IN VARCHAR2  
, ERROR_CODE IN VARCHAR2  
, ERROR_DESCRIPTION IN VARCHAR2 
, ERROR_LOCATION IN VARCHAR2 
, ERROR_MODULE IN VARCHAR2 

)
AS 
BEGIN

  INSERT INTO errorinfo
  (
   CONTRACT_CODE,
    EFFECTIVE_DATE,
    PLAN_PROXY_ID,
    QUOTE_LINE_ID,
    TXN_ID,
    ERROR_CODE,
    ERROR_DESCRIPTION,
    ERROR_TIMESTAMP
  )
  VALUES
  (
  CONTRACT_CODE, 
  EFFECTIVE_DATE,
  '',
  QUOTE_LINE_ID,
  TXN_ID,
  ERROR_CODE,
  ERROR_DESCRIPTION,
  sysdate
  );


 INSERT
INTO ERRORDETAILS
  (   
    ERROR_INFO_ID,
    ERROR_LOCATION,
    ERROR_MODULE,
    ERROR_DETAIL
  )
  VALUES
  (
    "USER"."ISEQ$$_941".CURRVAL,
    ERROR_LOCATION,
    ERROR_MODULE,
    ERROR_DESCRIPTION
  );
END;

EXEC SP_EXCEPTION ('4563','01-Jan-2020','ss123','ss123','603','Base not avail,'Find','Base version');

Where and how are you runing that code (which tool/environment)? Typically the code block for a stored procedure needs to be [ended with a `/`](https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695) in Oracle. — , Aug 23 '19 at 08:08
The code you have shown can't generate that error...? (It has potential issues, but not related to loops, as you don't have any loops. Also your `exec` is missing a single quote; and its second argument should be a date rather than a string.) — Alex Poole, Aug 23 '19 at 08:08
FWIW I think the `currval` is probably right-ish; it looks like the first insert gets a generated key, the second is a child table picking up that new key. Using `returning into` a variable and then using that would be clearer though, and not reliant on hard-coded system-generated object names. — Alex Poole, Aug 23 '19 at 08:51

score 0 · Answer 1 · answered Aug 23 '19 at 14:55

0

there is a missing single quote in the execute try below:

EXEC SP_EXCEPTION ('4563','01-Jan-2020','ss123','ss123','603','Base not avail','Find','Base version');

answered Aug 23 '19 at 14:55

GovindarajJ

1
1

EXEC SP_EXCEPTION ('4563','01-Jan-2020','ss123','ss123','603','Base not avail,'Find','Base version'); if the single quote is placed in the wrong place then the ; will not be recognized and it will throw that error. – GovindarajJ Aug 23 '19 at 15:20
It will throw *an* error, sure; but "ORA-01756: quoted string not properly terminated", not PLS-00103. – Alex Poole Aug 23 '19 at 15:34

pls-00103: encountered the symbol ";" when expecting one of the following: loop

1 Answers1