Oracle Cast using %TYPE attribute

Question

I want to cast a value using %TYPE attribute in my SQL statement. The %TYPE attribute lets you use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names.

This works:

insert into t1  select cast(v as varchar2(1)) from t2;

But I would like to

insert into t1  select cast(v as t1.v%TYPE) from t2;

Error starting at line 16 in command:
insert into t1  select cast(v as t1.v%TYPE) from t2
Error at Command Line:16 Column:37
Error report:
SQL Error: ORA-00911: Ongeldig teken.
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:

Can this (or something similar) be done?

EDIT: What I'm trying to achieve is: when t2.v is to large I want to truncate it. I'm trying to avoid using substr with a hard coded field length. So cast(v as t1.v%TYPE) instead of substr(v,1,1)

Answer 1

%TYPE is only available in PL/SQL, and can only be used in the declaration section of a block. So, you can't do what you're attempting.

You might think you could declare your own PL/SQL (sub)type and use that in the statement:

declare
    subtype my_type is t1.v%type;
begin
    insert into t1 select cast(v as my_type) from t2;
end;
/

... but that also won't work, because cast() is an SQL function not a PL/SQL one, and only recognises built-in and schema-level collection types; and you can't create an SQL type using the %TYPE either.

---

As a nasty hack, you could do something like:

insert into t1 select substr(v, 1,
    select data_length
    from user_tab_columns
    where table_name = 'T1'
    and column_name = 'V') from t2;

Which would be slightly more palatable if you could have that length stored in a variable - a substitution or bind variable in SQL*Plus, or a local variable in PL/SQL. For example, if it's a straight SQL update through SQL*Plus you could use a bind variable:

var t1_v_len number;
begin
    select data_length into :t1_v_len
    from user_tab_columns
    where table_name = 'T1' and column_name = 'V';
end;
/
insert into t1 select substr(v, 1, :t1_v_len) from t2;

Something similar could be done in other set-ups, it depends where the insert is being performed.