I am trying to create procedure with this insert statement which otherwise working fine but when trying to incorporate this insert query into the procedure it giving me below error
Error(11,1): PL/SQL: SQL Statement ignored and
Error(16,17): PL/SQL: ORA-00904: "DBMS_RANDOM"."VALUE": invalid identifier
I need to run this proc for 10 months in order to replicate the data for 10 month in a loop, but I am stuck on step one, and proc is not getting compiled.
{
create or replace PROCEDURE datareplicationProcedure
(Start_date IN Date, End_date IN Date) AS
BEGIN
`insert statement`
insert into SAM_CDS.transactions
(TENANT_CD,TRANSACTION_KEY, BATCH_ID ,ACCT_CURR_AMOUNT, BATCH_DATE_TIME,DAY_SK,WEEK_SK, MONTH_SK )
(
Select A.TENANT_CD, A.TRANSACTION_KEY,A.BATCH_ID,A.acct_curr_amount,A.BATCH_DATE_TIME,D.DAY_SK,D.WEEK_SK, D.MONTH_SK
from (
( select TENANT_CD,'TRANSACTION_'||(DBMS_RANDOM.string('x',10))as TRANSACTION_KEY,BATCH_ID,acct_curr_amount ,
TO_DATE( TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(DATE '2021-07-01','J'),TO_CHAR(DATE '2021-07-31','J'))),'J') BATCH_DATE_TIME
from SAM_CDS.transactions where batch_date_time
between to_date('04-FEB-19') and to_Date('05-FEB-19')
)A
left outer join
(select * from SAM_CDS.DAY where DATETIME between Start_date and End_date)D
on D.DATETIME=A.BATCH_DATE_TIME )
);
END datareplicationProcedure;
}
It would appear that the owner of the procedure has not been given access to the dbms_random package via a direct grant. If the insert statement compiles outside of a stored procedure, the grant was most likely through a role rather than through a direct grant. Definer's rights stored procedures cannot make use of privileges granted through a role only those privileges that are granted directly to the user. You could change the procedure to be an invoker's rights stored procedure which can use permissions granted through a role but that would require that the role was available to the caller in every session where the procedure is invoked. More likely, you want to ask your DBA to grant access to the dbms_random to the procedure owner directly.