Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Error handling???

    Can someone tell me what im doing wrong with this error handling...can't have a negative number...the points cant be negative

    Error report:
    ORA-06550: line 19, column 7:
    PLS-00103: Encountered the symbol "(" when expecting one of the following:

    begin case declare exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge pipe
    The symbol "mod" was substituted for "(" to continue.
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    thats the error im getting at the moment its not letting me remove the points but without the error handling i can remove points but it goes minus


    drop table elf_objtab;
    drop type elf_rew_objtyp;
    drop sequence elf_seq;

    create sequence elf_seq
    start with 1
    increment by 1;
    -- specification

    create type elf_rew_objtyp as object (
    elfid number(4),
    elfname varchar2(30),
    age number(4),
    points number(8),
    member procedure addpoints(no_points number),
    member procedure removepoints(no_points number));
    /
    SHOW ERRORS

    -- table
    create table elf_objtab of elf_rew_objtyp (elfid PRIMARY KEY);
    SHOW ERRORS


    CREATE OR REPLACE PROCEDURE add_elf (
    i_elfid elf_objtab.elfid%TYPE,
    i_elfname elf_objtab.elfname%TYPE,
    i_age elf_objtab.age%TYPE,
    i_points elf_objtab.points%TYPE) IS
    BEGIN
    INSERT INTO elf_objtab
    VALUES (elf_seq.nextval, i_elfname, i_age, i_points);
    COMMIT;
    END add_elf;
    /
    SHOW ERRORS
    -- body

    CREATE OR REPLACE TYPE BODY elf_rew_objtyp AS
    MEMBER PROCEDURE Addpoints(no_points NUMBER) IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE(SELF.elfname || ' currently has ' || SELF.points || 'points.');
    SELF.points := SELF.points + no_points;
    UPDATE elf_objtab
    set points = self.points
    where elfname = self.elfname;
    COMMIT;
    END Addpoints;

    MEMBER PROCEDURE Removepoints(no_points NUMBER) IS
    p_name VARCHAR2(30);
    BEGIN
    DBMS_OUTPUT.PUT_LINE(SELF.elfname || ' currently has ' || SELF.points || 'points.');
    SELF.points := SELF.points - no_points;
    UPDATE elf_objtab
    set points = self.points
    where elfname = self.elfname;
    COMMIT;
    END Removepoints;
    END;
    /


    -- part e
    EXECUTE add_elf ('', 'Sam', '78', '130')
    EXECUTE add_elf ('', 'Mancy', '34', '5')
    EXECUTE add_elf ('', 'Eva', '122', '70')
    EXECUTE add_elf ('', 'Legra', '56', '55')
    EXECUTE add_elf ('', 'Tumble', '155', '20')
    EXECUTE add_elf ('', 'Rolly', '123', '250')

    SET SERVEROUTPUT ON
    DECLARE
    elfman elf_rew_objtyp;
    new_points elf_objtab.points%TYPE;
    elfy_name elf_objtab.elfname%TYPE := &enter_elfy_name;
    elfy_points elf_objtab.points%TYPE := &enter_points;
    BEGIN
    SELECT VALUE(P) into elfman
    FROM elf_objtab p
    WHERE elfname = elfy_name;
    elfman.Addpoints(elfy_points);
    commit;
    DBMS_OUTPUT.PUT_LINE('The updated number of points for '|| elfman.elfname || ' is '|| elfman.points);
    END;
    /

    SET SERVEROUTPUT ON
    DECLARE
    elfman elf_rew_objtyp;
    new_points elf_objtab.points%TYPE;
    elfy_name elf_objtab.elfname%TYPE := &enter_elfy_name;
    elfy_points elf_objtab.points%TYPE := &enter_points;
    negative_points EXCEPTION;
    points NUMBER (8);

    BEGIN
    SELECT VALUE(P) into elfman
    FROM elf_objtab p
    WHERE elfname = elfy_name;
    elfman.Removepoints(elfy_points);
    commit;
    IF points < 0 THEN
    RAISE negative_points;
    END IF;
    EXCEPTION
    WHEN negative_points THEN
    ('errorinpoints');

    DBMS_OUTPUT.PUT_LINE('The updated number of points for '|| elfman.elfname || ' is '|| elfman.points);
    END;
    /

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    What ('errorinpoints') does mean?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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