Search code examples
databaseoracle-databaseoracle19c

DBMS_RANDOM installed by default or not?

I need to execute a SQL script to create a function. This function uses DBMS_RANDOM.STRING method. It throws an error like

Sentence in SQL Function:

SELECT DBMS_RANDOM.STRING('U', CODELENGTH) INTO CODEGEN FROM DUAL;

In DEV environment the script works perfectly and the function its created without compilation errors. But in preProd environment the Indus team always provide me error messages about this function. Finally seems that this class (DBMS_RANDOM) it's not installed.

Error message:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/5     PL/SQL: SQL Statement ignored
15/12    PL/SQL: ORA-00904: "DBMS_RANDOM"."STRING": invalid identifier

It's DBMS_RANDOM class in a package/library which should be installed ? If the answer it's YES, there is any QUERY to obtain installed packages/libraries?

We are using Oracle 19c in all environments.

Solution

  • DBMS_RANDOM is there; I presume that it's just that your user in preProd database doesn't have required privileges. It would certainly have helped if you posted which error you got. Talk to DBA and ask them to

    grant execute on dbms_random to your_username;