Oracle 19c - ORA-00910:specified length too long for its datatype - MAX_STRING_SIZE=EXTENDED - Demo!

June 07, 2023

Error:

ORA-00910: specified length too long for its datatype

Cause:

The above error message while creating table with string data types of below length:

- VARCHAR2(4001), where length is greater than 4000.

- NVARCHAR2(2001), where length is greater than 2000.

- RAW(2001), where length is greater than 2000.

Solution:

Max_string_size needs to be modified from STANDARD to EXTENDED.

Test Case to identify the Root Cause:

I am creating tables with below length and I am able to create tables, but if I tried to create tables with values greater than that i.e varchar2(4001), nvarchar2(2001), raw(2001), then I am getting above error message.

- varchar2(4000)

- nvarchar2(2000)

- raw (2000)

MAX_STRING_SIZE=STANDARD:

CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4000));

CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2000));

CREATE TABLE SRLAB.DEMO_RAW (comment1 raw(2000));

CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name varchar2(4001));

CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2001));

CREATE TABLE SRLAB.DEMO_RAW (comment1 RAW(2001));

We came to a conclusion for those examples, it is clear that there is defined limit at DB level beyond which we can not create a table with these data types.

Solution:

There is one parameter in Oracle Database called as "MAX_STRING_SIZE" which controls the max size of VARCHAR2, NVARCHAR2, and RAW data types.

The default value of these parameters is STANDARD, where the length limit is as:

- VARCHAR2 :- 4000 bytes

- NVARCHAR2 :- 2000 bytes

- RAW :- 2000 bytes

To increase the size of those data types, the parameter value needs to be changed from STANDARD to EXTENDED. The below high level stpes to be followed to over come this issue.

Please perform below prerequisites before proceeding.

Step 1 -> Database Vault should be disabled before executing the steps or grant DV_PATCH_ADMIN to SYS user.

Step 2 -> We need to take full database backup or create Guarantee Restore Point. In case of revert the settings from EXTENDED to STANDARD and we can restore from backup or Flashback the database through GRP.

Step 3 -> The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

Step 4 -> We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED, but revert is not possible i.e. from EXTENDED to STANDARD.

Step 5 -> The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required, not for all.

Step 1: Check DV is enabled in the database and database version.

set lines 300 pages 3000

col parameter for a26

col value for a26

select parameter,value from v$option where parameter like '%Vault%';

PARAMETER VALUE

---------------------- --------

Oracle Database Vault FALSE ------> DV is disabled.

SQL> select banner from v$version;

BANNER

-----------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE

------------- ----------- --------

compatible string 19.0.0

SQL> show parameter max_string_size

NAME TYPE VALUE

------------------- ----------- -----------

max_string_size string STANDARD

Step 2: Take full database backup. This is why because if there is any error occurs during DB startup post this script execution or during the script execution due to Database Vault or any other unknown reason then we can not start the Database or create Guaranteed Restore Point to flashback the database.

Step 3: Shutdown the database and start the same in upgrade mode.

SQL> shut immediate;

SQL> startup upgrade

SQL> select instance_name,status,logins from v$instance;

Step 4: Set the max_string_size parameter to extended and run 32k script.

SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=both;

SQL> @?/rdbms/admin/utl32k.sql

Step 5: Once the above script execution gets completed then stop the database and take normal startup.

SQL> shut immediate;

SQL> startup

Step 6: Perform post checks.

SQL> show parameter MAX_STRING_SIZE

SQL> CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name varchar2(4001));

SQL> CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2001));

SQL> CREATE TABLE SRLAB.DEMO_RAW (comment1 raw(2001));

For Physical Standby Database:

The above steps have been performed on Primary database, but what about Physical Standby database ? No need to execute too many steps on Physical Standby database. Just simply execute below fewer steps to set this parameter in Physical Standby database.

Step 1: Stop the database and start it in upgrade mode.

SQL> shut immediate;

SQL> startup upgrade

Step 2: Set the parameter max_string_size to EXTENDED.

SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=pfile;

Step 3: Stop the database and start the same mount mode.

SQL> shut immediate;

SQL> startup mount;

SQL> show parameter MAX_STRING_SIZE

For RAC Database:

If you want to change this parameter in RAC environment then follow the below steps:

Step 1: Set the cluster_database parameter to FALSE and stop the entire database by srvctl.

SQL> alter system set cluster_database = false scope = spfile;

$srvctl stop db -d srlab

$srvctl status db -d srlab

Step 2: From any once instance login as sqlplus and start the instance in upgrade mode.

SQL> startup upgrade;

Step 3: Set the parameter to EXTENDED value and execute 32k script.

SQL> show parameter MAX_STRING_SIZE

SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=both;

SQL> @?/rdbms/admin/utl32k.sql

Step 4: Set the cluster_database to TRUE and take restart of the database.

SQL> alter system set cluster_database = true scope = spfile;

SQL> shut immediate;

$srvctl start db -d srlab

$srvctl status db -d srlab

Step 5: Validate the changes

$sqlplus / sysdba

SQL> show parameter MAX_STRING_SIZE

Work Log:

oracle@192.168.217.49's password:

Last login: Sat Mar 18 05:11:34 2023

[oracle@srlab ~]$

[oracle@srlab ~]$ . oraenv

ORACLE_SID = [oracle] ? SRLAB

The Oracle base has been set to /u01/app/oracle

[oracle@srlab ~]$

[oracle@srlab ~]$ ps -ef |grep pmon

oracle 6504 6380 0 05:19 pts/1 00:00:00 grep --color=auto pmon

[oracle@srlab ~]$ ps -ef |grep tns

root 22 2 0 04:56 ? 00:00:00 [netns]

oracle 6517 6380 0 05:19 pts/1 00:00:00 grep --color=auto tns

[oracle@srlab ~]$

[oracle@srlab ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 18 05:19:17 2023

Version 19.14.0.0.0

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1627387544 bytes

Fixed Size 9135768 bytes

Variable Size 855638016 bytes

Database Buffers 754974720 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

SRLAB

SQL> set lines 200 pages 2000

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL>

SQL> show parameter lister

SQL> show parameter listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

forward_listener string

listener_networks string

local_listener string LISTENER_SRLAB

remote_listener string

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

[oracle@srlab ~]$ lsnrctl start LISTENER_SRLAB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-MAR-2023 05:31:38

Starting /u01/app/oracle/product/19.0.0.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production

System parameter file is /u01/app/oracle/product/19.0.0.0/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/srlab/listener_srlab/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srlab)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srlab)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER_SRLAB

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 18-MAR-2023 05:31:40

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/srlab/listener_srlab/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srlab)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

[oracle@srlab ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 18 05:31:49 2023

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> select * global_name;

select * global_name

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

SRLAB

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL>

SQL> alter session set container=SRLABPLUG;

Session altered.

SQL>

SQL> select distinct tablespace_name from dba_data_files;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

SQL>

SQL> create user srlab identified by ora$12345 default tablespace users;

User created.

SQL> grant connect, create session to srlab;

Grant succeeded.

SQL> grant unlimited tablespace to srlab;

Grant succeeded.

SQL> show parameter max_string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL> show parameter db

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

DBFIPS_140 boolean FALSE

allow_global_dblinks boolean FALSE

autotask_max_active_pdbs integer 2

awr_pdb_autoflush_enabled boolean FALSE

awr_pdb_max_parallel_slaves integer 10

cdb_cluster boolean FALSE

cdb_cluster_name string

clonedb boolean FALSE

clonedb_dir string

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_big_table_cache_percent_target string 0

db_block_buffers integer 0

db_block_checking string FALSE

db_block_checksum string TYPICAL

db_block_size integer 8192

db_cache_advice string ON

db_cache_size big integer 0

db_create_file_dest string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

db_domain string

db_file_multiblock_read_count integer 101

db_file_name_convert string

db_files integer 200

db_flash_cache_file string

db_flash_cache_size big integer 0

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

db_index_compression_inheritance string NONE

db_keep_cache_size big integer 0

db_lost_write_protect string NONE

db_name string SRLAB

db_performance_profile string

db_recovery_file_dest string /u01/oradata/srlab/fra

db_recovery_file_dest_size big integer 30G

db_recycle_cache_size big integer 0

db_securefile string PREFERRED

db_ultra_safe string OFF

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string SRLAB

db_unrecoverable_scn_tracking boolean TRUE

db_writer_processes integer 1

dbwr_io_slaves integer 0

disable_pdb_feature big integer 0

enable_automatic_maintenance_pdb boolean TRUE

enabled_PDBs_on_standby string *

max_datapump_jobs_per_pdb string 100

max_pdbs integer 254

noncdb_compatible boolean FALSE

one_step_plugin_for_pdb_with_tde boolean FALSE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

outbound_dblink_protocols string ALL

pdb_file_name_convert string

pdb_lockdown string

pdb_os_credential string

pdb_template string

rdbms_server_dn string

scheduler_follow_pdbtz boolean FALSE

standby_db_preserve_states string NONE

standby_file_management string MANUAL

standby_pdb_source_file_dblink string

standby_pdb_source_file_directory string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

target_pdbs integer 3

xml_db_events string enable

SQL> set lines 200 pages 2000

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SRLABPLUG READ WRITE NO

SQL>

SQL> CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4000));

Table created.

SQL> CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2000));

Table created.

SQL> CREATE TABLE SRLAB.DEMO_RAW (comment1 raw(2000));

Table created.

SQL> CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4001));

CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4001))

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SQL> CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2001));

CREATE TABLE SRLAB.DEMO_NVARCHAR2 (address NVARCHAR2(2001))

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SQL> CREATE TABLE SRLAB.DEMO_RAW (comment1 raw(2001));

CREATE TABLE SRLAB.DEMO_RAW (comment1 raw(2001))

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SQL> set lines 300 pages 3000

col parameter for a26

col value for a26

select parameter,value from v$option where parameter like '%Vault%';SQL> SQL> SQL>

PARAMETER VALUE

-------------------------- --------------------------

Oracle Database Vault FALSE

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

compatible string 19.0.0

noncdb_compatible boolean FALSE

SQL> show parameter max_string_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SRLABPLUG READ WRITE NO

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

SQL> show parameter recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /u01/oradata/srlab/fra

db_recovery_file_dest_size big integer 30G

recovery_parallelism integer 0

remote_recovery_file_dest string

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL> create restore point before_max_string_size guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME

--------------------------------------------------------------------------------------------------------------------------------

BEFORE_MAX_STRING_SIZE

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1627387544 bytes

Fixed Size 9135768 bytes

Variable Size 855638016 bytes

Database Buffers 754974720 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MOUNTED

SQL> alter pluggable database SRLABPLUG open upgrade;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MIGRATE YES

SQL> select instance_name,status,logins from v$instance;

INSTANCE_NAME STATUS LOGINS

---------------- ------------ ----------

SRLAB OPEN MIGRATE RESTRICTED

SQL> show paramete max_string SP2-0158: unknown SHOW option "paramete"

SP2-0158: unknown SHOW option "max_string"

SQL> show parameter max_string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL>

SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=both;

alter system set MAX_STRING_SIZE=EXTENDED scope=both

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1627387544 bytes

Fixed Size 9135768 bytes

Variable Size 855638016 bytes

Database Buffers 754974720 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MOUNTED

SQL> alter pluggable database SRLABPLUG open upgrade;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MIGRATE YES

SQL> show parameter max_string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

03/18/2023 05:50:50.635757000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MIGRATE YES

SQL> alter session set container=SRLABPLUG;

Session altered.

SQL>

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

------------------------------------------------------------------------------------------------------------------------03/18/2023 05:51:37.653300000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SRLABPLUG MIGRATE YES

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 SRLABPLUG MIGRATE YES

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1627387544 bytes

Fixed Size 9135768 bytes

Variable Size 855638016 bytes

Database Buffers 754974720 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MOUNTED

3 SRLABPLUG READ WRITE NO

SQL> show parameter max_string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL>

SQL> alter session set container=SRLABPLUG;

Session altered.

SQL> CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4001));

CREATE TABLE SRLAB.DEMO_VARCHAR2 (first_name VARCHAR2(4001))

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> CREATE TABLE SRLAB.DEMO1_VARCHAR2 (first_name VARCHAR2(4001));

Table created.

SQL> CREATE TABLE SRLAB.DEMO1_NVARCHAR2(2001));

CREATE TABLE SRLAB.DEMO1_NVARCHAR2(2001))

ERROR at line 1:

ORA-00904: : invalid identifier

SQL> CREATE TABLE SRLAB.DEMO1_NVARCHAR2 (address NVARCHAR2(2001));

Table created.

SQL> CREATE TABLE SRLAB.DEMO1.RAW (comment1 RAW(2001));

CREATE TABLE SRLAB.DEMO1.RAW (comment1 RAW(2001))

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> CREATE TABLE SRLAB.DEMO1_RAW (comment1 RAW(2001));

Table created.

SQL> set lines 100

SQL> desc SRLAB.DEMO1_RAW

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

COMMENT1 RAW(2001)

SQL> desc SRLAB.DEMO1_NVARCHAR2

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ADDRESS NVARCHAR2(2001)

SQL> desc SRLAB.DEMO1_VARCHAR2

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

FIRST_NAME VARCHAR2(4001)

SQL>

Hope this helps!

Cheers!

Ramesh

Sachin Ramesh Oracle DBA Blog

Stat Counter

Blog Visit Count

AdSense

Popular Posts

Labels

Social Widget

Report Abuse

Total Pageviews

Blog Archive

Tags

Social Plugin

Subscribe Us

Popular Posts

Menu Footer Widget

Wednesday, June 7, 2023

Oracle 19c - ORA-00910:specified length too long for its datatype - MAX_STRING_SIZE=EXTENDED - Demo!

Post a Comment: