| General Information |
| 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 (336) |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_ASSERT'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_ASSERT'; |
| Exceptions |
| Number |
Description |
| ORA-44001 |
INVALID_SCHEMA_NAME |
| ORA-44002 |
INVALID_OBJECT_NAME |
| ORA-44003 |
INVALID_SQL_NAME |
| ORA-44004 |
INVALID_QUALIFIED_SQL_NAME |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
| 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; |
set serveroutput on
DECLARE
table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
table_name := dbms_assert.enquote_literal(table_name);
dbms_output.put_line(table_name);
END;
/ |
| |
| ENQUOTE_NAME |
| This function encloses a name in double quotes |
dbms_assert.enquote_name(
str IN VARCHAR2,
capitalize IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2; |
set serveroutput on
DECLARE
table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
table_name := dbms_assert.enquote_name(table_name);
dbms_output.put_line(table_name);
END;
/ |
| |
| NOOP |
Returns the value without any checking.
This should be used only for proof-of-concept where the use of other DBMS_ASSERT functionality is being considered.
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; |
set serveroutput on
DECLARE
table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
table_name := dbms_assert.qualified_sql_name(table_name);
dbms_output.put_line(table_name);
END;
/
DECLARE
table_name user_tables.table_name%TYPE := 'BEGIN GRANT DBA TO myuser END;';
BEGIN
dbms_output.put_line(table_name);
table_name := dbms_assert.qualified_sql_name(table_name);
dbms_output.put_line(table_name);
END;
/ |
| |
| 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_asset.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; |
