title: Automatically truncate source strings exceeding destination string size
body: <p>I am writing a procedure that translates data from a vendor's tables into our local tables, and there are many assignments that map from a cursor on the source table into a <code>ROWTYPE</code> of the destination table. All of the vendor's string columns are defined with a size of 255, but our string columns are defined more stringently, usually only 30 or 50 characters. Certain of these mappings have thrown <code>VALUE_ERROR</code> exceptions when the source table's string is too long for the destination rowtype's column to accept.</p>
  
  <p>The intended result is for the destination <code>ROWTYPE</code>'s column to contain as much as it can hold, and for the rest to be truncated. My first instinct was simply to use <code>SUBSTR</code> and hard-code the maximum size, but there must be a more elegant - and robust - solution.</p>
  
  <p>One solution I've come up with (although it doesn't give me warm fuzzy feelings) is the below procedure, which simply handles the <code>VALUE_ERROR</code> exceptions and retries the mapping.</p>
  
  <pre><code>PROCEDURE p_safe_mapper(p_dest IN OUT VARCHAR2, p_out IN VARCHAR2) IS
  counter INTEGER := 0;
    BEGIN
    --only loop for as many characters are in the source string
    WHILE counter &lt; length(p_src) LOOP
      BEGIN
        --attempt to map, trimming COUNTER characters from the end of the source string. If it works, we're done.
        p_dest := substr(p_src,
                         0,
                         length(p_src) - counter);
        EXIT;
      EXCEPTION
        --else if we get an error, increment the counter to try again with the source string one character shorter.
        WHEN value_error THEN
          counter := counter + 1;
      END;
    END LOOP;
  END;
  </code></pre>
  
  <p>I've also considered querying <code>ALL_TAB_COLUMNS</code> for the table column's size, but I don't know any way to get there from having a <code>ROWTYPE</code>.</p>
  
  <p>Basically, since Oracle obviously knows the max size of my variables - be they <code>ROWTYPEs</code>, cursors, or plain old <code>VARCHAR2s</code> - I'm hoping to obtain a generalized procedure/function that can safely assign between any of them, truncating excess characters.</p>