Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: VALUE_ERROR exception

    Can anyone tell me what i'm doing wrong here? It picks up the no_data_found exception, but it won't use the value_error.. it sort of acts like it's not there

    both the v_course_no, and v_new_cost are supposed to be number type and i'm trying to get the handler throw when somethign else other than a number is submitted!! please help, this my first semester taking PL/SQL

    DECLARE

    v_course_no course2.course_no%TYPE := '&sv_course_no';
    v_new_cost course2.cost%TYPE := '&sv_new_cost';
    v_description course2.description%TYPE;

    BEGIN

    SELECT description
    INTO v_description
    FROM course2
    WHERE v_course_no = course_no;

    BEGIN

    update_course_cost(v_course_no, v_new_cost);

    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Course '||v_course_no||' - '||v_description||' has been updated in the DB');
    DBMS_OUTPUT.PUT_LINE('New Cost: '||v_new_cost);


    EXCEPTION

    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Update Failed.');
    DBMS_OUTPUT.PUT_LINE('Your input for course_no was something other than a number.');
    DBMS_OUTPUT.PUT_LINE('Please enter a a valid number type XXX to update cost.');

    END;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Sorry, cannot update course '||v_course_no||' because it does not exist in the database.');


    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Update Failed.');
    DBMS_OUTPUT.PUT_LINE('Your input for course_no was something other than a number.');
    DBMS_OUTPUT.PUT_LINE('Please enter a a valid number type XXX to update cost.');

    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: VALUE_ERROR exception

    It is because the VALUE_ERROR exception is being raised in the DECLARE section when the value is assigned to the variable. Exceptions raised here are not caught by the EXCEPTION handler for that block. You would need to nest this block within another like this:
    PHP Code:
    BEGIN
        
    DECLARE

         
    v_course_no course2.course_no%TYPE := '&sv_course_no';
         
    v_new_cost course2.cost%TYPE := '&sv_new_cost';
         
    v_description course2.description%TYPE;

        
    BEGIN

          SELECT description
            INTO v_description
            FROM course2
           WHERE v_course_no 
    course_no;

            
    update_course_cost(v_course_nov_new_cost);

            
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
            
    DBMS_OUTPUT.PUT_LINE('Course '||v_course_no||' - '||v_description||' has been updated in the DB');
            
    DBMS_OUTPUT.PUT_LINE('New Cost: '||v_new_cost);

        
    EXCEPTION

          WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT
    .PUT_LINE('-----------------------------------------');
          
    DBMS_OUTPUT.PUT_LINE('Sorry, cannot update course '||v_course_no||' because it does not exist in the database.');

        
    END;
    EXCEPTION

      WHEN VALUE_ERROR THEN
      DBMS_OUTPUT
    .PUT_LINE('-----------------------------------------');
      
    DBMS_OUTPUT.PUT_LINE('Update Failed.');
      
    DBMS_OUTPUT.PUT_LINE('Your input for course_no was something other than a number.');
      
    DBMS_OUTPUT.PUT_LINE('Please enter a a valid number type XXX to update cost.');
      
    END;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •