Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Default datatype for text in views

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
-
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.
-
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.
-
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.
-
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.
-
@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