28 DBMS_ASSERT

The DBMS_ASSERT package provides an interface to validate properties of the input value.

Using DBMS_ASSERT

Operational Notes

Operational Notes

If the condition which determines the property asserted in a function is not met then a value error is raised. Otherwise the input value is returned through the return value. Most functions return the value unchanged, however, several functions modify the value.

Summary of DBMS_ASSERT Subprograms

Table 28-1 DBMS_APPLICATION_INFO Package Subprograms

Subprogram	Description
ENQUOTE_LITERAL Function	Enquotes a string literal
ENQUOTE_NAME Function	Encloses a name in double quotes
NOOP Functions	Returns the value without any checking
QUALIFIED_SQL_NAME Function	Verifies that the input string is a qualified SQL name
SCHEMA_NAME Function	Verifies that the input string is an existing schema name
SIMPLE_SQL_NAME Function	Verifies that the input string is a simple SQL name
SQL_OBJECT_NAME Function	Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object

ENQUOTE_LITERAL Function

This function adds leading and trailing single quotes to a string literal.

Syntax

DBMS_ASSERT.ENQUOTE_LITERAL (
   str            VARCHAR2) 
RETURN VARCHAR2;

Parameters

Table 28-2 ENQUOTE_LITERAL Function Parameters

Parameter	Description
`str`	String to enquote

Usage Notes

Verify that all single quotes except leading and trailing characters are paired with adjacent single quotes.
No additional quotes are added if the name was already in quotes.

ENQUOTE_NAME Function

This function encloses a name in double quotes.

Syntax

DBMS_ASSERT.ENQUOTE_NAME (
   str            VARCHAR2, 
   capitalize     BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;

Parameters

Table 28-3 ENQUOTE_NAME Function Parameters

Parameter	Description
`str`	String to enquote
`capitalize`	If `TRUE` or defaulted, alphabetic characters of `str` which was not in quotes are translated to upper case

Usage Notes

No additional quotes are added if the name was already in quotes.
Verify that all other double quotes in the string are adjacent pairs of double quotes.

NOOP Functions

This function returns the value without any checking.

Syntax

DBMS_ASSERT.NOOP (
   str      VARCHAR2 CHARACTER SET ANY_CS)
 RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

DBMS_ASSERT.NOOP (
   str      CLOB CHARACTER SET ANY_CS)
 RETURN     CLOB CHARACTER SET str%CHARSET;

Parameters

Table 28-4 NOOP Function Parameters

Parameter	Description
`str`	Input value

QUALIFIED_SQL_NAME Function

This function verifies that the input string is a qualified SQL name.

Syntax

DBMS_ASSERT.QUALIFIED_SQL_NAME (
   str      VARCHAR2 CHARACTER SET ANY_CS)
 RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

Parameters

Table 28-5 QUALIFIED_SQL_NAME Function Parameters

Parameter	Description
`str`	Input value

Exceptions

ORA44004: string is not a qualified SQL name

Usage Notes

A qualified SQL name <qualified name> can be expressed by the following grammar:

   <local qualified name> ::= <simple name> {'.' <simple name>}
   <database link name> ::= <local qualified name> ['@' <connection string>]
   <connection string> ::= <simple name>
   <qualified name> ::= <local qualified name> ['@' <database link name>]

SCHEMA_NAME Function

This function verifies that the input string is an existing schema name.

Syntax

DBMS_ASSERT.SCHEMA_NAME (
   str      VARCHAR2 CHARACTER SET ANY_CS)
 RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

Parameters

Table 28-6 SCHEMA_NAME Function Parameters

Parameter	Description
`str`	Input value

Exceptions

ORA44001: Invalid schema name

Usage Notes

By definition, a schema name need not be just a simple SQL name. For example, "FIRST LAST" is a valid schema name. As a consequence, care must be taken to quote the output of schema name before concatenating it with SQL text.

SIMPLE_SQL_NAME Function

This function verifies that the input string is a simple SQL name.

Syntax

DBMS_ASSERT.SIMPLE_SQL_NAME (
   str      VARCHAR2 CHARACTER SET ANY_CS)
 RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

Parameters

Table 28-7 SIMPLE_SQL_NAME Function Parameters

Parameter	Description
`str`	Input value

Exceptions

ORA44003: string is not a simple SQL name

Usage Notes

The input value must be meet the following conditions:
- The name must begin with an alphabetic character. It may contain alphanumeric characters as well as the characters _, $, and # in the second and subsequent character positions.
- Quoted SQL names are also allowed.
- Quoted names must be enclosed in double quotes.
- Quoted names allow any characters between the quotes.
- Quotes inside the name are represented by two quote characters in a row, for example, "a name with "" inside" is a valid quoted name.
- The input parameter may have any number of leading and/or trailing white space characters.
The length of the name is not checked.

SQL_OBJECT_NAME Function

This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.

Syntax

DBMS_ASSERT.SQL_OBJECT_NAME (
   str      VARCHAR2 CHARACTER SET ANY_CS)
 RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

Parameters

Table 28-8 SQL_OBJECT_NAME Function Parameters

Parameter	Description
`str`	Input value

Exceptions

ORA44002: Invalid object name

Usage Notes

The use of synonyms requires that the base object exists.