title: oracle select into variable when select return nothing?
body: <pre><code>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;
  </code></pre>
  
  <p>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 :)</p>
  
  <p>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 :))</p>