![[Contents]](FIGURE/CONTENT.GIF)
Nonstop Database, HiRDB Version 9 SQL Reference
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
GET DIAGNOSTICS (Retrieve diagnostic information)
Function
If the SQL statement that was executed immediately before is any of the following, this statement acquires relevant error information and diagnostic information from the diagnostic area:
When recreating an SQL object in a routine, information on the successfully recreated routine is included in this diagnostic information.
Format
GET DIAGNOSTICS
{:embedded-variable=statement-information-item-name
[,:embedded-variable=statement-information-item-name]...
|EXCEPTION condition-number
:embedded-variable=each-item-of-condition-name
[,:embedded-variable=each-item-of-condition-name]...}
statement-information-item-name::={NUMBER | MORE}
each-item-of-condition-name::={RETURNED_SQLCODE
|ERROR_POSITION
|ERROR_SQL_NO
|ERROR_SQL
|ROUTINE_TYPE
|ROUTINE_SCHEMA
|ROUTINE_NAME
|TRIGGER_SCHEMA
|TRIGGER_NAME
|CONSTRAINT_SCHEMA
|CONSTRAINT_NAME
|MESSAGE_TEXT
|QUERY_NAME
|CONDITION_IDENTIFIER}
Operands
Specifies in an embedded variable the ordinal number of diagnostic information to be obtained.
Table 6-2 Character strings that are set in ERROR_SQL
| Classification (SQL type) | Whether character string is set | Character string that is set | ||||
|---|---|---|---|---|---|---|
| Routine | Other | |||||
| Define | Execute | Pre-process | Execute | |||
| Definition SQL | ALTER INDEX | N | N | Y | Y | ALTER INDEX |
| ALTER PROCEDURE | N | N | Y | Y | ALTER PROCEDURE | |
| ALTER ROUTINE | N | N | Y | Y | ALTER ROUTINE | |
| ALTER TABLE | N | N | Y | Y | ALTER TABLE | |
| ALTER TRIGGER | N | N | Y | Y | ALTER TRIGGER | |
| COMMENT (annotate a table) | N | N | Y | Y | COMMENT TABLE | |
| COMMENT (annotate a column) | N | N | Y | Y | COMMENT COLUMN | |
| CREATE AUDIT | N | N | Y | Y | CREATE AUDIT | |
| CREATE CONNECTION SECURITY | N | N | Y | Y | CREATE CONNECTION SECURITY | |
| CREATE FUNCTION | N | N | Y | Y | CREATE FUNCTION | |
| CREATE PUBLIC FUNCTION | N | N | Y | Y | CREATE FUNCTION | |
| CREATE INDEX | N | N | Y | Y | CREATE INDEX | |
| CREATE PROCEDURE | N | N | Y | Y | CREATE PROCEDURE | |
| CREATE PUBLIC PROCEDURE | N | N | Y | Y | CREATE PROCEDURE | |
| CREATE SCHEMA | N | N | Y | Y | CREATE SCHEMA | |
| CREATE SEQUENCE | N | N | Y | Y | CREATE SEQUENCE | |
| CREATE TABLE | N | N | Y | Y | CREATE TABLE | |
| CREATE TRIGGER | N | N | Y | Y | CREATE TRIGGER | |
| CREATE TYPE | N | N | Y | Y | CREATE TYPE | |
| CREATE VIEW | N | N | Y | Y | CREATE VIEW | |
| CREATE PUBLIC VIEW | N | N | Y | Y | CREATE VIEW | |
| DROP AUDIT | N | N | Y | Y | DROP AUDIT | |
| DROP CONNECTION SECURITY | N | N | Y | Y | DROP CONNECTION SECURITY | |
| DROP DATA TYPE | N | N | Y | Y | DROP DATA TYPE | |
| DROP FUNCTION | N | N | Y | Y | DROP FUNCTION | |
| DROP PUBLIC FUNCTION | N | N | Y | Y | DROP FUNCTION | |
| DROP INDEX | N | N | Y | Y | DROP INDEX | |
| DROP PROCEDURE | N | N | Y | Y | DROP PROCEDURE | |
| DROP PUBLIC PROCEDURE | N | N | Y | Y | DROP PROCEDURE | |
| DROP SCHEMA | N | N | Y | Y | DROP SCHEMA | |
| DROP SEQUENCE | N | N | Y | Y | DROP SEQUENCE | |
| DROP TABLE | N | N | Y | Y | DROP TABLE | |
| DROP TRIGGER | N | N | Y | Y | DROP TRIGGER | |
| DROP VIEW | N | N | Y | Y | DROP VIEW | |
| DROP PUBLIC VIEW | N | N | Y | Y | DROP VIEW | |
| GRANT CONNECT | N | N | Y | Y | GRANT CONNECT | |
| GRANT DBA | N | N | Y | Y | GRANT DBA | |
| GRANT RDAREA | N | N | Y | Y | GRANT RDAREA | |
| GRANT SCHEMA | N | N | Y | Y | GRANT SCHEMA | |
| GRANT access privileges | N | N | Y | Y | GRANT ACCESS | |
| GRANT AUDIT | N | N | Y | Y | GRANT AUDIT | |
| REVOKE CONNECT | N | N | Y | Y | REVOKE CONNECT | |
| REVOKE DBA | N | N | Y | Y | REVOKE DBA | |
| REVOKE RDAREA | N | N | Y | Y | REVOKE RDAREA | |
| REVOKE SCHEMA | N | N | Y | Y | REVOKE SCHEMA | |
| REVOKE access privileges | N | N | Y | Y | REVOKE ACCESS | |
| Data manipulation SQL | ALLOCATE CURSOR statement | N | N | N | Y | ALLOCATE CURSOR |
| ASSIGN LIST statement | N | N | Y | Y | ASSIGN LIST | |
| CALL statement | Y | Y | Y | Y | CALL | |
| CLOSE statement | Y | Y | N | N | CLOSE | |
| N | N | Y | Y | (See the SQL types specified in the cursor) | ||
| DEALLOCATE PREPARE statement | N | N | N | Y | -- | |
| DECLARE CURSOR | Y | N | N | N | DECLARE CURSOR | |
| N | N | Y | Y | (See the SQL types for the dynamic SELECT statement) | ||
| DELETE statement | Y | Y | Y | Y | DELETE | |
| Preparable dynamic DELETE statement: positioning | N | N | Y | Y | DELETE | |
| DESCRIBE statement | N | N | Y | Y | (See the SQL types specified in the DESCRIBE statement) | |
| DESCRIBE CURSOR statement | N | N | Y | Y | (See the SQL types specified in the DESCRIBE CURSOR statement) | |
| DESCRIBE TYPE statement | N | N | Y | Y | (See the SQL types specified in the DESCRIBE TYPE statement) | |
| DROP LIST statement | N | N | Y | Y | DROP LIST | |
| EXECUTE statement | N | N | Y | Y | (See the SQL types executed by the EXECUTE statement) | |
| EXECUTE IMMEDIATE statement | N | N | Y | Y | (See the SQL types executed by the EXECUTE IMMEDIATE statement) | |
| FETCH statement | Y | Y | N | N | FETCH | |
| N | N | Y | Y | (See the SQL types specified in the cursor) | ||
| FREE LOCATOR statement | N | N | Y | Y | FREE LOCATOR | |
| INSERT statement | Y | Y | Y | Y | INSERT | |
| OPEN statement | Y | Y | N | N | OPEN | |
| N | N | Y | Y | (See the SQL types specified in the cursor) | ||
| PREPARE statement | Y | Y | Y | Y | (See the SQL types specified in the PREPARE statement) | |
| PURGE TABLE statement | Y | Y | Y | Y | PURGE TABLE | |
| Single-line SELECT statement | Y | Y | Y | Y | SELECT | |
| Dynamic SELECT statement | N | N | Y | Y | SELECT | |
| UPDATE statement | Y | Y | Y | Y | UPDATE | |
| Preparable dynamic UPDATE statement: positioning | N | N | Y | Y | UPDATE | |
| Assignment statement | Y | Y | Y | Y | SET | |
| Control SQL | CALL COMMAND statement | Y | Y | Y | Y | CALL COMMAND |
| COMMIT statement | Y | Y | Y | Y | COMMIT | |
| CONNECT statement | N | N | N | N | -- | |
| DISCONNECT statement | N | N | N | N | -- | |
| LOCK statement | Y | Y | Y | Y | LOCK TABLE | |
| ROLLBACK statement | Y | Y | Y | Y | ROLLBACK | |
| SET SESSION AUTHORIZATION statement | N | N | Y | Y | SET SESSION AUTHORIZATION | |
| Embedded language grammar | BEGIN DECLARE SECTION | N | N | N | N | -- |
| END DECLARE SECTION | N | N | N | N | (Not applicable) | |
| ALLOCATE CONNECTION HANDLE | N | N | N | N | -- | |
| FREE CONNECTION HANDLE | N | N | N | N | -- | |
| DECLARE CONNECTION HANDLE SET | N | N | N | N | (Not applicable) | |
| DECLARE CONNECTION HANDLE UNSET | N | N | N | N | (Not applicable) | |
| GET CONNECTION HANDLE | N | N | N | N | -- | |
| COPY | N | N | N | N | (Not applicable) | |
| GET DIAGNOSTICS | N | N | N | N | -- | |
| COMMAND EXECUTE | N | N | N | N | -- | |
| SQL prefix | N | N | N | N | (Not applicable) | |
| SQL terminator | N | N | N | N | (Not applicable) | |
| WHENEVER | N | N | N | N | -- | |
| SQLCODE variable | N | N | N | N | (Not applicable) | |
| SQLSTATE variable | N | N | N | N | (Not applicable) | |
| PDCNCTHDL type variable declaration | N | N | N | N | (Not applicable) | |
| INSTALL JAR | N | N | Y | Y | INSTALL JAR | |
| REPLACE JAR | N | N | Y | Y | REPLACE JAR | |
| REMOVE JAR | N | N | Y | Y | REMOVE JAR | |
| INSTALL CLIB | N | N | Y | Y | INSTALL CLIB | |
| REPLACE CLIB | N | N | Y | Y | REPLACE CLIB | |
| REMOVE CLIB | N | N | Y | Y | REMOVE CLIB | |
| DECLARE AUDIT INFO SET | N | N | N | N | (Not applicable) | |
| Routine control SQL | SQL variable declaration | Y | Y | N | N | DECLARE |
| Cursor declaration | Y | N | N | N | DECLARE CURSOR | |
| Y | Y | N | N | SELECT | ||
| Condition declaration | Y | N | N | N | DECLARE CONDITION | |
| Handler declaration | Y | N | N | N | DECLARE HANDLER | |
| SQL procedure statement | Y | Y | N | N | (See the SQL types specified in the SQL procedure statement) | |
| Compound statement | Y | Y | N | N | BEGIN | |
| IF statement | Y | Y | N | N | IF | |
| LEAVE statement | Y | Y | N | N | LEAVE | |
| RETURN statement | Y | Y | N | N | RETURN | |
| WHILE statement | Y | Y | N | N | WHILE | |
| FOR statement | Y | Y | N | N | FOR | |
| WRITE LINE statement | Y | Y | N | N | WRITE LINE | |
| SIGNAL statement | Y | Y | N | N | SIGNAL | |
| RESIGNAL statement | Y | Y | N | N | RESIGNAL | |
| Other than the above | Y | Y | Y | Y | Single-byte space | |
Common rules
Example
Obtain the following diagnostics information for a previously executed CREATE FUNCTION statement:
![[Figure]](FIGURE/ZU6S000A.GIF)
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.