Forum Stats

  • 3,770,034 Users
  • 2,253,052 Discussions
  • 7,875,276 Comments

Discussions

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

user3975371
user3975371 Member Posts: 2 Green Ribbon

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

Best Answers

Answers