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

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

Within PL/SQL, varchar2 can be 32767 characters long.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."