CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ASSERT
Version 11.1
 
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;
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [52 users online]    © 2010 psoug.org