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;

A permissions problem with the filesystem directory? Can you execute it directly from your interface (sqldeveloper, sqlplus, sqlci)? — Jeff Holt, Feb 05 '20 at 18:51
@JeffHolt - a DROP DIRECTORY never touches the actual OS file system. The directory being dropped is just an object within the database ... an alias for a directory on the OS file system. — EdStevens, Feb 05 '20 at 19:19
@EdStevens True, but the OP said line 15 which is the create, not the drop. — Jeff Holt, Feb 05 '20 at 20:19
@JeffHolt - that applies to the create too. The OS side isn't evaluated until you try to use the directory (i.e. read/write) - which can be a bit confusing. The path isn't even validated when it's created. — Alex Poole, Feb 05 '20 at 20:23
Yes, I can run the statement just fine if I run it manually. — sois, Feb 05 '20 at 20:28
@JeffHolt - same applies to CREATE. It's just creating an object within the database that is nothing but an alias for an OS directory. The formatting limitations of comments here prevent posting a proof case, but it's easy enough. Just try to create a directory object referencing a non-existent OS directory. (Edit - I see Alex Poole beat me to the same observation). — EdStevens, Feb 05 '20 at 20:31
Solution was use varchar2 instead of nvarchar2. Execute immediate didn't like nvarchar2. TY all. — sois, Feb 05 '20 at 20:57

Alex Poole · Accepted Answer · 2020-02-05T20:33:10.300

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.

This is great! I tried this but made my v_stmt as a nvarchar2. It turns out, execute immediate doesn't like that! I change my original script variables to varchar2 and it works! Thank you! — sois, Feb 05 '20 at 20:35
Re: Or Replace and Drop: That's what Toad auto generates for some reason. I used the default script as my baseline. — sois, Feb 05 '20 at 20:36

score 0 · Answer 2 · answered Feb 05 '20 at 18:55

I prefer putting a command into a variable, display it (for verification) and then execute it:

SQL> declare
  2  v_path nvarchar2(256);
  3  v_object_exists number;
  4  l_str varchar2(200);
  5  begin
  6      -- Use the directory Oracle DB provide for tracing.
  7      select VALUE into v_path from V$DIAG_INFO where NAME = 'Diag Trace';
  8      --dbms_output.put_line(v_path);
  9
 10      -- Set up new directory!
 11      select count(*) into v_object_exists from all_objects where object_name = 'DIAG_TRACE' and object_type = 'DIRECTORY';
 12      if v_object_exists > 0 then
 13          execute immediate 'DROP DIRECTORY DIAG_TRACE';
 14      end if;
 15      l_str := 'CREATE OR REPLACE DIRECTORY DIAG_TRACE AS ' || chr(39) || v_path ||chr(39);
 16      dbms_output.put_line(l_str);
 17      execute immediate l_str;
 18  end;
 19  /
CREATE OR REPLACE DIRECTORY DIAG_TRACE AS
'C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace'

PL/SQL procedure successfully completed.

SQL>

Certainly, you have to run it as a privileged user (such as SYS).

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

2 Answers2