plsql for inserting when exception is encountered

Question

Write a PL/SQL program(anonymous block) to insert a new department record In dept table with the following values. (NULL,'LOGISTICS','SINGAPORE'). Check whether you are able to successfully insert a row with these values. If not successful, handle the error which is restricting the row from being inserted (use non predefined exception handling). The exception handling part should find the maximum deptno from the table, increment it by 1 and use it to insert a new record. I don't know how will i insert the new record with max department number +1 along with 'LOGISTICS','SINGAPORE'. the value should be (max(deptno)+1,'LOGISTICS','SINGAPORE').

my code.

declare
e_insert_excep exception;
pragma exception_init(e_insert_excep,-01400);
begin
insert into dept values(NULL,'LOGISTICS','SINGAPORE');
exception
when e_insert_excep then insert into dept(deptno) select max(deptno)+1 from dept;
dbms_output.put_line(sqlerrm);
end;

That is an assignment. You wrote code and you posted it without further explanation. Did you run your code ? What did you get ? Did you get an error message ? The answers below interpreted your post but you did not ask an actual question. — Koen Lostrie, Jul 25 '20 at 11:34

score 0 · Answer 1 · answered Jul 25 '20 at 08:01

You can INSERT ... SELECT ... more than one column, if this is what you ask for.

...
INSERT INTO dept
            (deptno,
             <the other targeted columns>)
            SELECT max(deptno) + 1,
                   'LOGISTICS',
                   'SINGAPORE'
                   FROM dept;
...

But it would be best to define the table with an automatically incrementing ID. Have a look at this question to learn how to achieve this.

score 0 · Accepted Answer · answered Jul 25 '20 at 09:30

Using max(field)+1 to generate unique values is a very bad idea. You should use a sequence for that. If you strictly need a value without gaps, you should create a table with the values and recover them from it.

What you want to do might be done easily using SQL, as other answers have already showed. Anyway, if you want to do it on PL/SQL and applying your logic stated on the question:

1.The error code for pragma is -1400 ( without the leading 0 ) 2.You need to do something after the exception, either raise the error or not. 3.You need to recover the max+1 after the pragma exception to insert what you want

declare
e_insert_excep exception;
pragma exception_init(e_insert_excep,-1400);
v_depto dept.deptno%type;
begin
    insert into dept values(NULL,'LOGISTICS','SINGAPORE');
    commit;
    exception
    when e_insert_excep then 
       select max(deptno)+1 into v_depto from dept;
       -- again the insert
       insert into dept values(v_depto,'LOGISTICS','SINGAPORE');   
       commit;
       null; -- I don't want to raise an error, therefore I set null;
    when others then 
      dbms_output.put_line(sqlerrm);
      rollback; -- In case you want to rollback any dml done ( not necessary in your case, though )
      raise; -- I raised because of other error
end;

Example

SQL> create table x ( deptno number not null , c1 varchar2(20) , c2 varchar2(20) ) ;

Table created.

SQL> insert into x values ( 1 , 'XX' , 'YY' ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> declare
n_null exception;
pragma exception_init(n_null,-1400);
v_depto x.deptno%type;
begin
    insert into x values(NULL,'LOGISTICS','SINGAPORE');
    commit;
    exception
    when n_null then 
       -- select max value
       select max(deptno)+1 into v_depto from x;
       insert into x values(v_depto,'LOGISTICS','SINGAPORE');   
       commit;
       null; -- if I don't want an error, I set null;
    when others then 
      -- dbms_output.put_line(sqlerrm);
      rollback; -- In case you want to rollback any dml done ( not necessary in your case, though )
      raise; -- I raised because of other error
end; 
/

PL/SQL procedure successfully completed.

SQL> select * from x ;

    DEPTNO C1                   C2
---------- -------------------- --------------------
         1 XX                   YY
         2 LOGISTICS            SINGAPORE

SQL>

plsql for inserting when exception is encountered

2 Answers2