Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    Unanswered: executing dynamic sql that is > 32767 characters

    in a nutshell..more details to follow:

    i'm part of team that is converting a sql server and ASP app to run on Oracle...the system basically allows users to create adhoc queries/reports. we're running into some issues when the underlying query that the user creates is greater than 32767 characters.

    i'm assuming that clobs cannot be used for dynamic sql execution [or is that wrong?]

    right now we're opening a refcursor in this fashion:
    OPEN RC1 FOR...statement

    which is subject to the char limit

    also hitting the same issue with calling EXECUTE

    so, my question...how can i rewrite this to accept queries bigger than 32767 chars...AND/OR is there something in DBMS_SQL that i've missed that could make my life easier here?

    please help...i can provide some code if needed, but i'm hoping this more of a how-to than a debugging question.

    THANKS!

    EDIT: using Oracle 9i (9.2.0.5)
    Last edited by aglio412; 04-27-04 at 10:45.

  2. #2
    Join Date
    Aug 2001
    Posts
    66
    If I recall correctly the cheap and dirty way to support this up to 64k is to do...
    Code:
    OPEN cursor FOR 32k_string || 32k_string;
    ...which I think also works in DBMS_SQL.PARSE, however you *have* missed something in DBMS_SQL - it has an overload of parse which accepts a PL/SQL table of VARCHAR2 (DBMS_SQL.VARCHAR2S) which avoids this restriction altogether.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  3. #3
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    thanks i'm going to look into that

Posting Permissions

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