APEX

Greetings,

I am constructing HTML into a CLOB in a PL/SQL routine which I later display in an APEX application. The HTML can get large, because I am constructing a grid over an image, and the grid can have a lot of columns and rows which means there can be a lot of <area shape="rect" HTML lines. I did a search and found that another person had the same problem, because there is a 32K limit with HTML contents. That was very helpful.

Thing is - I have another (maybe similar) issue. The procedure that constructs the HTML gets a numeric error when the number of cells in the grid reach a certain point. I checked and this happens when the CLOB in the procedure gets close to 32K. Sorry, it difficult to know exactly when it fails, but the most I have been able to put into the CLOB is 32,852 characters. Therefore my question - is there in internal limit in PL/SQL (or some other Oracle aspect) that is limited to 32K? The coluimn in the table that contains the HTML is a CLOB and the variable in the PL/SQL that the HTML is parsed into is a CLOB, so I thought I was good to go since the CLOB limit is 4GB. But, it looks like I may be missing something.

We are running Oracle 11i and APEX 4.0.2.00.07. If other system info is needed let me know and I will get that for you.

Also, if it helps. The code from the procedure is copied below. v_html is the variable that holds the HTML. The table column for the HTML is imagemap_html. Both v_html and imagemap_html are defined as CLOB's.

Thanks, Tony

= = = = = = =

create or replace
procedure create_grid (p_action IN VARCHAR2) as 

    v_wcount integer := 0;    
    v_wmax integer := 20;
    v_width integer := 720;
    v_w1 integer := 0;
    v_w2 integer := 0;
    v_winc integer := 0;
    v_wstart integer := 0;
    v_wend integer := 0;    
    
    v_hcount integer := 0;
    v_hmax integer := 10;
    v_height integer := 520;
    v_h1 integer := 0;
    v_h2 integer := 0;
    v_hinc integer := 0;
    v_hstart integer := 0;
    v_hend integer := 0;    
    
    v_cell_row integer := 0;
    v_cell_col integer := 0;
    v_cell_title varchar2(10) := NULL;

    v_whitespace varchar2(10) := NULL;
    v_url_1 varchar2(100) := NULL; 
    v_url_2 varchar2(100) := NULL;
    v_url_3 varchar2(100) := NULL; 
    
    v_brand_id integer := 0;
    v_division_id integer := 0;
    v_plant_id integer := 0;
    v_model_id integer := 0;
    v_acc_group integer := 0;
    v_accessory integer := 0;
    v_acc_grp integer := 0;
    v_acc integer := 0;
    v_station_id integer := 0;
    v_substation_id integer := 0;      
    v_image_id integer := 0;
    
    v_id integer := 0;   
    v_html clob;
  begin
  v_brand_id := v('P4_BRAND_ID');
  v_division_id :=v('P4_DIVISION_ID');
  v_plant_id := v('P4_PLANT_ID');
  v_model_id := v('P4_MODEL_ID');
  v_acc_group := v('P4_ACC_GROUP');
  v_accessory := v('P4_ACCESSORY');
  v_station_id := v('P4_STATION_ID');
  v_substation_id := v('P4_SUBSTATION_ID');
  v_image_id := v('P4_IMAGES_ID');
  v_wmax := v('P4_COLUMNS');
  v_hmax := v('P4_ROWS');
  v_wstart := v('P4_XSTART');
  v_hstart := v('P4_YSTART');
  v_wend := v('P4_XEND');
  v_hend := v('P4_YEND');  
  v_whitespace := v('P4_WHITESPACE'); 

if p_action = 'INSERT' then
-- insert the row now, so that the cell table rows can be inserted with the correct FK  
insert into IM_TEMPLATE_DRAFT 
(plant_id, brand_id, division_id, model_id, acc_group, accessory, station_id, 
substation_id, image_id)
values
(v_plant_id,v_brand_id,v_division_id,v_model_id,v_acc_group,v_accessory,v_station_id,v_substation_id,v_image_id); 
commit;
end if;

-- get the id of the row that was just inserted 
select header_id into v_id from  IM_TEMPLATE_DRAFT where
plant_id=v_plant_id and brand_id=v_brand_id and division_id=v_division_id and 
model_id=v_model_id and acc_group=v_acc_group and accessory=v_accessory and
station_id=v_station_id and substation_id=v_substation_id and image_id=v_image_id;

-- remove all the cell rows for the draft, they will be created anew
delete from qcis_draft_cells where draft_id = v_id;
  
  BEGIN
  select pixel_width, pixel_height into v_width, v_height from images 
  where images_id = v_image_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_height := 720;
    v_width := 520;
  END;

-- the first part of the href for the image is stored in the keyword table and put into v_url_1 for use later
  BEGIN
  select keyword_value into v_url_1 from qcis_keywords 
  where keyword_type = 'Control' and keyword_code = 'URL_PATH';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_url_1 := 'http://xxx.xxx.com:8000/apex40/f?p=';
  END;  
  
-- construct the first three lines of the div tag  
  v_html := '<div style="text-align:center; width:'||v_width||'px; margin-left:auto; margin-right:auto;">';
  v_html := v_html || chr(10) || '<img id="ImageMap" src="download_image?p_id='||v_image_id||'" usemap="#ImageMap" border="0" width="'||v_width||'" height="'||v_height||'" alt="" />';
  v_html := v_html || chr(10) || '<map id="_ImageMap" name="ImageMap">';

-- subtract the ending inset amounts from the image dimensions
  v_width := v_width - v_wend;
  v_height := v_height - v_hend;

-- calculate the increment for each cell and subtract the starting inset amounts
  v_winc := floor((v_width - v_wstart) / v_wmax);
  v_hinc := floor((v_height - v_hstart) / v_hmax);
  
-- there are two main loops, one for the columns, one for the rows
-- one loop is inside the other, which helps to build the cells in logical order and helps with the naming of the cells  
  loop

-- if this is the first row (count = 0) and they have inset values we need to adust the first x y values to something other than zero  
if ((v_wstart != 0) and (v_wcount = 0)) then v_w1 := v_wstart; end if;
if ((v_hstart != 0) and (v_hcount = 0)) then v_h1 := v_hstart; end if;
if ((v_wstart != 0) and (v_wcount = 0)) then v_w2 := v_wstart; end if;
if ((v_hstart != 0) and (v_hcount = 0)) then v_h2 := v_hstart; end if;
  
 v_wcount := v_wcount + 1;
 v_w2 := v_w2 + v_winc;
-- checking to see if this is the last row and whether they want the grid to go to the end or have all cells the same size which may leave whitespace
         if (v_wcount = v_wmax) and (v_whitespace = 'Y') then v_w2 := v_width - 2; end if;
  v_cell_row := 0;
  v_cell_col := v_cell_col +1; 
         loop
         v_hcount := v_hcount + 1;            
         v_h2 := v_h2 + v_hinc;
-- checking to see if this is the last row and whether they want the grid to go to the end or have all cells the same size which may leave whitespace
                if (v_hcount = v_hmax) and (v_whitespace = 'Y') then v_h2 := v_height - 2; end if;
         v_cell_row := v_cell_row + 1;
         -- put it all together and construct the line for the area shape tag
         v_html := v_html || chr(10) || '<area shape="rect" coords="'
         ||v_w1||','||v_h1||','||v_w2||','||v_h2||
         '" href="'||v_url_1|| '" alt="'||v_cell_col||'-'||v_cell_row||'" title="'||v_cell_col||'-'||v_cell_row||'"    />'; 
         v_cell_title := v_cell_col||'-'||v_cell_row; 
            insert into DRAFT_CELLS (DRAFT_ID, CELL_TITLE) values(v_id, v_cell_title);           
         v_h1 := v_h1 + v_hinc; 
            exit when v_hcount = v_hmax;
        end loop;
  v_hcount := 0;
  v_h1 := 0;
  v_h2 := 0;
  v_w1 := v_w1 + v_winc; 
     exit when v_wcount = v_wmax;      
  end loop;
    
  v_html := v_html || chr(10) || '</div>'; 
  
update IM_TEMPLATE_DRAFT set imagemap_html = v_html
where header_id = v_id;   
  
END create_grid;

Edited by: cloaked on Nov 7, 2011 4:45 AM