PL/SQL varchar(10) to varchar(9)

Question

How would I write a loop that has a select in the “in” clause of the loop that selects a col of type varchar(10), but then inserts those values in a col that wants them to be varchar(9)? Basically I’m trying to “typecast” from one precision to another, if that makes any sense. Example:

FOR V_TEN IN (SELECT THIS_IS_VARCHAR_TEN FROM TABLE WHERE SOMETHING=’VALUE’)
LOOP
                INSERT INTO OTHER_TABLE
                (THIS_IS_VARCHAR_NINE)
                VALUES
                (V_TEN);
END LOOP;

The error is that the column types aren’t the same. I’ve tried looking at to_char() and cast() but neither seem to be what I want. I realize there is a loss of precision here and am okay with that, since I actually know that the values in the varchar(10) column are always going to be 9 chars.

Answer 1

Use:

FOR V_TEN IN (SELECT SUBSTR(t.this_is_varchar_ten, 1, 9)
                FROM TABLE t
               WHERE t.something = 'VALUE')
LOOP

  INSERT INTO OTHER_TABLE
    (THIS_IS_VARCHAR_NINE)
  VALUES
    (V_TEN);

END LOOP;

Use the SUBSTR function to substring the VARCHAR(10) data so it is returned as VARCHAR(9)

Answer 2

You are looking for the SUBSTR function.

Also, do not use PL/SQL for this, plain SQL will do and be faster.

 INSERT INTO OTHER_TABLE
   SELECT OTHER_COLUMN, SUBSTR(THIS_IS_VARCHAR_TEN,1,9) 
   FROM TABLE WHERE SOMETHING=’VALUE’;

And if there are really no values longer than nine character, you do not even need to call the substr function (it will be converted automatically, and raise an error if too long).

Answer 3

since I actually know that the values in the varchar(10) column are always going to be 9 chars.

If that's true, then you don't even need to use SUBSTR as others have been suggesting.

I believe the reason that you're getting an error is that you are trying to insert the value of V_TEN. When you use a construct like FOR x IN (SELECT ...) LOOP, x is implicitly declared as a record type. In your case, it's a record with only one field, but you still can't use it directly as a scalar type.

You just need to reference the field of the record by name in your insert.

FOR V_TEN IN (SELECT THIS_IS_VARCHAR_TEN FROM TABLE WHERE SOMETHING=’VALUE’)
LOOP
                INSERT INTO OTHER_TABLE
                (THIS_IS_VARCHAR_NINE)
                VALUES
                (V_TEN.THIS_IS_VARCHAR_TEN);
END LOOP;

In any case, as Thilo pointed out, there's no reason to do this in an explicit loop at all. Just write it as a single INSERT ... SELECT.