title: oracle pl: ref parameter in procedure how to
body: <p>I would like to check for each insert on a table that a specific value is unique.</p>
  
  <p>employee_table is like:</p>
  
  <pre><code>employee_table(
    position varchar2,
    branch_ref ref branch scope is branch_table
  );
  </code></pre>
  
  <p>There is only one "Head" position <strong>per branch</strong>. So there can be several 'Head' values in the column, that's why I can't use a unique constraint.</p>
  
  <p>I cannot use triggers because of the 'table in mutation' error.</p>
  
  <p>I decided to use a procedure to do my insert and validate the input.</p>
  
  <p>However, one of the parameters is a ref to branch_table.</p>
  
  <p>How can I pass a ref type parameter to a procedure ? I tried a lof stuffs that didn't work.</p>
  
  <p>for example:</p>
  
  <pre><code>SELECT ref(b)
  INTO br_ref
  FROM branch_table
  WHERE b.bid = 1;
  /
  EXECUTE proc_name('Head', br_ref);
  </code></pre>
  
  <p>Thank you</p>