Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Brand-ArrayAug 26 2021

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

This post has been answered by Brand-Array on Aug 31 2021
Jump to Answer

Comments

Processing

Post Details

Added on Aug 26 2021
2 comments
2,238 views