Restriction for length of the sql query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Restriction for length of the sql query

  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Smile Restriction for length of the sql query

    Is there any restriction for length of the sql query (size of the query) in oracle?

    Any advice will be greatly appreciated

    Configuration is as follows


    1. Web server and oracle client on one machine,

    2. Oracle database on one machine.
    I am searching on the internate bou not getting any information.Please advice.
    Thank you,
    Minal

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Size of the SQL query, Never heard this question. I think there is no size limit, but why do u ask this? Are you going to right some SQL query which will be in GB?
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Mar 2008
    Posts
    6

    Smile

    Hi ,

    Actually one developer asked me about it. He wanted to know what will be the impact on memory at webserver and database server if he is increasing the length of the query as per requirement(customer requirement).

    Thanks for reply.


    Minal
    Last edited by minal; 12-21-2009 at 04:22 AM.

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    There would not be any noticeable difference on the web server or the database server.

    Web server would pass thsi string to the database server and database server needs to parse it once (I dobt it will take more then a few millisonds and some bytes of ram)

    regards

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by minal View Post
    Is there any restriction for length of the sql query (size of the query) in oracle?
    I suppose it should be some physical limit... taking into consideration all SQL statements show up on v$sqltext we just have to find out if there is a limit there.

    SQL statements on v$sqltext are organized in chunks -or pieces- of 64 characters each so we just have to find out how many "pieces" are allowed... "piece" column is of the NUMBER datatype which lets you store positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits so... just do the math.

    Good luck testing/troubleshooting such a monster
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Talking The sky is (or not) the limit

    Quote Originally Posted by OracleŽ Database Reference
    Stored Packages Maximum size: PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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