Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E25788-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
ARPLS231

26 DBMS_ASSERT

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

See Also:

Oracle Database PL/SQL Language Reference for more information about "Avoiding SQL Injection in PL/SQL"

This chapter contains the following topics:

ARPLS65375

Using DBMS_ASSERT

ARPLS65376

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.

ARPLS65377

Summary of DBMS_ASSERT Subprograms

ARPLS65378Table 26-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


ARPLS65379

ENQUOTE_LITERAL Function

This function enquotes a string literal.

Syntax

DBMS_ASSERT.ENQUOTE_LITERAL (
   str            VARCHAR2) 
RETURN VARCHAR2;

Parameters

ARPLS65380Table 26-2 ENQUOTE_LITERAL Function Parameters

Parameter Description

str

String to enquote


Usage Notes

ARPLS65381

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

ARPLS65382Table 26-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


ARPLS65383

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

ARPLS65384Table 26-4 NOOP Function Parameters

Parameter Description

str

Input value


ARPLS65385

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

ARPLS65386Table 26-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>] 
ARPLS65387

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

ARPLS65388Table 26-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.

ARPLS65389

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

ARPLS65390Table 26-7 SIMPLE_SQL_NAME Function Parameters

Parameter Description

str

Input value


Exceptions

ORA44003: string is not a simple SQL name

Usage Notes

ARPLS65391

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

ARPLS65392Table 26-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.

Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Page

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF