Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trouble with very long sql string for an Oracle procedure....need suggestions

Re: Trouble with very long sql string for an Oracle procedure....need suggestions

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 21 Feb 2002 22:19:30 +0100
Message-ID: <f1pa7u4lak48evljn5lfi0of2in3pkl9lm@4ax.com>


On 21 Feb 2002 12:21:03 -0800, kgrigg_at_diamonddata.com (Kelly) wrote:

>Hi all,
>
>I am dynamically creating a string 'v_sql' that I need to use to
>create
>a HTML table. I had previously used 'htp.cellsprint' which can take
>either
>a query, or a cursor (it's number). I have used the query...however,
>it
>is expecting a string of type VARCHAR2.
>
>My v_sql string is over 8000 long. So, with VARCHAR2 only being
>4000...this
>is out. So, thought I'd try to create a cursor...tried doing a REF
>CURSOR,
>opening it with the v_sql...it blows up. Thought about usine
>DBMS_SQL. However
>the DBMS_SQL.PARSE part...once again, is expecting VARCHAR2.
>
>Now, I looked around and found something about using a type for extra
>long
>strings called dbms_sql.varchar2s. That didn't seem to work either.
>It
>blew up complaining about the concatention (||'s) in my string...(can
>someone explain this datatype?)
>
>I tried declaring v_sql as a CLOB, it too complained about the
>concatenations.
>
>Here is the v_sql with is part of a large select statement...this is
>just the
>part in the LOOP which builds the largest part of it...
>LOOP
>...
>
>v_sql := v_sql||'decode(generic_structure.check_date(to_date('||start_date||'
>'||v_hour_txt||':'||v_minute_txt||' '||v_day_night||',''MON DD, YYYY
>HH:MI AM''),a.room_id),''open'',''Open'',''booked'',''Booked'')
>'||v_hour||':'||v_minute_txt||' '||v_day_night;
>
>...
>
>END LOOP;
>
>etc......
>
>I know the v_sql is correct...when I did it as a LONG variable..I was
>able
>to send it to the browser with a htp.p call....so, I have verified to
>the
>best of my ability that this is a valid select statement...
>
>
>Any suggestions or pointers greatly appreciated...
>
>Kelly

The maximum length of a varchar2 in pl/sql is 32767 bytes, 8 times as much as 4000

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Feb 21 2002 - 15:19:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US