Solved

Using DBMS_RANDOM in Quick Reports

  • 26 February 2020
  • 9 replies
  • 320 views

Userlevel 7
Badge +20
  • Superhero (Partner)
  • 662 replies

Hi All,

 

Seems quick report SQL does not allow DBMS_RANDOM.VALUE.

Error Message:

Forbidden PLSQL code rejected: "DBMS_RANDOM"

FNDSQLFORBIDDEN: Forbidden PLSQL code rejected: "DBMS_RANDOM"

 

Is there an alternate to get a random value in a quick report?

 

Cheers!

icon

Best answer by eqbstal 26 February 2020, 21:38

View original

9 replies

Userlevel 6
Badge +15

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:

 

Userlevel 7
Badge +20

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 :sunglasses:

 

/DSJ

Userlevel 6
Badge +15

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. 
 

 

Userlevel 7
Badge +21

DSJ,

Is the following ‘allowed’ in your situation:

select regexp_substr(sys_guid(), '\d') from dual;

It is pseudo random, but it is quite simple to implement, I think.

Regards,
Steve

Userlevel 6
Badge +15

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


 

Userlevel 7
Badge +20

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!

Userlevel 6
Badge +15

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

 

Userlevel 7
Badge +18

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

 

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

Reply