| General |
|
Note: Provides functions which assert various properties of the input value. 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. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsasrt.sql |
| First Available |
10.2 |
| Dependencies |
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_ASSERT'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_ASSERT';
|
| Exceptions |
| Number |
Description |
| -44001 |
INVALID_SCHEMA_NAME |
| -44002 |
INVALID_OBJECT_NAME |
| -44003 |
INVALID_SQL_NAME |
| -44004 |
INVALID_QUALIFIED_SQL_NAME |
|
| |
| ENQUOTE_LITERAL |
Enquote a string literal. Add leading and trailing single quotes to a string
literal |
dbms_assert.enquote_literal(str IN VARCHAR2)
RETURN VARCHAR2; |
SELECT dbms_assert.enquote_literal('SERVERS')
FROM dual;
SELECT dbms_assert.enquote_literal('"SERVERS"')
FROM dual; |
| |
| ENQUOTE_NAME |
This function encloses a name in double quotes |
dbms_assert.enquote_name(
str IN VARCHAR2,
capitalize IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2; |
SELECT dbms_assert.enquote_name('SERVERS')
FROM dual;
SELECT dbms_assert.enquote_name('"SERVERS"')
FROM dual; |
| |
| NOOP |
Returns the value without any checking
Overload 1 |
dbms_assert.noop(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET; |
SELECT dbms_assert.noop('SERVERS')
FROM dual; |
| Overload 2 |
dbms_assert.noop(str IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET str%CHARSET; |
| TBD |
| |
| QUALIFIED_SQL_NAME |
Verify that the input string is a qualified SQL name |
dbms_assert.qualified_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET; |
SELECT dbms_assert.qualified_sql_name('UWCLASS.SERVERS1')
FROM dual;
SELECT dbms_assert.qualified_sql_name('UWCLASS.1SERVERS')
FROM dual; |
| |
| SCHEMA_NAME |
Verifies that the input string is an existing schema name |
dbms_assert.schema_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET; |
SELECT dbms_assert.schema_name('UWCLASS')
FROM dual;
SELECT dbms_assert.schema_name('UWCLASZ')
FROM dual;
CREATE TABLE user_pwd (
username VARCHAR2(30),
password VARCHAR2(30));
INSERT INTO user_pwd VALUES ('UWCLASS', 'UWCLASS');
INSERT INTO user_pwd VALUES ('MORGAN', 'AceDir');
CREATE OR REPLACE PROCEDURE ckpwd (usr IN VARCHAR2, pwd IN VARCHAR2) IS
v_query VARCHAR2(100);
v_output PLS_INTEGER;
BEGIN
v_query := q'{SELECT COUNT(*) FROM user_pwd}' || ' ' ||
q'{WHERE username = '}' || dbms_assert.schema_name(usr) ||
q'{' AND password = '}' || pwd || q'{'}';
dbms_output.put_line(CHR(10) || 'Built the following statement: ' ||
CHR(10) || v_query);
EXECUTE IMMEDIATE v_query INTO v_output;
dbms_output.put_line(CHR(10) || usr || ' is authenticated');
dbms_output.put_line(TO_CHAR(v_output));
EXCEPTION
WHEN DBMS_ASSERT.INVALID_SCHEMA_NAME THEN
dbms_output.put_line(CHR(10) || ' access denied');
END ckpwd;
/
set serveroutput on
exec ckpwd('UWCLASS', 'UWCLASS');
exec ckpwd('MORGAN', 'ACEDIR'); |
| |
| SIMPLE_SQL_NAME |
Verifies that the input string is a simple SQL name |
dbms_assert.simple_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET; |
SELECT dbms_assert.simple_sql_name('SERVERS1')
FROM dual;
SELECT dbms_assert.simple_sql_name('1SERVERS')
FROM dual; |
| |
| SQL_OBJECT_NAME |
|
This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object |
dbms_assert.sql_object_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET; |
SELECT
dbms_assert.sql_object_name('UWCLASS.SERVERS')
FROM dual;
SELECT dbms_assert.sql_object_name('UWCLASS.SERVERZ')
FROM dual; |
