문제 설명
즉시 실행으로 디렉토리 객체를 생성하려고 할 때 ORA‑00900 Invalid SQL 문을 받는 이유는 무엇입니까? (Why am I getting ORA‑00900 Invalid SQL statement when trying to create a directory object with execute immediate?)
저는 Oracle에서 이것이 가능한 많은 예를 봅니다. 그것은 나를 위해 작동하지 않습니다. Oracle 11. 15행에서 이 오류가 발생합니다. 모두 감사합니다!
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;
참조 솔루션
방법 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.
방법 2:
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).
(by sois、Alex Poole、Littlefoot)