PLSQL - exception in a procedure

Question

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

Jacob · Accepted Answer · 2019-01-30 08:49:32Z

2

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;

edited Jan 30, 2019 at 8:49

answered Jan 30, 2019 at 8:24

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user10966491 Over a year ago

When I call this procedure: set serveroutput on begin pSaveProductGroup('G5','Group 13'); end; it doesn't print exception. It prints 01403. 00000 - "no data found" *Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch.

Jacob Over a year ago

@user10966491 I have updated my answer, re-execute the procedure to find out the error details. I doubt your select statement does not return any rows.

user10966491 Over a year ago

But I want to print "Undefined group" when exception is raised. How to do that?

Jacob Over a year ago

@user10966491 IF (v_var IS NULL) This statement should execute to RAISE Undefined group exception. Correct me if I am wrong. The point is there are other errors in the procedure which is preventing the execution to catch your Undefined group. First you need to find out which error line is having NO_DATA_FOUND

Jacob Over a year ago

@user10966491 DBMS_UTILITY.format_error_stack will provide more insight about the error.

Collectives™ on Stack Overflow

PLSQL - exception in a procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related