Maximum Size of Sql query

[Anurag_2003]

Hi Gurus...


I need to fire a very large Sql query for some report generation.

Now I want to know wot is the maximum size of sql statement(in bytes or characters) that can be executed in Oracle

Does it depend on any buffer size ?
Is this size configurable ?


Anybody here who can put some light on this topic.

Thanx & Regards
Anurag

[chrisrlong]

Hmmmm....
Well, I've run dynamic SQL statements that were 32K (the maximum string size in PL/SQL).

As for direct statements, I'm not sure.

- Chris

[tamilselvan]

The source code (PL/SQL) limitations are:
Upto 7.3.4, it is 64K
From 8.0 upto 8.1.3.4, it is 128K.
From 8.1.3.4 , it is 256M.

[Anurag_2003]

Thnx chrisrlong & tamilselvan for the information.



tamilselvan,

Wot all limitations u have mentioned are for PL/SQL.
Does it holds good with simple sql query( or statements) also.

Plzz clarify.




Regards
Anurag

[tamilselvan]

Within Begin...End, a simple SQL can be written. So the limitation is applicable to a simple SQL statement also.

[slimdave]

Let me refer you gentlemen to the Oracle documentation, specifically the excellent Oracle 9iR2 Reference with which we should all be passingly familiar.

Maximum SQL Statement length is 64Kb.

Found it in 27 seconds.

[jmodic]

Originally posted by tamilselvan
The source code (PL/SQL) limitations are:
Upto 7.3.4, it is 64K
From 8.0 upto 8.1.3.4, it is 128K.
From 8.1.3.4 , it is 256M.

Those are only *very roughly estimated limits* of maximal PL/SQL source code length. PL/SQL compiler is actualy not limited by the size of source code, its actual limit (regarding the size of the code it has to compile) is the size of the "parse tree" it builts out of the source code. And the corelation between the size of the source code and its parse tree can only be very roughly estimated. So for example, with 7.3 you could easily compile PL/SQL object that has much more source code than 128K, but you won't be able to compile another PL/SQL object that is much smaler than 128K.

[slimdave]

The PL/SQL User guide appendix E has some useful information on PL/SQL limits.

It suggests that the approximate limit on PL/SQL package spec, object type spec, standalone subprogram, or anonymous block is 6,000,000 lines of code.