Changing the column precision while creating a view in Oracle

oracle, precision, varchar, view

Solution

You can use the `CAST` function, i.e. `CAST( GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') AS VARCHAR2(64)`

If you create a function

SQL> create or replace function my_function
  2    return varchar2
  3  is
  4  begin
  5    return 'foo';
  6  end;
  7  /

Function created.

Then a simple view that selects this function will have a `VARCHAR2(4000)` data type.

SQL> create or replace view v1 as select my_function col1 from dual;

View created.

SQL> desc v1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(4000)

If you wrap the call in a `CAST`, however, the length changes

SQL> ed
Wrote file afiedt.buf

  1  create or replace view v2
  2  as
  3  select cast( my_function as varchar2(10) ) col1
  4*   from dual
SQL> /

View created.

SQL> desc v2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10)

Problem

``` CREATE OR REPLACE VIEW USER_AUD_VIEW ("AUDIT_ID", "USER_ID", "TABLE_NAME_TXT", "COLUMN_NAME_TXT", "OLD_VALUE_TXT", "NEW_VALUE_TXT", "AUDIT_LAST_UPDATED_DT", "AUDIT_UPDATED_USER_ID", "EVALUATOR_ID", "PRODUCT_ID") AS SELECT acm.audit_id, GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') AS user_id, acm.table_name_txt, acm.column_name_txt, CASE WHEN UPPER(acm.column_name_txt) = 'PASSWORD_TXT' THEN '******' ELSE acm.old_value_txt END AS old_value_txt, CASE WHEN UPPER(acm.column_name_txt) = 'PASSWORD_TXT' THEN '******' ELSE acm.new_value_txt END AS new_value_txt, acm.audit_last_updated_dt, CASE WHEN UPPER(acm.audit_updated_user_id) = 'UNKNOWN' THEN acm.audit_updated_user_id ELSE (users.user_id || ' (' || DECODE(users.last_name_txt, NULL,' ' , users.last_name_txt) || ', ' || DECODE(users.first_name_txt, NULL,'' , users.first_name_txt) || ')' ) END AS audit_uupdated_user_id, acm.evaluator_id, TO_NUMBER(GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,2,','),2,':')) AS product_id FROM audit_config_maintenance acm, users WHERE acm.table_name_txt in ('USERS','XREF_USER_PRODUCT') AND UPPER(acm.audit_updated_user_id) = UPPER(users.user_id) AND acm.primary_key_values is not null ``` While creating the view as above, the get_token() function makes the column type a varchar2(4000). How do I change the command so that it makes that field a varchar2(64) ?

Original source