Use PL/SQL to generate random passwords, and plsql to generate

Source: Internet
Author: User

Use PL/SQL to generate random passwords, and plsql to generate
1. Create a Function program

CREATE OR REPLACE FUNCTION random_password(in_template IN VARCHAR2) RETURN VARCHAR2 IS  l_criteria VARCHAR2(1);  l_password VARCHAR2(500);  l_pattern  VARCHAR2(500);  l_indx    NUMBER;BEGIN  /*1-Character should be UPPERCASE     =====>  [U]  2- Character should be LOWERCASE      =====>  [L]  3- Character should be NUMBER         =====>  [N]  4- Character should be any character     =====>  [A]  5- Character should be NON-ALPHANUMERIC character =====>  [S]*/  l_criteria := '';  l_password := '';  FOR i IN 1 .. length(in_template) LOOP    l_criteria := substr(in_template,                         i,                         1);    IF upper(l_criteria) = 'U' THEN      l_pattern := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';    ELSIF upper(l_criteria) = 'L' THEN      l_pattern := q'[abcdefghijklmnopqrstuvwxyz]';    ELSIF upper(l_criteria) = 'N' THEN      l_pattern := q'[0123456789]';    ELSIF upper(l_criteria) = 'A' THEN      l_pattern := q'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';    ELSIF upper(l_criteria) = 'S' THEN      l_pattern := q'[~!@#$%^&*()_+-}{|":;?.,<>[]/\]';    ELSE      l_pattern := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789]';    END IF;    l_indx    := trunc(length(l_pattern) * dbms_random.value) + 1;    l_password := l_password || substr(l_pattern,                                       l_indx,                                       1);  END LOOP;  RETURN l_password;END random_password;

2. Usage

Input parameters:
U represents a random uppercase letter.
L represents a random lowercase letter.
N represents a random number.
A Represents any character
S represents any special symbol

Example 1:
SQL> select random_password ('ulnasn') from dual; RANDOM_PASSWORD('ULNASN')--------------------------------------------------------------------------------Hc67|8

Example 2:
SQL> select random_password ('99999999999') from dual; RANDOM_PASSWORD('99999999999')--------------------------------------------------------------------------------agrXKng6bfe


Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.