Exception handling problem??

Discussion in 'Databases' started by nvidia, Jan 16, 2007.

  1. #1
    Hi, i have a database whereby people can look up customer records based on their order numbers. If the order number does not exist, an error should appear.

    
    SET SERVEROUTPUT ON;
    DECLARE
            v_ordNum NUMBER(8) := &sv_odNum;
            v_fname VARCHAR2 (30);
            v_lname VARCHAR2 (30);
            v_add   VARCHAR2 (30);
            v_num   VARCHAR2 (10);
    BEGIN
            SELECT first, last, cadd, dphone
            INTO   v_fname, v_lname, v_add, v_num
            FROM CUSTOMER c, ORDERS o
            WHERE c.custid = o.custid
            AND  v_ordNum = o.orderid;
    
            DBMS_OUTPUT.PUT_LINE(' The name, address and telephone number of the customer follow: '
            ||v_fname|| ' ' ||v_lname ||' ' ||v_add||' ' ||v_num);
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE(v_ordNum  'was not found. Please try again');
    END;
    /
    
    Code (markup):
    When i enter a order number that does not exist i get this instead, y:
    Error
    
    SQL> @q1.sql
    Enter value for sv_odnum: 2331
    old   2:        v_ordNum NUMBER(8) := &sv_odNum;
    new   2:        v_ordNum NUMBER(8) := 2331;
            DBMS_OUTPUT.PUT_LINE(v_ordNum  'was not found. Please try again');
                                           *
    ERROR at line 18:
    ORA-06550: line 18, column 33:
    PLS-00103: Encountered the symbol "was not found. Please try again" when
    expecting one of the following:
    . ( ) , * @ % & | = - + < / > at in mod not range rem => ..
    <an exponent (**)> <> or != or ~= >= <= <> and or like as
    between from using is null is not || is dangling
    The symbol "." was substituted for "was not found. Please try again" to
    continue.
    
    Code (markup):
    Can som1 tell me y please??
     
    nvidia, Jan 16, 2007 IP
  2. nvidia

    nvidia Peon

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    problem solved
     
    nvidia, Jan 16, 2007 IP