25 DBMS_ASSERT

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 via return value. Most functions return the value unchanged, however, several functions modify the value.

Summary of DBMS_ASSERT Subprograms

---

ENQUOTE_LITERAL Function

This function enquotes a string literal.

Syntax

DBMS_ASSERT.ENQUOTE_LITERAL (
   str            VARCHAR2) 
RETURN VARCHAR2;

Parameters

Usage Notes

• Add leading and trailing single quotes to a string literal.

• Verify that all single quotes except leading and trailing characters are paired with adjacent single 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

---

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

---

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

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

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

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

Exceptions

ORA44002: Invalid object name