Dynamically building DBMS_CLOUD.CREATE_EXTERNAL_TABLE, Execute Immediate doesn't work

Question

I'm facing an issue when I'm trying to build a table dynamically using DBMS_CLOUD.CREATE_EXTERNAL_TABLE inside a stored procedure or packaged program. A dbms_ouput.put_line of the dynamic code will produce the correct code which i can copy and run in a new session no problem, but not inside the PROC or PACKAGE. Error: Development - ORA-06550: line 1, column 1: PLS-00201: identifier 'DBMS_CLOUD' must be declared

select *
  from USER_SYS_PRIVS; 

username privilege              admin_option
TEST     CREATE RULE SET             NO
TEST     CREATE TABLE                NO
TEST     CREATE EVALUATION CONTEXT   NO
TEST     SELECT ANY DICTIONARY       NO
TEST     CREATE JOB                  NO
TEST    CREATE RULE NO

Test PROC

create or replace procedure test
 AS
 
l_cloud_statement clob;
L_error_msg       varchar2(4000);


begin

        l_cloud_statement:= 'BEGIN'||CHR(13)||
                                                'DBMS_CLOUD.CREATE_EXTERNAL_TABLE('||CHR(13)||
                                                'table_name => ''TESTING_EXT_TAB'','||CHR(13)||
                                                'credential_name => ''TEST_OBJ_STORE'','||CHR(13)||
                                                'file_uri_list => ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/rks6fos4/b/bucketuploads/o/testing_tab.csv'','||CHR(13)||
                                                'format => json_object(''delimiter'' value '','',''skipheaders'' value ''1''),'||CHR(13)||
                                                'column_list => ''col1  varchar2(200)'');'||chr(13)||                                        
                                                'END;';


dbms_output.put_line(l_cloud_statement);

execute IMMEDIATE l_cloud_statement;

exception
 WHEN OTHERS THEN
    L_error_msg := SQLERRM;
    TEST_SYSTEM.log_error(GP_error_msg,
                                'TEST',         -- program
                                'DEV',                              -- error type
                                '0001',                          -- error code
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                1);


end;

OUPUT CODE that works when executed in a new session

BEGIN

DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name => 'TESTING_EXT_TAB',
credential_name => 'TEST_OBJ_STORE',
file_uri_list =>
'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/rks6fos4/b/bucketuploads/o/testing_tab.csv',
format => json_object('delimiter' value ',','skipheaders' value '1'),
column_list => 'col1  varchar2(200)');
END;

Any idea what I'm missing or doing incorrectly?

Answer 1

You are probably hitting a definer's rights versus invoker's rights problem and need to add AUTHID CURRENT_USER to the procedure declaration.

SQL statements and anonymous PL/SQL blocks run with invoker's rights by default, which means they can use all of the direct privileges, system privileges, and role privileges. PL/SQL objects, such as a procedure, run with definer's rights by default, which means they cannot use role privileges. (I believe this is because roles can be selectively enabled or disabled, so when you compile a procedure you can't know for sure what roles will be enabled when it runs.)

If DBMS_CLOUD is only granted to your user through a role, you will need to recompile the PL/SQL object with invoker's rights, like this:

create or replace procedure test authid current_user is
...