Oracle和SQL Server中的长查询

I'm working on an app that will connect to either Oracle or SQL Server and builds up an SQL string to be executed on the server. What I've found is that my approach fails in Oracle when the SQL string exceeds ~4000 characters. I've got higher than 4000, but only slightly; I'm assuming Oracle ignores some whitespace/formatting characters.

我正在开发一个应用程序，它将连接Oracle或SQL Server，并构建在服务器上执行的SQL字符串。我发现，当SQL字符串超过~4000个字符时，我的方法在Oracle中失败。我比4000要高，但只有一点点;我假设Oracle忽略了一些空格/格式化字符。

Once I'm inside an Oracle Procedure, the VARCHAR2 I'm using has a max length of 32,767 - which I think should be more than enough, but I can't seem to get a VARCHAR2 passed into Oracle that is longer than ~4000.

一旦进入Oracle过程，我正在使用的VARCHAR2的最大长度为32,767——我认为这应该足够了，但我似乎无法将一个超过4000的VARCHAR2传递给Oracle。

Also, I'm trying to minimize the differences between Oracle and SQL Server as much as possible.

此外，我还在尽量减少Oracle和SQL Server之间的差异。

What are other approaches I could take to get past the 4,000 limit in both Oracle and SQL Server.

在Oracle和SQL Server中，我还可以采取哪些其他方法来克服这4000个限制。

EDIT

编辑

I agree that in many cases an SQL query that is 4,000 characters long is excessive and probably could be re-written better or that the underlying data might benefit from a restructuring. Often times, aliasing some tables is more than enough to get things going. But my goal is for any valid SQL query to return the same results through my app as you'd get through a robust tool like SQL Developer....because that's what the users say, 'Don't tell me my query is poorly written it works fine in $tool, but doesn't work in yours'

我同意，在许多情况下，一个长度为4,000个字符的SQL查询是过量的，可能会重新编写得更好，或者潜在的数据可能会从重组中受益。通常情况下，对某些表进行混叠就足够了。但我的目标是任何有效的SQL查询返回相同的结果通过我的程序,你会得到一个健壮的工具像SQL开发人员....因为用户是这么说的，不要告诉我我的查询写得很糟糕它在$tool上运行得很好，但是在您的工具上就不行

3 个解决方案

#1

the 32000 VARCHAR2 is only visible within PL/SQL (have a look @ this link http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7032414607769).

32000 VARCHAR2只在PL/SQL中可见(在这个链接http://asktom.oracle.com/pls/asktom/f?p=100:11:0::: P11_QUESTION_ID:7032414607769)。

The SQL VARCHAR2 is, as you have noted, limited to 4000 characters. Since you are using an outside product (ie not PL/SQL but .net) you are thus limited to 4000k. That is, if you decide to use varchar2.

正如您所注意到的，SQL VARCHAR2限制为4000个字符。由于您使用的是一个外部产品(即不是PL/SQL而是。net)，所以您只能使用4000k。也就是说，如果您决定使用varchar2。

to get around this problem you will need to use CLOB.

为了解决这个问题，你需要使用CLOB。

I am assuming that you are utilizing dbms_sql.parse to prepare your statement, this does indeed have an overload that would take a CLOB and you should be set:

我假设您正在使用dbms_sql。为了准备你的语句，它确实有一个重载，这会占用一个CLOB，你应该设置:

DECLARE
 sqlstr  CLOB;
 tCursor PLS_INTEGER;
 RetVal  NUMBER;
BEGIN
  sqlstr := 'SELECT * FROM DUAL';
  tCursor := dbms_sql.open_cursor;
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  RetVal := dbms_sql.execute(tCursor);
  dbms_sql.close_cursor(tCursor);
END;
/

#2

Use the TEXT instead the VARCHAR type.

使用文本代替VARCHAR类型。

#3

I might respectfully suggest that if 4,000 characters are needed for a query, perhaps the underlying data structure or the query approach is the real problem, not any arbitrary character limit.

我可能恭敬地建议，如果查询需要4000个字符，那么真正的问题可能是底层数据结构或查询方法，而不是任意的字符限制。

I'm not going to ask anyone to post a 4,000 character query, but perhaps you could give us "the nuts" and discuss it some?

我不会让任何人发布4000个字符的查询，但也许你可以给我们“坚果”，并讨论它?

#1