Structured Query Language/SQLSTATE

From Wikibooks, open books for an open world
Jump to: navigation, search
Transactions Structured Query Language
SQLSTATE
Glossary

First versions of the SQL standard used a variable called SQLCODE to flag special processing situations like exceptions, warnings or regular termination. SQLCODE is no longer part of the standard and is replaced by SQLSTATE.

SQLSTATE values consist of 5 characters where the first two denotes a class and the following three a subclass. The following table lists such values of SQLSTATE which are part of the standard. Implementations usually use much more values than those defined by the standard.

SQLSTATE values belong to one of four categories: "S" denotes "Success" (class 00), "W" denotes "Warning" (class 01), "N" denotes "No data" (class 02) and "X" denotes "Exception" (other classes).

Cat. Class Class Text Subclass Subclass Text
S 00 successful completion 000 (no subclass)
W 01 warning 000 (no subclass)
W 01 warning 001 cursor operation conflict
W 01 warning 002 disconnect error
W 01 warning 003 null value eliminated in set function
W 01 warning 004 string data, right truncation
W 01 warning 005 insufficient item descriptor areas
W 01 warning 006 privilege not revoked
W 01 warning 007 privilege not granted
W 01 warning 009 search condition too long for information schema
W 01 warning 00A query expression too long for information schema
W 01 warning 00B default value too long for information schema
W 01 warning 00C result sets returned
W 01 warning 00D additional result sets returned
W 01 warning 00E attempt to return too many result sets
W 01 warning 00F statement too long for information schema
W 01 warning 012 invalid number of conditions
W 01 warning 02F array data, right truncation
N 02 no data 000 (no subclass)
N 02 no data 001 no additional result sets returned
X 07 dynamic SQL error 000 (no subclass)
X 07 dynamic SQL error 001 using clause does not match dynamic parameter specifications
X 07 dynamic SQL error 002 using clause does not match target specifications
X 07 dynamic SQL error 003 cursor specification cannot be executed
X 07 dynamic SQL error 004 using clause required for dynamic parameters
X 07 dynamic SQL error 005 prepared statement not a cursor specification
X 07 dynamic SQL error 006 restricted data type attribute violation
X 07 dynamic SQL error 007 using clause required for result fields
X 07 dynamic SQL error 008 invalid descriptor count
X 07 dynamic SQL error 009 invalid descriptor index
X 07 dynamic SQL error 00B data type transform function violation
X 07 dynamic SQL error 00C undefined DATA value
X 07 dynamic SQL error 00D invalid DATA target
X 07 dynamic SQL error 00E invalid LEVEL value
X 07 dynamic SQL error 00F invalid DATETIME_INTERVAL_CODE
X 08 connection exception 000 (no subclass)
X 08 connection exception 001 SQL-client unable to establish SQL-connection
X 08 connection exception 002 connection name in use
X 08 connection exception 003 connection does not exist
X 08 connection exception 004 SQL-server rejected establishment of SQL-connection
X 08 connection exception 006 connection failure
X 08 connection exception 007 transaction resolution unknown
X 09 triggered action exception 000 (no subclass)
X 0A feature not supported 000 (no subclass)
X 0A feature not supported 001 multiple server transactions
X 0D invalid target type specification 000 (no subclass)
X 0E invalid schema name list specification 000 (no subclass)
X 0F locator exception 000 (no subclass)
X 0F locator exception 001 invalid specification
X 0L invalid grantor 000 (no subclass)
X 0M invalid SQL-invoked procedure reference 000 (no subclass)
X 0P invalid role specification 000 (no subclass)
X 0S invalid transform group name specification 000 (no subclass)
X 0T target table disagrees with cursor specification 000 (no subclass)
X 0U attempt to assign to non-updatable column 000 (no subclass)
X 0V attempt to assign to ordering column 000 (no subclass)
X 0W prohibited statement encountered during trigger execution 000 (no subclass)
X 0W prohibited statement encountered during trigger execution 001 modify table modified by data change delta table
X 0Z diagnostics exception 000 (no subclass)
X 0Z diagnostics exception 001 maximum number of stacked diagnostics areas exceeded
X 21 cardinality violation 000 (no subclass)
X 22 data exception 000 (no subclass)
X 22 data exception 001 string data, right truncation
X 22 data exception 002 null value, no indicator parameter
X 22 data exception 003 numeric value out of range
X 22 data exception 004 null value not allowed
X 22 data exception 005 error in assignment
X 22 data exception 006 invalid interval format
X 22 data exception 007 invalid datetime format
X 22 data exception 008 datetime field overflow
X 22 data exception 009 invalid time zone displacement value
X 22 data exception 00B escape character conflict
X 22 data exception 00C invalid use of escape character
X 22 data exception 00D invalid escape octet
X 22 data exception 00E null value in array target
X 22 data exception 00F zero-length character string
X 22 data exception 00G most specific type mismatch
X 22 data exception 00H sequence generator limit exceeded
X 22 data exception 00P interval value out of range
X 22 data exception 00Q multiset value overflow
X 22 data exception 010 invalid indicator parameter value
X 22 data exception 011 substring error
X 22 data exception 012 division by zero
X 22 data exception 013 invalid preceding or following size in window function
X 22 data exception 014 invalid argument for NTILE function
X 22 data exception 015 interval field overflow
X 22 data exception 016 invalid argument for NTH_VALUE function
X 22 data exception 018 invalid character value for cast
X 22 data exception 019 invalid escape character
X 22 data exception 01B invalid regular expression
X 22 data exception 01C null row not permitted in table
X 22 data exception 01E invalid argument for natural logarithm
X 22 data exception 01F invalid argument for power function
X 22 data exception 01G invalid argument for width bucket function
X 22 data exception 01H invalid row version
X 22 data exception 01S invalid Query regular expression
X 22 data exception 01T invalid Query option flag
X 22 data exception 01U attempt to replace a zero-length string
X 22 data exception 01V invalid Query replacement string
X 22 data exception 01W invalid row count in fetch first clause
X 22 data exception 01X invalid row count in result offset clause
X 22 data exception 021 character not in repertoire
X 22 data exception 022 indicator overflow
X 22 data exception 023 invalid parameter value
X 22 data exception 024 unterminated C string
X 22 data exception 025 invalid escape sequence
X 22 data exception 026 string data, length mismatch
X 22 data exception 027 trim error
X 22 data exception 029 noncharacter in UCS string
X 22 data exception 02D null value substituted for mutator subject parameter
X 22 data exception 02E array element error
X 22 data exception 02F array data, right truncation
X 22 data exception 02G invalid repeat argument in a sample clause
X 22 data exception 02H invalid sample size
X 23 integrity constraint violation 000 (no subclass)
X 23 integrity constraint violation 001 restrict violation
X 24 invalid cursor state 000 (no subclass)
X 25 invalid transaction state 000 (no subclass)
X 25 invalid transaction state 001 active SQL-transaction
X 25 invalid transaction state 002 branch transaction already active
X 25 invalid transaction state 003 inappropriate access mode for branch transaction
X 25 invalid transaction state 004 inappropriate isolation level for branch transaction
X 25 invalid transaction state 005 no active SQL-transaction for branch transaction
X 25 invalid transaction state 006 read-only SQL-transaction
X 25 invalid transaction state 007 schema and data statement mixing not supported
X 25 invalid transaction state 008 held cursor requires same isolation level
X 26 invalid SQL statement name 000 (no subclass)
X 27 triggered data change violation 000 (no subclass)
X 27 triggered data change violation 001 modify table modified by data change delta table
X 28 invalid authorization specification 000 (no subclass)
X 2B dependent privilege descriptors still exist 000 (no subclass)
X 2C invalid character set name 000 (no subclass)
X 2D invalid transaction termination 000 (no subclass)
X 2E invalid connection name 000 (no subclass)
X 2F SQL routine exception 000 (no subclass)
X 2F SQL routine exception 002 modifying SQL-data not permitted
X 2F SQL routine exception 003 prohibited SQL-statement attempted
X 2F SQL routine exception 004 reading SQL-data not permitted
X 2F SQL routine exception 005 function executed no return statement
X 2H invalid collation name 000 (no subclass)
X 30 invalid SQL statement identifier 000 (no subclass)
X 33 invalid SQL descriptor name 000 (no subclass)
X 34 invalid cursor name 000 (no subclass)
X 35 invalid condition number 000 (no subclass)
X 36 cursor sensitivity exception 000 (no subclass)
X 36 cursor sensitivity exception 001 request rejected
X 36 cursor sensitivity exception 002 request failed
X 38 external routine exception 000 (no subclass)
X 38 external routine exception 001 containing SQL not permitted
X 38 external routine exception 002 modifying SQL-data not permitted
X 38 external routine exception 003 prohibited SQL-statement attempted
X 38 external routine exception 004 reading SQL-data not permitted
X 39 external routine invocation exception 000 (no subclass)
X 39 external routine invocation exception 004 null value not allowed
X 3B savepoint exception 000 (no subclass)
X 3B savepoint exception 001 invalid specification
X 3B savepoint exception 002 too many
X 3C ambiguous cursor name 000 (no subclass)
X 3D invalid catalog name 000 (no subclass)
X 3F invalid schema name 000 (no subclass)
X 40 transaction rollback 000 (no subclass)
X 40 transaction rollback 001 serialization failure
X 40 transaction rollback 002 integrity constraint violation
X 40 transaction rollback 003 statement completion unknown
X 40 transaction rollback 004 triggered action exception
X 42 syntax error or access rule violation 000 (no subclass)
X 44 with check option violation 000 (no subclass)


Transactions Structured Query Language
SQLSTATE
Glossary