Example for calling procedure

Give an example for calling procedure with user and system exception

Questions by ravenkumar   answers by ravenkumar

Showing Answers 1 - 12 of 12 Answers

/* simple example for calling procedure from a another procedure with system and user define exception */
set serveroutput on

create or replace procedure proc1 is -- calling procedure from emp_id.

  v_time timestamp; -- time stamp for calling procedure.
begin
 select systimestamp -- selecting time stamp from dual table.
 into v_time from dual;
 dbms_output.put_line(v_time);

  update emp -- update employees.
  set salary = 25000
  where employee_id = 100;
end proc1;
/

create or replace procedure emp_id -- mail procedure.
    
   (emp in  emp.employee_id%type,
    sal out emp.salary%type) is 
    excep exception;
    excep1 exception;
    v_time timestamp; -- time stamp for mail procedure.

     begin
     
      select systimestamp -- selecting time stamp from dual table.
      into v_time from dual;
      dbms_output.put_line(v_time);

      select salary -- fetch the salary for analyze.     
      into   sal
      from emp
      where employee_id = emp;

      /* check conditions */
      
      if sal = 24000     then proc1;
       elsif sal >= 25000 then raise excep;
       else raise excep1; -- raise exception.
      end if;
         
      exception
              when no_data_found -- system exception.
                                 then dbms_output.put_line('no record found');
              when excep         -- user define exception.
                                 then dbms_output.put_line('already have the salary');
              when excep1        -- user define exception.
                                 then dbms_output.put_line('not in employee_id in proc1');
     end emp_id;             
/
show errors;
/
variable sa number
execute emp_id (100, :sa)

show errors;

other wise you can use error stack method. here is the example.
create or replace procedure handle_errors
  (object_name in varchar2,
   module_name in varchar2:= null,
   table_name  in varchar2:= null,
   sql_error_code in varchar2:= null,
   sql_error_message in varchar2:= null,
   user_error_message in varchar2:=null) is
   raised_error exception;
   type error_stack is table of varchar2(80);
   errors error_stack := error_stack();
  
   function object_type(object_name_in in varchar2)

     return varchar2 is
     return_type varchar2(12) := 'PROCEDURE';

   begin

     for i in (select object_type
     from user_objects
     where object_name = object_name_in) loop
     return_type := i.object_type;
     end loop;
     return return_type;
  end object_type;

begin
 
  errors.extend;
  errors(errors.count) := object_type(object_name)||' ['||object_name||']';

  if module_name is not null then
     errors.extend;
     errors(errors.count) := 'MODULE NAME : ['||module_name||']';
  end if;
 
  if table_name is not null then
     errors.extend;
     errors(errors.count) := 'TABLE NAME  : ['||table_name||']';
  end if;
 
  if sql_error_code is not null then
     errors.extend;
     errors(errors.count) := 'SQL CODE    : ['||sql_error_code||']';
  end if;
 
  if sql_error_message is not null then
     errors.extend;
     errors(errors.count) := 'SQL MESSAGE : ['||sql_error_message||']';
  end if;
 
  if user_error_message is not null then
     errors.extend;
     errors(errors.count) := 'uer message : ['||user_error_message||']';
  end if;
 
  errors.extend;
  errors(errors.count) := '-----------------------------------------';
  raise raised_error;
  exception when raised_error then
  for i in 1..errors.count loop
  dbms_output.put_line(errors(i));
  end loop;
  return;

end;
 /
 show errors
 for procedure handle_errors;
----------------------------------------------------------------------------------------------------
-- first you should compile the handle_errors stack.
set serveroutput on;
create or replace procedure proc1
 (emp_id  in  emp.employee_id%type,
  emp_na  out emp.last_name%type,
  emp_sal out emp.salary%type,
  emp_job out emp.job_id%type) is
 
  excep  exception;
  excep1 exception;
  excep2 exception;
  excep3 exception;
  excep4 exception;
 
  local_object       varchar2(30) := 'proc1';
  local_module       varchar2(30) := 'main';
  local_table        varchar2(30) :=  null;
  local_user_message varchar2(80) :=  null;
 
begin
    select last_name, salary, job_id
    into emp_na, emp_sal, emp_job
    from emp
    where employee_id = emp_id;
 
 if       emp_sal = 25000 and emp_job = 'AD_PRES'  then raise excep;
    elsif emp_sal > 25000 and emp_job = 'AD_PRES'  then raise excep1;
    elsif emp_sal < 25000 and emp_job = 'AD_PRES'  then raise excep2;
    elsif emp_sal = 3100  and emp_job = 'PU_CLERK' then raise excep3;
    elsif emp_sal > 3100  and emp_job = 'PU_CLERK' then raise excep4;
 end if;
   
exception
          when excep  then
               dbms_output.put_line('AD_PRES already have the salary');
          when excep1 then
               dbms_output.put_line('AD_PRES greater than the update salary');
          when excep2 then
               update emp
               set salary = 26000
               where employee_id = emp_id;
          when excep3  then
               update emp
               set salary = 4000
               where employee_id = emp_id;
          when excep4 then
               dbms_output.put_line('PU_CLERK greater than the update salary');
          when others then
               handle_errors(object_name       => local_object,
                             module_name       => local_module,
                             sql_error_code    => sqlcode,
                             sql_error_message => sqlerrm);


   
end proc1;
/
variable na varchar2(30)
variable sa number
variable jo varchar2(10)
execute proc1(15, :na, :sa, :jo)


  Was this answer useful?  Yes

set serveroutput on;
create or replace procedure proc1
(emp_id in emp.employee_id%type,
 emp_na out emp.last_name%type,
 emp_sal out emp.salary%type,
 emp_job out emp.job_id%type) is
 
 excep  exception;
 excep1 exception;
 excep2 exception;
 excep3 exception;
 excep4 exception;
 
begin
    select last_name, salary, job_id
    into emp_na, emp_sal, emp_job
    from emp
    where employee_id = emp_id;
 
 if       emp_sal = 25000 and emp_job = 'AD_PRES'  then raise excep;
    elsif emp_sal > 25000 and emp_job = 'AD_PRES'  then raise excep1;
    elsif emp_sal < 25000 and emp_job = 'AD_PRES'  then raise excep2;
    elsif emp_sal = 3100  and emp_job = 'PU_CLERK' then raise excep3;
    elsif emp_sal > 3100  and emp_job = 'PU_CLERK' then raise excep4;
 end if;
   
exception
          when excep  then
               dbms_output.put_line('AD_PRES already have the salary');
          when excep1 then
               dbms_output.put_line('AD_PRES greater than the update salary');
          when excep2 then
               update emp
               set salary = 26000
               where employee_id = emp_id;
          when excep3  then
               update emp
               set salary = 4000
               where employee_id = emp_id;
          when excep4 then
               dbms_output.put_line('PU_CLERK greater than the update salary');
          when no_data_found then 
               dbms_output.put_line('NO RECORD FOUND');
end proc1;
/

variable na varchar2(30)
variable sa number
variable jo varchar2(10)
execute proc1(15, :na, :sa, :jo)


  Was this answer useful?  Yes

Simple example for predefined exception

CREATE OR REPLACE PROCEDURE TEST_PROC AS
A NUMBER ;
BEGIN A := 'TEST';
DBMS_OUTPUT.PUT_LINE('THE VALUE OF A = '||A);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('The value of A should be numeric');
 END;

Example for user defined exception
CREATE OR REPLACE PROCEDURE TEST_PROC (V_SAL NUMBER, V_EMPNO NUMBER) AS
SAL_CHECK EXCEPTION;
BEGIN
IF V_SAL >0 THEN
UPDATE EMP
SET    SAL = V_SAL *1.10;
ELSE
RAISE SAL_CHECK;
END IF;
EXCEPTION
WHEN SAL_CHECK  THEN
DBMS_OUTPUT.PUT_LINE('SALARY SHOULD BE MORE THAN ZERO');
END;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions