Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Limit on SQL Query String's length?

    Is there a maximum permissible length for a SQL query in Oracle? I have a query (dynamic query) which goes beyond 6000 characters and this query fails during execution.

    Could someone let me know whether there is a limit and if yes, how to find and modify the limit?


  2. #2
    Join Date
    Mar 2007
    Not in the matter of length, only the number of its operations. This figure is sufficiently high for most commands. The error message would reveal it to you, but as you did not post it here, I cannot say if you hitted it.
    When (ab)using dynamic execution of the query in PL/SQL (execute immediate), you are limited to the VARCHAR2 limit, which is 32767 bytes.
    Again, as I do not know the failure reason, I cannot say more (as I hate guessing).

  3. #3
    Join Date
    Aug 2004
    PL/SQL User's Guide and Reference

    Native dynamic SQL using the EXECUTE IMMEDIATE and OPEN-FOR statements is faster and requires less coding than the DBMS_SQL package. However, the DBMS_SQL package should be used in these situations:

    * There is an unknown number of input or output variables, such as the number of column values returned by a query, that are used in a dynamic SQL statement (Method 4 for dynamic SQL).
    * The dynamic code is too large to fit inside a 32K bytes VARCHAR2 variable.
    You can use DBMS_SQL .


    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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