Oracle Cast using the% TYPE attribute

Question

I want to assign a value using the% TYPE attribute in my SQL statement. The% TYPE attribute allows you to use the data type of a field, record, nested table, database column, or variable in your own declarations, rather than hard-coded type names.

It works:

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

But I would like

 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: 

Could this (or something like that) do?

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

Answer 1

%TYPE is available only in PL / SQL and can only be used in the declaration section . Thus, you cannot do what you are trying.

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

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

... but that won't work either, because cast() is a non-PL / SQL SQL function and only recognizes data collection types at assembly and schema level; and you cannot create an SQL type using %TYPE .

---

As a nasty hack, you can 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 a bit more acceptable if you could keep this length in a variable - a replacement or binding in SQL * Plus or a local variable in PL / SQL. For example, if this is a simple SQL update through SQL * Plus, you can use the 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 can be done in other settings, it depends on where the insertion is performed.