The table below lists the SQL numeric error codes and their error messages for InterSystems IRIS® data platform. These codes are returned as the SQLCODE variable value.
Note:
While this document lists error codes as negative values, JDBC and ODBC clients always receive positive values. For example, if an ODBC or JDBC application returns error code 30, look up error code -30 in this table.
SQLCODE 0 and 100
There are two SQLCODE values that do not represent an SQL error:
SQL Error Codes 0 and 100
Error Code | Description |
---|---|
0 | Successful Completion |
100 | No (more) data |
-
SQLCODE=0 indicates successful completion of an SQL operation. For a SELECT statement, this usually means the successful retrieval of data from a table. However, if the SELECT performs an aggregate operation, (for example: SELECT SUM(myfield)) the aggregate operation is successful and an SQLCODE=0 is issued even when there is no data in myfield; in this case SUM returns NULL and %ROWCOUNT=1.
-
SQLCODE=100 indicates that the SQL operation was successful, but found no data to act upon. This can occur for a number of reasons. For a SELECT these include: the specified table contains no data; the table contains no data that satisfies the query criteria; or row retrieval has reached the final row of the table. For an UPDATE or DELETE these include: the specified table contains no data; or the table contains no row of data that satisfies the WHERE clause criteria. In these cases %ROWCOUNT=0.
In Embedded SQL, when SQLCODE=100 the output host variables specified in the INTO clause are nulled.
SQLCODE -400
The SQLCODE -400 error “Fatal error occurred” is a general error. It is generated when a more specific SQLCODE error code is not available.
Retrieving SQL Message Texts
To determine the meaning of an SQLCODE numeric code, use the following ObjectScript statement:
WRITE "SQLCODE=",$SYSTEM.SQL.Functions.SQLCODE(-nnn)
This SQLCODE()Opens in a new tab method can also be called as a stored procedure: %SYSTEM_SQL.Functions_SQLCODE(-nnn).
When possible (usually at SQL compile time), error messages include the name of the field, table, view, or other element that caused the error. Placeholders for these names are shown using the <name> syntax convention.
The %msg variable may contain an additional message error text for certain errors. For further details, refer to System Variables in the “Using Embedded SQL” chapter of Using InterSystems SQL.
The message texts returned are shown below in their English versions. The actual message text returned depends upon your locale setting.
For information on generating ObjectScript general errors from SQLCODE errors, refer to the %SYSTEM.ErrorOpens in a new tab class in the InterSystems Class Reference.
Table of SQL Error Codes and Messages
For ease of use, the SQL Error Codes Table has been divided into the following sub-tables:
-
Error Codes 0 and 100
-
Error Codes -1 to -99
-
Error Codes -101 to -399
-
Error Codes -400 to -500
-
WinSock Error Codes -10050 to -11002
SQL Error Codes -1 to -99
Error Code | Description |
---|---|
-1 | Invalid SQL statement |
-2 | Exponent digits missing after ‘E’ |
-3 | Closing quote («) missing |
-4 | A term expected, beginning with one of the following: identifier, constant, aggregate, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, %UPPER, $$, :, +, -, (, NOT, EXISTS, or FOR |
-5 | Column number specified in ORDER does not match SELECT list |
-6 | ORDER BY column after UNION not found as SELECT column |
-7 | Exponent out of range |
-8 | Invalid DATEPART code for DATEPART(), DATENAME(), DATEADD(), or DATEDIFF() |
-9 | Incompatible SELECT lists used in UNION |
-10 | The SELECT list of the subquery must have exactly one item |
-11 | A scalar expression expected, not a condition |
-12 | A term expected, beginning with one of the following: identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, or %UPPER |
-13 | An expression other than a subquery expected here |
-14 | A comparison operator is required here |
-15 | A condition expected after NOT |
-16 | Quantifier SOME expected after the FOR in the for-expression |
-17 | A for-condition expected after the ( in the for-expression |
-18 | IS (or IS NOT) NULL predicate can be applied only to a field |
-19 | An aggregate function cannot be used in a WHERE or GROUP BY clause |
-20 | Name conflict in the FROM list over label |
-21 | Pointer->Field reference may not be modified by an INSERT or UPDATE statement |
-22 | ORDER must specify column names, not numbers, when after ‘SELECT *’ |
-23 | Label is not listed among the applicable tables |
-24 | Ambiguous sort column |
-25 | Input encountered after end of query |
-26 | Missing FROM clause |
-27 | Field is ambiguous among the applicable tables |
-28 | Host variable name must begin with either % or a letter |
-29 | Field not found in the applicable tables |
-30 | Table or view not found |
-31 | Field not (found/unique) in table(s) |
-32 | Outer-join symbol ( =* or *= ) must be between two fields |
-33 | No field(s) found for table |
-34 | Optimizer failed to find a usable join order |
-35 | INSERT/UPDATE/DELETE not allowed for non-updateable view |
-36 | WITH CHECK OPTION (CHECKOPTION class parameter) not allowed for non-updateable views |
-37 | SQL Scalar/Aggregate/Unary function not supported for Stream fields |
-38 | No master map for table |
-39 | No RowID field for table |
-40 | ODBC escape extension not supported |
-41 | An extrinsic function call must have the form ‘$$tag^routine(…)’ |
-42 | Closing quotes («») missing following pattern match |
-43 | Table is ambiguous within #IMPORT schema name list |
-44 | Duplicate method or query characteristic |
-45 | Duplicate method in ObjectScript query body |
-46 | Required method missing in ObjectScript query body |
-47 | Invalid method or query characteristic |
-48 | Invalid trigger REFERENCING clause for the trigger’s event |
-49 | Trigger REFERENCING clause cannot be specified when trigger language not SQL |
-50 | Trigger specifies UPDATE OF <fieldlist> clause when trigger language not SQL |
-51 | SQL statement expected |
-52 | Cursor (Already/Was Not) DECLAREd |
-53 | Constant or variable expected as new value |
-54 | Array designator (last subscript omitted) expected after VALUES |
-55 | Invalid GRANT <role> TO or REVOKE <role> FROM |
-56 | GRANT/REVOKE Action not applicable to an object of this type |
-57 | Trigger specifies WHEN clause when trigger language not SQL |
-58 | Duplicate field found in trigger UPDATE OF <fieldlist> clause |
-59 | Cannot have more than one field |
-60 | An action (%ALTER, SELECT, UPDATE, etc.) expected |
-61 | Cursor not updateable |
-62 | Additional new values expected for INSERT/UPDATE |
-63 | Data exception — invalid escape character |
-64 | Incompatible SELECT list is used in INSERT |
-65 | Positive integer constant or variable expected |
-66 | Redundant fields found in SELECT list |
-67 | Implicit join (arrow syntax) not supported in ON clause |
-68 | Legacy outer join (=*, *=) not supported in ON clause |
-69 | SET <field> = <value expression> not allowed with WHERE CURRENT OF <cursor> |
-70 | Multi-Line field only valid for LIKE, Contains ([), or NULL Comparison. |
-71 | Multi-Line field must be the left operand of the Comparison. |
-72 | Multi-Line field not valid in ORDER BY clause |
-73 | Aggregates not supported in ORDER BY clause |
-74 | Duplicate <select-list> alias names found |
-75 | <trim_spec> and/or <trim_char> required before FROM in TRIM function. |
-76 | Cardinality mismatch between the SELECT-list and INTO-list. |
-77 | Qualified column reference not allowed in this JOIN context. |
-78 | Invalid transaction state. |
-79 | Referencing key and referenced key must be the same size |
-80 | Integer expected |
-81 | Column Constraint expected |
-82 | Multiple table %DESCRIPTION definitions found |
-83 | Multiple table %FILE definitions found |
-84 | Multiple table %NUMROWS definitions found |
-85 | Multiple table %ROUTINE definitions found |
-86 | Invalid field definition, no datatype defined |
-87 | Invalid table name |
-88 | Invalid field name |
-89 | Invalid index name |
-90 | Invalid view name |
-91 | Transaction mode cannot be specified more than once |
-92 | Level of isolation cannot be READ UNCOMMITTED or READ VERIFIED if READ WRITE specified |
-93 | number of conditions for the DIAGNOSTICS SIZE must be exact numeric |
-94 | Unsupported usage of OUTER JOIN |
-95 | Operation disallowed by operation table |
-96 | Specified level of isolation is not supported |
-97 | Duplicate select-list names found. |
-98 | License violation |
-99 | Privilege violation |
SQL Error Codes -101 to -399
Error Code | Description |
---|---|
-101 | Attempt to open a cursor that is already open |
-102 | Operation (FETCH/CLOSE/UPDATE/DELETE/…) attempted on an unopened cursor |
-103 | Positioned UPDATE or DELETE attempted, but the cursor is not positioned on any row |
-104 | Field validation failed in INSERT, or value failed to convert in DisplayToLogical or OdbcToLogical |
-105 | Field validation failed in UPDATE |
-106 | Row to DELETE not found |
-107 | Cannot UPDATE RowID or RowID based on fields |
-108 | Required field missing; INSERT or UPDATE not allowed |
-109 | Cannot find the row designated for UPDATE |
-110 | Locking conflict in filing |
-111 | Cannot INSERT into a ‘Default Only’ RowID or RowID based on field |
-112 | Access violation |
-113 | %THRESHOLD violation |
-114 | One or more matching rows is locked by another user |
-115 | Cannot INSERT/UPDATE/DELETE on a read only table |
-116 | Cardinality mismatch on INSERT/UPDATE between values list and number of table columns. |
-117 | Aggregates not supported in views |
-118 | Unknown or non-unique User or Role |
-119 | UNIQUE or PRIMARY KEY constraint failed uniqueness check upon INSERT |
-120 | UNIQUE or PRIMARY KEY constraint failed uniqueness check upon UPDATE |
-121 | FOREIGN KEY constraint failed referential check upon INSERT of row in referencing table |
-122 | FOREIGN KEY constraint failed referential check upon UPDATE of row in referencing table |
-123 | FOREIGN KEY constraint failed referential check upon UPDATE of row in referenced table |
-124 | FOREIGN KEY constraint failed referential check upon DELETE of row in referenced table |
-125 | UNIQUE or PRIMARY KEY Constraint failed uniqueness check upon creation of the constraint |
-126 | REVOKE with RESTRICT failed. |
-127 | FOREIGN KEY Constraint failed referential check upon creation of the constraint |
-128 | Argument to scalar function %OBJECT() must be a stream field |
-129 | Illegal value for SET OPTION locale property |
-130 | Before Insert trigger failed |
-131 | After Insert trigger failed |
-132 | Before Update trigger failed |
-133 | After Update trigger failed |
-134 | Before Delete trigger failed |
-135 | After Delete trigger failed |
-136 | View’s WITH CHECK OPTION validation failed in INSERT |
-137 | View’s WITH CHECK OPTION validation failed in UPDATE |
-138 | Cannot INSERT/UPDATE a value for a read only field |
-139 | Concurrency failure on update: row versions not the same |
-140 | Invalid length parameter passed to the SUBSTRING function |
-141 | Invalid input value passed to the CONVERT function |
-142 | Cardinality mismatch between the view-column-list and view query’s SELECT clause |
-143 | ORDER BY not valid in a view’s query |
-144 | A subquery is not allowed in an insert statement’s set/values clause |
-145 | SQLPREVENTFULLSCAN class parameter is 1 for this table. Query that performs full scan of data map is not allowed |
-146 | Unable to convert date input to a valid logical date value |
-147 | Unable to convert time input to a valid logical time value |
-148 | CREATE VIEW, ALTER VIEW, or a view’s query may not contain host variable references |
-149 | SQL Function encountered an error |
-150 | Optimistic concurrency locking for a class definition failed |
-151 | Index is not found within tables used by this statement |
-152 | Index is ambiguous within tables used by this statement |
-153 | SQL compile options comment contains invalid JSON string |
-154 | Cannot UPDATE fields that are part of the shard key |
-155 | INSERT/UPDATE into external (linked) cannot use an array variable for input of new values |
-161 | References to an SQL connection must constitute a whole subquery |
-162 | SQL Connection is not defined |
-163 | Heterogeneous queries via the JDBC gateway are not supported |
-180 | Model name not unique |
-181 | Model or Trained Model not found |
-182 | No query is defined for the model |
-183 | Predicting Column cannot appear in the specified WITH column list |
-184 | Provider class not found |
-185 | Predicting Column only has one unique value in the dataset |
-186 | Model’s Provider is unavailable on this instance |
-187 | ML Configuration not found |
-188 | ML Configuration property is not supported for this provider |
-189 | Cannot DROP the System Default ML Configuration |
-190 | IntegratedML not permitted with current license |
-191 | Model has no default trained model. It may not have been trained. |
-192 | ML Configuration name not unique |
-193 | Model Column / With Column type mismatch |
-194 | Cannot specify NOT DEFAULT when the Trained Model name is the same as the Model’s DefaultTrainedModel |
-201 | Table or view name not unique |
-220 | Gateway query error |
-221 | Gateway query GetConnection() failed |
-222 | Gateway query AllocStatement() failed |
-223 | Gateway query Prepare() failed |
-225 | Gateway query BindParameters() failed |
-226 | Gateway query Execute() failed |
-227 | Gateway query Fetch() failed |
-228 | Gateway query GetData() failed |
-229 | Foreign table query error |
-230 | Foreign tables Execute() failed |
-231 | Foreign tables Fetch() failed |
-232 | Foreign tables Close() failed |
-233 | Cardinality mismatch between COLUMNS/VALUES clause and number of table columns |
-234 | Invalid foreign server type |
-235 | Invalid foreign data wrapper |
-241 | Parallel query queue error |
-242 | Parallel query run-time error |
-251 | Sharded query queue error |
-252 | Sharded query run-time error |
-253 | Sharded INSERT/UPDATE/DELETE run-time error |
-300 | DDL not allowed on this table definition |
-301 | No Savepoint name |
-302 | Savepoint names starting with «SYS» are reserved |
-303 | No implicit conversion of Stream value to non-Stream field in UPDATE assignment is supported |
-304 | Attempt to add a NOT NULL field with no default value to a table which contains data |
-305 | Attempt to make field required when the table has one or more rows where the column value is NULL |
-306 | Column with this name already exists |
-307 | Primary key already defined for this table |
-308 | Identity column already defined for this table |
-309 | The left operand of %CONTAINS is not a property that supports the %Text interface |
-310 | Foreign key references non-existent table |
-311 | Foreign key with same name already defined for this table |
-312 | Invalid schema name. Must use delimited identifiers to reference this schema name |
-313 | Condition expression not supported for Stream fields |
-314 | Foreign key references non-unique key/column collection |
-315 | Constraint or Key not found |
-316 | Foreign key references non-existent key/column collection |
-317 | Cannot DROP Constraint — One or more Foreign Key constraints reference this Unique constraint |
-319 | Referenced table has no primary key defined |
-320 | Cannot DROP table — One or more Foreign Key constraints reference this table |
-321 | Cannot DROP view — One or more views reference this view |
-322 | Cannot DROP column — column is defined on one or more indexes or constraints |
-324 | Index with this name already defined for this table |
-325 | Index cannot be dropped because it is the IDKEY index and the table has data |
-326 | Duplicate TUNE TABLE option clause found |
-327 | Duplicate table option found |
-328 | Duplicate foreign server option found |
-329 | Required foreign server option missing |
-333 | No such index defined |
-334 | Index name is ambiguous. Index found in multiple tables. |
-340 | No such database (namespace) defined |
-341 | Database file already exists |
-342 | Cannot delete system namespace |
-343 | Invalid database name |
-344 | Cannot drop database that you are currently using or connected to |
-350 | An unexpected error occurred executing SqlComputeCode |
-356 | SQL Function (function stored procedure) is not defined to return a value |
-357 | SQL Function (function stored procedure) is not defined as a function procedure |
-358 | SQL Function (function stored procedure) name not unique |
-359 | SQL Function (function stored procedure) not found |
-360 | Class not found |
-361 | Method or Query name not unique |
-362 | Method or Query not found |
-363 | Trigger not found |
-364 | Trigger with same EVENT, TIME, and ORDER already defined |
-365 | Trigger name not unique |
-366 | Schema name mismatch between trigger name and table name |
-370 | SQL CALL, more arguments specified than defined in the stored procedure |
-371 | :HVar = CALL … Specified for a procedure which does not return a value |
-372 | Support for extrinsic function calls are disabled |
-373 | An extrinsic function call may not call a % routine |
-374 | Cannot alter the datatype of a field to/from a stream type when the table contains data |
-375 | Cannot ROLLBACK to unestablished savepoint |
-376 | Unsupported CAST target specified |
-377 | Field appears more than once in assignment list of insert or update statement |
-378 | Datatype mismatch, explicit CAST is required |
-380 | Invalid or Missing argument to scalar function |
-381 | Too many arguments to scalar function |
SQL Error Codes -400 to -500
Error Code | Description |
---|---|
-400 | Fatal error occurred |
-401 | Fatal Connection error |
-402 | Invalid Username/Password |
-405 | Unable to read from communication device |
-406 | Unable to Write to Server |
-407 | Unable to Write to Server Master |
-408 | Unable to start server |
-409 | Invalid server function |
-410 | Invalid Directory |
-411 | No stream object defined for field |
-412 | General stream error |
-413 | Incompatible client/server protocol |
-415 | Fatal error occurred within the SQL filer |
-416 | Info Error |
-417 | Security Error |
-422 | SELECT request processed via ODBC, JDBC, or Dynamic SQL cannot contain an INTO clause |
-425 | Error processing stored procedure request |
-426 | Error preparing stored procedure |
-427 | Invalid stored procedure name |
-428 | Stored procedure not found |
-429 | Invalid number of input/output parameters for stored procedure |
-430 | Cannot initialize procedure context |
-431 | Stored procedure parameter type mismatch |
-432 | Function returned multiple rows when only a single value is expected |
-450 | Request timed out due to user timeout |
-451 | Unable to receive server message |
-452 | Message sequencing error |
-453 | Error in user initialization code |
-454 | Error sending external interrupt request |
-456 | SQL query execution interrupted by user |
-459 | Kerberos authentication failure |
-460 | General error |
-461 | Communication link failure |
-462 | Memory allocation failure |
-463 | Invalid column number |
-464 | Function sequence error |
-465 | Invalid string or buffer length |
-466 | Invalid parameter number |
-467 | Column type out of range |
-468 | Fetch type out of range |
-469 | Driver not capable |
-470 | Option value changed |
-471 | Duplicate cursor name |
-472 | A collection-valued property was expected |
-473 | Schema not found |
-475 | Schema is not empty |
-476 | Schema already exists |
-478 | Query recompiled: Result Set mismatch |
-500 | Fetch row count limit reached |
WinSock Error Codes -10050 to -11002
Error Code | Description |
---|---|
-10050 | WinSock: Network is down |
-10051 | WinSock: Network is unreachable |
-10052 | WinSock: Net dropped connection or reset |
-10054 | WinSock: Connection reset by peer (due to timeout or reboot) |
-10055 | WinSock: No buffer space available |
-10056 | WinSock: Socket is already connected |
-10057 | WinSock: Socket is not connected |
-10058 | WinSock: Cannot send after socket shutdown |
-10060 | WinSock: Connection timed out |
-10061 | WinSock: Connection refused |
-10064 | WinSock: Host is down |
-10065 | WinSock: No route to host |
-10070 | WinSock: Stale NFS file handle |
-10091 | WinSock: Network subsystem is unavailable |
-10092 | WinSock: WINSOCK DLL version out of range |
-10093 | WinSock: Successful WSASTARTUP not yet performed |
-11001 | WinSock: Host not found |
-11002 | WinSock: Nonauthoritative host not found |
I have following query
SELECT a.col1 into v_acc_account FROM account_t a
WHERE a.col1=<input paramter> FOR UPDATE OF a.col1 NOWAIT;
The statement returns sqlcode of 100 and does not throw exception
From oracle documentation I understand that the errorcode is accompanied by an exception. What may be the reason behind this behavior.
The return code 100 explains ‘DATA NOT FOUND», we have data for the select query.
Data not found means, the select is failing or the locks not available.
any help much appreciated
Regards,
Nandish
Stop wasting time with computer errors.
Click here to get a complimentary download of this powerful PC optimization tool.
You may have encountered an error code saying ora error 100. Now there are several ways to solve this problem, and we will return to them shortly. SQLCODE=100 indicates that the particular SQL operation completed successfully but found no data to process. This may be due to a number of related events. SELECT is concerned with the following: The specified table certainly does not contain data; the table does not contain data that primarily matches the query criteria; or the row fetch has reached the last band in the table.
Find out the reason for fixing the ORA-00100 error message through Oracle.
Description
Stop wasting time with computer errors.
Your computer is running slow and you�re getting errors? Don�t worry, ASR Pro can fix it. ASR Pro will find out what is wrong with your PC and repair Windows registry issues that are causing a wide range of problems for you. You don�t have to be an expert in computers or software � ASR Pro does all the work for you. The application will also detect files and applications that are crashing frequently, and allow you to fix their problems with a single click. Click this now:
When clients encounter the ORA-00100 error, an appropriate error message is displayed:
Reason
What is Oracle error code for exception connection is null?
Error code: 71. Reason. A null pointer exception occurs when you set the value instance of to differ from the value object.
The application was trying to find data that was not available or didn’t exist.
Resolution
Not
Possibility. 1
This error should be handled as part of the approach. Set up the app accordingly.
-
Or When I Open The Application, I Get An Oracle Error Code -100.
What Is Usual?thank You Not You
-
ORA-00100: Data Found
Cause. The App Referred To Unknown, Possibly Inaccessible Data.Action: Eliminate This Dilemma In The Application, Or Make Satisfactory Changes To The Application Code. Note. If The Application Uses SQL Mode In Oracle Instead Of SQL, ORA-01403 May Be Generated In Au Mode Instead Of ORA-00100.
Sanjay G.
Oracle Certified Professional 8i, 9i.“Details Of Normality In A Database Can Be Described As Inversely Proportional To Its Final DBA”
< /quote>
-
This is a standard error indicating that the data you are obviously looking for cannot be found. This type of error can only be detected by debugging the monitored application. Give your brain a little and find out for yourself
Agasimani
OCP(10g/9i/8i/8)
Or At -100
When I open the application I get an Oracle error code -100.
What was it?
What is the meaning of Sqlcode?
SQLCODE is a powerful integer variable where the DBMS changes the state of the last executed SQL statement. See the Embedded Companion sql guide for details on your own declaration requirements.denial of the SQLCODE aspect in embedded programs.
Thanks
Sanjay is G.
Oracle Certified Professional 8i, 9i.“The degree of normality in a brand new database is inversely proportional to the level of the DBA”
/quote>
ORA-00100: files not found
Cause: The application referred to recognized or inaccessible data.How do I fix an ORA error?
Option 1. You make it clear that the real TNSNAMES.ORA file is also located in the protection tools directory.Option 2: Make sure the names of the products and services you are connecting to include TNSNAMES in. ORA and it is correctly defined.Option 3: Make sure that some of these TNSNAMEs do not contain syntax errors. OPA file.
Action: Eliminate this type of condition in the application, or make appropriate changes to the application code. Note. If the application uses Oracle mode instead of SQL in sql, ORA-01403 will be generated in Ansi mode instead of ORA-00100.
ORA-00100: not found
Reason: Data The application received a link to or unknown inaccessible computer files.
Action: Correct this condition in the application, or make appropriate changes to the application code. Note. If you see that the application is using SQL instead of mode, pleaseIn general Oracle, for SQL ORA-01403 is being developed in Ansi mode instead of ORA-00100.Color=”red”>”Der
Agasimani
OCP(10g/9i/8i/8)
This is a known bug indicating that the data you are looking for cannot be found. These types of errors can only be tracked down by debugging the application. Give your brain a little exercise and find it yourself
Publishing Permissions
Blog entry about Oracle error codes? Maybe even curiosities?
If buyers are in doubt that this could be any more affordable or interesting, schedule these two questions:
Can a big bug in Oracle have more than one human code?What’s wrong?
If you answered “Yes” yourself the first time, and then also answered “Yes” and “No”, you probably don’t need to hear this message.
>
Oracle Errors With More Than One Error Code?
But is it true that there is at least one bug that links two only different error codes at home and is one of the many common “bugs” you will run into in your favorite code:
What is Ora 01403 No data found?
The ORA-01403 error is related to SQL parsing, which was supposed to return all the data you needed, but the data was not found. The error is usually associated with SELECT INTO clauses, rows that can retrieve sets of columns from a list. Write an exception that “if nations NO_DATA_FOUND return ‘No content in selected variable’”.
When I execute your SELECT INTO statement, Oracle returns NO_DATA_FOUND if no rows were found for the query. It fires too_many_rows when multiple rows can be found.
what
Something that shows conforms to these rules. I’m creating a chart with no data. So my SELECT-INTO finds no rows and no error because the message displayed (there was no exception from this rule engine) shows that the error encodes -1403 (or is it something like 1403? I’ll look at that quirk later).
CREATE TABLE t (n COUNT)/EXPLAIN l_n NUMBER;BEGIN N select INTO FROM l_n t;END;/ORA-01403: data not found
Now I will handle the exception, first with OTHERS, then with WHEN NO_DATA_FOUND, and I will display the profit returned by SQLCODE.
EXPLAIN L_n NUMBER;BEGIN SELECT FROM nINTOL_n t;AN EXCEPTION IF OTHER THEN DBMS_OUTPUT.put_line('Error code is || ' SQLCODE);END;/Error code 100EXPLAIN = l_n number;begin SELECT n INTO l_n FROM t;AN EXCEPTION IF no_data_found THEN DBMS_OUTPUT.put_line('Error code = || sqlcode code);End;/Error=100
This is, of course, very strange. How can it be good? You say exactly that: buy the best answer, since the source in this case is the actual code of the STANDARD package, which defines many data types and exceptions in the PL/SQL.STANDARD language, the owners in will find:
Click here to get a complimentary download of this powerful PC optimization tool.
Of Een Fout 100
Eller Fel 100
Oder Fehler 100
Blad Czasu 100
Ili Oshibka 100
Ora Erro 100
오류 100
Ou Une Erreur 100
Ora Error 100
Ora Errore 100
From Wikipedia, the free encyclopedia
SQL Return Codes are used on a day-to-day basis for the diagnosis of programming failures as a result of SQL calls by IBM Db2 programs. An important feature of IBM Db2 programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field SQLCODE within the Db2 SQLCA block.
SQLCODE is no longer part of the SQL-standard. The SQL-standard replaced SQLCODE by the more detailed SQLSTATE.
SQLCA[edit]
The SQL communications area (SQLCA) structure is used within the IBM Db2 program to return feedback to the application program.
SQLCODE[edit]
The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive:
- 0 means that the execution was successful.
- Negative values indicate an unsuccessful execution with an error.
An example is -911, which means that a timeout has occurred with a rollback. - Positive value mean a successful execution with a warning.
An example is +100, which means that no matching rows were found or that the cursor has reached the end of the table.
Here is a more comprehensive list of the SQLCODEs for DB2. Note that this list is not exhaustive. Also note that some SQLCODEs may only occur in specific Db2 products; e.g., only on Db2 z/OS, only on Db2 LUW, or only on Db2 for IBM i.
Zero (Successful)[edit]
0 Successful
Negative values (Errors)[edit]
-007 | The specified ‘character’ is not a valid character in SQL statements. |
-010 | THE string constant beginning with string is not terminated properly. |
-029 | INTO Clause required. |
-060 | INVALID type SPECIFICATION : spec |
-084 | Unacceptable SQL statement. |
-101 | The statement is too long or too complex. |
-102 | String constant is too long. |
-103 | String constant is too long. Max is 128 characters. |
-104 | Illegal symbol encountered in the SQL statement. |
-105 | String constant is too long. |
-117 | The number of values in the INSERT does not match the number of columns. |
-119 | Error in GROUP BY or HAVING clause. |
-122 | Column or Expression in the Select List is not valid |
-156 | Invalid syntax near keyword. |
-180 | Bad data in Date/Time/Timestamp. |
-181 | Bad data in Date/Time/Timestamp. |
-188 | The host variable in a DESCRIBE statement is not a valid string representation of a name. |
-199 | Illegal use of the specified keyword. |
-203 | A REFERENCE TO COLUMN column-name IS AMBIGUOUS |
-204 | Object not defined to Db2. |
-205 | Column name not in table. |
-206 | Column does not exist in any table of the SELECT. |
-207 | Invalid column name. |
-208 | THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN column-name IS NOT PART OF THE RESULT TABLE |
-209 | Ambiguous column name |
-216 | Not the same number of expressions on both sides of the comparison in a SELECT. |
-224 | FETCH cannot make an INSENSITIVE cursor SENSITIVE. |
-229 | The locale specified in a SET LOCALE statement was not found. |
-257 | Implicit conversion in datatype is not allowed. |
-289 | Db2 is running out of space in tablespace |
-302 | THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE |
-303 | A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE THE DATA TYPES ARE NOT COMPARABLE |
-305 | Null indicator needed. |
-310 | The value of a decimal is null |
-311 | Varchar, insert or update. -LEN field with the right data length not set. |
-313 | The number of host variables specified is not equal to the number of parameter markers. |
-401 | The data types of the operands of an operation are not compatible. |
-404 | The Sql Statement specified contains a String that is too long. |
-407 | AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES |
-408 | A value is not compatible with the data type of its assignment target. Target name is «<name>». (***OBS: Para alguns casos o CAST resolve ) |
-413 | Overflow occurred during numeric data type conversion. |
-414 | A like predicate is invalid because the first operand is not a string. |
-415 | The corresponding columns, column-number, of the operands of a set operator are not compatible. |
-418 | Use of parameter marker not valid. |
-420 | The value of a string argument was not acceptable to the ‘function-name’ function |
-421 | THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS |
-433 | The data is too large |
-438 | Application raised error with diagnostic text: text |
-440 | Routine &1 in &2 not found with specified parameters.A function or procedure with the specified name and compatible arguments was not found.[1] |
-482 | The procedure returned no locators. |
-501 | Cursor not open on FETCH. |
-502 | Opening cursor that is already open. |
-503 | Updating column needs to be specified. |
-504 | Cursor name not declared. |
-510 | The Table designated by the cursor of the Update or Delete statement cannot be modified. |
-530 | Referential integrity preventing the INSERT/UPDATE |
-532 | Referential integrity (DELETE RESTRICT rule) preventing the DELETE. |
-536 | Referential integrity (DELETE RESTRICT rule) preventing the DELETE. |
-545 | Check constraint preventing the INSERT/UPDATE. |
-551 | Authorization failure. |
-554 | An authorization ID or a role cannot GRANT a privilege to itself. |
-601 | You tried to create an object that already exists |
-602 | Too many columns specified in a create index. |
-603 | a unique index cannot be created because the table contains rows which are duplicates with respect to the values of the identified columns and periods |
-604 | a data type definition specifies an invalid length, precision, or scale attribute |
-607 | operation or option operation is not defined for this object |
-610 | warning: a create/alter on object has placed object in pending |
-611 | only lockmax 0 can be specified when the lock size of the tablespace is tablespace or table |
-612 | identifier is a duplicate name |
-613 | the primary key or a hash key or a unique constraint is too long or has too many columns and periods |
-614 | the index cannot be created or altered, or the length of a column cannot be changed because the sum of the internal lengths of the columns for the index is greater than the allowable maximum |
-615 | operation-type is not allowed on a package in use |
-616 | obj-type1 obj-name1 cannot be dropped because it is referenced by obj-type2 obj-name2 |
-617 | a type 1 index is not valid for table |
-618 | operation operation is not allowed on system databases |
-619 | operation disallowed because the database is not stopped |
-620 | keyword keyword in stmt-type statement is not permitted for a space-type space in the database-type database |
-621 | duplicate dbid dbid was detected and previously assigned to database-name |
-622 | for mixed data is invalid because the mixed data install option is no |
-623 | cluster is not valid for table-name |
-624 | table table-name already has a primary key or unique constraint with specified columns and periods |
-625 | table table-name does not have an index to enforce the uniqueness of the primary or unique key |
-625 | warning: the definition of table has been changed to incomplete |
-626 | the alter statement is not executable because the page set is not stopped |
-627 | the alter statement is invalid because the table space or index has user-managed data sets |
-628 | the clauses are mutually exclusive |
-629 | set null cannot be specified because foreign key name cannot contain null values |
-630 | error: the WHERE NOT NULL specification is invalid for type 1 indexes |
-631 | foreign key name is too long or has too many columns |
-632 | the table cannot be defined as a dependent of table-name because of DELETE rule restrictions |
-633 | the DELETE rule must be DELETE-rule |
-634 | the DELETE rule must not be cascade |
-635 | the DELETE rules cannot be different or cannot be set null |
-636 | ranges specified for partition part-num are not valid |
-637 | duplicate keyword-name keyword or clause |
-638 | table table-name cannot be created because column definition is missing |
-639 | a nullable column of a foreign key with a DELETE rule of set null cannot be a column of the key of a partitioned index |
-640 | locksize row cannot be specified because table in this tablespace has type 1 index |
-642 | too many columns in unique constraints |
-643 | a check constraint or the value of an expression for a column of an index exceeds the maximum allowable length key expression |
-644 | invalid value specified for keyword or clause keyword-or-clause in statement stmt-type |
-645 | WHERE NOT NULL is ignored because the index key cannot contain null values |
-646 | table table-name cannot be created in specified table space table-space-name because it already contains a table |
-647 | bufferpool bp-name for implicit or explicit tablespace or indexspace name has not been activated |
-650 | the alter statement cannot be executed, reason reason-code copyright 2014 TheAmericanProgrammer.com Unauthorized copying prohibited |
-651 | table description exceeds maximum size of object descriptor. |
-652 | violation of installation defined edit or validation procedure proc-name |
-653 | table table-name in partitioned table space tspace-name is not available because its partitioned index has not been created |
-655 | the create or alter stogroup is invalid because the storage group would have both specific and non-specific volume ids |
-658 | a object-type cannot be dropped using the statement statement |
-660 | index index-name cannot be created or altered on partitioned table space tspace-name because key limits are not specified |
-661 | object-type object-name cannot be created on partitioned table space tspace-name because the number of partition specifications is not equal to the number of partitions of the table space |
-662 | a partitioned index cannot be created on a table space, or a table space cannot be index-controlled. table space tspace-name, reason reason-code |
-663 | the number of key limit values is either zero, or greater than the number of columns in the key of index index-name |
-664 | the internal length of the limit-key fields for the partitioned index exceeds the length imposed by the index manager |
-665 | the partition clause of an alter statement is omitted or invalid |
-666 | stmt-verb object cannot be executed because function is in progress |
-667 | the clustering index for a partitioned table space cannot be explicitly dropped |
-668 | the column cannot be added to the table because the table has an edit procedure defined with row attribute sensitivity |
-669 | the object cannot be explicitly dropped. reason reason-code |
-670 | the record length of the table exceeds the page size limit |
-671 | the bufferpool attribute of the table space cannot be altered as specified because it would change the page size of the table space |
-672 | operation drop not allowed on table table_name |
-676 | the physical characteristics of the index are incompatible with respect to the specified statement. the statement has failed. reason reason-code |
-677 | insufficient virtual storage for bufferpool expansion |
-678 | the constant specified for the index limit key must conform to the data type data-type of the corresponding column column-name |
-679 | the object name cannot be created because a drop is pending on the object |
-680 | too many columns specified for a table, view or table function |
-681 | column column-name in violation of installation defined field procedure. rt: return-code, rs: reason-code, msg: message-token |
-682 | field procedure procedure-name could not be loaded |
-683 | the specification for column, distinct type, function, or procedure data-item contains incompatible clauses |
-684 | the length of constant list beginning string is too long |
-685 | invalid field type, column-name |
-686 | column defined with a field procedure can not compare with another column with different field procedure |
-687 | field types incomparable |
-688 | incorrect data returned from field procedure, column-name, msgno |
-689 | too many columns defined for a dependent table |
-690 | the statement is rejected by data definition control support. reason reason-code |
-691 | the required registration table table-name does not exist |
-692 | the required unique index index-name for ddl registration table table-name does not exist |
-693 | the column column-name in ddl registration table or index name is not defined properly |
-694 | the schema statement cannot be executed because a drop is pending on the ddl registration table table-name |
-694 | the ddl statement cannot be executed because a drop is pending on the ddl registration table |
-695 | invalid value seclabel specified for security label column of table table-name |
-696 | the definition of trigger trigger-name includes an invalid use of correlation name or transition table name name. reason code=reason-code |
-697 | old or new correlation names are not allowed in a trigger defined with the for each statement clause. old_table or new_table names are not allowed in a trigger with the before clause. |
-747 | The table is not available. |
-803 | Duplicate key on insert or update. |
-804 | Error in input parameters for the SQL statement. |
-805 | DBRM or package not found in plan. |
-811 | More than one row retrieved in SELECT INTO. |
-818 | Plan and program: timestamp mismatch. |
-902 | Pointer to Essential Control Block(RDA/CT) has value 0, Rebind Required. This may also occur if one of the DB volumes is still present, but inaccessible. |
-904 | Unavailable resource. Someone else is locking your data. |
-911 | Deadlock or timeout. Rollback has been done. |
-913 | Deadlock or timeout. No rollback. |
-922 | Authorization needed. |
-924 | Db2 Connection internal error. |
-927 | The language interface was called but no connection had been made. |
-955 | Sort memory cannot be allocated to process the statement. |
-964 | The transaction log space is depleted or temporary increase in the number of active transactions. |
-970 | wrong (also missing) permissions on Db2 file systems / Db2 filesystems owned by root. |
-998 | Error occurred during transaction or heuristic processing. |
-2310 | The utility could not generate statistics |
-3508 | Load Error accessing scratch space. Error in accessing a file or path of type «TEMP_FILE» during load or load query. The scratch directory where the msg files in a load job are stored does not have the required permissions. |
-7008 | The object that is specified is not valid for the requested operation. |
-10330 | Permission denied to change database. Contact system administrator. |
-30090 | Remote operation invalid for application execution environment. |
[1]
Positive Values (Warnings)[edit]
+98 | A dynamic SQL statement ends with a semicolon |
+100 | Row not found or end of cursor. |
+222 | Trying to fetch a row within a DELETE statement. |
+223 | Trying to fetch a row within an UPDATE statement. |
+231 | FETCH after a BEFORE or AFTER but not on a valid row. |
+304 | A value with data type (data type 1) cannot be assigned to a host variable because the value is not within the range of the host variable in position (position number) with data type (data type 2) |
+354 | A rowset fetch statement may have returned one or more rows of data. however, one or more warning conditions were also encountered. use the get diagnostics statement for more information regarding the conditions that were encountered |
+562 | A grant of a privilege was ignored because the grantee already has the privilege from the grantor. |
+802 | The null indicator was set to -2 as an arithmetic. |
References[edit]
- ^ «IBM Docs».
- «DB2 Version 9.1 for z/OS Codes» (PDF). September 2013.
- «DB2 SQL CODE Description and solutions».
- «SQL codes». September 2013.
This page contains an Oracle SQLCODE list of errors that you may encounter when working with Oracle SQL. The codes are ANSI-standard, which means you should find them with all relational databases.s
The list of SQLCODE messages is shown below.