Forum Stats

  • 3,768,281 Users
  • 2,252,770 Discussions
  • 7,874,513 Comments

Discussions

Default datatype for text in views

User_RC7ZM
User_RC7ZM Member Posts: 4 Green Ribbon

When I create a view with hard-coded fixed length text, Oracle automatically views it as CHAR datatype. The only way to change it to VARCHAR2 is explicitly CAST it. Is that assumption true? Is there a database level setting that will always use VARCHAR2 for all text instead?

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

    Docs:

    Within expressions and conditions, Oracle treats text literals as though they have the data type CHAR by comparing them using blank-padded comparison semantics.

    To check datatype use the function dump.

  • EdStevens
    EdStevens Member Posts: 28,519 Gold Crown
    edited Oct 19, 2021 6:41PM

    perhaps it would help if you gave an example (actual formatted code) of creating a " view with hard-coded fixed length text".. If it is what it sounds like, then what do you care if oracle calls it CHAR(n) or VARCHAR2(n)? After all, it is hard-coded and fixed. It is what it is.

  • User_RC7ZM
    User_RC7ZM Member Posts: 4 Green Ribbon

    Thanks for replies. If the answer is 'it is what it is' please don't bother with the response. Question is if the default behavior can be changed or not? We have a logic which handles 'CHAR' and 'VARCHAR2' differently in one of our applications and checking if there is a way to change them as VARCHAR2 without explicitly modifying views/application logic(Something like NLS_SESSION_PARAMETERS). I did not find any but curious if there is something I did not know about.

    create or replace view test_char

    as

    select 'abc' as col1

    from dual;


    Above view would show 'col1' as CHAR datatype in data dictionary.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    hI, @User_RC7ZM

    Question is if the default behavior can be changed or not? 

    I've never heard of a way. Continue to use CAST.

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

    @EdStevens what do you care if oracle calls it CHAR(n) or VARCHAR2(n)

    Mixing CHAR/VARCHAR2 can lead to hard-to-diagnose errors. For example, equality may be non-transitive

    select
       case when x0=x1 then 'x0=x1' else 'x0≠x1' end a,
       case when x1=x2 then 'x1=x2' else 'x1≠x2' end b,
       case when x2=x0 then 'x2=x0' else 'x2≠x0' end c
    from (
       select
          '* ' x0,
          case when dummy='X' then '*' else '#'  end x1,
          case when dummy='X' then '*' else '##' end x2
       from dual
    );
    
    A     B     C    
    ----- ----- -----
    x0=x1 x1=x2 x2≠x0