HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v3.0

Documentation:v3.0

Supported Versions:
Current(v5.0)/
v3.1/
v3.0/
v2.1/
v2.0

Other Versions:
v1.1

DBMS_RANDOM - Generating Random Data (Numbers, Strings and Dates) in MogDB with compat-tools


Introduction to compat-tools

Compat-tools is a set of compatibility tools. It aims to provide compatibility for necessary functions and system views tha are created for OSs migrated from other asynchronous databases to MogDB, thereby facilitating the follow-up system maintenance and application modification.


compat-tools Download

To install compat-tools, please download the tool of the latest version from https://gitee.com/enmotech/compat-tools.


Features of compat-tools

  1. runMe.sql: General scheduling script
  2. Oracle_Views.sql: Compatible with Oracle database data dictionaries and views
  3. Oracle_Functions.sql: Compatible with Oracle database functions
  4. Oracle_Packages.sql: Compatible with Oracle database packages
  5. MySQL_Views.sql: Compatible with MySQL database data dictionaries and views //TODO
  6. MySQL_Functions.sql: Compatible with MySQL database functions //TODO

MogDB Versions Supported By compat-tools

  • MogDB 2.0
  • MogDB 1.1

Installing and Using compat-tools

  1. Download compat-tools: https://gitee.com/enmotech/compat-tools

  2. Store the downloaded files to a customized directory (/opt/compat-tools-0902 is taken as an example in this article).

    [root@mogdb-kernel-0005 compat-tools-0902]# pwd
/opt/compat-tools-0902
[root@mogdb-kernel-0005 compat-tools-0902]# ls -l
total 228
-rw-r--r-- 1 root root   9592 Sep  2 14:40 LICENSE
-rw-r--r-- 1 root root      0 Sep  2 14:40 MySQL_Functions.sql
-rw-r--r-- 1 root root      0 Sep  2 14:40 MySQL_Views.sql
-rw-r--r-- 1 root root  41652 Sep  2 14:40 Oracle_Functions.sql
-rw-r--r-- 1 root root  34852 Sep  2 14:40 Oracle_Packages.sql
-rw-r--r-- 1 root root 125799 Sep  2 14:40 Oracle_Views.sql
-rw-r--r-- 1 root root   4708 Sep  2 14:40 README.md
-rw-r--r-- 1 root root    420 Sep  2 14:40 runMe.sql

  3. Switch to user omm.

    su - omm

  4. Run the following command (26000 is the port for connnecting the database).

    gsql -d mogdb -p 26000 -f /opt/compat-tools-0902/runMe.sql

Testing DBMS_RANDOM

Log In to the mogdb Database

[omm@mogdb-kernel-0005 ~]$ gsql -d mogdb -p 26000
gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

mogdb=#


SEED

The SEED procedure allows you to seed the pseudo-random number generator, making it more random. SEED is limited to binary integers or strings up to 2000 characters. If you want to consistently generate the same set of pseudo-random numbers, always use the same seed.

declare
BEGIN
  DBMS_OUTPUT.put_line('Run 1 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;

  DBMS_OUTPUT.put_line('Run 2 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;

END;
/
NOTICE:  Run 1 : seed=0
CONTEXT:  SQL statement "CALL dbms_output.put_line('Run 1 : seed=0')"
PL/pgSQL function inline_code_block line 3 at PERFORM
NOTICE:  i=1 : value=2.53745232429355
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=2 : value=7.749117821455
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=3 : value=1.86734489817172
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=4 : value=8.83418704243377
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=5 : value=6.19573155790567
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  Run 2 : seed=0
CONTEXT:  SQL statement "CALL dbms_output.put_line('Run 2 : seed=0')"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  i=1 : value=2.53745232429355
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=2 : value=7.749117821455
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=3 : value=1.86734489817172
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=4 : value=8.83418704243377
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=5 : value=6.19573155790567
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

VALUE

The VALUE function is used to produce random numbers with a specified range. When called without parameters it produce a number greater than or equal to 0 and less than 1, with 38 digit precision.

DECLARE
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('value= ' || DBMS_RANDOM.value());
  END LOOP;
END;
/
NOTICE:  value= .785799258388579
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .692194153089076
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .368766269646585
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .87390407640487
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .745095098391175
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE

If the parameters are used, the resulting number will be greater than or equal to the low value and less than the high value, with the precision restricted by the size of the high value.

declare
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100));
  END LOOP;
END;
/

NOTICE:  value(1,100)= 45.158544998616
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 36.0190920610912
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 73.5194435422309
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 26.7619780991226
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 40.035083710216
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

Use TRUNC or ROUND to alter the precision as required. For example, to produce random integer values between 1 and 10 truncate the output and add 1 to the upper boundary.

mogdb=# select TRUNC(DBMS_RANDOM.value(1,11)) ;

 trunc
-------

     6

(1 row)

mogdb=#

STRING

The STRING function returns a string of random characters of the specified length. The OPT parameter determines the type of string produced as follows:

  • 'u', 'U' - uppercase alpha characters
  • 'l', 'L' - lowercase alpha characters
  • 'a', 'A' - mixed case alpha characters
  • 'x', 'X' - uppercase alpha-numeric characters
  • 'p', 'P' - any printable characters

The LEN parameter, not surprisingly, specifies the length of the string returned.

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
  END LOOP;
END;
/

NOTICE:  string('x',10)= i5S6XOZxrA
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= HGvRm75w19
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= N9WsQGJl6l
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= hDlPevVgRb
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= ZdSd8x8RKx
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

Combine the STRING and VALUE functions to get variable length strings.

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))));
  END LOOP;
END;
/

NOTICE:  string('L',?)= kcyzowdxqbyzu
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= ohzpljyatsplqtbbus
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= hbrjsfeevoi
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= lfsapmytdamvwcw
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= pcvtxnwzomkqwpfzes
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

NORMAL

The NORMAL function returns random numbers in a normal distribution.

declare
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('normal= ' || DBMS_RANDOM.normal());
  END LOOP;
END;
/

NOTICE:  normal= .838851847718988
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.523612260373397
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.241931681458075
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.120847761874286
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= .360125112757284
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

RANDOM

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('random= ' || DBMS_RANDOM.random());
  END LOOP;
END;
/
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -1023930867
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= 1068572119
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= 95361253
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -712638729
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -1251059926
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

Generating Random Dates

There are no specific functions for generating random dates currently, but we can add random numbers to an existing date to make it random. The following example generates random dates over the next year.

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)));
  END LOOP;
END;
/

NOTICE:  date= 2021-10-06 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-05-09 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-04-07 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2021-11-29 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-06-04 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

By doing the correct divisions, we can add random numbers of hours, seconds or minutes to a date.

DECLARE
  l_hours_in_day NUMBER := 24;
  l_mins_in_day  NUMBER := 24*60;
  l_secs_in_day  NUMBER := 24*60*60;
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('hours= ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)));
  END LOOP;
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('mins = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)));
  END LOOP;
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('secs = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)));
  END LOOP;
END;
/
NOTICE:  hours= 2021-10-13 22:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-10-10 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-07 02:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-26 11:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-19 22:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  mins = 2021-09-04 00:01:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 11:56:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 00:53:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 00:21:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 12:38:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  secs = 2021-09-04 00:10:28
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:15:31
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:09:07
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:06:54
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:06:32
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#

Generating Random Data

The DBMS_RANDOM package is useful for generating random test data. You can generate large amounts quickly by combining it into a query.

mogdb=# CREATE TABLE random_data (
  id           NUMBER,
  small_number NUMBER(5),
  big_number   NUMBER,
  short_string VARCHAR2(50),
  long_string  VARCHAR2(400),
  created_date DATE,
  CONSTRAINT random_data_pk PRIMARY KEY (id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "random_data_pk" for table "random_data"
CREATE TABLE
mogdb=#
mogdb=# INSERT INTO random_data
SELECT generate_series(1,29999),
       TRUNC(DBMS_RANDOM.value(1,5)) AS small_number,
       TRUNC(DBMS_RANDOM.value(100,10000)) AS big_number,
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS short_string,
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(100,400))) AS long_string,
       TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date;
INSERT 0 29999
mogdb=#
mogdb=#  select count(*) from random_data;
 count
-------
 29999
(1 row)

mogdb=#

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.