Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    42

    Unanswered: Error handling in SQL

    Hi

    Id like to error handle. I get this error code ORA-02291. Its an intentional error i have set up at the moment. How can i turn that into a more understandable message. At the moment im working on the command prompt, do I need a trigger to achieve this. Thanks for your help.

    Jon

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    You can achieve that by putting the SQL code inside of a PL/SQL block and then utilizing the exceptions section of the PL/SQL block.

  3. #3
    Join Date
    Mar 2004
    Posts
    42
    Can that be done via the SQL command line. The error is a constraint error that i just want to handle with a mpre user friendly message.

    I couldn't put the SQL code in a pl/sql block as the sql code is constantly being updated.

    Jon

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't need a trigger, but you can only modify the error message in your application by trapping it and inspecting it, like this:
    Code:
    PROCEDURE insert_emp( ... )
    IS
      e_parent_not_found EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_parent_not_found,-2291);
    BEGIN
      INSERT INTO emp (empno, ename, deptno, ...) VALUES (...);
    EXCEPTION
      WHEN e_parent_not_found THEN
        RAISE_APPLICATION_ERROR(-20000,'Your error message here');
    END;
    Since a table may have several foreign keys you may want to go further and see which one was violated, e.g.

    Code:
    ...
    EXCEPTION
      WHEN e_parent_not_found THEN
        l_errmsg := SQLERRM;
        IF l_errmsg LIKE '%EMP_DEPT_FK%' THEN
          RAISE_APPLICATION_ERROR(-20000,'Invalid department code');
        ELSIF ...
          ...
        END IF;

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jonrohan
    I couldn't put the SQL code in a pl/sql block as the sql code is constantly being updated.
    I don't follow that - in what way is the code being constantly updated?

  6. #6
    Join Date
    Mar 2004
    Posts
    42
    Basically i have a shift table and a roster table.

    A attempt to assign a memeber of staff to the roster table should be prevented if they aren't already assigned to the shift. These are the alterations to my tables to prevent this:


    select user from dual;

    DROP TABLE roster CASCADE CONSTRAINTS;
    CREATE TABLE ROSTER
    (
    shift varchar2(10) NOT NULL,
    shift_date DATE NOT NULL,
    staff_id varchar2(10) NOT NULL,
    Ward_name varchar2(10) NOT NULL,
    Centre_name varchar2(10) NOT NULL,
    CONSTRAINT ROSTER_PK
    PRIMARY KEY (staff_id, shift, shift_date, Ward_name, Centre_name)
    );

    DROP TABLE shift CASCADE CONSTRAINTS;
    CREATE TABLE SHIFT
    (
    shift varchar2(10) NOT NULL,
    shift_date DATE NOT NULL,
    staff_id varchar2(10) NOT NULL,
    CONSTRAINT SHIFT_PK
    PRIMARY KEY (shift,shift_date,staff_id)
    );



    ALTER TABLE ROSTER ADD (
    CONSTRAINT Roster_has
    FOREIGN KEY (shift,shift_date,staff_id)
    REFERENCES SHIFT(shift,shift_date,staff_id));

    If this error occurs the error message

    ORA-02291: integrity constraint (SYSTEM.ROSTER_HAS) violated - parent key not found

    Is displayed. Which is fine. What i want to do is make that error message more user friendly by making another message or making that message say " you need to assign the staff to the shift etc...."

    Sorry i didn't explain it better in the firstplace.

    jon

    Edit i need something like this:

    On error message ORA-02291
    raise error message-20001("you need to assign the staff to the shift etc....");

    END;
    Last edited by jonrohan; 05-25-04 at 13:17.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, you could look at Database Event Triggers. I haven't tried them myself though. I'm not sure they give you what you need - can you find out the name of the constraint in these triggers?

    In my experience, users interact with the database via an application. They do not sit at the SQL Plus prompt and type in INSERT statements. The application may be a Forms screen or ASP web page or whatever, and stored procedures are often used for business logic. In the application (preferably in a stored procedure), you can trap the error message using SQLERRM as I showed earlier and parse it to see what it means and give a more user friendly message. It is quite common to build an error handling package to do this, with a lookup table of constraint names and friendly error messages, e.g.
    Code:
    select * from constraint_errors;
    
    constraint   message
    ----------   ----------
    EMP_DEPT_FK Invalid department code
    EMP_PK      Employee number already in use
    ...
    In your application, you would trap any exceptions and call the error handler like this:
    Code:
    EXCEPTION
      WHEN OTHERS THEN error_pkg.handle_error(SQLERRM, ...);
    END;
    The handle_error procedure would parse the SQLERRM text, see that it was a constraint violation, extract the constraint name, and look up the appropriate message from the table.

Posting Permissions

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