Long Queries in Oracle and 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 cha开发者_StackOverflowracters.

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.

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

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

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'

---

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

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.

to get around this problem you will need to use 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:

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

---

Use the TEXT instead the VARCHAR type.

---

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.

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?