Length of Datatype for Virtual Column populated by Function in Oracle

Question

I have created a basic example below that shows a table with a virtual column in Oracle. The virtual column is populated by a function.

create or replace 
function awesomeness(
  val in varchar2
)
  return varchar2 deterministic as retVal varchar2(255);
  begin
    retVal := 'Some amazing value';
    return retval;
  end;
/  

drop table sometable;
/
create table sometable( 
  value varchar2(255),
  awesome varchar2(4000) as (awesomeness(value))
);
/

The above SQL executes fine, but actually I'm not happy with the 'awesome' column being 4000 characters. I want to make this much smaller, but according to the documentation on functions - you can't. However it suggests that it adapts to the caller, except it doesn't seem to be doing that.

The datatype cannot specify a length, precision, or scale. Oracle Database derives the length, precision, or scale of the return value from the environment from which the function is called.

If I change the table definition to that shown below, I get an error.

drop table sometable;
/
create table sometable( 
  value varchar2(255),
  awesome varchar2(30) as (awesomeness(value))
);
/

Error report:
SQL Error: ORA-12899: value too large for column "AWESOME" (actual: 30, maximum: 4000)
12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"
*Cause:    An attempt was made to insert or update a column with a value
           which is too wide for the width of the destination column.
           The name of the column is given, along with the actual width
           of the value, and the maximum allowed width of the column.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Examine the SQL statement for correctness.  Check source
           and destination column data types.
           Either make the destination column wider, or use a subset
           of the source column (i.e. use substring).

Does my virtual column really need to be varchar2(4000) or can I reduce it somehow?

Answer 1

Having the size reported as 4000 doesn't really seem to be a problem - you won't actually be able to put long values in anyway (assuming your function only returns short ones), and it's not like it's wasting space.

But if you want it to be neater and appear as a smaller size you can use cast(), which isn't treated the same as other functions (even built-in ones like substr()):

create table sometable( 
  value varchar2(255),
  awesome varchar2(30) as (cast(awesomeness(value) as varchar2(30)))
);

Table sometable created.

desc sometable

Name    Null Type          
------- ---- ------------- 
VALUE        VARCHAR2(255) 
AWESOME      VARCHAR2(30)  

If your function somehow returns a longer value - as it could in your example since retVal is 255, though your string literal is short enough - your virtual column value would be truncated to 30 characters.

With your original function, this is what happens when the virtual column is too small for what it returns:

create table sometable( 
  value varchar2(255),
  awesome varchar2(10) as (cast(awesomeness(value) as varchar2(10)))
);

insert into sometable (value) values ('X');

select awesome from sometable;

AWESOME   
----------
Some amazi