PLS-00103: Encountered the symbol "IS" when expecting one of the following: := . ( @ % ; not null range default character

Question

I am facing this error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PLS-00103: Encountered the symbol "IS" when expecting one of the
         following:
         := . ( @ % ; not null range default character

My package is:

CREATE OR REPLACE PACKAGE BODY EMP_PK AS
 
PROCEDURE INSERT_TR(EMPNO EMP_20171250.EMPNO%TYPE,ENAME EMP_20171250.ENAME%TYPE,SAL EMP_20171250.SAL%TYPE)  IS  
INSERT_ERROR EXCEPTION;  
CRUSOR C1 IS INSERT INTO EMP_20171250(EMPNO,ENAME,SAL) VALUES(EMPNO,ENAME,SAL);  
BEGIN  
IF(C1%FOUND) THEN  
DBMS_OUTPUT.PUT_LINE('RECORD INSERTED');  
ELSE   
RAISE INSERT_ERROR; 
END IF;  
EXCEPTION    
WHEN INSERT_ERROR THEN 
DBMS_OUTPUT.PUT_LINE('ERROR WHILE RECORD INSERTION');  
END INSERT_TR; 
END EMP_PK;

score 0 · Answer 1 · answered Nov 09 '20 at 10:48

it is not CRUSOR but CURSOR
cursor can't contain INSERT statement; it is a SELECT
you are checking whether cursor returned something, but - you never opened it nor fetched from it so it is pretty much useless
insert_error looks like there was an error while inserting a row, but - you are actually raising it if cursor didn't return anything

Basically, you don't need a cursor at all. Such a procedure would do:

   PROCEDURE insert_tr (p_empno  emp_20171250.empno%TYPE,
                        p_ename  emp_20171250.ename%TYPE,
                        p_sal    emp_20171250.sal%TYPE)
   IS
   BEGIN
         INSERT INTO emp_20171250 (empno, ename, sal)
              VALUES (p_empno, p_ename, p_sal);
   END insert_tr;

If insert fails, Oracle will raise an exception anyway so - yes, you can handle it/them if you want.

Also, it is good to distinguish parameter names from column names; for example, precede their names with a p_ (as I did).

PLS-00103: Encountered the symbol "IS" when expecting one of the following: := . ( @ % ; not null range default character

1 Answers1