与其他命令一起使用时无效的CREATE命令 [英] Invalid CREATE command when it is used with another command

问题描述

我正在尝试使用该页面中的解决方案来打印查询结果.假脱机解决方案在我的项目中不起作用，但是另一个在sqldeveloper中工作.该页面中定义的"run_query"过程.问题在于它在c#代码中不起作用.代码是这样的:

I am trying to print query result using solution in that page. The spool solution didn't work in my project but the other one worked in sqldeveloper. "run_query" procedure defined in that page. The problem is that it doesn't work in c# code. The code is like that:

cmd.CommandText = string.Format(@"CREATE OR REPLACE DIRECTORY TEST_DIR AS '{0}';
             exec run_query('SELECT {1} FROM  {2} {3} ORDER BY {4}','TEST_DIR','{5}');"
             ,strDirForFiles.Substring(0, strDirForFiles.Length - 2), 
             strColToSelect, strTableName, strWhere, strColToSelect, strTMPFileName);
cmd.ExecuteNonQuery();

在运行时查询如下:

CREATE DIRECTORY TEST_DIR AS 'C:\\';
exec run_query('SELECT AccountNumber 
                FROM  LowCreditList WHERE VersionNumber=1 AND ChangeStatus = 0 
                ORDER BY AccountNumber','TEST_DIR','ASD.TMP'); 

但是该代码引发了这样的异常:

But that code throws an exception like that:

消息="ERROR [HY000] [Oracle] [ODBC] [Ora] ORA-00901:无效的CREATE 命令\ n"

Message = "ERROR [HY000] [Oracle][ODBC][Ora]ORA-00901: invalid CREATE command\n"

当我执行

CREATE DIRECTORY TEST_DIR AS 'C:\\';

命令，没有例外.

我不知道.它可以在sqldeveloper中使用，但不能在C#中使用.

I can not figure it out. It works in sqldeveloper but not in C#.

注意:我使用Visual Studio作为管理员.

NOTE: I use Visual Studio as administrator.

推荐答案

我不知道C#，但我假设您只能发送单个SQL语句或PL/SQL块，而不能发送由分号分隔的列表.无论如何，exec都不是(这是某些IDE支持的SQL * Plus快捷方式.)

I don't know C# but I assume you can only send individual SQL statements or PL/SQL blocks, not a list separated by semicolons. In any case, exec is neither (it's a SQL*Plus shortcut supported by some IDEs.)

您可能需要发送单独的命令，例如:

You probably need to send separate commands as something like:

create directory test_dir as 'C:\\'

和

begin run_query('SELECT {1} FROM  {2} {3} ORDER BY {4}','TEST_DIR','{5}'); end;

(注意PL/SQL使用分号，但是SQL没有本机语句终止符，尽管工具通常需要终止符，而分号通常是默认值.)

(Note PL/SQL uses semicolons but SQL has no native statement terminator, although tools often require a terminator character and semicolon is often the default.)

这篇关于与其他命令一起使用时无效的CREATE命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！