Forum Stats

  • 3,741,459 Users
  • 2,248,431 Discussions
  • 7,861,818 Comments

Discussions

Handling exceptions. ORA-06550.

2652078
2652078 Member Posts: 9
edited Jun 12, 2014 3:13PM in SQL & PL/SQL

Hi folks

I'm using 11g Express Edition 11.2.0.2.0.

The code below runs fine. It takes a parameter (student_id in this instance), and returns some test data including 'pass' or 'fail' etc.

At the moment i'm struggling with creating some exceptions that will handle things such as the parameter not being an integer, the parameter being null etc.

Replicating these errors returns the error code ORA-06550, which i understand from researching this forum, cant be handled as its a compilation time error and not an execution time error.

Is it possible to alter my code somehow, so that i can still get the same outcome, but also write some exceptions based on the parameter input.

Any help is much appreciated.

Thanks

Ben

SET SERVEROUTPUT ON
DECLARE 
  CURSOR c_pass_fail_cursor 
    (p_studentid number) IS
    SELECT STUDENTS.FIRSTNAME,
      TEST_HISTORY.SCORE,
      TEST_ID.TEST_NAME,
      TEST_ID.PASSING_GRADE
    FROM STUDENTS
    INNER JOIN TEST_HISTORY
      ON STUDENTS.STUDENT_ID = TEST_HISTORY.STUDENT_ID
    INNER JOIN TEST_ID
      ON TEST_ID.TEST_ID = TEST_HISTORY.TEST_ID
    WHERE students.STUDENT_ID = p_studentid;
    
    v_name students.firstname%type;
    v_score test_history.score%type;
    v_test test_id.test_name%type;
    v_passing test_id.passing_grade%type;
    v_result varchar2(4);
    
    
BEGIN
  OPEN c_pass_fail_cursor (3);
  LOOP
    FETCH c_pass_fail_cursor INTO v_name, v_score, v_test, v_passing;
  EXIT WHEN c_pass_fail_cursor%NOTFOUND;
  If v_score < v_passing THEN
    v_result := 'Fail';
    DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
  ELSE
    v_result := 'Pass';
    DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
  END IF;
  
  END LOOP;
  
  CLOSE c_pass_fail_cursor;
  
END;
/
Tagged:

Answers

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown

    You could change your parameter into a varchar, and then translate it using cast.

    This way as long as they do input something extremely exotic you could then trap the conversion error as most things can be varchar.


  • 2652078
    2652078 Member Posts: 9

    So when I declare the cursor, I make the parameter p_student varchar instead.

    How would I then use the case statement. Sorry its a bit beyond me!

    Thanks for your help

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown

    Make your p_student varchar2

    Also declare

    v_student number;

    Then change all of your instances after the parameters from p_student to v_student.

    And as a first action; -

    v_student:= p_student;

    Which implicitly changes your varchar2 to number, if this fails it will error at that point and you can explicitly capture the errors that occur.


  • Brian Bontrager
    Brian Bontrager Member Posts: 767
    edited May 28, 2014 2:10PM
    You could change your parameter into a varchar, and then translate it using cast.
    
    

    That introduces more work, and more potential bugs. If it is expected to be a number, defining the parameter as NUMBER is the right decision.

    For the OP, Are you really running this as an anonymous PL/SQL block, or are you creating a stored procedure?  If your stored procedure is called with the wrong data type (passing a character to a number parameter), then the call to that procedure will fail with ORA-06502 PL/SQL numeric or value error, which raises the VALUE_ERROR exception.  Whatever calls the procedure can trap that.

    ORA-06550 is a symptom of some other issue.  There is no need to handle it directly.

    I'm not sure why you started a new thread, since this seems to be related to your other question?

    Message was edited by: Brian Bontrager

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown

    I agree on it being sub-optimal, but I was addressing the requirement as it was defined, how to have it such that when the wrong kind of parameter was passed in the error trapping WITHIN THE CODE trapped the error.

  • 2652078
    2652078 Member Posts: 9

    Sorry for the delay in my response.

    Brian

    Thanks for the response, but i'll have to do some research to understand what your asking.

    I think the problem might be, not only do i have to figure out how to write the code for the exceptions, but how to manipulate the current code to produce the errors in the first place.

    Back to the drawing board.

    Thanks for your help

    Ben

This discussion has been closed.