0

I want to create a new column based on an existing column plus some uniform random numbers.

Data

-- borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` float unsigned NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', '1.24546'),
  ('2', '1', '1.245546546'),
  ('1', '2', '1.25654546'),
  ('1', '3', '1.2421323546');

Based on the OracleSQL documentation here, I tried:

SELECT id, rev, content,
  content + DBMS_RANDOM.VALUE AS content2
FROM docs

There obviously is no "expected output" here, given the randomness, but I hope the schema + code are sufficiently clear to demonstrate what I am trying to achieve

safex
  • 2,398
  • 17
  • 40

2 Answers2

2

Try this

SELECT id, rev, content,
  content + DBMS_RANDOM.VALUE(1,10) AS content2
FROM docs
dsk
  • 1,863
  • 2
  • 10
  • 13
  • This throws `ORA-00904: "DMBS_RADNOM"."VALUE": invalid identifier` – safex Jul 15 '20 at 08:00
  • Probably because there's no DBMS_RA**DN**OM, but DBMS_RA**ND**OM. – Littlefoot Jul 15 '20 at 08:02
  • 1
    This could be due to Insufficient user priviledges. Don't have access to the dbms_random package. ..................You need to GRANT EXECUTE to your schema. Login as your SYS user and run the following, replacing 'your-schema-name' with the Oracle user who you wish to allow run DBMS_RANDOM. GRANT EXECUTE ON DBMS_RANDOM TO your-schema-name; – dsk Jul 15 '20 at 08:03
  • @Littlefoot that was a typo. The problem still exists. – safex Jul 15 '20 at 08:14
1

You have to use the start value and end value as the parameters in the dbms_random function.

SELECT id, rev, content,
  content + DBMS_RANDOM.VALUE(1, 20) AS content2  -- 1 - Start value, 20 - End value
FROM docs;

But not sure why you wanted to select random numbers and add it to your original value. If that is your requirement, above is the query

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53