Learn the cause and how to resolve the ORA-01430 error message in Oracle.
Description
When you encounter an ORA-01430 error, the following error message will appear:
- ORA-01430: column being added already exists in table
Cause
You tried to add a column to a table, but the column name already exists in that table.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Rewrite your ALTER TABLE command to create a column with a unique name. Each column name must be unique within a table.
For example, if you had a table called suppliers defined as follows:
CREATE TABLE suppliers ( supplier_id number not null, supplier_name varchar2(50) not null, city varchar2(30), state varchar2(2), zip_code varchar2(10) );
And you executed the following ALTER TABLE command:
ALTER TABLE suppliers ADD supplier_name varchar2(50);
You would receive the following error message:
The column called supplier_name already exists. Each column name in your table must be unique.
In a table, I want to check if a particular column exists or not. If the column does not exist, I want to alter the table and create that column.
I am using Oracle 11g.
Luc M
16.7k26 gold badges75 silver badges89 bronze badges
asked Jun 13, 2012 at 6:12
1
Try this:
declare p_count NUMBER;
select count(1) int p_count
from ALL_TAB_COLUMNS
where OWNER = '<SCHEMA_NAME>'
and TABLE_NAME = '<TABLE_NAME>'
and COLUMN_NAME = '<COLUMN_NAME>';
IF p_count = 0 THEN
--add your column
END IF;
Eventually (depending on the rights) You can use user_tab_columns.
answered Jun 13, 2012 at 6:24
Grzegorz WGrzegorz W
3,4871 gold badge21 silver badges21 bronze badges
1
Or, you can ignore the error:
declare
column_exists exception;
pragma exception_init (column_exists , -01430);
begin
execute immediate 'ALTER TABLE db.tablename ADD columnname NVARCHAR2(30)';
exception when column_exists then null;
end;
/
answered Oct 1, 2014 at 21:47
grokstergrokster
5,9391 gold badge36 silver badges22 bronze badges
If you just want to add a column if it doesn’t exist, just issue an ALTER TABLE ADD (mycolumn ...);. If the statement raises an exception (ORA-01430: column being added already exists in table), the column was already there and you can ignore the exception.
answered Jun 13, 2012 at 7:10
Jeffrey KempJeffrey Kemp
59.2k14 gold badges106 silver badges158 bronze badges
look into user_tab_columns table to check if the column exists , and do accordingly
answered Jun 13, 2012 at 6:20
SatyaSatya
8,7035 gold badges34 silver badges55 bronze badges
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-01400 to ORA-01500
- ORA-01400: cannot insert NULL into (string)
Cause: An attempt was made to insert NULL into previously listed objects.
Action: These objects cannot accept NULL values.
- ORA-01401: inserted value too large for column
Cause: The value inserted was too large for the given column.
Action: Do not insert a value greater than what the column can hold.
- ORA-01402: view WITH CHECK OPTION where-clause violation
- ORA-01403: no data found
Cause: No data was found from the objects.
Action: There was no data from the objects which may be due to end of fetch.
- ORA-01404: ALTER COLUMN will make an index too large
- ORA-01405: fetched column value is NULL
- ORA-01406: fetched column value was truncated
Cause: The fetched column values were truncated.
Action: Use the right data types to avoid truncation.
- ORA-01407: cannot update (string) to NULL
- ORA-01408: such column list already indexed
- ORA-01409: NOSORT option may not be used; rows are not in ascending order
Cause: Creation of index with NOSORT option when rows were not ascending. For non-unique indexes the rowid is considered part of the index key. Therefore, if you create an index nosort and two of the rows in the table have the same key and are stored in ascending order, but get split accross two extents where the dba of the first block in the second extent is less than the dba of the last block in the first extent, then the create index nosort may fail.
Action: Create the index without the NOSORT option, or ensure table is stored in one extent.
- ORA-01410: invalid ROWID
- ORA-01411: cannot store the length of column in the indicator
Cause: Tried to fetch a column of size more than 64K and couldn’t store the length of the column in the given indicator of size 2 bytes.
Action: Use the new bind type with call backs to fetch the long column.
- ORA-01412: zero length not allowed for this datatype
Cause: The length for type 97 is 0
Action: Specify the correct length.
- ORA-01413: illegal value in packed decimal number buffer
Cause: The user buffer bound by the user as packed decimal number contained an illegal value.
Action: Use a legal value.
- ORA-01414: invalid array length when trying to bind array
Cause: An attempt was made to bind an array without either a current array length pointer or a zero maximum array length.
Action: Sepcify a valid length.
- ORA-01415: too many distinct aggregate functions
Cause: The query contains more distinct aggregates than can be processed. The current limit is 255.
Action: Reduce the number of distinct aggregate functions in the query.
- ORA-01416: two tables cannot be outer-joined to each other
- ORA-01417: a table may be outer joined to at most one other table
Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
Action: Check that this is really what you want, then join b and c first in a view.
- ORA-01418: specified index does not exist
- ORA-01419: datdts: illegal format code
- ORA-01420: datstd: illegal format code
- ORA-01421: datrnd/dattrn: illegal precision specifier
- ORA-01422: exact fetch returns more than requested number of rows
Cause: The number specified in exact fetch is less than the rows returned.
Action: Rewrite the query or change number of rows requested
- ORA-01423: error encountered while checking for extra rows in exact fetch
- ORA-01424: missing or illegal character following the escape character
Cause: The character following the escape character in LIKE pattern is missing or not one of the escape character, ‘%’, or ‘_’.
Action: Remove the escape character or specify the missing character.
- ORA-01425: escape character must be character string of length 1
Cause: Given escape character for LIKE is not a character string of length 1.
Action: Change it to a character string of length 1.
- ORA-01426: numeric overflow
Cause: Evaluation of an value expression causes an overflow/underflow.
Action: Reduce the operands.
- ORA-01427: single-row subquery returns more than one row
- ORA-01428: argument ‘string‘ is out of range
- ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Cause: No overflow segment defined.
Action: Add overflow segment.
- ORA-01430: column being added already exists in table
- ORA-01431: internal inconsistency in GRANT command
- ORA-01432: public synonym to be dropped does not exist
- ORA-01433: synonym to be created is already defined
- ORA-01434: private synonym to be dropped does not exist
- ORA-01435: user does not exist
- ORA-01436: CONNECT BY loop in user data
- ORA-01437: cannot have join with CONNECT BY
- ORA-01438: value larger than specified precision allowed for this column
Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
Action: Enter a value that complies with the numeric column’s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.
- ORA-01439: column to be modified must be empty to change datatype
- ORA-01440: column to be modified must be empty to decrease precision or scale
- ORA-01441: cannot decrease column length because some value is too big
- ORA-01442: column to be modified to NOT NULL is already NOT NULL
- ORA-01443: internal inconsistency; illegal datatype in resultant view column
- ORA-01444: internal inconsistency; internal datatype maps to invalid external type
- ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
- ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
- ORA-01447: ALTER TABLE does not operate on clustered columns
- ORA-01448: index must be dropped before changing to desired type
- ORA-01449: column contains NULL values; cannot alter to NOT NULL
- ORA-01450: maximum key length (string) exceeded
- ORA-01451: column to be modified to NULL cannot be modified to NULL
Cause: the column may already allow NULL values, the NOT NULL constraint is part of a primary key or check constraint.
Action: if a primary key or check constraint is enforcing the NOT NULL constraint, then drop that constraint.
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
- ORA-01453: SET TRANSACTION must be first statement of transaction
Cause: self-evident
Action: commit (or rollback) transaction, and re-execute
- ORA-01454: cannot convert column into numeric datatype
- ORA-01455: converting column overflows integer datatype
- ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
Cause: A non-DDL insert/delete/update or select for update operation was attempted
Action: commit (or rollback) transaction, and re-execute
- ORA-01457: converting column overflows decimal datatype
- ORA-01458: invalid length inside variable character string
- ORA-01459: invalid length for variable character string
- ORA-01460: unimplemented or unreasonable conversion requested
- ORA-01461: can bind a LONG value only for insert into a LONG column
- ORA-01462: cannot insert string literals longer than 4000 characters
- ORA-01463: cannot modify column datatype with current constraint(s)
Cause: An attempt was made to modify the datatype of column which has referential constraints; or has check constraints which only allows changing the datatype from CHAR to VARCHAR or vise versa.
Action: Remove the constraint(s) or do not perform the offending operation.
- ORA-01464: circular grant (granting to grant ancestor) of table or view
- ORA-01465: invalid hex number
- ORA-01466: unable to read data – table definition has changed
Cause: Query parsed after tbl (or index) change, and executed w/old snapshot
Action: commit (or rollback) transaction, and re-execute
- ORA-01467: sort key too long
- ORA-01468: a predicate may reference only one outer-joined table
- ORA-01469: PRIOR can only be followed by a column name
Cause: Attempting to specify “PRIOR something” where something is not a column name.
Action: Only a column name can follow PRIOR. Replace with a column name.
- ORA-01470: In-list iteration does not support mixed operators
Cause: Constants of different types are specified in an in-list.
Action: Use constants of same type for in-lists.
- ORA-01471: cannot create a synonym with same name as object
- ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc.
- ORA-01473: cannot have subqueries in CONNECT BY clause
- ORA-01474: cannot have START WITH or PRIOR without CONNECT BY
- ORA-01475: must reparse cursor to change bind variable datatype
- ORA-01476: divisor is equal to zero
- ORA-01477: user data area descriptor is too large
- ORA-01478: array bind may not include any LONG columns
Cause: User is performing an array bind with a bind variable whose maximum size is greater than 2000 bytes.
Action: Such bind variables cannot participate in array binds. Use an ordinary bind operation instead.
- ORA-01479: last character in the buffer is not Null
Cause: A bind variable of type 97 does not contain null at the last position
Action: Make the last character null
- ORA-01480: trailing null missing from STR bind value
Cause: A bind variable of type 5 (null-terminated string) does not contain the terminating null in its buffer.
Action: Terminate the string with a null character
- ORA-01481: invalid number format model
Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter.
Action: Consult your manual.
- ORA-01482: unsupported character set
Cause: The character set used to perform the operation, such as the CONVERT function, is not a supported character set.
Action: Use one of the supported character sets.
- ORA-01483: invalid length for DATE or NUMBER bind variable
Cause: A bind variable of type DATE or NUMBER is too long.
Action: Consult your manual for the maximum allowable length.
- ORA-01484: arrays can only be bound to PL/SQL statements
Cause: You tried to bind an array to a non-PL/SQL statement.
Action: n/a
- ORA-01485: compile bind length different from execute bind length
Cause: You bound a buffer of type DTYVCS (VARCHAR with the two byte length in front) and at execute time the length in the first two bytes is more than the maximum buffer length (given in the bind call). The number of elements in the array and the current number of elements in the array cannot be more than the maximum size of the array.
Action: n/a
- ORA-01486: size of array element is too large
Cause: You tried to bind a data value which was either too large for the datatype (for example, NUMBER) or was greater than 4000 bytes (for example, VARCHAR or LONG).
Action: n/a
- ORA-01487: packed decimal number too large for supplied buffer
Cause: An impossible request for decimal to oracle number conversion was made
Action: This conversion cannot be performed
- ORA-01488: invalid nibble or byte in the input data
Cause: An impossible request for decimal to oracle number conversion was made
Action: This conversion cannot be performed
- ORA-01489: result of string concatenation is too long
Cause: String concatenation result is more than the maximum size.
Action: Make sure that the result is less than the maximum size.
- ORA-01490: invalid ANALYZE command
Cause: Incorrect syntax specified
Action: Retry the command
- ORA-01491: CASCADE option not valid
Cause: The CASCADE option is only valid for tables or clusters.
Action: Do not specify CASCADE
- ORA-01492: LIST option not valid
Cause: The LIST option is only valid for tables or clusters.
Action: Do not specify LIST
- ORA-01493: invalid SAMPLE size specified
Cause: The specified SAMPLE size is out of range
Action: Specify a value within the proper range.
- ORA-01494: invalid SIZE specified
Cause: The specified histogram SIZE value was out of range.
Action: Specify a value within the proper range.
- ORA-01495: specified chain row table not found
Cause: The specified table either does not exist or user does not have the proper privleges.
Action: Specify the correct table to use.
- ORA-01496: specified chain row table form incorrect
Cause: The specified table does not have the proper field definitions.
Action: Specify the correct table to use.
- ORA-01497: illegal option for ANALYZE CLUSTER
Cause: The FOR COLUMNS column-list clause may not be used with ANALYZE CLUSTER.
Action: Retry with a legal syntax.