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

[Quoted] 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 Received on Thu Feb 21 2002 - 21:21:03 CET