2

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.

2
  • Very interesting question! I tried the sys.anydata+sys.anytype combo, but with no luck so far. I tried putting the p_src to a ref cursor selecting from dual and describing the cursor columns via dbms_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. :-) Commented Jul 4, 2014 at 12:17
  • I had also been looking into anydata and anytype, 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. Commented Jul 4, 2014 at 16:10

1 Answer 1

1

I can see two ways to do it - either

A) Change p_safe_mapper to accept a third parameter defining the max allowable output string size

or

B) Change p_safe_mapper to accept the destination table and column names, and use those to find the max size in one of the *_TAB_COLUMNS views.

Share and enjoy.

Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the advice. Regarding option A, I definitely could do this with little trouble, but I'm trying to avoid having to hard-code the size. Regarding option B, is there a way to query the *_TAB_COLUMNS views for size data of ROWTYPEs, or cursors? Unfortunately, I'm not writing directly into the table, but into these other intermediate containers.
@TheDIMMReaper rowtype and cursor are PL/SQL constructs so I don't think you'll find any metadata for them. Instead you have to know the table and column.
@user272735 Thanks, that makes sense. Is there another way to arrive at similar data from a PL/SQL side, without necessarily knowing the table?
As far as I'm aware there's nothing in PL/SQL which will give you the size of a variable or column.
@BobJarvis - I've implemented and used the non-warm-and-fuzzy solution in my question in production long ago, but I've still been hoping to one day have a better method. Regardless, your previous comment seems to be the final answer, that there is no way to get what I was looking for. Cheers!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.