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 ROWTYPE 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 VALUE_ERROR exceptions when the source table's string is too long for the destination rowtype's column to accept.
The intended result is for the destination ROWTYPE's column to contain as much as it can hold, and for the rest to be truncated. My first instinct was simply to use SUBSTR and hard-code the maximum size, but there must be a more elegant - and robust - solution.
One solution I've come up with (although it doesn't give me warm fuzzy feelings) is the below procedure, which simply handles the VALUE_ERROR exceptions and retries the mapping.
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 < 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;
I've also considered querying ALL_TAB_COLUMNS for the table column's size, but I don't know any way to get there from having a ROWTYPE.
Basically, since Oracle obviously knows the max size of my variables - be they ROWTYPEs, cursors, or plain old VARCHAR2s - I'm hoping to obtain a generalized procedure/function that can safely assign between any of them, truncating excess characters.
sys.anydata+sys.anytypecombo, but with no luck so far. I tried putting thep_srcto a ref cursor selecting fromdualand describing the cursor columns viadbms_sql... with no luck, either. So far, the only "optimization" which comes to my mind would be that you do a binary search for the maximum string length instead of linear. :-)anydataandanytype, but I'm not familiar enough with them to have come up with anything yet. A few other people I work with had also suggested the binary search - if there isn't a better option, I will definitely implement this.