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
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(L_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/ anon block
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? any advice / help would be greatly appreaciated