Is Oracle being selected in a variable when select returns nothing?

Question

declare fName varchar2(255 char); begin SELECT x.constraint_name into fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME'; if fName is not null THEN execute immediate 'alter table MY_TABLE_NAME drop constraint ' || fName; end if; SELECT x.constraint_name into fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = 'OTHER_MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='OTHER_MY_COLUMN_NAME'; if fName is not null THEN execute immediate 'alter table OTHER_MY_TABLE_NAME drop constraint ' || fName; end if; end; 

Hi @, I am using the above code to get the names of the constraints that I want to drop, and I have many such attachments, not if → drop. My problem is that if one of the selected items returns nothing, an exception is thrown. I could catch the exception, but at the end of the structure "begin end" (so after all select statements, so that the rest of the drops will never be executed). How can I arrange this, that if select return nothing, I just don't want to let go of anything :)

If there is another way to define a variable and populate it from select without throwing an exception, regardless of whether the returned name is null or not, I would prefer it :) (now I have this choice for work, except when select returns nothing :))

Answer 1

Use multiple begin / exception / end blocks:

 declare fName varchar2(255 char); begin begin SELECT x.constraint_name into fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME'; exception when no_data_found then fName := null; end; if fName is not null THEN execute immediate 'alter table MY_TABLE_NAME drop constraint ' || fName; end if; begin SELECT x.constraint_name into fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = 'OTHER_MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='OTHER_MY_COLUMN_NAME'; exception when no_data_found then fName := null; end; if fName is not null THEN execute immediate 'alter table OTHER_MY_TABLE_NAME drop constraint ' || fName; end if; end; 

Answer 2

Use the local procedure:

 declare procedure drop_constraint(i_table_name in varchar2, i_column_name in varchar2) is l_constr_name varchar2(255 char); begin SELECT x.constraint_name into fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = i_table_name AND x.constraint_type = 'R' AND c.column_name = i_column_name; execute immediate 'alter table ' || i_table_name || ' drop constraint ' || l_constr_name; exception when NO_DATA_FOUND then null; -- ignore or print message end drop_constraint; begin drop_constraint('MY_TABLE_NAME', 'MY_COLUMN_NAME'); drop_constraint('OTHER_TABLE_NAME', 'OTHER_COLUMN_NAME'); end; /