I am recieving the error: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: in out table ... columns long double ref char time timestamp interval date binary national character nchar The symbol " was inserted before "EXCEPTION" to continue. Errors: check compiler log

CREATE OR REPLACE PROCEDURE register_new_customer(
    invalid_value EXCEPTION,
    user_id CUSTOMER.CUST_ID%TYPE,
    user_forename CUSTOMER.CUST_FORENAME%TYPE,
    user_surname CUSTOMER.CUST_SURNAME%TYPE,
    user_housenumber CUSTOMER.CUST_HOUSENUMBER%TYPE,
    user_street CUSTOMER.CUST_STREET%TYPE,
    user_town CUSTOMER.CUST_TOWN%TYPE,
    user_postcode CUSTOMER.CUST_POSTCODE%TYPE,
    user_phone CUSTOMER.CUST_PHONE%TYPE,
    user_dob CUSTOMER.CUST_DOB%TYPE
    
)


AS
BEGIN
    IF user_forename = NULL THEN
    RAISE invalid_value;
    END IF;
    INSERT INTO customer    (cust_id,cust_forename,cust_surname,cust_housenumber,cust_street,cust_town,cust_postcode,cust_phone,cust_dob)
    VALUES (user_id,user_forename,user_surname,user_housenumber,user_street,user_town,user_postcode,user_phone,user_dob);
    

EXCEPTION
    WHEN invalid_value THEN
    DBMS_OUTPUT.PUT_LINE('You have entered a NULL value for something that cant be NULL');

END;
/

The above code is what I am running and if the invalid_value EXCEPTION is removed it works fine, but I am trying to add some basic Exception handeling to the procedure and I am not really getting anywhere. Apologies if this has been asked before as I am still very new to all of this. Thanks in advance

CodePudding user response：

You can't pass an exception as an argument; you need to declare it as part of the procedure itself, not as an argument:

CREATE OR REPLACE PROCEDURE register_new_customer(
    user_id CUSTOMER.CUST_ID%TYPE,
    user_forename CUSTOMER.CUST_FORENAME%TYPE,
    user_surname CUSTOMER.CUST_SURNAME%TYPE,
    user_housenumber CUSTOMER.CUST_HOUSENUMBER%TYPE,
    user_street CUSTOMER.CUST_STREET%TYPE,
    user_town CUSTOMER.CUST_TOWN%TYPE,
    user_postcode CUSTOMER.CUST_POSTCODE%TYPE,
    user_phone CUSTOMER.CUST_PHONE%TYPE,
    user_dob CUSTOMER.CUST_DOB%TYPE    
)
AS
    invalid_value EXCEPTION;
BEGIN
...

(And as @Gary_W said, change = NULL to IS NULL.)

fiddle with simplified table.