- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: CHR function & Oracle10g & ORA-01401
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-09-2007 01:29 AM
тАО05-09-2007 01:29 AM
CHR function & Oracle10g & ORA-01401
We have a application running with Oracle9i. There is something typical done for creating the tables in our database. Any column not part of primary key is assigned a default value(which is actually null) using:
DEFAULT (LPAD(CHR(0),008,CHR(0))),
But if I try to create a similar table in Oracle10g database using the same script, it throws this error:
ORA-01401: inserted value too large for column
Is there some change in the syntax in 10g or the way in which it parses/interprets the SQL command different from 9i?
Regards,
Ankit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-09-2007 01:37 AM
тАО05-09-2007 01:37 AM
Re: CHR function & Oracle10g & ORA-01401
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-09-2007 01:41 AM
тАО05-09-2007 01:41 AM
Re: CHR function & Oracle10g & ORA-01401
This has to do with NLS_LENGTH_SEMANTICS paramter which must have been set in your 10g environment.
For more information, please see Metalink Note:144808.1 - Subject: Examples and limits of BYTE and CHAR semantics usage
Below a simple quote from this doc:
..
The default sizing of character data types (CHAR, VARCHAR2, and LONG) is in BYTES. CHAR(10) in a table definition means 10 bytes not 10
...
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-09-2007 05:43 PM
тАО05-09-2007 05:43 PM
Re: CHR function & Oracle10g & ORA-01401
I had mixed the differnet problemd I was facing regarding databases while creating the thread. The error is not coming for Oracle10g but in Oracle9i with UTF8 character set.
It means CHR(0) , ie, null, should be taking more than 1 byte for storage. Is it true?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-09-2007 05:50 PM
тАО05-09-2007 05:50 PM
Re: CHR function & Oracle10g & ORA-01401
The concept behind using CHR(0) in default definition has something to do with the retrieval of data. Our application is designed in such a way that the returned data should always be in fixed in the length. Thats why no varchar datatype has been used in our case.
I have been told that CHR(0) is different from NULL in the sense that it will return the column with fixed length even if the value is NULL in it.
Though I am not very sure how is it happening. I inserted values into only 2 columns out of 4 columns giving Oracle the power to insert default values in rest of 2 columns. But on fetching data through SQL*Plus, I am getting NULL in those 2 columns.
I'll confirm how application is handliong such cases and will get back with the progress.