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;
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.