Random Numbers & Strings in Oracle

Generating random numbers and strings in Oracle

Do you know how to auto generate random numbers or strings in Oracle? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size--a very common requirement for websites that create and maintain logins for users.
Whatever the need, the fact is that Oracle provides us with a random number generator. This option is faster than writing your own random generation logic in PL/SQL as Oracle's internal processing logic is used. In addition, it can also be used to generate both character and alphanumeric strings.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the <ORACLE_HOME>/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.

• The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.Providing any other character will return the output in upper case only.
  The size of the string should also be provided as the second parameter.

Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.

Examples:

Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.

jaJA>declare
  2     l_num    number;
  3  begin
  4    l_num := dbms_random.random;
  5    dbms_output.put_line(l_num);
  6    dbms_random.seed('amar testing 67890');
  7    l_num := dbms_random.random;
  8    dbms_output.put_line(l_num);
  9  end;
 10  /
483791552
478774329

PL/SQL procedure successfully completed.

Conclusion

DBMS_RANDOM is a good utility and will find its way into lot of development projects, especially web based ones. However, this Package is not exhaustively documented. One should not use it just for the sake of it being there. Make sure that there is a true requirement or a necessity of random values before making use of this package. If you already have a custom code meant for the same purpose, check out the benefits that are available when using this package compared to your application.

Article Reference:
https://www.databasejournal.com/features/oracle/article.php/3341051/Generating-random-numbers-and-strings-in-Oracle.htm

PL/SQL Developer » Changing background color for different connections

Are you Looking for a way to set a different background color in the command window for a specific connection?


Is your intention is to have some sort of visual cue when you are connecting to the production database to prevent accidents?



Solution:

Yes! You can found the feature in:

Tools --> Preferences --> Appearance tab --> change the Connection Indicator settings

Oracle SQL: How to subtract 2 dates in oracle to get the result in hour and minute

Objective: To subtract 2 dates and represent the result in hour and minute in one decimal figure.

START_TIME          END_TIME            (END_TIME-START_TIME)*24    
------------------- ------------------- ------------------------      
21-06-2011 14:00:00 21-06-2011 16:55:00  2.9166667      
21-06-2011 07:00:00 21-06-2011 16:50:00  9.8333333      
21-06-2011 07:20:00 21-06-2011 16:30:00  9.1666667  

Required result (end_time-start_time) as below.

16:55- 14:00 = 2.55      
16:50-07:00 = 9.5      
16:30-7:20 = 9.1 and so on.   

Solution:

SQL> select start_date
     , end_date
     , (24 * extract(day from (end_date - start_date) day(9) to second))
      + extract(hour from (end_date - start_date) day(9) to second)
      + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR_MINUTE"
 from table;
/

START_DATE          END_DATE            HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00        2.55
21-06-2011 07:00:00 21-06-2011 16:50:00         9.5
21-06-2011 07:20:00 21-06-2011 16:30:00         9.1

Change Oracle Forms MDI Background Image and Splash Screen

Use existing virtual directories.
For. e.g. use the 
virtual directory /forms/html/ 
that maps to <ora10g-home>/tools/web/html

for 10g put your images in the directory: <ora10g-home>/tools/web/html

for 12c put your images in the directory:
Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\WLS_FORMS\tmp\_WL_user\formsapp_12.2.1\pgdzbo\war\html

and in your formsweb.cfg assign parameters as follows

splashScreen=/forms/html/<your-file>.gif or .jpg
background=/forms/html/<your-bg-file>.gif or .jpg

You can also check the virtual directory by using the URL
<your-server-name>/forms/html/<image-name>

Make sure that logo, background and splash screen image name must be mentioned in same case in formsweb.cfg file as the source image filename in Oracle 12c Forms