Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Increase Varray size
Increase Varray size [message #486751] Mon, 20 December 2010 01:13 Go to next message
kalyugwarriors
Messages: 1
Registered: December 2010
Location: India
Junior Member
declare
 sm number:=0;
 temp_str varchar2(4000);
 pos pls_integer;

  type check_box_array is varray(200) of varchar2(40);

  check_arr check_box_array := check_box_array();
 cursor c1 is SELECT service_registration_id, (total_charge-discount) charge from patient_account where patient_id=:F112_X and status is null and date_to is not null and date_from is not null;


c1_record c1%rowtype;
begin

  pos:=instr(:F112_CHECKBOX,':');
 temp_str:=:F112_CHECKBOX;

 while pos > 0 loop

    check_arr.extend;
  check_arr(check_arr.count):=substr(temp_str,1,pos-1);


    temp_str:=substr(temp_str,pos+1);
  pos:=instr(temp_str,':');
 end loop;

 
 if temp_str is not null then

  check_arr.extend;
  check_arr(check_arr.count):=temp_str;
end if;

 
  if check_arr.last>0 then

    for i in 1..check_arr.last loop
        FOR c1_record IN c1 loop
        
        if c1_record.service_registration_id=check_arr(i) then
        
        
          sm:=sm+c1_record.charge;
    
       end if;  
       
end loop;
   end loop;
:P6_CHARGES_SUM:=sm;   
        
 end if;
   
end;


Here I am facing the error message "Subscript out of limit"

I want to increase the Varray size, Could you please help me, How could I increase Varray size in this code...


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Mon, 20 December 2010 03:58] by Moderator

Report message to a moderator

Re: Increase Varray size [message #486756 is a reply to message #486751] Mon, 20 December 2010 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, you have to use another type with larger array.
For instance:
type check_box_array is varray(1000) of varchar2(40);


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 20 December 2010 01:34]

Report message to a moderator

Re: Increase Varray size [message #486759 is a reply to message #486751] Mon, 20 December 2010 01:47 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I have not gone through whole code you provided, but on the fly, I can say use NESTED TABLE instead of VARRAY.

regards,
Delna
Previous Topic: unable to login after successful installation
Next Topic: Apex 4.0.2 Import from textarea to tables
Goto Forum:
  


Current Time: Thu Mar 28 12:11:23 CDT 2024