CODEWITHSUNDEEP
sqlstored-proceduresplsql
WT86
WT86

Reputation: 833

PL/SQL Procedure with NOT EXISTS Compilation Error

I'm trying to create a procedure that allows me to add a new record into the shipment_value table, but I don't want to add any record that already exists. So I'm using WHERE NOT EXISTS condition but I'm getting some compilation error.

CREATE OR REPLACE PROCEDURE Add_Shipment_Method(
shipment_method_id_p IN NUMBER,
shipment_description_p IN VARCHAR2)
AS
BEGIN
INSERT INTO shipment_method VALUES(shipment_method_id_p, shipment_description_p)
WHERE NOT EXISTS (SELECT * FROM SHIPMENT_METHOD WHERE SHIPMENT_METHOD_ID = shipment_method_id_p AND SHIPMENT_DESCRIPTION = shipment_description_p);
COMMIT;
END;

My procedure was working well before adding the "NOT EXISTS", but now I'm having compilation ERROR

Upvotes: 0

Views: 182

Answers (1)

1010
1010

Reputation: 1858

to use not exists you must be inserting the results of a query, not a list of values.

if your db is oracle you can chanage your insert like this

INSERT INTO shipment_method 
    SELECT shipment_method_id_p, shipment_description_p
    FROM dual
    WHERE NOT EXISTS (SELECT * FROM SHIPMENT_METHOD WHERE SHIPMENT_METHOD_ID = shipment_method_id_p AND SHIPMENT_DESCRIPTION = shipment_description_p);

or if you have a unique index you can just catch the exception.

Upvotes: 1

Related Questions