The CHECK_PARAMETER stored procedure can be used to validate the existence of various types of database objects as well as check the values of various other parameters that are often used within the IN_DB_UTIL framework.
List of supported database objects that have an owner and require the format of OWNER.OBJECT_NAME (FQN)
- CONSTRAINT
- INDEX
- PACKAGE
- SEQUENCE
- TABLE
- TRIGGER
List of supported database objects that do not require an owner and use the format of OBJECT_NAME
- SCHEMA
- TABLESPACE
List of supported IN_DB_UTIL framework parameters
- BATCH_SIZE
- BULK_LIMIT
- IDENTIFIER
- LOGGING
- MAX_MINUTES
- PARALLEL
- SYNC_UPGRADE
- VALIDATE_FK
- VERBOSE
| Parameter | Description | |
|---|---|---|
| CHECK_TYPE | Type: | INPUT |
| Datatype: | VARCHAR2(30) | |
| Default Value: | NULL | |
| Description | Specifies the parameter type being checked. | |
| Options | See the list of supported parameters above. | |
| CHECK_VALUE | Type: | INPUT |
| Datatype: | VARCHAR2(257) | |
| Default Value: | NULL | |
| Description | Specifies the value of the parameter type being checked. | |
| Options | Certain parameters are restricted to specific values. | |
| CHECK_STATUS | Type: | OUTPUT |
| Datatype: | NUMBER | |
| Default Value: | NULL | |
| Description |
Returned status regarding the parameter check results. The returned status depends on the type of object that is checked. For database objects it returns the count of objects found. |
|
| Options |
0 = Object does not exist 1 = Object exists 2 = Ambiguous Object Name (more than one) For parameter checks that are not database objects then 0 = Success (Valid parameter value) 1 = Error (Invalid parameter value) Invalid conditions for any of the parameter checks -1 = Invalid check which could be any of the exceptions listed in the Exceptions section below. Refer to the CHECK_MESSAGE output for details. |
|
| CHECK_MESSAGE | Type: | OUTPUT |
| Datatype | VARCHAR2(4000) | |
| Default Value: | NULL | |
| Description | Returned message regarding the parameter check results. | |
Exceptions
- INVALID_CHECK_TYPE – The specified CHECK_TYPE is unsupported.
- INVALID_OBJECT – Either an improper value or no value was specified for CHECK_VALUE.
- INVALID_FQN – The CHECK_TYPE requires a fully qualified name but was not provided.
- INVALID_NUMBER – An invalid value was specified for a parameter expecting a numeric value.
- INVALID_IDENTIFIER
- INVALID_SQL_NAME – An invalid qualified SQL name was provided for the object type.
- OTHERS
Examples
IN_DB_UTIL.CHECK_PARAMETER('TABLE', 'INUSER.IN_DOC', V_CHECK_STATUS, V_CHECK_MESSAGE);
IN_DB_UTIL.CHECK_PARAMETER('CONSTRAINT', 'INUSER.PK_DOC', V_CHECK_STATUS, V_CHECK_MESSAGE);
IN_DB_UTIL.CHECK_PARAMETER('INDEX', 'INUSER.DOC_IDX2', V_CHECK_STATUS, V_CHECK_MESSAGE);
IN_DB_UTIL.CHECK_PARAMETER('TABLESPACE', 'DATA', V_CHECK_STATUS, V_CHECK_MESSAGE);
Example output
SET SERVEROUTPUT ON;
DECLARE V_CHECK_TYPE VARCHAR2(30); -- Parameter Type
V_CHECK_VALUE VARCHAR2(257); -- Parameter Value
V_CHECK_STATUS NUMBER;
V_CHECK_MESSAGE VARCHAR2(500);
BEGIN
V_CHECK_TYPE := 'INDEX';
V_CHECK_VALUE := 'inuser.DOC_IDX2';
IN_DB_UTIL.CHECK_PARAMETER
(
V_CHECK_TYPE, V_CHECK_VALUE, V_CHECK_STATUS, V_CHECK_MESSAGE
);
DBMS_OUTPUT.PUT_LINE('CHECK_STATUS = '||V_CHECK_STATUS);
DBMS_OUTPUT.PUT_LINE('CHECK_MESSAGE = '||V_CHECK_MESSAGE);
END;
/
CHECK_STATUS = 1
CHECK_MESSAGE = The INUSER.DOC_IDX2 index exists.