Using DBMS_RANDOM in Quick Reports

Put it in a package?

Header:

 FUNCTION getRandom(val_ IN NUMBER, val2_ IN NUMBER) RETURN NUMBER;

Body: 

FUNCTION getRandom(val_ IN NUMBER, val2_ IN NUMBER) RETURN NUMBER IS
 random_ NUMBER;
 BEGIN
 random_ := DBMS_RANDOM.value(val_, val2_);
 RETURN random_;
END getRandom;

Quick Report: 

Output:

Thanks @CallumW!

Creating a new package is something we avoid in a customer environment due to licensing, version handling and permission setup issues. I was wondering if this can be solve using already existing things 

/DSJ

I can’t see any other way around it, other than by having a customisation to get RnD to take out the code which is blocking it. 
 

This is an alternative, but it’s limited to the set ranges 

SELECT substr(to_char(systimestamp, 'FF6'), 6, 6) FROM dual; -- Random from 0 to 9
SELECT substr(to_char(systimestamp, 'FF6'), 5, 6) FROM dual; -- Random from 0 to 99
SELECT substr(to_char(systimestamp, 'FF6'), 4, 6) FROM dual; -- Random from 0 to 999
SELECT substr(to_char(systimestamp, 'FF6'), 3, 6) FROM dual; -- Random from 0 to 9999
SELECT substr(to_char(systimestamp, 'FF6'), 2, 6) FROM dual; -- Random from 0 to 99999
SELECT to_char(systimestamp, 'FF6') FROM dual; -- Random from 0 to 999999

Thanks both for the answers.

I put both answers to test and here’s the result

Quick report SQL

SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')   FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual

result

I read somewhere that systimestamp    precision varies with DB settings and it could be the reason I’m getting same value with the systimestamp?

Therefore I choose answer by @eqbstal 

Thanks both!

dsj wrote:

Thanks both for the answers.

I put both answers to test and here’s the result

 

Quick report SQL

 SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')   FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual 
union all 
SELECT to_char(systimestamp, 'FF6'),regexp_substr(sys_guid(), '\d')  FROM dual

 

result

 

I read somewhere that systimestamp    precision varies with DB settings and it could be the reason I’m getting same value with the systimestamp?

 

Therefore I choose answer by @eqbstal 

 

Thanks both!

Makes sense. When I run regexp_substr(sys_guid(), '\d') I only get 9 returned every time

A) You can use a custom logical unit (rnd.zip in rnd.docx).

1. Create a logical unit: MyRnd
2. Create a persistent field (numeric): p
3. Create a read only field (numeric): Rng
      a) argument: t.cf$_p
      b) select statment: select dbms_random.value +:cf$_p -:cf$_p from dual
  4) Then Approve & Publish LU

In report
select ifsapp.my_rnd_clp.Get_Cf$_Rnd(0) from dual

B)  You can use a IAL (my_rnd.ial.txt)

SELECT x.rnd FROM ifsinfo.my_rnd x

This will return a pseudo-random number between 0 and 1.

SELECT TO_NUMBER(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / POWER(2, 128) FROM DUAL