DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Maximum Size of Sql query

  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Talking Maximum Size of Sql query

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  4. #4
    Join Date
    Apr 2003
    Posts
    2

    Thumbs up Does it holds good with simple sql query( or statements) also.

    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

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Within Begin...End, a simple SQL can be written. So the limitation is applicable to a simple SQL statement also.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width