Forum Stats

  • 3,780,689 Users
  • 2,254,426 Discussions
  • 7,879,413 Comments

Discussions

Categories

Default datatype for text in views

User_RC7ZM
User_RC7ZM Member Posts: 4 Green Ribbon
in SQL & PL/SQL

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?

· Share on TwitterShare on Facebook

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 811 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.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,594 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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,372 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.

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 811 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


    · Share on TwitterShare on Facebook