How to Execute DDL with Dynamic Bind Variable (Doc ID 1296076.1)

Last updated on AUGUST 29, 2017

Applies to:

PL/SQL - Version 11.2.0.1 and later
Information in this document applies to any platform.

Goal

When trying to execute a DDL statement with a dynamic text it fails with both DBMS_UTILITY.EXEC_DDL_STATEMENT and EXECUTE IMMEDIATE.

No error when filename is hard coded:

DECLARE
 run_statement VARCHAR2(500);
BEGIN
 run_statement := 'CREATE SMALLFILE TABLESPACE kcdev01 LOGGING DATAFILE ''/testcases/k.dbf'' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M';
 dbms_utility.exec_ddl_statement(run_statement);
END;
/

PL/SQL procedure successfully completed.

Error occurs when variable is used:

DECLARE
 g_filename VARCHAR2(500) :='/testcases/k.dbf';
 run_statement VARCHAR2(500);
BEGIN
 run_statement := 'CREATE SMALLFILE TABLESPACE kcdev01 LOGGING DATAFILE '||g_filename||' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M';
 dbms_utility.exec_ddl_statement(run_statement);
END;
/
DECLARE
*
ERROR at line 1:
ORA-02236: invalid file name
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 9

Execute immediate fails with the same error:

DECLARE
 g_filename VARCHAR2(500) := '/testcases/k.dbf';
 run_statement VARCHAR2(500);
BEGIN
 run_statement := 'CREATE SMALLFILE TABLESPACE kcdev01 LOGGING DATAFILE :1 SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M';
 execute immediate run_statement using g_filename;
END;
/
DECLARE
*
ERROR at line 1:
ORA-02236: invalid file name
ORA-06512: at line 9

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms