-3

Create a stored procedure called UPD_JOBSAL to update the minimum and maximum salaries for a specific job ID in the JOBS table. The procedure should provide three parameters: the job ID, a new minimum salary, and a new maximum salary.

Add exception handling to account for an invalid job ID in the JOBS table.

create or replace procedure sma212 (jobidnew varchar2, newmin number, newmax number)
   is
   e exception;
 begin
  update jobs
  set min_salary=newmin, max_salary=newmax
    where job_id=jobidnew;
    if job_id<>jobidnew THEN
   raise e;
 end if;
     exception 
 when e then
 dbms_output.put_line('invalid jobid');
 end;

Upon execution shows an error: job_id must be declared?

Screengrab 1

Screengrab 2

Any suggestions?

5
  • 1
    Where is there a loop?
    – Gandolf989
    Feb 3, 2016 at 14:47
  • Is this homework? That's allowed but please disclose it if it is
    – Tom V
    Feb 3, 2016 at 14:54
  • @TomV yeh its like practice ..do we require loop compulsory here ?
    – arya
    Feb 3, 2016 at 15:27
  • if job_id<>jobidnew THEN raise e; In what situation do you expect this to occur? Your UPDATE statement is neither returning rows, nor would it return a row that doesn't meet your WHERE criteria. Feb 3, 2016 at 15:37
  • What is the DDL for the table and what is the command string you are executing when you get the error?
    – Kevin Walz
    Feb 3, 2016 at 15:38

1 Answer 1

2

This line:

if job_id<>jobidnew THEN

is not part of the UPDATE which precedes it, so the identifier job_id does not refer to the column by that name but to a separate (undeclared) variable.

The way to test whether the UPDATE did anything or not is to test the implicit cursor attribute SQL%notfound, as in

if SQL%notfound THEN
  RAISE e;

The complete procedure definition should be

create or replace procedure sma212 (jobidnew varchar2, newmin number, newmax number)
  is
    e exception;
  begin
    update jobs
      set min_salary=newmin, max_salary=newmax
      where job_id=jobidnew;

    if SQL%notfound THEN
      raise e;
    end if;

    exception 
      when e then
        dbms_output.put_line('invalid jobid');
  end;
2
  • I missed sql%notfound
    – arya
    Feb 3, 2016 at 17:50
  • i am just going through tutorailpoint website about sql%notfound. thanksagain
    – arya
    Feb 4, 2016 at 14:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.