CODEWITHSUNDEEP
oracle-databaseplsqlexception
user10966491
user10966491

Reputation: 5

PLSQL - exception in a procedure

Use exception in a procedure.

Procedure for insert into statement.

CREATE OR REPLACE PROCEDURE pSaveProductGroup (p_parentCode VARCHAR2, p_nameGroup VARCHAR2) IS

v_var "ProductGroups"."code"%type;
BEGIN

select p."code" into v_var
from "ProductGroups" p
where p."code"=p_parentCode;

if v_var is not null then

INSERT INTO "ProductGroups"
("parentCode","nameGroup")
VALUES(p_parentCode,p_nameGroup);
end if;

EXCEPTION
WHEN v_var is null then
dbms_output.put_line('Undefined group.');

END;

It should print exception when needed.

Error: Error(18,12): PLS-00103: Encountered the symbol "IS" when expecting one of the following:     . then or

Upvotes: 0

Views: 515

Answers (1)

Jacob
Jacob

Reputation: 14741

You can do something like this to catch the exception.

CREATE OR REPLACE PROCEDURE pSaveProductGroup (p_parentCode    VARCHAR2,
                                               p_nameGroup     VARCHAR2)
IS
   v_var   "ProductGroups"."code"%TYPE;
   myex    EXCEPTION;
   PRAGMA EXCEPTION_INIT (myex, -20016);
BEGIN
   SELECT p."code"
     INTO v_var
     FROM "ProductGroups" p
    WHERE p."code" = p_parentCode;

   IF v_var IS NOT NULL
   THEN
      INSERT INTO "ProductGroups" ("parentCode", "nameGroup")
           VALUES (p_parentCode, p_nameGroup);
   ELSE
      IF (v_var IS NULL)
      THEN
         RAISE myex;
      END IF;
   END IF;
EXCEPTION
   WHEN myex
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: "Undefined group --> ' || DBMS_UTILITY.format_error_stack);
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: NO_DATA_FOUND --> ' || DBMS_UTILITY.format_error_stack);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: OTHERS --> ' || DBMS_UTILITY.format_error_stack);
END;

The modified version of the procedure

CREATE OR REPLACE PROCEDURE pSaveProductGroup (p_parentCode    VARCHAR2,
                                               p_nameGroup     VARCHAR2)
IS
   v_var   "ProductGroups"."code"%TYPE;
   myex    EXCEPTION;
   PRAGMA EXCEPTION_INIT (myex, -20016);
BEGIN
   SELECT p."code"
     INTO v_var
     FROM "ProductGroups" p
    WHERE p."code" = p_parentCode;

   IF v_var IS NOT NULL
   THEN
      INSERT INTO "ProductGroups" ("parentCode", "nameGroup")
           VALUES (p_parentCode, p_nameGroup);
   ELSE
      RAISE myex;
   END IF;
EXCEPTION
   WHEN myex
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: Undefined group --> ' || DBMS_UTILITY.format_error_stack);
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: NO_DATA_FOUND --> ' || DBMS_UTILITY.format_error_stack);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'ERROR_STACK: OTHERS --> ' || DBMS_UTILITY.format_error_stack);
END;

Upvotes: 2

Related Questions