Why am I getting ORA-00900 Invalid SQL statement when trying to create a directory object with execute immediate?

Question

I see many examples of this being possible in Oracle. It's just not working for me. Oracle 11. I am getting this error on line 15. Thank you all!

declare 
v_path nvarchar2(256);
v_object_exists number;
begin
    -- Use the directory Oracle DB provide for tracing.
    select VALUE into v_path from V$DIAG_INFO where NAME = 'Diag Trace';
    --dbms_output.put_line(v_path);

    -- Set up new directory!
    select count(*) into v_object_exists from all_objects where object_name = 'DIAG_TRACE' and object_type = 'DIRECTORY';
    if v_object_exists > 0 then 
        execute immediate 'DROP DIRECTORY DIAG_TRACE'; 
    end if;
    dbms_output.put_line('CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ''' || v_path || '''');
    execute immediate 'CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ''' || v_path || '''';

end;

Answer 1

This appears to be a bug; though not one listed on the ORA-00900 reference note on MoS.

It doesn't like the path variable being concatenated in as part of the execute immediate. This fails:

v_path := '/some/path';
execute immediate 'CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ''' || v_path || '''';

but this is OK, even though the final statement is the same:

execute immediate 'CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ''/some/path''';

After some searching, it may be something to do with bug 7036176: "CONCATENATED DYNAMIC OBJECT NAME RAISES ORA-00900 IN 10G & 11G". It's not exactly the same but close. You'll need to look on My Oracle Support for further info, though there isn't much.

You can work around it with a variable:

declare
v_stmt varchar2(256);
v_path nvarchar2(256);
v_object_exists number;
begin
    -- Use the directory Oracle DB provide for tracing.
    select VALUE into v_path from V$DIAG_INFO where NAME = 'Diag Trace';
    --dbms_output.put_line(v_path);
    -- Set up new directory!
    select count(*) into v_object_exists from all_objects where object_name = 'DIAG_TRACE' and object_type = 'DIRECTORY';
    if v_object_exists > 0 then
        execute immediate 'DROP DIRECTORY DIAG_TRACE';
    end if;
    v_stmt := 'CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ''' || v_path || '''';
    dbms_output.put_line(v_stmt);
    execute immediate v_stmt;
end;
/

Which saves repeating the string to print it, though you might only have done that because of this issue.

Not sure why you're dropping first with or replace, incidentally.