oracle select into variable when select return 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 code above to get the names of the constraints that I want to drop and I have a lot of such select into and than if -> drop statements. My problem is, that if one of the selects returns nothing an exception is thrown. I could catch the exception but at the end of the "begin end" structure (so after all the select statements, so the rest of the drops will never be executed). How can I arrange this, that if the select return nothing I just don't want to drop anything :)

If there is another way to define a variable and fill it from select without throwing an exception, no matter if the name returned is null or not, I would prefer it :) (for now I just have this select into working, except for the case that the select return 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 a 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;
/