0

I have a varchar2(8) column in a table, and I want to convert it to varchar2(100) in the select statement (i.e. I don't want to to call an "alter table" afterwards).* Is it possible? Is there some function like "to_varchar2(x, 100)"?

(*) the reason is that I need to use the column in a "model" statement, where there will be additional identifiers that are longer. I don't want to create an intermediate table and use "alter table" to change the varchar2 length. Example:

  select date, mystringvariable r, somenumber v from MyTable
  model
  unique single reference
  return updated rows   
  partition by(date) dimension by(r) measures(v)
  rules upsert automatic order ( 
  v['too_long_identifier']  = v['X1']
  );

     Error at line 5:

     ORA-12899: value too large for column ??? (actual: 19, maximum: 8)
          partition by(date) dimension by(r) measures(v)
                                          ^
Giuseppe
  • 518
  • 10
  • 22

1 Answers1

2

Not sure where in your query you need to do this (I don't have your data to test), but if str is any expression of data type varchar2(8) and you need to recast it as varchar2(100), you can use the cast function:

 .... cast(str as varchar2(100)) ....