0

Create a procedure ADD_DEPT. This procedure will take 3 IN arguments DEPTNO,DNAME,LOC. Insert these values into the DEPT table. Handle the exception if the user tries to insert a duplicate row using User Defined Exception Handler.

Ishika
  • 1
  • Doesn't seem to be too difficult. What did you manage to do so far? Go step-by-step, i.e. first create an "empty" procedure (which doesn't do anything, just set list of arguments). Then add the INSERT part. Finally, add EXCEPTION. Test frequently. – Littlefoot Jun 29 '22 at 06:18
  • StackOverflow isn't a place to do your homework, so if you have questions you have to show you have worked, specifying where you are stuck. So, start again editing your question, how the table where you need to insert data looks like? What error are you getting in your create procedure code? Don't use screenshots, StackOverflow provides tools to format the code you paste into your question, that helps to make the questions searchable to new users with same questions as you. – Ana GH Jun 29 '22 at 08:54

1 Answers1

0

First and foremost Stack Overflow is not a place, where we solve your homework. We help you through your problems. Which is by you showing us your current progress, issue/current output and desired outcome/desired output.

But I'll sympathize with you, knowing I was there once too.

CREATE PROCEDURE ADD_DEPT (
  P_DEPTNO IN NUMBER,
  P_DNAME  IN VARCHAR2,
  P_LOC    IN VARCHAR2
) AS
BEGIN
    INSERT INTO DEPT(DEPTNO, DNAME, LOC)
    VALUES (P_DEPTNO, P_DNAME, P_LOC);
EXCEPTION WHEN dup_val_on_index THEN
    -- Handle your code here
END;

If you want to go the easy route next time, you can simply do that with SQL Developer: Browse your tables, right click, then click on Generate table API.

Then to handle the exception, right before the END; statement insert the following: EXCEPTION WHEN dup_val_on_index THEN --your code here

Tilen
  • 484
  • 1
  • 15