Home » SQL & PL/SQL » SQL & PL/SQL » A regular user cannot create Varchar2(32767) (Oracle Database 12c Standard Edition Release - 64bit Production 0 Windows)
A regular user cannot create Varchar2(32767) [message #682645] Wed, 04 November 2020 14:51 Go to next message
Darth Waiter
Messages: 41
Registered: October 2020
How can I allow a regular user to create tables with Varchar2(32767) columns?
When I issue this command under a regular user:

CREATE TABLE TestVarchar32767 (A VARCHAR2(32767))
It throws this error:

Error starting at line : 1 in command -
CREATE TABLE HttpingTestVarchar32767 (A VARCHAR2(32767))
Error report -
SQL Error: ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW
But if I issue it under SYS as SYSDBA, then it works.
Re: A regular user cannot create Varchar2(32767) [message #682646 is a reply to message #682645] Wed, 04 November 2020 15:01 Go to previous message
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To allow this you must set the parameter MAX_STRING_SIZE to EXTENDED (please read the link before taking this action).

Also read SYS/SYSDBA is special.

Previous Topic: Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure
Next Topic: How can I model a column in a new table after a column in an existing table?
Goto Forum:

Current Time: Thu Mar 30 07:15:20 CDT 2023