为什么我在尝试使用立即执行创建目录对象时收到 ORA-00900 无效的 SQL 语句?

Question

我在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;

Answer 1

这似乎是一个错误;尽管没有在 MoS 的 ORA-00900 参考说明中列出。

它不喜欢将路径变量连接为execute immediate的一部分。此操作失败：

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

但这没关系，即使最后的陈述是相同的：

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

经过一番搜索，它可能与错误7036176有关："串联的动态对象名称在10G和11G中引发ORA-00900"。它不完全相同，但很接近。您需要查看我的 Oracle 支持以获取更多信息，尽管没有太多信息。

您可以使用变量来解决它：

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

这样可以节省重复字符串以打印它，尽管您可能只是因为此问题才这样做。

不知道为什么你先用or replace掉下来，顺便说一下。

Answer 2

我更喜欢将命令放入变量中，显示它(用于验证(，然后执行它：

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:ORACLEXEAPPORACLEdiagrdbmsxexetrace'
PL/SQL procedure successfully completed.
SQL>

当然，您必须以特权用户(例如 SYS(身份运行它。