Oracle Generate Random Primary Key

admin

The DBMS_RANDOM package provides a built-in random number generator.

This chapter contains the following topics:

    • Operational notes

Note:

DBMS_RANDOM

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. May 25, 2004  The DBMSRANDOM 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 ORACLEHOME/rdbms/admin directory. I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. If the number generated by function is unique and does not exists in database a separate procedure will insert it into database. Jan 10, 2020  This article talks how to create table in Oracle,primary key,Foreign keys,create table syntax in oracle with Examples.This will be very useful for Oracle DBA’ s and Developer both.They play with it many times in the day and an good knowledge can definitely help them expedite the task.They often get confused the datatype and what to use in what circumstances. 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. Summary: in this tutorial, you will learn how to use Oracle PRIMARY KEY constraint to manage the primary key of a table. Introduction to the primary key. A primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.

is not intended for cryptography.

Using DBMS_RANDOM

Operational notes

  • The RANDOM function produces integers in the range [-2^^31, 2^^31).

  • The VALUE function produces numbers in the range [0,1) with 38 digits of precision.

DBMS_RANDOM can be explicitly initialized but does not require initialization before a call to the random number generator. It automatically initializes with the date, user ID, and process ID if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it produces the same result in both cases.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling an overload of SEED. To produce different output for every run, simply omit the seed call. Then the system chooses a suitable seed for you.

Summary of DBMS_RANDOM subprograms

Table 6-1 DBMS_RANDOM package subprograms

SubprogramDescription

Initializes the package with a seed value.

Returns random numbers in a normal distribution.

Generates a random number.

Resets the seed.

Gets a random string.

Terminates package.

One version gets a random number greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal point (38-digit precision). The other version gets a random Oracle Database number x, where x is greater than or equal to a specified lower limit and less than a specified higher limit.


Note:

The INITIALIZE procedure, RANDOM function and TERMINATE procedure are deprecated. They are included in this release for legacy reasons only.

Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

  • The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

INITIALIZE procedure

This procedure is deprecated. Although currently supported, it should not be used. It initializes the random number generator.

Parameters

Table 6-2 INITIALIZE procedure parameters

ParameterDescription

val

Seed number used to generate a random number


Usage notes

This procedure is obsolete as it simply calls the SEED procedure.

NORMAL function

This function returns random numbers in a standard normal distribution.

Return value

The random number, a NUMBER value

RANDOM function

This procedure is deprecated. Although currently supported, it should not be used. It generates and returns a random number.

Return value

A random BINARY_INTEGER value greater than or equal to -power(2,31) and less than power(2,31)

Usage notes

See the NORMAL function and the VALUE function. Rsa private and public key generator javascript library.

SEED procedure

This procedure resets the seed used in generating a random number.

Parameters

Table 6-3 SEED procedure parameters

ParameterDescription

val

Seed number or string used to generate a random number


Usage notes

The seed can be a string up to length 2000.

STRING function

This function generates and returns a random string.

Parameters

Table 6-4 STRING function parameters

ParameterDescription

opt

What the returning string looks like:

  • 'u', 'U' - Returning string is in uppercase alpha characters.

  • 'l', 'L' - Returning string is in lowercase alpha characters.

  • 'a', 'A' - Returning string is in mixed-case alpha characters.

  • 'x', 'X' - Returning string is in uppercase alpha-numeric characters.

  • 'p', 'P' - Returning string is in any printable characters.

Otherwise the returning string is in uppercase alpha characters.

len

Length of the returned string


Return value

A VARCHAR2 value with the random string

TERMINATE procedure

Oracle Generate Random Integer

This procedure is deprecated. Although currently supported, it should not be used. It would be called when the user is finished with the package.

Key

VALUE function

One version returns a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). The other version returns a random Oracle Database NUMBER value x, where x is greater than or equal to the specified low value and less than the specified high value.

Parameters

Oracle Generate Random Primary Key Of Life

Table 6-5 VALUE function parameters

ParameterDescription

low

Lower limit of the range in which to generate a random number

high

Upper limit of the range in which to generate a random number


Return value

A NUMBER value that is the generated random number


Primary Key Generation Using Oracle's Sequence

Oracle provides the sequence utility to automatically generate unique primary keys. To use this utility to auto-generate primary keys for a CMP entity bean, you must create a sequence table and use the @AutomaticKeyGeneration annotation to point to this table.

In your Oracle database, you must create a sequence table that will create the primary keys, as shown in the following example:

Add Primary Key Oracle

This creates a sequences of primary key values, starting with 1, followed by 2, 3, and so forth. The sequence table in the example uses the default increment 1, but you can change this by specifying the increment keyword, such as increment by 3. When you do the latter, you must specify the exact same value in the cacheSize attribute of the @AutomaticKeyGeneration annotation:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see below.

Key

Primary Key Generation Using SQL Server's IDENTITY

In SQL Server you can use the IDENTITY keyword to indicate that a primary-key needs to be auto-generated. The following example shows a common scenario where the first primary key value is 1, and the increment is 1:

In the CMP entity bean definition you need to specify SQLServer(2000) as the type of automatic key generator you are using. You can also provide a cache size:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see below.

Oracle Generate Random Primary Keyboard

Primary Key Generation Using a Named Sequence Table

Oracle Select Primary Key

A named sequence table is similar to the Oracle sequence functionality in that a dedicated table is used to generate primary keys. However, the named sequence table approach is vendor-neutral. To auto-generate primary keys this way, create a named sequence table using the two SQL statements shown in the example:

In the CMP entity bean definition you need to specify the named sequence table as the type of automatic key generator you are using. You can also provide a cache size:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see the next section.

Note. When you specify a cacheSize value for a named sequence table, a series of unique values are reserved for entity bean creation. When a new cache is necessary, a second series of unique values is reserved, under the assumption that the first series of unique values was entirely used. This guarantees that primary key values are always unique, although it leaves open the possibility that primary key values are not necessarily sequential. For instance, when the first series of values is 10..20, the second series of values is 21-30, even if not all values in the first series were actually used to create entity beans.

Defining the CMP Entity Bean

When defining a CMP entity bean that uses one of the primary key generators, you use the the @AutomaticKeyGeneration annotation to point to the name of the primary key generator table to obtain primary keys. Also, you must define a primary key field of type Integer or Long to set and get the auto-generated primary key. However, the ejbCreate method does not take a primary key value as an argument. Instead the EJB container adds the correct primary key to the entity bean record.

The following example shows what the entity bean might look like. Notice that the bean uses the named sequence option described above, and that ejbCreate method does not take a primary key:

Oracle Generate Random Primary Key Excel

Related Topics