Oracle Generate Random Primary Key
Posted May 25, 2004
By Amar Kumar Padhi
Watch out for sequential Oracle GUIDs! (the GUID generator offered by Oracle) was returning sequentially-incremented values, rather than pseudo-random combinations of characters. Never, please never use random Guids as primary key for pretty large database. SELECT/UPDATE/DELETE will be OK, but INSERT will make DBMS to rebuild index. The JPA specification supports 4 different primary key generation strategies that generate the primary key values programmatically or use database features, like auto-incremented columns or sequences. The only thing you have to do is to add the @GeneratedValue annotation to your primary key attribute and choose a generation strategy. Jun 21, 2004 Hi, I am constructing the key as follows: KeyGenerator keyGenerator = KeyGenerator.getInstance('Blowfish'); keyGenerator.init(128); Key key = keyGenerator.generateKey; Now, I want to generate a new random key. Can you guys tell me how can I do that. Thanks in advance.
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. Forex tester 2 key generator torrent.
- 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)
E.g.: Generating a random number between 0 and 1.
E.g.: Generating a random number from a range, between 1 to 1000.
E.g.: Generating a 12 digit random number.
E.g.: Generating an upper case string of 20 characters
E.g.: Generating a lower case string of 20 characters
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.
E.g.: Generating an upper case alphanumeric string of 20 characters
E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
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.
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.
» See All Articles by ColumnistAmar Kumar Padhi
| Latest Forum Threads | |||
| Oracle Forum | |||
| Topic | By | Replies | Updated |
| Oracle Data Mining: Classification | jan.hasller | 0 | July 5th, 07:19 AM |
| Find duplicates - Unique IDs | Lava | 5 | July 2nd, 08:30 AM |
| no matching unique or primary key | rcanter | 1 | April 25th, 12:32 PM |
| Update values of one table based on condition of values in other table using Trigger | Gladiator | 3 | February 29th, 06:01 PM |
Oracle Random Number Example
Date: June 07, 2007 09:22PM
1 do {
2 $random = generate_random_primary_key();
3 mysql_query(check if $random exists in database);
4 if not { insert data row to MySQL, quit loop } // else continue with loop
5 } while $random is in database
However, I have a couple of concerns:
1. Wouldn't this be a little slow and database-intensive, especially when the database already contains a lot of entries as I'll be checking the primary key's existence every time I need to insert an entry? And worse, it'll be inside a loop that continues until a non-existing primary key is found (imagine a 'nearly full' database).
2. I'm concerned about multiple users doing inserts at the same time. How can I be sure that there won't be another user inserting another entry using the same 'random' primary key in between lines 3 and 4?
Any comments would be very much be welcomed!
Oracle Generate Random Primary Key 2017
Oracle Generate Random Number
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.