SQL & PL/SQL

Hi,

   I have oracle database 12c 12.2.0.1 and created a common user, granted some privileges to it and created a stored procedure for that user, that basically iterates over all pdbs, executing alter session set container=xx, and then executes some commands on that pdb, in this example just selects from v$datafile.

  When i execute the procedure, i returns error ORA-00942: table or view does not exist. Either if i execute it as sys or as the user. If i create the same procedure in user sys, then it executes without problem. I think it might be related with some privileges but dont find which one.

create user c##demo identified by tiger;

grant connect to c##demo container=all;

grant set container to c##demo container=all;

grant read, write on directory MY_DIR to c##demo;

alter user c##demo set container_data=all container=current;

GRANT SELECT ON V_$CONTAINERS TO C##demo CONTAINER=ALL;

GRANT SELECT ON V_$DATAFILE TO C##demo CONTAINER=ALL;

create or replace procedure c##demo.chk_test is

--create a file pointer type variable

v_os_touch_file UTL_FILE.FILE_TYPE;

in_file VARCHAR2(50);

dbrole VARCHAR2(20);

CURSOR pdbs IS

  SELECT name, 'alter session set container='||NAME sentencia

  FROM v$containers

  WHERE OPEN_MODE='READ WRITE';

CURSOR test IS

  SELECT name, bytes

  FROM v$datafile;

BEGIN

SELECT to_char(sysdate,'YYYY-MM-DD-hh24miss') || '-test.txt'

into in_file

from dual;

--associate file pointer variable to a file and open it for write operation

v_os_touch_file := UTL_FILE.FOPEN('MY_DIR', in_file, 'a');

--write to the file

FOR i IN pdbs LOOP

    execute immediate i.sentencia;

   FOR j IN test LOOP

           UTL_FILE.PUT_LINE(v_os_touch_file, i.name || '|' || j.name || '|' || j.bytes);

   END LOOP;

END LOOP;

execute immediate 'ALTER SESSION SET CONTAINER=CDB$ROOT';

--close the file pointer

UTL_FILE.FCLOSE(v_os_touch_file);

END chk_test;

/

SQL> connect / as sysdba

Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCL1                          READ WRITE NO

         4 ORCL2                          READ WRITE NO

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> exec c##demo.chk_test

BEGIN c##demo.chk_test; END;

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "C##DEMO.CHK_TEST", line 13

ORA-06512: at "C##DEMO.CHK_TEST", line 28

ORA-06512: at "C##DEMO.CHK_TEST", line 28

ORA-06512: at line 1

SQL> show con_name

CON_NAME

------------------------------

ORCL2

Any suggestion?

thanks