Hello, I have the following select:
SELECT CONSTRAINT_NAME FROM user_cons_columns WHERE COLUMN_NAME = 'CANCELAMENTO' and table_name = 'NAVIO';
After getting the name it goes to another query but if it returns null I end up having an error and interrupt my script. How would it be a condition to verify whether it has a result or not? If something is returned, it will be executed:
alter table NAVIO DROP CONSTRAINT || constr_name;
If you are using PL-SQL in your script, you can solve it like this:
DECLARE
CURSOR C_CONSTRAINT IS
SELECT CONSTRAINT_NAME
FROM user_cons_columns
WHERE COLUMN_NAME = 'CANCELAMENTO'
AND table_name = 'NAVIO';
V_CONSTRAINT C_CONSTRAINT%ROWTYPE;
BEGIN
OPEN C_CONSTRAINT;
LOOP
FETCH C_CONSTRAINT INTO V_CONSTRAINT;
EXIT WHEN C_CONSTRAINT%NOTFOUND;
EXECUTE IMMEDIATE 'ALTER TABLE NAVIO DROP CONSTRAINT ' || V_CONSTRAINT.CONSTRAINT_NAME;
END LOOP;
END;